Constructing the Database Connection String Dynamically at Run Time

 Once you have finished your Delphi database solution, the final step is to successfully deploy it the user's computer.

ConnectionString On-The-Fly

If you were using dbGo (ADO) components, theConnectionString property of the TADOConnection specifies the connection information for the data store.

Obviously, when creating database applications that are to be run on various machines, the connection to the data source should not be hard-coded in the executable. In other words, the database may be located anywhere on the user's computer (or on some other computer in a network) — the connection string used in the TADOConnection object must be created at run time. One of the suggested places to store the connection string parameters is the Windows Registry (or, you might decide to use the "plain" INI files).

In general, to create the connection string at run time you have to 
  a) place the Full Path to the database in Registry; and 
  b) each time you start your application, read the information from the Registry, "create" the ConnectionString and "open" the ADOConnection.

Database... Connect!

To help you understand the process, we've created a sample "skeleton" application consisting of one form (main form of the application) and a data module. Delphi's Data Modules provide a convenient organizational tool that is used to isolate the parts of your application that handle database connectivity and business rules.

The OnCreate event of the Data Module is where you place the code to dynamically construct the ConnectionString and connect to the database.

procedure TDM.DataModuleCreate(Sender: TObject);
begin
if DBConnect then
ShowMessage('Connected to Database!')
else
ShowMessage('NOT connected to Database!');
end;

Note: The name of the Data Module is "DM". The name of the TADOConnection component is "AdoConn".

The DBConnect function does the actual work of connecting to the database, here's the code:

function TDM.DBConnect: boolean;
var
conStr : string;
ServerName, DBName : string;
begin
ServerName := ReadRegistry('DataSource');
DBName := ReadRegistry('DataCatalog');
conStr := 'Provider=sqloledb;' +
'Data Source=' + ServerName + ';'+
'Initial Catalog=' + DBName + ';'+
'User Id=myUser;Password=myPasword';
Result := false;
AdoConn.Close;
AdoConn.ConnectionString := conStr;
AdoConn.LoginPrompt := False;
if (NOT AdoConn.Connected) then
try
AdoConn.Open;
Result:=True;
except on E:Exception do
begin
MessageDlg('There was an error connecting to
the database. Error:' + #13#10 +
e.Message,
mtError, [mbOk],0);
if NOT TDatabasePromptForm.Execute(ServerName, DBName)
then
Result := false
else
begin
WriteRegistry('DataSource', ServerName);
WriteRegistry('DataCatalog', DBName);
//recall this function
Result := DBConnect;
end;
end;
end;
end; //DBConnect

The DBConnect function connects to the MS SQL Server database — the ConnectionString is constructed using the local connStr variable.

The name of the database server is stored in the ServerName variable, the name of the database is held in the DBName variable. The function starts by reading those two values from the registry (using the custom ReadRegistry() procedure). Once the ConnectionString is assembled, we simply call then AdoConn.Open method. If this call returns "true", we have successfully connected to the database. 

Note: Since we are explicitly passing login information through the ConnectionString, the Since the data module is created before the main form, you can safely call the methods from the data module in the MainForm's OnCreate event.LoginPrompt property is set to false to prevent an unnecessary login dialog.

The "fun" starts if an exception occurs. While there might be many reasons for the Open method to fail, let's presume that the server name or the database name is bad.
If this is the case, we'll give a chance to the user to specify the correct parameters by displaying a custom dialog form. 
The sample application also contains one additional form (DatabasePromptForm) that enables the user to specify the server and the database name for the Connection component. This simple form only provides two edit boxes, if you want to provide a more user-friendly interface, you could add two ComboBoxes and fill those by enumerating available SQL Servers and retrieving databases on a SQL Server.

The DatabasePrompt form provides a custom class method named Execute that accepts two variable (var) parameters: ServerName and DBName.

With the "new" data provided by a user (server and database name), we simply call the DBConnect() function again (recursively). Of course, the information is first stored in the Registry (using another custom method: WriteRegistry).

Make Sure DataModule Is the First "Form" Created!

If you try creating this simple project on your own, you might be experiencing Access Violation exceptions when you run the application. 
By default, the first form added to the application gets to be the MainForm (the first one created). When you add a data module to the application, the data module is added to the list of "auto-create forms" as the form that gets created after the main form.
Now, if you try calling any of the Data Module's properties or methods in the OnCreate event of the MainForm, you'll get an Access Violation exception — as the data module is not yet created.
To solve this problem, you need to manually change the created order of the data module — and set it to be the first form that gets created by the application (either using Project-Properties dialog or by editing the Projects source file).

Since the data module is created before the main form, you can safely call the methods from the data module in the MainForm's OnCreate event.