delphi 参数化sql

鸣翠柳 發表於 2019-7-3 15:46:00

曾经查过资料，后来忘了，现在做一下记录。
---------
在 sql 语句里占位使用一个冒号和一个用来表示这个位置的符号, 例如：SELECT * FROM aTableName WHERE aCol =  :ColVal
<div class="cnblogs_code">
<pre>SELECT * FROM aTableName WHERE aCol =:ColVal</pre>
</div>
对占位了的地方赋值这里使用的是 paremeters 属性的 paramByName 作的, 例如：
<div class="cnblogs_code">
<pre>LDataSet.Parameters.ParamByName('ColVal').Value := 'SomeValue';</pre>
</div>
---------
最近在 Delphi 7 中通过参数化的方式存汉字字符串，出现只存入了一部分的情况，搜索资料并测试之后，发现设置 DataType = ftWideString 可以解决。
示例：
<div class="cnblogs_code">
<pre>LCommand.Parameters.ParamByName('AKeyName').DataType := ftWideString;
LCommand.Parameters.ParamByName('AKeyName').Value := '我没有，我不是，别瞎说';</pre>
</div>
DataType 的类型：DB.pas/TFieldType
--date=2019-08-16
 
---------
为了使贴出来的代码具有完成性和尽量的简单性, 把以前的代码改写了一下, 现在的代码是控制台程序,
在程序体中对数据库访问代码作了简单的调用.
---------
今天重新看了一遍代码，发现一个问题，就是程序入口里的变量 QueryResult 所使用过的对象都没有释放！！！
--date=2019-09-11
---------
数据访问单元的代码：
<div class="cnblogs_code">
<pre>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 destructorDestroy(); 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 ID=%s;'
 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;
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.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;
138 end;
139 end;
140
141 // 也可以直接在 EmptyParam 的位置直接写格式的内容，
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, );
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, );
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, );
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.</pre>
</div>
 
控制台程序入口代码，对上面的单元进行简单的调用：
<div class="cnblogs_code">
<pre>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 ', );
 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.</pre>
</div>
 
--------- THE END --------- 
来源：https://www.cnblogs.com/shadow-abyss/p/11126900.html

頁: [1]

琼殿技术论坛's Archiver

delphi 参数化sql