|
Connecting to SQL Server |
|
|
|
Connecting to SQL Server |
|
This tutorial describes how to connect to SQL Server.
Contents
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.
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.
The following assumes that you have the IDE running, and you are currently focused on the form designer.
After you have done these steps, you should set up the newly created MSConnection1 component. You can do this in two ways:
Note: If SQL Server Authentication is chosen and Username and Password are not specified, the sa user name and the blank password are 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.
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.
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.
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;
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.
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: