Classe de Conexão MSSQL

unit uConnection;

interface

uses
  FireDAC.Stan.Intf, FireDAC.Stan.Option,
  FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys,
  Data.DB, FireDAC.Comp.Client, FMX.Dialogs, uXMLData.Model,
  Xml.XMLIntf, Xml.XMLDoc,
  FireDAC.VCLUI.Wait, FireDAC.Comp.UI, SysUtils, Encryp,
  System.Classes, FireDAC.Comp.DataSet,
  FireDAC.Phys.MSSQL, uFuncoes;

type
  TConnection = class
  strict private
    class var FInstance: TConnection;
    constructor CreatePrivate;
  private
    TiCrypto: TTicrypto;
    FConexao: TFDConnection;
    FDGWCur: TFDGUIxWaitCursor;
    FDPMSSQLdriver: TFDPhysMSSQLDriverLink;
    Trans: TFDTransaction;
    protected
    function Criptografar(AValue: String): String;
    function Descriptografar(AValue: String): String;

  public
    constructor Create;
    class function GetInstance: TConnection;
    property Conexao: TFDConnection read FConexao write FConexao;
    function Execute(const ACmd: String; var Error: String): Boolean;
    function ExecuteQuery(const ACmd: String): TFDQuery;
    procedure BeginTrans;
    procedure Rollback;
    procedure Commit;
    function Conectar(var Error: String): Boolean;
    function LerTagXml(AValue: String): string;

  var
    Con: TConnection;
    ObjCrypto: TTiCrypto;

  end;

implementation

{ TConnection }

procedure TConnection.BeginTrans;
begin
  FConexao.StartTransaction;
end;

procedure TConnection.Commit;
begin
  FConexao.Commit;
end;

function TConnection.Conectar(var Error: String): Boolean;
begin
  Result := False;
  FConexao.Connected := False;
  try
    FConexao.Connected := True;
    Result := FConexao.Connected;
  except
    on E:Exception do
        begin
    Result := False;
     Error := 'Houve um problema durante a conexão ao MSSQL: ' + E.Message + sLineBreak
        + 'Ajuste a conexão na próxima tela...' ; //Tratar um redirecionamento para o form de ajuste
        end;
  end;
end;

constructor TConnection.Create;
begin
  raise Exception.Create('Object Singleton');
end;

constructor TConnection.CreatePrivate;
var
  Error: String;
  Teste: String;
begin
  inherited Create;
    FConexao := TFDConnection.Create(nil);
    FConexao.Params.Clear;
    Trans := TFDTransaction.Create(nil);
    FConexao.DriverName := 'MSSQL';
    FConexao.Transaction := Trans;
    FDPMSSQLdriver := TFDPhysMSSQLDriverLink.Create(nil);
    FConexao.Params.BeginUpdate;
    FConexao.Params.Add('DriverID=' + Trim('MSSQL'));
    FConexao.Params.Add('Server=' + Trim(LerTagXml('Servidor')+ '\' + Trim(LerTagXml('Instancia'))));
    FConexao.Params.Add('Database=' + Trim(LerTagXml('Base')));
    FConexao.Params.Add('User_name=' + Trim(LerTagXml('Usuario')));
    FConexao.Params.Add('Password=' + Trim(LerTagXml('Senha')));
    FConexao.Params.Add('Port='+ Trim(LerTagXml('Porta')));
    FConexao.Params.Add('ApplicationName=Architect');
    FConexao.Params.Add('Workstation='+ Trim(LerTagXml('Servidor')));
    FConexao.Params.Add('LoginTimeout=10');
    FConexao.Params.Add('MARS=Yes');
    FConexao.Params.Add('Encrypt=No');
    FConexao.Params.Add('MetaDefSchema=dbo');
    FConexao.Params.Add('MetaDefCatalog=' + Trim(LerTagXml('Base')));
    FConexao.Params.EndUpdate;
    if not (Conectar(Error)) then
      raise Exception.Create(Error);
end;

function TConnection.Criptografar(AValue: String): String;
var
  TextoCifrado: String;
begin
  ObjCrypto := TTiCrypto.Create(nil);
  ObjCrypto.Chave := 'suachavesecreta';
  Result := '';
  try
    begin
      ObjCrypto.Entrada := AValue;
      ObjCrypto.Acao := acCifrar;
      ObjCrypto.Execute;
      TextoCifrado := (ObjCrypto.Saida);
      Result := TextoCifrado;
    end;
  finally
    FreeAndNil(ObjCrypto);
  end;

end;

function TConnection.Descriptografar(AValue: String): String;
var
  TextoDecifrado: String;
begin
  ObjCrypto := TTiCrypto.Create(nil);
  ObjCrypto.Chave := 'suachavesecreta';
  Result := '';
  try
    begin
      ObjCrypto.Entrada := AValue;
      ObjCrypto.Acao := acDecifrar;
      ObjCrypto.Execute;
      TextoDecifrado := (ObjCrypto.Saida);
      Result := TextoDecifrado;
    end;
  finally
    FreeAndNil(ObjCrypto);
  end;

end;

function TConnection.Execute(const ACmd: String; var Error: String): Boolean;
begin
  Result := True;
  try
    FConexao.ExecSQL(ACmd);
  except
    on E: Exception do
    begin
      Error := E.Message;
      Result := False;
    end;
  end;
end;

{Caso precise pode-se executar uma query diretamente pela classe TConnection}
function TConnection.ExecuteQuery(const ACmd: String): TFDQuery;
begin
  Result := TFDQuery.Create(nil);
  try
    Result.Connection := FInstance.Conexao;
      Result.ExecSQL(ACmd);
  except
    Result := nil;
  end;
end;

class function TConnection.GetInstance: TConnection;
begin
  if not Assigned(FInstance) then
    begin
    FInstance := TConnection.CreatePrivate;
    end;
    Result := FInstance;
end;

function TConnection.LerTagXml(AValue: String): string;
var
  vXmlDoc: IXMLDocument;
  vNoXML:  IXMLNode;
  Resultado: String;
begin
  vXmlDoc := TXMLDocument.Create(nil);
  try
    vXmlDoc.LoadFromFile('C:\meusistema\conexao.xml');
    vXmlDoc.Active := True;
    vNoXML := vXmlDoc.DocumentElement.ChildNodes.FindNode('Parametro');
    Resultado := Descriptografar(vNoXML.ChildNodes[(AValue)].Text);// Descriptografar(ObjXMLNode.ChildNodes[(AValue)].Text);
    Result := Resultado;
  finally
    vXmlDoc := nil;
  end;
end;

procedure TConnection.Rollback;
begin
  FConexao.Rollback;
end;

end.