1 {
  2   odbc操作sqlite帮助类
  3   author：yywang
  4   date:2013-5-15
  5 }
  6 unit CommUtils;
  7 
  8 interface
  9 
 10 uses
 11   SysUtils, Windows, ActiveX, ADODB;
 12 
 13 type
 14   TParameterMap = class
 15 
 16   private
 17     thisKey: string;
 18     thisValue: Variant;
 19   public
 20     constructor Create(key: string; value: Variant); overload;
 21 
 22     function GetKey: string;
 23     function GetValue: Variant;
 24 
 25     procedure SetKey(key: string);
 26     procedure SetValue(value: Variant);
 27 
 28   published
 29 
 30     property key: string read GetKey write SetKey;
 31     property value: Variant read GetValue write SetValue;
 32 
 33   end;
 34 
 35   TSqlHelper = class
 36 
 37   public
 38     { 执行单个插入更新删除sql }
 39     function ExecSQL(sql: string): Integer; overload;
 40 
 41     { 执行单个插入更新删除sql 参数化 }
 42     function ExceSQL(sql: string; parms: array of TParameterMap): Integer;
 43       overload;
 44 
 45     { 执行批量的sqlc插入更新删除 }
 46     function ButchExecSQL(sqls: array of string): Integer;
 47 
 48     { 获取单个值得查询 }
 49     function GetSingle(sql: string): Variant; overload;
 50 
 51     { 获取单个值得查询 参数化 }
 52     function GetSingle(sql: string; parms: array of TParameterMap): Variant;
 53       overload;
 54 
 55     { 获取一个连接 }
 56     function GetConnection(): TADOConnection;
 57 
 58     { 获取一个查询query对象 }
 59     function GetQuery(sql: string; connection: TADOConnection): TADOQuery;
 60       overload;
 61 
 62     { 获取一个query对象，参数话 }
 63     function GetQuery(sql: string; parms: array of TParameterMap;
 64       connection: TADOConnection): TADOQuery; overload;
 65 
 66     { 关闭连接 }
 67     procedure CloseConnection(conn: TADOConnection);
 68 
 69     { 关闭Query }
 70     procedure CloseQuery(query: TADOQuery);
 71 
 72     { 关闭连接及Query }
 73     procedure Close(query: TADOQuery; connection: TADOConnection);
 74 
 75   private
 76     { nidf }
 77 
 78   end;
 79 
 80 implementation
 81 
 82 const
 83   constr: string =
 84     'Provider=MSDASQL.1;Persist Security Info=False;Data Source=SQLite3 Datasource';
 85 
 86 function TSqlHelper.ExecSQL(sql: string): Integer;
 87 var
 88   ars: array of TParameterMap;
 89 begin
 90   Result := ExceSQL(sql, ars);
 91 end;
 92 
 93 function TSqlHelper.ExceSQL(sql: string; parms: array of TParameterMap)
 94   : Integer;
 95 
 96 var
 97   command: TADOCommand;
 98   conn: TADOConnection;
 99   parm: TParameterMap;
100 begin
101   try
102     conn := GetConnection;
103     command := TADOCommand.Create(nil);
104     with command do
105     begin
106       connection := conn;
107       CommandText := sql;
108       if (Length(parms) > 0) then
109       begin
110         for parm in parms do
111         begin
112           Parameters.FindParam(parm.key).value := parm.value;
113         end;
114       end;
115       Execute;
116     end;
117     Result := 1;
118   finally
119     command.Cancel;
120     CloseConnection(conn);
121   end;
122 end;
123 
124 function TSqlHelper.ButchExecSQL(sqls: array of string): Integer;
125 var
126   command: TADOCommand;
127   sql: string;
128   conn: TADOConnection;
129 begin
130   try
131     try
132       conn := GetConnection;
133       command := TADOCommand.Create(nil);
134       conn.BeginTrans;
135       with command do
136       begin
137         connection := conn;
138         for sql in sqls do
139         begin
140           if sql <> '' then
141           begin
142             CommandText := sql;
143             Execute;
144           end;
145         end;
146       end;
147       Result := 1;
148       conn.CommitTrans;
149     except
150       Result := 0;
151       conn.RollbackTrans;
152     end;
153   finally
154     command.Cancel;
155     CloseConnection(conn);
156   end;
157 end;
158 
159 function TSqlHelper.GetSingle(sql: string): Variant;
160 var
161   ars: array of TParameterMap;
162 begin
163   Result := GetSingle(sql, ars);
164 end;
165 
166 function TSqlHelper.GetSingle(sql: string; parms: array of TParameterMap)
167   : Variant;
168 var
169   query: TADOQuery;
170   conn: TADOConnection;
171 begin
172   try
173     conn := GetConnection;
174     query := GetQuery(sql, parms, conn);
175     if query.RecordCount < 0 then
176       Result := '';
177     query.First;
178     Result := query.Fields.Fields[0].AsVariant;
179   finally
180     CloseQuery(query);
181     CloseConnection(conn);
182   end;
183 end;
184 
185 function TSqlHelper.GetQuery(sql: string;
186   connection: TADOConnection): TADOQuery;
187 var
188   ars: array of TParameterMap;
189 begin
190   Result := GetQuery(sql, ars, connection);
191 end;
192 
193 function TSqlHelper.GetQuery(sql: string; parms: array of TParameterMap;
194   connection: TADOConnection): TADOQuery;
195 var
196   query: TADOQuery;
197   parm: TParameterMap;
198 begin
199   query := TADOQuery.Create(nil);
200   query.connection := connection;
201   query.sql.Add(sql);
202   if (Length(parms) > 0) then
203   begin
204     for parm in parms do
205     begin
206       query.Parameters.FindParam(parm.key).value := parm.value;
207     end;
208   end;
209   query.Open;
210   Result := query;
211 end;
212 
213 function TSqlHelper.GetConnection: TADOConnection;
214 var
215   conn: TADOConnection;
216 begin
217   conn := TADOConnection.Create(nil);
218   conn.ConnectionString := constr;
219   conn.Open();
220   Result := conn;
221 end;
222 
223 procedure TSqlHelper.CloseQuery(query: TADOQuery);
224 begin
225   { if query.Active then }
226   query.Close;
227 end;
228 
229 procedure TSqlHelper.CloseConnection(conn: TADOConnection);
230 begin
231   if conn.Connected then
232     conn.Close;
233 end;
234 
235 procedure TSqlHelper.Close(query: TADOQuery; connection: TADOConnection);
236 begin
237   if query <> nil then
238     CloseQuery(query);
239   if connection <> nil then
240     CloseConnection(connection);
241 end;
242 
243 function TParameterMap.GetKey;
244 begin
245   Result := thisKey;
246 end;
247 
248 function TParameterMap.GetValue;
249 begin
250   Result := thisValue;
251 end;
252 
253 procedure TParameterMap.SetKey(key: string);
254 begin
255   thisKey := key;
256 end;
257 
258 procedure TParameterMap.SetValue(value: Variant);
259 begin
260   thisValue := value;
261 end;
262 
263 constructor TParameterMap.Create(key: string; value: Variant);
264 begin
265   thisValue := value;
266   thisKey := key;
267 end;
268 
269 initialization
270 
271 CoInitialize(nil);
272 
273 finalization
274 
275 CoUnInitialize;
276 
277 end.