1. Technology
Send to a Friend via Email
Enumerating available SQL Servers. Retrieving databases on a SQL Server
Here's 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.
 Win prizes by sharing code!
Do you have some Delphi code you want to share? Are you interested in winning a prize for your work?
Delphi Programming Quickies Contest
 More of this Feature
• FULL SOURCE CODE
 Join the Discussion
"Post your views, comments, questions and doubts to this article."
Discuss!
 Related Resources
• ADO Course
• Database Programming
• Working with SQL Server

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.

Open SQL Server Form

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):

Calling the OpenSQLServerForm dialog

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;

Adopt, adapt and improve...
I've tested this code on Windows XP and Windows 2000. I'm not sure if it will work on some older (Win 9x) versions. Sometimes, the Databases drop down box does not get populated, therefore you are able to enter the name of the SQL Server manually to try to get the list of databases.

If you need any help with the code, feel free to post a message on the Delphi Programming Forum.

©2014 About.com. All rights reserved.