Connecting Delphi with SDF (Sql Compact) Database using ADO

This Delphi Tutorial will show how to connecting Delphi with SQL Compact Database using ADO.

To connecting ADO with SDF file, first we must install Sql Compact on windows. We can download it here for Sql Compact 3.5 SP2.

After we install it, go to registry editor. Windows -> Run -> type "regedit".

find this key : HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{F49C559D-E9E5-467C-8C18-3326AAE4EBCC}

(this was in the Windows 7 PC. In the other one it was HKEY_CLASSES_ROOT\CLSID\{F49C559D-E9E5-467C-8C18-3326AAE4EBCC} )

Then, I add a new key into in: "OLE DB Provider". And finally, set the default value of this key to "Microsoft SQL Server Compact OLE DB Provider".

Now, ADO will shows this provider on the list and we can select it.

Open our delphi project, add These components :
- ADOConnection
- ADODataSet
- ClientDataSet
- DataSetProvider
- DataSource
- Table Grid (i'm using cxGrid)

fill this connection string on ADOConnection :
Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=D:\BBlocksStock\test.sdf;Mode=ReadWrite;SSCE:Max Buffer Size=4096;SSCE:Database Password="";SSCE:Encrypt Database=False;SSCE:Default Lock Escalation=100;SSCE:Temp File Directory="";SSCE:Default Lock Timeout=5000;SSCE:AutoShrink Threshold=60;SSCE:Flush Interval=10;SSCE:Test Callback Pointer=0;SSCE:Max Database Size=256;SSCE:Temp File Max Size=128;SSCE:Encryption Mode=0;SSCE:Case Sensitive=False;

change Data Source value with your database path.

set ADODataSet Connection with ADOConnection. Fill the ADODataSet CommandText with SQL Select Command. ex : SELECT * FROM TEST

set DataSetProvider DataSet with ADODataSet.
set ClientDataSet Provider Name with DataSetProvider
set DataSource DataSet with ClientDataSet.

now on Form onCreate event, write this code :

procedure TForm1.FormCreate(Sender: TObject);beginADODataSet1.Active:=true;end;

if it run correctly, data from database will show on table grid component.

Why we use ADODataSet?? I try use ADOTable Component, and when i try to select TableName properties, we will get error "Multiple-step OLE DB operation generated errors.Check each OLE DB status value, if available. No work was done."

We use ClientDataSet for database transaction. i try to insert new data using ADODataSet Component, i always get error.

so we use ClientDataSet Component for database data transactions.
This is example code for insert new data :

procedure TForm1.Button1Click(Sender: TObject);
begin
ClientDataSet1.Append;
ClientDataSet1ID.AsInteger := 13;
ClientDataSet1.ApplyUpdates(-1);
ADODataSet1.Active:=false;
ADODataSet1.Active:=true;
ClientDataSet1.Active:=false;
ClientDataSet1.Active:=true;
end;