delphi 参数化sql
曾经查过资料，后来忘了，现在做一下记录。
---------
在 sql 语句里占位使用 一个冒号和一个用来表示这个位置的符号, 例如：SELECT * FROM aTableName WHERE aCol = :ColVal
SELECT * FROM aTableName WHERE aCol = :ColVal
对占位了的地方赋值这里使用的是 paremeters 属性的 paramByName 作的, 例如：
LDataSet.Parameters.ParamByName('ColVal').Value := 'SomeValue';---------
最近在 Delphi 7 中通过参数化的方式存汉字字符串，出现只存入了一部分的情况，搜索资料并测试之后，发现设置 DataType = ftWideString 可以解决。
示例：
LCommand.Parameters.ParamByName('AKeyName').DataType := ftWideString;
LCommand.Parameters.ParamByName('AKeyName').Value := '我没有，我不是，别瞎说';DataType 的类型：DB.pas/TFieldType
--date=2019-08-16
---------
为了使贴出来的代码具有完成性和尽量的简单性, 把以前的代码改写了一下, 现在的代码是控制台程序,
在程序体中对数据库访问代码作了简单的调用.
---------
今天重新看了一遍代码，发现一个问题，就是 程序入口里的变量 QueryResult 所使用过的对象都没有释放！！！
--date=2019-09-11
---------
数据访问单元的代码：
1 unit uDBAccesser;
2
3 interface
4
5 uses
6 System.Generics.Collections, Data.Win.ADODB;
7
8
9 type TTAbleInfo = record
10 DataSource : String;
11 DbName : String;
12 Username : String;
13 Password : String;
14 Name : String;
15 IntCol : String;
16 TxtCol : String;
17 end;
18
19 type TEntity = record
20 SomeInt : Integer;
21 SomeTxt : String;
22 end;
23
24 type TDBAccesser = class
25 private
26 FConn : TADOConnection;
27
28 function Query( const ASql : String; const AParams : TDictionary<String, Variant>) : TList<TEntity>;
29 // 广泛含义的更新
30 function Update(Const ASql : String; const AParams : TDictionary<String, Variant>) : Boolean; overload;
31 public
32 constructor Create();
33 destructor Destroy(); override;
34
35 function QueryAll() : TList<TEntity>;
36 function QueryByInt(const AInt : Integer) : TList<TEntity>;
37 function QueryByTxt(const ATxt : String ) : TList<TEntity>;
38 function InsertOne(const AEntity : TEntity) : Boolean;
39 function Delete(const AEntity : TEntity) : Boolean;
40 // 狭义的更新
41 function Update(const AOldValue : TEntity; const ANewValue : TEntity) : Boolean; overload;
42 end;
43
44
45 var
46 TableInfo : TTableInfo;
47
48 implementation
49
50 uses
51 System.SysUtils, System.Variants, Winapi.ActiveX;
52
53
54 constructor TDBAccesser.Create();
55 const // MS SQL
56 LConnStrFormat : String = 'provider=SQLOLEDB.1;password=%s;User
57 + 'Initial CataLog=%s;Data source=%s;';
58 var
59 LConnStr : String;
60 begin
61 try
62 LConnStr := Format(LConnStrFormat, [TableInfo.Password, TableInfo.Username,
63 TableInfo.DbName, TableInfo.DataSource]);
64
65 Self.FConn := TADOConnection.Create(nil);
66 Self.FConn.ConnectionString := LConnStr;
67 Self.FConn.LoginPrompt := False;
68 Self.FConn.Connected := True;
69 except
70 on Err : Exception do begin
71 FreeAndNil(Self.FConn);
72 WriteLn('Error on create DBAccesser: ' + Err.Message);
73 end;
74 end;
75 end;
76
77 destructor TDBAccesser.Destroy;
78 begin
79 FreeAndNil(Self.FConn);
80 end;
81
82
83 function TDBAccesser.Query(const ASql: string; const AParams: TDictionary<System.string,System.Variant>) : TList<TEntity>;
84 var
85 LDataSet : TADODataSet;
86 LRow : TEntity;
87 LKey : String;
88 begin
89 LDataSet := nil;
90 try
91 LDataSet := TADODataSet.Create(nil);
92 LDataSet.Connection := Self.FConn;
93 LDataSet.CommandText := ASql;
94
95 // 我印象有篇文章说这句必须要有, 但不写也没发现问题
96 LDataSet.Parameters.ParseSQL(LDataSet.CommandText, True);
97 if (AParams <> nil) then begin
98 for LKey in AParams.Keys do begin
99 LDataSet.Parameters.ParamByName(LKey).Value := AParams.Items[LKey];
100 end;
101 end;
102
103 LDataSet.Open;
104 LDataSet.First;
105
106 Result := TList<TEntity>.Create();
107 while not LDataSet.Eof do begin
108 LRow.SomeInt := LDataSet.FieldByName(TableInfo.IntCol).AsInteger; // 也可以这种格式 LDataSet.Fields[0].AsBoolean;
109 LRow.SomeTxt := LDataSet.FieldByName(TableInfo.TxtCol).AsString;
110
111 Result.Add(LRow);
112
113 LDataSet.Next;
114 end;
115 finally
116 FreeAndNil(LDataSet);
117 end;
118 end;
119
120 function TDBAccesser.Update(Const ASql : String; const AParams : TDictionary<String, Variant>) : Boolean;
121 var
122 LCmd : TADOCommand;
123 LKey : String;
124 LRowsAffected : Integer;
125 begin
126 Result := False;
127 LCmd := nil;
128 try
129 LCmd := TADOCommand.Create(nil);
130 LCmd.Connection := Self.FConn;
131 LCmd.CommandText := ASql;
132
133 LCmd.Parameters.ParseSQL(LCmd.CommandText, True);
134
135 if (AParams <> nil) then begin
136 for LKey in AParams.Keys do begin
137 LCmd.Parameters.ParamByName(LKey).Value := AParams.Items[LKey];
138 end;
139 end;
140
141 // 也可以直接在 EmptyParam 的位置直接写 [Param1, Param2, ...] 格式的内容，
142 // 而不使用上面的 Parameters，两者不能同时使用，
143 // 但具体什么情况我也忘了，相应代码也找不到了，要想知道真实情况，还得再动手去摸索
144 LCmd.Execute(LRowsAffected, EmptyParam);
145
146 if (LRowsAffected > 0) then begin
147 Result := True;
148 end
149 else begin
150 Result := False;
151 end;
152 finally
153 FreeAndNil(LCmd);
154 end;
155 end;
156
157
158 function TDBAccesser.QueryAll() : TList<TEntity>;
159 const
160 LSqlFormat : String = 'SELECT %s, %s FROM %s';
161 var
162 LSql : String;
163 begin
164 LSql := Format(LSqlFormat, [TableInfo.TxtCol, TableInfo.IntCol, TableInfo.Name]);
165
166 Result := Self.Query(LSql, nil);
167 end;
168
169 function TDBAccesser.QueryByInt(const AInt: Integer) : TList<TEntity>;
170 const
171 LSqlFormat : String = 'SELECT %s, %s FROM %s WHERE %s = :$Int ';
172 var
173 LSql : String;
174 LParams : TDictionary<String, Variant>;
175 begin
176 LParams := TDictionary<String, Variant>.Create();
177 try
178 LSql := Format(LSqlFormat, [TableInfo.TxtCol, TableInfo.IntCol,
179 TableInfo.Name, TableInfo.IntCol]);
180
181 LParams.Add('$Int', AInt);
182
183 Result := Self.Query(LSql, LParams);
184 finally
185 FreeAndNil(LParams);
186 end;
187 end;
188
189 function TDBAccesser.QueryByTxt(const ATxt: String) : TList<TEntity>;
190 const
191 LSqlFormat : String = 'SELECT %s, %s FROM %s WHERE %s = :$Txt ';
192 var
193 LSql : String;
194 LParams : TDictionary<String, Variant>;
195 begin
196 LParams := TDictionary<String, Variant>.Create();
197 try
198 LSql := Format(LSqlFormat, [TableInfo.TxtCol, TableInfo.IntCol,
199 TableInfo.Name, TableInfo.TxtCol]);
200
201 LParams.Add('$Txt', ATxt);
202
203 Result := Self.Query(LSql, LParams);
204 finally
205 FreeAndNil(LParams);
206 end;
207 end;
208
209 function TDBAccesser.InsertOne(const AEntity: TEntity) : Boolean;
210 const
211 LSqlFormat : String = 'INSERT INTO %s (%s, %s) '
212 + 'VALUES (:$Txt, :$Int) ';
213 var
214 LSql : String;
215 LParams : TDictionary<String, Variant>;
216 begin
217 LParams := TDictionary<String, Variant>.Create();
218 try
219 LSql := Format(LSqlFormat, [TableInfo.Name, TableInfo.TxtCol, TableInfo.IntCol]);
220
221 LParams.Add('$Txt', AEntity.SomeTxt);
222 LParams.Add('$Int', AEntity.SomeInt);
223
224 Result := Self.Update(LSql, LParams);
225 finally
226 FreeAndNil(LParams);
227 end;
228 end;
229
230 function TDBAccesser.Delete(const AEntity: TEntity) : Boolean;
231 const
232 LSqlFormat : String = 'DELETE FROM %s '
233 + 'WHERE %s = :$Txt AND %s = :$Int ';
234 var
235 LSql : String;
236 LParams : TDictionary<String, Variant>;
237 begin
238 LParams := TDictionary<String, Variant>.Create();
239 try
240 LSql := Format(LSqlFormat, [TableInfo.Name, TableInfo.TxtCol, TableInfo.IntCol]);
241
242 LParams.Add('$Txt', AEntity.SomeTxt);
243 LParams.Add('$Int', AEntity.SomeInt);
244
245 Result := Self.Update(LSql, LParams);
246 finally
247 FreeAndNil(LParams);
248 end;
249 end;
250
251 function TDBAccesser.Update(const AOldValue: TEntity; const ANewValue: TEntity) : Boolean;
252 const
253 LSqlFormat : String = 'UPDATE %s '
254 + 'SET %s = :$TxtVal, %s = :$IntVal '
255 + 'WHERE %s = :$OldTxtVal AND %s = :$OldIntVal ';
256 var
257 LSql : String;
258 LParams : TDictionary<String, Variant>;
259 begin
260 LParams := TDictionary<String, Variant>.Create();
261 try
262 LSql := Format(LSqlFormat, [TableInfo.Name,
263 TableInfo.TxtCol, TableInfo.IntCol,
264 TableInfo.TxtCol, TableInfo.IntCol]);
265
266 LParams.Add('$TxtVal', ANewValue.SomeTxt);
267 LParams.Add('$IntVal', ANewValue.SomeInt);
268
269 LParams.Add('$OldTxtVal', AOldValue.SomeTxt);
270 LParams.Add('$OldIntVal', AOldValue.SomeInt);
271
272 Result := Self.Update(LSql, LParams);
273 finally
274 FreeAndNil(LParams);
275 end;
276 end;
277
278
279 initialization
280 TableInfo.DataSource := '.';
281 TableInfo.DbName := 'simpleTestByX';
282 TableInfo.Username := 'sa';
283 TableInfo.Password := '123456';
284 TableInfo.Name := 'tab_simple_test';
285 TableInfo.IntCol := 'some_int';
286 TableInfo.TxtCol := 'some_txt';
287
288 CoInitialize(nil);
289
290 finalization
291 CoUninitialize();
292
293
294 end.控制台程序入口代码，对上面的单元进行简单的调用：
1 program ProjectParameterizedSql;
2
3 {$APPTYPE CONSOLE}
4
5 {$R *.res}
6
7 uses
8 System.SysUtils,
9 System.Generics.Collections,
10 uDBAccesser in 'uDBAccesser.pas';
11
12
13 function FormatEntity(AEntity : TEntity) : String;
14 begin
15 Result := Format(' SomeInt = %d, SomeTxt = %s ', [AEntity.SomeInt, AEntity.SomeTxt]);
16 end;
17
18
19 var
20 DBAccesser : TDBAccesser;
21 EntityQuery : TEntity;
22 EntityInsert : TEntity;
23 EntityDelete : TEntity;
24 EntityUpdateNew : TEntity;
25 EntityUpdateOld : TEntity;
26 EntityCommon : TEntity;
27 QueryResult : TList<TEntity>;
28 begin
29 try
30 DBAccesser := TDBAccesser.Create();
31 try
32 WriteLn('1) insert one: ');
33
34 EntityInsert.SomeInt := 1;
35 EntityInsert.SomeTxt := 'Hello';
36 WriteLn(' entity = ', FormatEntity(EntityInsert));
37
38 WriteLn(' insert success? ', DBAccesser.InsertOne(EntityInsert));
39 WriteLn('----------------');
40
41 WriteLn('2) query all: ');
42
43 QueryResult := DBAccesser.QueryAll();
44
45 WriteLn(' total = ', QueryResult.Count);
46 for EntityCommon in QueryResult do begin
47 WriteLn(' ', FormatEntity(EntityCommon));
48 end;
49 WriteLn('----------------');
50
51 WriteLn('3) update: ');
52
53 EntityUpdateOld := EntityInsert;
54 EntityUpdateNew.SomeInt := 2;
55 EntityUpdateNew.SomeTxt := 'World';
56
57 WriteLn(' old : ', FormatEntity(EntityUpdateOld));
58 WriteLn(' new : ', FormatEntity(EntityUpdateNew));
59
60 WriteLn(' update success? ', DBAccesser.Update(EntityUpdateOld, EntityUpdateNew));
61 WriteLn('----------------');
62
63 WriteLn('4) query by int:');
64
65 EntityQuery := EntityUpdateNew;
66 WriteLn(' int = ', EntityQuery.SomeInt);
67
68 QueryResult := DBAccesser.QueryByInt(EntityQuery.SomeInt);
69
70 WriteLn(' total : ', QueryResult.Count);
71 for EntityCommon in QueryResult do begin
72 WriteLn(' ', FormatEntity(EntityCommon));
73 end;
74 WriteLn('----------------');
75
76 WriteLn('5) query by txt:');
77
78 EntityQuery := EntityUpdateNew;
79 WriteLn(' txt = ', EntityQuery.SomeTxt);
80
81 QueryResult := DBAccesser.QueryByTxt(EntityQuery.SomeTxt);
82
83 WriteLn(' total : ', QueryResult.Count);
84 for EntityCommon in QueryResult do begin
85 WriteLn(' ', FormatEntity(EntityCommon));
86 end;
87 WriteLn('----------------');
88
89 WriteLn('6) delete:');
90
91 EntityDelete := EntityUpdateNew;
92 WriteLn(' entity = ', FormatEntity(EntityDelete));
93
94 WriteLn(' delete success? ', DBAccesser.Delete(EntityDelete));
95 WriteLn('----------------');
96
97 WriteLn('7) query all: ');
98
99 QueryResult := DBAccesser.QueryAll();
100 WriteLn(' total : ', QueryResult.Count);
101 for EntityCommon in QueryResult do begin
102 WriteLn(' ', FormatEntity(EntityCommon));
103 end;
104 WriteLn('----------------');
105
106 WriteLn('--- THE END ---');
107 finally
108 FreeAndNil(DBAccesser);
109 end;
110 except
111 on E: Exception do
112 Writeln(E.ClassName, ': ', E.Message);
113 end;
114 ReadLn;
115 end.--------- THE END ---------