|
Connecting to SQL Server Compact |
|
|
|
Connecting to SQL Server Compact |
|
This tutorial describes how to connect to SQL Server Compact.
Contents
In order to connect to SQL Server Compact, you need the server itself installed, SDAC installed, and IDE running. In addition, you need to know the full path to the database file (.SDF). If a database is password-protected, you also need to know the password.
It is possible to connect to SQL Server Compact using both TMSCompactConnection and TMSConnection components. To establish connection to the server, you have to provide some connection parameters to SDAC. This information is used by the TMSCompactConnection or TMSConnection component to establish connection to the server. The parameters are represented by the properties of the TMSCompactConnection or TMSConnection component (Database, Password). If TMSConnection is used, the TMSConnection.Options.Provider property must be set to prCompact.
To choose a version of SQL Server Compact you want to work with using TMSCompactConnection, you can use the TMSCompactConnection.Options.CompactVersion property. Here is a list of possible values:
To choose a version of SQL Server Compact you want to work with using TMSConnection, you can use the Provider connection string option in the TMSConnection.ConnectString property. Here is a list of possible values:
Note: If a database exists before a connection attempt, SDAC tries to determine the correct version of SQL Server Compact to use by reading it from the database itself. If SDAC obtaines the version of SQL Server Compact from the database, an appropriate provider is used.
Note: If SDAC cannot find the choosen provider, the "Required provider is not installed" error is generated.
The following assumes that you have IDE running, and you are currently focused on the form designer.
TMSCompactConnection:
TMSConnection:
After you have done these steps, you should set up the newly created MSCompactConnection1 or MSConnection1 component. You can do this in two ways:
TMSCompactConnection:
TMSCompactConnection:
TMSConnection:
The same operations performed in runtime look as follows:
TMSCompactConnection:
[Delphi]
procedure TMainForm.ButtonConnectClick(Sender: TObject);
var
con: TMSCompactConnection;
begin
con := TMSCompactConnection.Create(nil);
try
con.Options.CompactVersion := cv40;
con.Database := 'database'; // if the database does not exist, it will be created on con.Open
con.Password := 'password'; // if the database is password-protected
con.LoginPrompt := False; // to prevent showing of the connection dialog
con.Open;
finally
con.Free;
end;
end;Note: To run this code, you have to add the MSCompactConnection and OLEDBAccess units to the USES clause of your unit.
[C++Builder]
void __fastcall TMainForm::ButtonConnectClick(TObject *Sender)
{
TMSCompactConnection* con = new TMSCompactConnection(NULL);
try
{
con->Options->CompactVersion = cv40;
con->Database = "database"; // if the database does not exist, it will be created on con.Open
con->Password = "password"; // if the database is password-protected
con->LoginPrompt = False; // to prevent showing of the connection dialog
con->Open();
}
__finally
{
con->Free();
}
}Note: To run this code, you have to include the MSCompactConnection.hpp header file to your unit.
TMSConnection:
[Delphi]
procedure TMainForm.ButtonConnectClick(Sender: TObject);
var
con: TMSConnection;
begin
con := TMSConnection.Create(nil);
try
con.ConnectString := 'Provider=MICROSOFT.SQLSERVER.MOBILE.OLEDB.4.0';
con.Database := 'database'; // if the database does not exist, it will be created on con.Open
con.Password := 'password'; // if the database is password-protected
con.LoginPrompt := False; // to prevent showing of the connection dialog
con.Open;
finally
con.Free;
end;
end;Note: To run this code, you have to add the MSAccess unit to the USES clause of your unit.
[C++ Builder]
void __fastcall TMainForm::ButtonConnectClick(TObject *Sender)
{
TMSConnection* con = new TMSConnection(NULL);
try
{
con->ConnectString = "Provider=MICROSOFT.SQLSERVER.MOBILE.OLEDB.4.0";
con->Database = "database"; // if the database does not exist, it will be created on con.Open
con->Password = "password"; // if the database is password-protected
con->LoginPrompt = False; // to prevent showing of the connection dialog
con->Open();
}
__finally
{
con->Free();
}
}Note: To run this code, you have to include the MSAccess.hpp header file to your unit.
As you can see above, opening connection at run-time is as simple as calling of the Open method:
[Delphi]
con.Open;
[C++ Builder]
con->Open();
Another way to open connection at run-time is to set the Connected property to True:
[Delphi]
con.Connected := True;
[C++ Builder]
con->Connected = True;
This way can be used at design-time as well. Of course, connection (TMSCompactConnection or TMSConnection) must have valid connection options assigned earlier. When you call Open, SDAC tries to open the database. If any problem occurs, it raises an exception with brief explanation on what is wrong. If no problem is encountered and the database is opened, the Open method returns and the Connected property is changed to True.
To close connection, call its Close method, or set its Connected property to False:
[Delphi]
con.Close;
[C++ Builder]
con.Close();
or:
[Delphi]
con.Connected := False;
[C++ Builder]
con.Connected = False;
You can modify connection by changing the properties of the TMSCompactConnection or TMSConnection component. Keep in mind that while some of the properties can be altered freely, most of them close connection when a new value is assigned. For example, if you change the Server property, it is closed immediately, and you have to reopen it manually.
SDAC has a wide set of features you can take advantage of. The following list enumerates some of them, so you can explore the advanced techniques to achieve better performance, balance network load or enable additional capabilities: