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

发布时间：2020-12-12 19:43:07 所属栏目：百科来源：网络整理

导读：可把下面代码直接贴在空白窗体上,以快速完成窗体设计: object DBGrid1: TDBGrid Left = 0 Top = 0 Width = 265 Height = 338 Align = alLeft DataSource = DataSource1 TabOrder = 0 TitleFont.Charset = DEFAULT_CHARSET TitleFont.Color = clWindowText Ti

可把下面代码直接贴在空白窗体上,以快速完成窗体设计:
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,Age
begin
 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,%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);';
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 指令.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!