FireDAC 下的 Sqlite [4]

来源:转载


建立数据库的代码:


{建立内存数据库的一般代码:}

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


你可以复制下面文本框中的内容, 然后直接往窗体上贴, 以快速完成以上的添加过程:



测试代码:


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 命令更简单:


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}

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 的表, 字段包括: ID, Name, Age, Note, Picture}

MyExecSQL(FDConnection1, 'CREATE TABLE MyTable(ID integer PRIMARY KEY, Name string(10), Age byte, Note text, Picture blob)');

{查看表}

FDQuery1.Open('SELECT * FROM MyTable');

end;



关于数据类型, SQLite 本身只支持(Null, Integer, Real, Text, Blob), 但我们可以放心使用 Delphi 的大多数类型(也包括 Delphi 没有的), 因为 FireDAC 幕后做了转换工作.

SQLite 到 FireDAC 数据类型映射表: (http://docwiki.embarcadero.com/RADStudio/XE6/en/Using_SQLite_with_FireDAC)

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, s)]

dtSByte / dtInt16 / dtInt32 / dtInt64


dtByte / dtUInt16 / dtUInt32 / dtUInt64


dtBCD / dtFmtBCD

money | smallmoney | currency | financial [(p, s)]

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)]

dtAnsiString, Len = L

nchar | national char | national character [(l)]

dtWideString, Len = L, Attrs = [caFixedLen]

nvarchar | nvarchar2 | national char varying | string [(l)]

dtWideString, Len = L

raw | tyniblob | varbinary | binary | binary varying [(l)]

dtByteString, Len = L

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

Type nameDescription

分享给朋友:
您可能感兴趣的文章:
随机阅读: