delphi 版 sqlHelper第一版

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

interface

uses
  SysUtils, Windows, ActiveX, ADODB;

type
  TParameterMap = class

  private
    thisKey: string;
    thisValue: Variant;
  public
    constructor Create(key: string; value: Variant); overload;

    function GetKey: string;
    function GetValue: Variant;

    procedure SetKey(key: string);
    procedure SetValue(value: Variant);

  published

    property key: string read GetKey write SetKey;
    property value: Variant read GetValue write SetValue;

  end;

  TSqlHelper = class

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

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

    { 执行批量的sqlc插入更新删除 }
    function ButchExecSQL(sqls: array of string): Integer;

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

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

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

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

    { 获取一个query对象，参数话 }
    function GetQuery(sql: string; parms: array of TParameterMap;
      connection: TADOConnection): TADOQuery; overload;

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

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

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

  private
    { nidf }

  end;

implementation

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

function TSqlHelper.ExecSQL(sql: string): Integer;
var
  ars: array of TParameterMap;
begin
  Result := ExceSQL(sql, ars);
end;

function TSqlHelper.ExceSQL(sql: string; parms: array of 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 (Length(parms) > 0) then
      begin
        for parm in parms do
        begin
          Parameters.FindParam(parm.key).value := parm.value;
        end;
      end;
      Execute;
    end;
    Result := 1;
  finally
    command.Cancel;
    CloseConnection(conn);
  end;
end;

function TSqlHelper.ButchExecSQL(sqls: array of 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;
var
  ars: array of TParameterMap;
begin
  Result := GetSingle(sql, ars);
end;

function TSqlHelper.GetSingle(sql: string; parms: array of 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;
var
  ars: array of TParameterMap;
begin
  Result := GetQuery(sql, ars, connection);
end;

function TSqlHelper.GetQuery(sql: string; parms: array of TParameterMap;
  connection: TADOConnection): TADOQuery;
var
  query: TADOQuery;
  parm: TParameterMap;
begin
  query := TADOQuery.Create(nil);
  query.connection := connection;
  query.sql.Add(sql);
  if (Length(parms) > 0) then
  begin
    for parm in parms do
    begin
      query.Parameters.FindParam(parm.key).value := parm.value;
    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;

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

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

constructor TParameterMap.Create(key: string; value: Variant);
begin
  thisValue := value;
  thisKey := key;
end;

initialization

CoInitialize(nil);

finalization

CoUnInitialize;

end.