Delphi FireDAC 下的 Sqlite（十一） 批量提交 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

更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/Programming/Delphi/

代碼:

{建立}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 指令.

Author：cnblogs 萬一