delphi 版 sqlHelper第二版

{
  odbc操作sqlite帮助类
  author：yywang
  date:2013-5-15
}
unit CommUtils;

interface

uses
  SysUtils, Windows, ActiveX, DB, ADODB, Generics.Collections;

type
  TParameterMap = class

  private
    thisKey: string;
    thisValue: Variant;
    thisDataType: TDataType;
    thisSize: Integer;
  public
    constructor Create(key: string; value: Variant); overload;
    constructor Create(key: string; value: Variant; dataType: TFieldType;
      size: Integer); overload;

    function GetKey: string;
    function GetValue: Variant;
    function GetDataType: TDataType;
    function GetSize: Integer;

    procedure SetKey(key: string);
    procedure SetValue(value: Variant);
    procedure SetDataType(dataType: TDataType);
    procedure SetSize(size: Integer);

  published
    property key: string read GetKey write SetKey;
    property value: Variant read GetValue write SetValue;
    property dataType: TDataType read GetDataType write SetDataType;
    property size: Integer read GetSize write SetSize;

  end;

  TSqlHelper = class

  public
    { 执行单个插入更新删除sql }
    function ExecSQL(sql: string): Integer; overload;

    { 执行单个插入更新删除sql 参数化 }
    function ExceSQL(sql: string; parms: TList<TParameterMap>): Integer;
      overload;

    { 执行批量的sql插入更新删除 }
    function ButchExecSQL(sqls: TList<string>): Integer;

    { 获取单个值得查询 }
    function GetSingle(sql: string): Variant; overload;

    { 获取单个值得查询 参数化 }
    function GetSingle(sql: string; parms: TList<TParameterMap>): Variant;
      overload;

    { 获取一个连接 }
    function GetConnection(): TADOConnection;

    { 获取一个查询query对象 }
    function GetQuery(sql: string; connection: TADOConnection): TADOQuery;
      overload;

    { 获取一个query对象，参数化 }
    function GetQuery(sql: string; parms: TList<TParameterMap>;
      connection: TADOConnection): TADOQuery; overload;

    { 关闭连接 }
    procedure CloseConnection(conn: TADOConnection);

    { 关闭Query }
    procedure CloseQuery(query: TADOQuery);

    { 关闭连接及Query }
    procedure Close(query: TADOQuery; connection: TADOConnection);

  private
    { 私有 }

  end;

implementation

const
  constr: string =
    'Provider=MSDASQL.1;Persist Security Info=False;Data Source=SQLite3 Datasource';

function TSqlHelper.ExecSQL(sql: string): Integer;
begin
  Result := ExceSQL(sql, nil);
end;

function TSqlHelper.ExceSQL(sql: string; parms: TList<TParameterMap>): Integer;

var
  command: TADOCommand;
  conn: TADOConnection;
  parm: TParameterMap;
begin
  try
    conn := GetConnection;
    command := TADOCommand.Create(nil);
    with command do
    begin
      connection := conn;
      CommandText := sql;
      if (parms <> nil) and (parms.Count > 0) then
      begin
        for parm in parms do
        begin
          Parameters.FindParam(parm.key).value := parm.value;
          if (parm.size <> -1) then
          begin
            Parameters.FindParam(parm.key).dataType := parm.dataType;
            Parameters.FindParam(parm.key).size := parm.size;
          end;
        end;
      end;
      Execute;
    end;
    Result := 1;
  finally
    command.Cancel;
    CloseConnection(conn);
  end;
end;

function TSqlHelper.ButchExecSQL(sqls: TList<string>): Integer;
var
  command: TADOCommand;
  sql: string;
  conn: TADOConnection;
begin
  try
    try
      conn := GetConnection;
      command := TADOCommand.Create(nil);
      conn.BeginTrans;
      with command do
      begin
        connection := conn;
        for sql in sqls do
        begin
          if sql <> '' then
          begin
            CommandText := sql;
            Execute;
          end;
        end;
      end;
      Result := 1;
      conn.CommitTrans;
    except
      Result := 0;
      conn.RollbackTrans;
    end;
  finally
    command.Cancel;
    CloseConnection(conn);
  end;
end;

function TSqlHelper.GetSingle(sql: string): Variant;
begin
  Result := GetSingle(sql, nil);
end;

function TSqlHelper.GetSingle(sql: string; parms: TList<TParameterMap>)
  : Variant;
var
  query: TADOQuery;
  conn: TADOConnection;
begin
  try
    conn := GetConnection;
    query := GetQuery(sql, parms, conn);
    if query.RecordCount < 0 then
      Result := '';
    query.First;
    Result := query.Fields.Fields[0].AsVariant;
  finally
    CloseQuery(query);
    CloseConnection(conn);
  end;
end;

function TSqlHelper.GetQuery(sql: string;
  connection: TADOConnection): TADOQuery;
begin
  Result := GetQuery(sql, nil, connection);
end;

function TSqlHelper.GetQuery(sql: string; parms: TList<TParameterMap>;
  connection: TADOConnection): TADOQuery;
var
  query: TADOQuery;
  parm: TParameterMap;
begin
  query := TADOQuery.Create(nil);
  query.connection := connection;
  query.sql.Add(sql);
  if (parms <> nil) and (parms.Count > 0) then
  begin
    for parm in parms do
    begin
      query.Parameters.FindParam(parm.key).value := parm.value;
      if (parm.size <> -1) then
      begin
        query.Parameters.FindParam(parm.key).dataType := parm.dataType;
        query.Parameters.FindParam(parm.key).size := parm.size;
      end;
    end;
  end;
  query.Open;
  Result := query;
end;

function TSqlHelper.GetConnection: TADOConnection;
var
  conn: TADOConnection;
begin
  conn := TADOConnection.Create(nil);
  conn.ConnectionString := constr;
  conn.Open();
  Result := conn;
end;

procedure TSqlHelper.CloseQuery(query: TADOQuery);
begin
  { if query.Active then }
  query.Close;
end;

procedure TSqlHelper.CloseConnection(conn: TADOConnection);
begin
  if conn.Connected then
    conn.Close;
end;

procedure TSqlHelper.Close(query: TADOQuery; connection: TADOConnection);
begin
  if query <> nil then
    CloseQuery(query);
  if connection <> nil then
    CloseConnection(connection);
end;

function TParameterMap.GetKey;
begin
  Result := thisKey;
end;

function TParameterMap.GetValue;
begin
  Result := thisValue;
end;

function TParameterMap.GetDataType;
begin
  Result := thisDataType;
end;

function TParameterMap.GetSize;
begin
  Result := thisSize;
end;

procedure TParameterMap.SetKey(key: string);
begin
  thisKey := key;
end;

procedure TParameterMap.SetValue(value: Variant);
begin
  thisValue := value;
end;

procedure TParameterMap.SetDataType(dataType: TFieldType);
begin
  thisDataType := dataType;
end;

procedure TParameterMap.SetSize(size: Integer);
begin
  thisSize := size;
end;

constructor TParameterMap.Create(key: string; value: Variant);
begin
  thisValue := value;
  thisKey := key;
  thisDataType := ftUnknown;
  thisSize := -1;
end;

constructor TParameterMap.Create(key: string; value: Variant;
  dataType: TFieldType; size: Integer);
begin
  thisKey := key;
  thisValue := value;
  thisDataType := dataType;
  thisSize := size;
end;

initialization

CoInitialize(nil);

finalization

CoUnInitialize;

end.