delphi 参数化sql-白红宇

delphi 参数化sql

阅读量：5125 次

发布时间：2019-06-13

本文共 11665 字，大约阅读时间需要 38 分钟。

曾经查过资料，后来忘了，现在做一下记录。

---------

在 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
     
      ) : TList
      
       ; 29     // 广泛含义的更新 30     function Update(Const ASql : String; const AParams : TDictionary
       
        ) : Boolean; overload; 31   public 32     constructor Create(); 33     destructor  Destroy(); override; 34  35     function QueryAll() : TList
        
         ; 36     function QueryByInt(const AInt : Integer) : TList
         
          ; 37     function QueryByTxt(const ATxt : String ) : TList
          
           ; 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
           
            ) : TList
            
             ; 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
             
              .Create();107 while not LDataSet.Eof do begin108 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 finally116 FreeAndNil(LDataSet);117 end;118 end;119 120 function TDBAccesser.Update(Const ASql : String; const AParams : TDictionary
              
               ) : Boolean;121 var122 LCmd : TADOCommand;123 LKey : String;124 LRowsAffected : Integer;125 begin126 Result := False;127 LCmd := nil;128 try129 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 begin136 for LKey in AParams.Keys do begin137 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 begin147 Result := True;148 end149 else begin150 Result := False;151 end;152 finally153 FreeAndNil(LCmd);154 end;155 end;156 157 158 function TDBAccesser.QueryAll() : TList
               
                ;159 const160 LSqlFormat : String = 'SELECT %s, %s FROM %s';161 var162 LSql : String;163 begin164 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
                
                 ;170 const171 LSqlFormat : String = 'SELECT %s, %s FROM %s WHERE %s = :$Int ';172 var173 LSql : String;174 LParams : TDictionary
                 
                  ;175 begin176 LParams := TDictionary
                  
                   .Create();177 try178 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 finally185 FreeAndNil(LParams);186 end;187 end;188 189 function TDBAccesser.QueryByTxt(const ATxt: String) : TList
                   
                    ;190 const191 LSqlFormat : String = 'SELECT %s, %s FROM %s WHERE %s = :$Txt ';192 var193 LSql : String;194 LParams : TDictionary
                    
                     ;195 begin196 LParams := TDictionary
                     
                      .Create();197 try198 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 finally205 FreeAndNil(LParams);206 end;207 end;208 209 function TDBAccesser.InsertOne(const AEntity: TEntity) : Boolean;210 const211 LSqlFormat : String = 'INSERT INTO %s (%s, %s) '212 + 'VALUES (:$Txt, :$Int) ';213 var214 LSql : String;215 LParams : TDictionary
                      
                       ;216 begin217 LParams := TDictionary
                       
                        .Create();218 try219 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 finally226 FreeAndNil(LParams);227 end;228 end;229 230 function TDBAccesser.Delete(const AEntity: TEntity) : Boolean;231 const232 LSqlFormat : String = 'DELETE FROM %s '233 + 'WHERE %s = :$Txt AND %s = :$Int ';234 var235 LSql : String;236 LParams : TDictionary
                        
                         ;237 begin238 LParams := TDictionary
                         
                          .Create();239 try240 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 finally247 FreeAndNil(LParams);248 end;249 end;250 251 function TDBAccesser.Update(const AOldValue: TEntity; const ANewValue: TEntity) : Boolean;252 const253 LSqlFormat : String = 'UPDATE %s '254 + 'SET %s = :$TxtVal, %s = :$IntVal '255 + 'WHERE %s = :$OldTxtVal AND %s = :$OldIntVal ';256 var257 LSql : String;258 LParams : TDictionary
                          
                           ;259 begin260 LParams := TDictionary
                           
                            .Create();261 try262 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 finally274 FreeAndNil(LParams);275 end;276 end;277 278 279 initialization280 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 finalization291 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
     
      ; 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 begin102         WriteLn('  ', FormatEntity(EntityCommon));103       end;104       WriteLn('----------------');105 106       WriteLn('--- THE END ---');107     finally108       FreeAndNil(DBAccesser);109     end;110   except111     on E: Exception do112       Writeln(E.ClassName, ': ', E.Message);113   end;114   ReadLn;115 end.

--------- THE END ---------

转载于:https://www.cnblogs.com/shadow-abyss/p/11126900.html

你可能感兴趣的文章

算法和数据结构（三）

查看>>

Ubuntu下的eclipse安装subclipse遇到没有javahl的问题...(2天解决了)

查看>>

alter database databasename set single_user with rollback IMMEDIATE 不成功问题

查看>>

WCF揭秘——使用AJAX+WCF服务进行页面开发

查看>>

【题解】青蛙的约会