首页 / 软件开发 / Delphi / Delphi FireDAC 下的 Sqlite（十一） 批量提交 SQL 命令的测试

Delphi FireDAC 下的 Sqlite（十一） 批量提交 SQL 命令的测试2015-02-13可把下面代码直接贴在空白窗体上, 以快速完成窗体设计: object DBGrid1: TDBGrid Left = 0 Top = 0 Width = 265 Height = 338 Align = alLeft DataSource = DataSource1 TabOrder = 0 TitleFont.Charset = DEFAULT_CHARSET TitleFont.Color = clWindowText TitleFont.Height = -11 TitleFont.Name = "Tahoma" TitleFont.Style = [] end object Button1: TButton Left = 280 Top = 24 Width = 75 Height = 25 Caption = "Button1" TabOrder = 1 OnClick = Button1Click end object Button2: TButton Left = 280 Top = 64 Width = 75 Height = 25 Caption = "Button2" TabOrder = 2 OnClick = Button2Click end object Button3: TButton Left = 280 Top = 104 Width = 75 Height = 25 Caption = "Button3" TabOrder = 3 OnClick = Button3Click end object Button4: TButton Left = 280 Top = 144 Width = 75 Height = 25 Caption = "Button4" TabOrder = 4 OnClick = Button4Click end object FDConnection1: TFDConnection Left = 66 Top = 48 end object FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink Left = 167 Top = 48 end object FDGUIxWaitCursor1: TFDGUIxWaitCursor Provider = "Forms" Left = 164 Top = 120 end object FDQuery1: TFDQuery Connection = FDConnection1 Left = 56 Top = 192 end object DataSource1: TDataSource DataSet = FDQuery1 Left = 60 Top = 120 end

代码:

{建立}procedure TForm1.FormCreate（Sender: TObject）;conststrTable = "CREATE TABLE MyTable（Id integer PRIMARY KEY AUTOINCREMENT, Name string（10）, Age integer）"; //Id（自增）, Name, AgebeginFDConnection1.DriverName := "SQLite";FDQuery1.ExecSQL（strTable）;FDQuery1.Open（"SELECT * FROM MyTable"）;end;{逐条插入}procedure TForm1.Button1Click（Sender: TObject）;conststrInsert = "INSERT INTO MyTable（Name, Age） VALUES（:name, :age）";begin//  FDQuery1.FetchOptions.AutoClose := True; //默认值FDQuery1.ExecSQL（strInsert, ["A", 1]）;FDQuery1.ExecSQL（strInsert, ["B", 2]）;FDQuery1.ExecSQL（strInsert, ["C", 3]）;FDQuery1.ExecSQL（strInsert, ["D", 4]）;FDQuery1.Open（"SELECT * FROM MyTable"）;end;{用 ; 分割, 一次行插入}procedure TForm1.Button2Click（Sender: TObject）;conststrInsert = "INSERT INTO MyTable（Name, Age） VALUES（"%s", %d）";varLStr: string;beginLStr := "";LStr := LStr + Format（strInsert, ["AA", 11]） + ";";LStr := LStr + Format（strInsert, ["BB", 22]） + ";";LStr := LStr + Format（strInsert, ["CC", 33]） + ";";LStr := LStr + Format（strInsert, ["DD", 44]） + ";";LStr := LStr + "SELECT * FROM MyTable";FDQuery1.ExecSQL（LStr）;FDQuery1.Open（）;end;{使用 NextRecordSet 方法提取并执行所有命令}procedure TForm1.Button3Click（Sender: TObject）;conststrInsert = "INSERT INTO MyTable（Name, Age） VALUES（"%s", %d）;";beginFDQuery1.FetchOptions.AutoClose := False; //按说这个是必须要设置的, 但测试时不设置也可以FDQuery1.SQL.Clear;FDQuery1.SQL.Add（Format（strInsert, ["AAA", 111]））;FDQuery1.SQL.Add（Format（strInsert, ["BBB", 222]））;FDQuery1.SQL.Add（Format（strInsert, ["CCC", 333]））;FDQuery1.SQL.Add（Format（strInsert, ["DDD", 444]））;FDQuery1.SQL.Add（"SELECT * FROM MyTable"）;FDQuery1.Execute（）;FDQuery1.NextRecordSet;end;{使用 DML 数组参数}procedure TForm1.Button4Click（Sender: TObject）;conststrInsert = "INSERT INTO MyTable（Name, Age） VALUES（:name, :age）;";beginFDQuery1.FetchOptions.AutoClose := False; //FDQuery1.SQL.Text := strInsert;FDQuery1.Params.ArraySize := 4; //准备把上面的语句执行 4 次{分别设置 4 次的参数}FDQuery1.Params[0].AsStrings[0] := "AAAA";FDQuery1.Params[1].AsIntegers[0] := 1111;FDQuery1.Params[0].AsStrings[1] := "BBBB";FDQuery1.Params[1].AsIntegers[1] := 2222;FDQuery1.Params[0].AsStrings[2] := "CCCC";FDQuery1.Params[1].AsIntegers[2] := 3333;FDQuery1.Params[0].AsStrings[3] := "DDDD";FDQuery1.Params[1].AsIntegers[3] := 4444;FDQuery1.Execute（4, 0）; //从 1 条开始执行 4 次FDQuery1.SQL.Add（"SELECT * FROM MyTable"）;FDQuery1.NextRecordSet;end;

测试效果图:

另外, 还可以使用 FireDAC 扩展的 SQL Script（TFDScript）, 它还能直接调用文件中的 SQL 指令.

Author：cnblogs 万一