Mega Code Archive
How to use ADO Connections ConnectionString property in a flexible way using .udl files
<![CDATA[
Title: How to use ADO Connection's ConnectionString property in a flexible way using .udl files.
Question: The ConnectionString property of the ADO Connection object is indeed very flexible, as long as you use IDataInitialize interface, which handles the Microsoft Data Link file (".udl").
Answer:
Abstract
The ConnectionString property of the ADO Connection object is indeed very flexible, as long as you use IDataInitialize interface, which handles the Microsoft Data Link file (".udl") -- which is an ".ini" like file with just one section.
Microsoft Data Link Files
The problem of creating and configuring a connection to a database using a UDL file is much more user-friendly than just using a simple .ini file, because Windows automatically recognizes a .udl file and opens up the "Data Link Properties" dialog box.
To create a new .udl file is just a few seconds away: right-clicking on the Windows Explorer allows the user to select New and then Microsoft Data Link to create a .udl file. To edit it, just double-click the file to open up the properties dialog.
Creating a .udl file programmatically
OLE-DB, which is the low-level layer used by ADO, has an interface named IDataInitialize that can be used to create a data source object using a connection string and also retrieve a connection string from an existing data source object.
The interface has a method called WriteStringToStorage that we can use to write a default .udl file, if none is found.
So, for instance, our MyApp application can have a default MyApp.udl file in the same directory and we use this file to connect to the database freeing the application from being recompiled to alter the database which its point to, allowing it to be database-independent (as long as you do not use any SQL specifc to a database).
The sample procedure below will show how to use the method:
----------------------------------------------------------
// NOTE: the sample below uses unit files OLEDB and ComObj.
procedure WriteUDLFile (const UDLFile: string);
const
// Default ConnectionString used by our application (SQL Server)
SConnect = 'Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=OurExampleDB;' +
'Data Source=OURSERVER;Packet Size=4096';
var
DataInitialize: IDataInitialize;
wUDLFile: array[0..MAX_PATH - 1] of WideChar;
begin
// Create the DataInitialize object
DataInitialize := CreateComObject(CLSID_DataLinks) as IDataInitialize;
// Convert AnsiString parameter to WideChar
StringToWideChar (UDLFile, @wUDLFile, MAX_PATH);
// Call method WriteStringToStorage with the default ConnectionString above.
if Failed(DataInitialize.WriteStringToStorage(wUDLFile, SConnect, CREATE_NEW)) then
raise Exception.Create('Can''t write UDL');
end;
----------------------------------------------------------
Delphi's VCL support
I've just discovered that the Delphi VCL already comes with a procedure named CreateUDLFile that creates a basic .udl file (with just Provider and Data Source defined). The above code is very similar (was done with no knowledge of Delphi's own routine).
If you take a look at the source code of ADODB.pas, you'll encounter the following public routines:
procedure CreateUDLFile(const FileName, ProviderName, DataSourceName: WideString);
function DataLinkDir: string;
procedure GetProviderNames(Names: TStrings);
function PromptDataSource(ParentHandle: THandle; InitialString: WideString): WideString;
function PromptDataLinkFile(ParentHandle: THandle; InitialFile: WideString): WideString;
function GetDataLinkFiles(FileNames: TStrings; Directory: string = ''): Integer;
I've found no documentation on the above procedures, but looking at the source code we can more precisely defines what it does. I'll comment briefly on each one here; but if you want more inside information, I'll reinforce you to look at the source code. It's the best way to learn!
** CreateUDLFile: This procedure creates a .udl file with just two properties: Provider and Data Source.
** DataLinkDir: Gets from the registry the default path to save .udl files, defined by Microsoft OLE-DB.
** GetProviderNames: Returns all the names of the Providers available on the system.
** PromptDataSource: Shows the Data Link Properties dialog box to enable the user to edit the ConnectionString easily. The ParentHandle parameter allows the dialog to be centered within a given window handle, which can be your main form (Handle property).
** PromptDataLinkFile: Opens the Select Data Link dialog box. Allows the user to browse and organize .udl files. Returns a fully qualified path to the user-selected .udl file.
** GetDataLinkFiles: Returns all found .udl files in the Directory given.
Using a .udl file
To use a .udl file, just pass "File Name=MyUdlFile.udl" string to the DatabaseConnect property of the ADODatabase as show in the procedure below:
----------------------------------------------------------
procedure ConnectFromUDL (const UDLFile: String);
begin
ADODatabase.Close;
ADODatabase.DatabaseConnect := Format('File Name=%s', [UDLFile]);
ADODatabase.Open;
end;
----------------------------------------------------------
Conclusion
OLE-DB and ADO are really a very good way to deal with different data sources and also allows the programmer to be very flexible dealing with them.
This article here shows how to use .udl files to dynamically handles the connection to a database. Another alternative is to present the user with a dialog box where he/she can enter the server name, user name and password and dynamically create the ConnectionString property and use it to connect to the database. But using .udl files allow more flexibility as the user can deal with a Windows default dialog (the same as the ODBC dialog was in the past).
]]>