{建立内存数据库的一般代码:} begin FDConnection1.DriverName := 'sqlite'; //同 FDConnection1.Params.Add('DriverID=sqlite'); // FDConnection1.Params.Add('Database=:memory:'); //可省略这行,FireDAC 的源码显示,if Database = '' then Database := ':memory:'; // FDConnection1.Params.Add('sqliteAdvanced=page_size=4096'); //可指定内存页大小,这是默认值 FDConnection1.Connected := True; end {建立文件数据库的一般代码:} begin FDConnection1.Params.Add('DriverID=sqlite'); FDConnection1.Params.Add('Database=C:\Temp\New1.sdb'); //如果文件存在就打开,不存在就建立 // FDConnection1.Params.Add('sqliteAdvanced=temp_store=Memory'); //可强制临时文件在内存以提高效率. 0:DEFAULT; 1:FILE; 2:MEMORY // FDConnection1.Params.Add('sqliteAdvanced=temp_store_directory=C:\Temp'); //默认的临时文件路径应该是 C:\Documents and Settings\user-name\Local Settings\Temp\ // FDConnection1.Params.Add('OpenMode=CreateUTF8'); //默认是 CreateUTF8,也可选择 CreateUTF16 // FDConnection1.Params.Add('LockingMode=Normal'); //默认是多用户模式,如果使用独占模式 LockingMod=Exclusive 会更有效率 FDConnection1.Connected := True; end;
所有建立参数参见: http://www.sqlite.org/pragma.html
先在空白窗体上添加: TFDConnection、TFDPhyssqliteDriverLink、TFDGUIxWaitCursor; 数据库的建立主要通过 TFDConnection 完成.
同时添加用于呈现数据的 TFDQuery、TDataSource、TDBGrid,还要添加一个 TFDCommand 用于提交建表命令,然后调整如下属性:
FDQuery1 . Connection = FDConnection1 DataSource1 . DataSet = FDQuery1 DBGrid1 . DataSource = DataSource1 FDCommand1 . Connection = FDConnection1
你可以复制下面文本框中的内容,然后直接往窗体上贴,以快速完成以上的添加过程:
object FDConnection1: TFDConnection
Left = 34
Top = 24
end
object FDPhyssqliteDriverLink1: TFDPhyssqliteDriverLink
Left = 143
Top = 24
end
object FDGUIxWaitCursor1: TFDGUIxWaitCursor
Provider = 'Forms'
Left = 260
Top = 24
end
object FDQuery1: TFDQuery
Connection = FDConnection1
Left = 32
Top = 80
end
object DataSource1: TDataSource
DataSet = FDQuery1
Left = 148
Top = 80
end
object FDCommand1: TFDCommand
Connection = FDConnection1
Left = 264
Top = 88
end
object DBGrid1: TDBGrid
Left = 24
Top = 144
Width = 409
Height = 137
DataSource = DataSource1
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
end
测试代码:
procedure TForm1.FormCreate(Sender: TObject); const dbPath = 'C:\Temp\sqliteTest.sdb'; begin if FileExists(dbPath) then DeleteFile(dbPath); with FDConnection1 do begin Params.Add('DriverID=sqlite'); Params.Add('Database=' + dbPath); Connected := True; end; {创建一个名为 MyTable 的表,字段包括: ID,Name,Age,Note,Picture} with FDCommand1.CommandText do begin Add('CREATE TABLE MyTable('); Add('ID integer PRIMARY KEY,'); //Integer 类型,同时设为主键 Add('Name string(10),'); //能容下 10 个字符的 String 类型 Add('Age byte,'); //Byte 类型 Add('Note text,'); //Memo 类型 Add('Picture blob'); //Blob(二进制)类型 Add(')'); end; FDCommand1.Active := True; {查看表} FDQuery1.Open('SELECT * FROM MyTable'); end;
效果图:
直接使用 TFDConnection 提交 DDL 命令更简单:
FDConnection1.Execsql('CREATE TABLE MyTable(ID integer PRIMARY KEY,Name string(10),Age byte,Note text,Picture blob)'); {查看表} FDQuery1.Open('SELECT * FROM MyTable'); end;
使用 sqlite 底层包装完成的建表提交(这样应该更有效率):
uses FireDAC.Phys.sqliteWrapper; //为使用 TsqliteStatement {使用 TsqliteStatement 完成的提交 sql 命令的函数} procedure MyExecsql(ACon: TFDConnection; const Asql: String); begin with TsqliteStatement.Create(ACon.CliObj) do try Prepare(Asql); Execute; while PrepareNextCommand do Execute; finally Free; end; end; procedure TForm1.FormCreate(Sender: TObject); const dbPath = 'C:\Temp\sqliteTest.sdb'; begin if FileExists(dbPath) then DeleteFile(dbPath); with FDConnection1 do begin Params.Add('DriverID=sqlite'); Params.Add('Database=' + dbPath); Connected := True; end; {创建一个名为 MyTable 的表,Picture} MyExecsql(FDConnection1,'CREATE TABLE MyTable(ID integer PRIMARY KEY,Arial; font-size:14px; line-height:18px">关于数据类型,sqlite 本身只支持(Null,Integer,Real,Text,Blob),但我们可以放心使用 Delphi 的大多数类型(也包括 Delphi 没有的),因为 FireDAC 幕后做了转换工作.
sqlite 到 FireDAC 数据类型映射表: ( http://docwiki.embarcadero.com/RADStudio/XE6/en/Using_SQLite_with_FireDAC )
| Type name | Description | |||||
|---|---|---|---|---|---|---|
| rowid | _rowid_ | oid | dtInt64,Attrs = [caSearchable,caAllowNull,caROWID] | |||||
| bit | bool | boolean | logical | yesno | dtBoolean | |||||
| tinyint | shortint | int8 [unsigned] | dtSByte / dtByte | |||||
| byte | uint8 | dtByte | |||||
| smallint | int16 [unsigned] | dtInt16 / dtUInt16 | |||||
| word | uint16 | year | dtUInt16 | |||||
| mediumint | integer | int | int32 [unsigned] | dtInt32 / dtUInt32 | |||||
| longword | uint32 | dtUInt32 | |||||
| bigint | int64 | counter | autoincrement | identity [unsigned] | dtInt64 / dtUInt64 | |||||
| longlongword | uint64 | dtUInt64 | |||||
| real | float | double | dtDouble | |||||
| single [precision] [(p,s)] | dtSingle / dtBCD / dtFmtBCD | |||||
| decimal | dec | numeric | number [unsigned] [(p,sans-serif; color:#000000">dtSByte / dtInt16 / dtInt32 / dtInt64 dtByte / dtUInt16 / dtUInt32 / dtUInt64 dtBCD / dtFmtBCD |
||||||
| money | smallmoney | currency | financial [(p,sans-serif; color:#000000">dtCurrency | ||||||
| date | smalldate | dtDate | |||||
| datetime | smalldatetime | dtDateTime | |||||
| timestamp | dtDateTimeStamp | |||||
| time | dtTime | |||||
| char | character [(l)] | dtAnsiString,Len = L,Attrs = [caFixedLen] | |||||
| varchar | varchar2 | tynitext | character varying | char varying [(l)] | nchar | national char | national character [(l)] | dtWideString,sans-serif; color:#000000">nvarchar | nvarchar2 | national char varying | string [(l)] | raw | tyniblob | varbinary | binary | binary varying [(l)] | dtByteString,sans-serif; color:#000000">blob | mediumblob | image | longblob | long binary | long raw | longvarbinary | general | oleobject | tinyblob | dtBlob | |
| mediumtext | longtext | clob | memo | note | long | long text | longchar | longvarchar | tinytext | dtMemo | |||||
| text | ntext | wtext | nclob | nmemo | long ntext | long wtext | national text | longwchar | longwvarchar | html | dtWideMemo | |||||
| xmldata | xmltype | xml | dtXML | |||||
| guid | uniqueidentifier | dtGUID | |||||
| other data types | dtWideString |
