FireDAC 下的 Sqlite [11] - 关于批量提交 SQL 命令的测试

可把下面代码直接贴在空白窗体上, 以快速完成窗体设计:
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);const  strTable = 'CREATE TABLE MyTable(Id integer PRIMARY KEY AUTOINCREMENT, Name string(10), Age integer)'; //Id(自增), Name, Agebegin  FDConnection1.DriverName := 'SQLite';  FDQuery1.ExecSQL(strTable);  FDQuery1.Open('SELECT * FROM MyTable');end;{逐条插入}procedure TForm1.Button1Click(Sender: TObject);const  strInsert = '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);const  strInsert = 'INSERT INTO MyTable(Name, Age) VALUES("%s", %d)';var  LStr: string;begin  LStr := '';  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);const  strInsert = 'INSERT INTO MyTable(Name, Age) VALUES("%s", %d);';begin  FDQuery1.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);const  strInsert = 'INSERT INTO MyTable(Name, Age) VALUES(:name, :age);';begin  FDQuery1.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 指令.