SDAC

Connecting to SQL Server

This tutorial describes how to connect to SQL Server.

Contents

  1. Requirements
  2. General information
  3. Creating Connection
  4. Opening connection
  5. Closing connection
  6. Modifying connection
  7. Additional information
  8. See Also

Requirements

In order to connect to SQL Server, you need the server itself running, SDAC installed, and IDE running. Also, you need to know the server name (if the server is run on the remote computer), the port that the server listens to (if you use not the 1433 standard port), the authentication mode, and the database name. If SQL Server Authentication is used, you also need to know the user name and the password.

General information

To establish connection to the server, you have to provide some connection parameters to SDAC. This information is used by the TMSConnection component to establish connection to the server. The parameters are represented by the properties of the TMSConnection component (Server, Database, Authentication, Username, Password). If Windows Authentication is used, the Username and Password properties are ignored.

Note: All these options can be specified at once using the ConnectString property.

There are two ways to connect to SQL Server: using the OLE DB provider and using the SQL Server Native Client provider. This is controlled by the TMSConnection.Options.Provider property. It indicates the provider that is used for connection to SQL Server. By default, the Provider property is set to prAuto, which means that an available provider with the most recent version is used. In this case, SDAC looks for an available provider in the following sequence: Native Client 11, Native Client 10, Native Client 9, OLEDB. If Provider is set to prNativeClient, SDAC looks for an available provider in the following sequence: Native Client 11, Native Client 10, Native Client 9. The first found provider from the sequence is used. If Provider is set to prSQL, SDAC uses the OLEDB provider.

If Provider is set to prCompact, SDAC uses the SQL Server Compact provider. For more information about connecting to SQL Server Compact, please refer to the "Connecting To SQL Server Compact" topic.

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 the IDE running, and you are currently focused on the form designer.

  1. Open the Component palette and find the TMSConnection component in the SQL Server Access category.
  2. Double-click on the component. Note that the 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 MSConnection1 component. You can do this in two ways:

Using TMSConnection Editor

  1. Double-click on the MSConnection1 object.
  2. In the Server edit box specify a DNS name or IP address of the computer, where SQL Server resides. If not the 1433 standard port must be used, it can be specified in the Server edit box in the following format: server,port (for example, localhost,1433).
  3. Choose the authentication mode, SQL Server or Windows.
  4. If SQL Server Authentication is chosen, specify a login (for example, sa) in the Username edit box.
  5. If SQL Server Authentication is chosen, specify a password (for example, password ) in the Password edit box. If a login does not have a password, leave the Password edit box blank.
  6. In the Database edit box specify the database name (for example, master). If Database is not specified, the master system database is used.

Note: If SQL Server Authentication is chosen and Username and Password are not specified, the sa user name and the blank password are used.

Using Object Inspector

  1. Click on the MSConnection1 object and press F11 to focus on object's properties.
  2. Set the Server property to a DNS name or IP address of the computer, where SQL Server resides. If not the 1433 standard port must be used, it can be specified in the Server property in the following format: server,port (for example, localhost,1433).
  3. In the Authentication property choose the authentication mode, SQL Server or Windows.
  4. If SQL Server Authentication is chosen, specify a login in the Username property (for example, sa).
  5. If SQL Server Authentication is chosen, specify a password in the Password property (for example, password). If a login does not have a password, leave the Password property blank.
  6. In the Database property specify the database name (for example, master ). If Database is not specified, the master system database is used.

Note: If SQL Server Authentication is chosen and Username and Password are not specified, the sa user name and the blank password are used.

Run time creation

The same operations performed in runtime look as follows:
[Delphi]


var
  con: TMSConnection;
begin
  con := TMSConnection.Create(nil);
  try
    con.Server := 'server';
    con.Authentication := auServer;
    con.Username := 'username';
    con.Password := 'password';
    con.Database := 'database';
    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 and OLEDBAccess units to the USES clause of your unit.


[C++Builder]


{
  TMSConnection* con = new TMSConnection(NULL);
  try
  {
    con->Server = "server";
    con->Authentication = auServer;
    con->Username = "username";
    con->Password = "password";
    con->Database = "database";
    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.

And using the ConnectString property:


[Delphi]


var
  con: TMSConnection;
begin
  con := TMSConnection.Create(nil);
  try
    con.ConnectString := 'Data Source=server;User ID=username;Password=password;Initial Catalog=database';
    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 units to the USES clause of your unit.


[C++ Builder]


{
  TMSConnection* con = new TMSConnection(NULL);
  try
  {
    con->ConnectString = "Data Source=server;User ID=username;Password=password;Initial Catalog=database";
    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 a connection at run-time is as simple as calling of the Open method:

[Delphi]


con.Open;

[C++ Builder]


con->Open();

Another way to open a 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, MSConnection1 must have valid connection options assigned earlier. When you call Open, SDAC tries to find the host and connect to the server. If any problem occurs, it raises an exception with brief explanation on what is wrong. If no problem is encountered, SDAC tries to establish connection. Finally, when connection is established, the Open method returns and the Connected property is changed to True.

Closing connection

To close a 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 properties of the TMSConnection object. Keep in mind that while some of the properties can be altered freely, most of them close connection when the a value is assigned. For example, if you change 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-2024 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback