Mirror

numerating available SQL Servers. Retrieving databases on a SQL Server (Views: 743)

Problem/Question/Abstract:

how to create your own connection dialog for a SQL Server database. Full Delphi source code for getting the list of available MS SQL Servers (on a network) and listing database names on a Server.

Answer:

If you are doing database development with Delphi and Microsoft's SQL Server is your choice (I'm thinking of using Delphi over ADO and OLE DB), then creating connection strings for a particular database on a particular SQL Server is what you do every day.
The ConnectionString property of the TAdoConnection component is pretty easy to construct if you know the name of the SQL Server and the database you are trying to connect to. On the other hand, if your application needs to connect to different databases on various Servers, then having a custom dialog form that enables your users to pick a database to connect to, is much more appropriate.

This article and the accompanying Delphi code shows how to enumerate the list of SQL Servers and databases that a particular SQL server has.

Open SQL Server Form
Here's the idea: mimic the PromptDataSource function (calls the standard OLE DB Data Link Properties dialog box) found in the ADODB unit, but only display the Connection Tab for SQL Server. Our custom dialog should only return the connection string to a specified database on a selected Server.


The result of this modal dialog is the simplest form of the connection string for connecting to a database on a MS SQL Server for use with the TAdoConnection component. The string returned by the class function named Execute is valid for a connection either using the integrated Windows security or using the custom login / password information. The result will be the string similar to either

Data Source=Home;Initial Catalog=MyDatabase;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;

where "Home" is the name of the MS SQL Server instance and "MyDatabase" is the name of the database, or

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=DataServer;Initial Catalog=DelphiWeb;uid=sa;pwd=adminpwd;

where "DataServer" is the name of the SQL Server, "DelphiWeb" is the name of the database; "sa" is the login name, and "adminpwd" is the password for the "sa" user.

Here's how to call the OpenSQLServerForm form's Execute method (screen capture below displays the result):



procedure TCallingForm.btnConnectClick(Sender:TObject);
begin
Memo1.Text := TOpenSQLServerForm.Execute;

//Just testing
//PromptDataSource(Handle, Memo1.Text);
end;

Note: you can download the full source code and alter it to suit your needs.

In general, the Execute method creates the dialog (modal form) and starts by populating a drop down list with all available SQL Servers on the network. When the OK button is clicked the Execute method returns the connection string for a specified database.

We'll now only explore the two main methods found in the code, notably the code that enumerates network-visible instances of Microsoft SQL Server; and the code that gets all the databases on a particular SQL Server.
List Available SQL Servers
The ListAvailableSQLServers fills in the TStrings object passed as the only argument. This method retrieves all available SQL Server instances on a network.

uses DB, ADODB, Variants, ActiveX,
ComObj, AdoInt, OleDB;
...
procedure ListAvailableSQLServers(Names : TStrings);
var
RSCon: ADORecordsetConstruction;
Rowset: IRowset;
SourcesRowset: ISourcesRowset;
SourcesRecordset: _Recordset;
SourcesName, SourcesType: TField;

function PtCreateADOObject
(const ClassID: TGUID): IUnknown;
var
Status: HResult;
FPUControlWord: Word;
begin
asm
FNSTCW FPUControlWord
end;
Status := CoCreateInstance(
CLASS_Recordset,
nil,
CLSCTX_INPROC_SERVER or
CLSCTX_LOCAL_SERVER,
IUnknown,
Result);
asm
FNCLEX
FLDCW FPUControlWord
end;
OleCheck(Status);
end;
begin
SourcesRecordset :=
PtCreateADOObject(CLASS_Recordset)
as _Recordset;
RSCon :=
SourcesRecordset
as ADORecordsetConstruction;
SourcesRowset :=
CreateComObject(ProgIDToClassID('SQLOLEDB Enumerator'))
as ISourcesRowset;
OleCheck(SourcesRowset.GetSourcesRowset(
nil,
IRowset, 0,
nil,
IUnknown(Rowset)));
RSCon.Rowset := RowSet;
with TADODataSet.Create(nil) do
try
Recordset := SourcesRecordset;
SourcesName := FieldByName('SOURCES_NAME');
SourcesType := FieldByName('SOURCES_TYPE');
Names.BeginUpdate;
try
while not EOF do
begin
if
(SourcesType.AsInteger = DBSOURCETYPE_DATASOURCE)
and (SourcesName.AsString <> '') then
Names.Add(SourcesName.AsString);
Next;
end;
finally
Names.EndUpdate;
end;
finally
Free;
end;
end;


List database names on a SQL Server
The DatabasesOnServer function populates the Databases string list passed as the only argument.

procedure TOpenSQLServerForm.
DatabasesOnServer(Databases : TStrings);
var
rs : _RecordSet;
begin
Databases.Clear;
with TAdoConnection.Create(nil) do
try
//simple ConnectionString without the DB name
ConnectionString := ConnStr;
LoginPrompt := False;
try
Open;
rs := ConnectionObject.OpenSchema(
adSchemaCatalogs,
EmptyParam,
EmptyParam);
with rs do
begin
try
Databases.BeginUpdate;
while not Eof do
begin
Databases.Add(
VarToStr(
Fields['CATALOG_NAME'].Value));
MoveNext;
end;
finally
Databases.EndUpdate;
end;
end;
Close;
except
on e:exception do
MessageDlg(e.Message,mtError, [mbOK],0);
end;
finally
Free;
end;
end;

<< Back to main page