Constructing the Database Connection String Dynamically at Run Time

ConnectionString On-The-Fly

If you were using dbGo (ADO) components, the ConnectionString 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, I'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 are 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. Note: if you are using MS Access, try this solution.
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 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 are 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).

And, ... hm, ..., that's all. Download this sample project to get the full source code of the ReadRegistry, WriteRegistry and the DatabasePrompt form.

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 create 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.

If you need any kind of help at this point, please post to the Delphi Programming Forum where all the questions are answered and beginners are treated as experts.