SDAC

Logging on to SQL Server Compact

This tutorial describes how to connect to SQL Server Compact.

Contents

  1. Requirements
  2. General information
  3. Creating Connection
    1. 3.1. Design time creation
      1. 3.1.1. Using Connection Editor
      2. 3.1.2. Using Object Inspector
    2. 3.2. Run time creation
  4. Opening connection
  5. Closing connection
  6. Modifying connection
  7. Additional information
  8. See Also

Requirements

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.

General information

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.

Creating Connection

Design time creation

The following assumes that you have IDE running, and you are currently focused on the form designer.

TMSCompactConnection:

  1. Open the Component palette and find the TMSCompactConnection component in the SQL Server Access category.
  2. Double-click on the component. Note that a new object appears on the form. If this is the first time you create TMSCompactConnection in this application, it is named MSCompactConnection1.

TMSConnection:

  1. Open the Component palette and find the TMSConnection component in the SQL Server Access category.
  2. Double-click on the component. Note that a new object appears on the form. If this is the first time you create TMSConnection in this application, it is named MSConnection1.

After you have done these steps, you should set up the newly created MSCompactConnection1 or MSConnection1 component. You can do this in two ways:

Using Connection Editor

TMSCompactConnection:

  1. Double-click on the TMSCompactConnection object.
  2. In the Database edit box specify the database name (for example, C:\test.sdf). If the specified database does not exist, it will be created on a connection attempt.
  3. If the specified database is password-protected, specify the password in the Password edit box.

Using Object Inspector

TMSCompactConnection:

  1. Click on the MSCompactConnection1 object and press F11 to focus on the object properties.
  2. In the Database property specify the database name (for example, C:\test.sdf). If the specified database does not exist, it will be created on connection attempt.
  3. If the specified database is password-protected, specify the password in the Password property.

TMSConnection:

  1. Click on the MSConnection1 object and press F11 to focus on the object properties.
  2. Set the Options.Provider property to prCompact.
  3. In the Database property specify the database name (for example, C:\test.sdf). If the specified database does not exist, it will be created on connection attempt.
  4. If the specified database is password-protected, specify the password in the Password property.

Run time creation

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.

Opening connection

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.

Closing connection

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;

Modifying connection

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.

Additional information

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:

See Also

© 1997-2019 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback