How to connect to MS SQL Server using Inno Setup?

Question

I would like to connect to the MS SQL Server 2008 during installation. There's a similar question, which offers a solution by using isql.exe tool, which is not compatible with SQL Server 2008.

Could you suggest, how to connect to a MS SQL Server 2008 ?

Answer 1

Here is a simple example for connecting to Microsoft SQL Server using ADO:

[Setup]
AppName=My Program
AppVersion=1.5
DefaultDirName={pf}\My Program
DefaultGroupName=My Program
UninstallDisplayIcon={app}\MyProg.exe
Compression=lzma2
SolidCompression=yes

[Code]
const
  adCmdUnspecified = $FFFFFFFF;
  adCmdUnknown = $00000008;
  adCmdText = $00000001;
  adCmdTable = $00000002;
  adCmdStoredProc = $00000004;
  adCmdFile = $00000100;
  adCmdTableDirect = $00000200;
  adOptionUnspecified = $FFFFFFFF;
  adAsyncExecute = $00000010;
  adAsyncFetch = $00000020;
  adAsyncFetchNonBlocking = $00000040;
  adExecuteNoRecords = $00000080;
  adExecuteStream = $00000400;
  adExecuteRecord = $00000800;
var
  CustomerLabel: TLabel;
  ConnectButton: TButton;

procedure ConnectButtonClick(Sender: TObject);
var
  Name, Surname: string;
  SQLQuery: AnsiString;  
  ADOCommand: Variant;
  ADORecordset: Variant;
  ADOConnection: Variant;  
begin
  try
    // create the ADO connection object
    ADOConnection := CreateOleObject('ADODB.Connection');
    // build a connection string; for more information, search for ADO
    // connection string on the Internet 
    ADOConnection.ConnectionString := 
      'Provider=SQLOLEDB;' +               // provider
      'Data Source=Default\SQLSERVER;' +   // server name
      'Initial Catalog=Northwind;' +       // default database
      'User Id=UserName;' +                // user name
      'Password=12345;';                   // password
    // open the connection by the assigned ConnectionString
    ADOConnection.Open;
    try
      // create the ADO command object
      ADOCommand := CreateOleObject('ADODB.Command');
      // assign the currently opened connection to ADO command object
      ADOCommand.ActiveConnection := ADOConnection;
      // load a script from file into the SQLQuery variable
      if LoadStringFromFile('d:\Script.sql', SQLQuery) then
      begin
        // assign text of a command to be issued against a provider
        ADOCommand.CommandText := SQLQuery;
        // this will execute the script; the adCmdText flag here means
        // you're going to execute the CommandText text command, while
        // the adExecuteNoRecords flag ensures no data row will be get
        // from a provider, what should improve performance
        ADOCommand.Execute(NULL, NULL, adCmdText or adExecuteNoRecords);
      end;
      // assign text of a command to be issued against a provider
      ADOCommand.CommandText := 'SELECT Name, Surname FROM Customer';
      // this property setting means, that you're going to execute the 
      // CommandText text command; it does the same, like if you would
      // use only adCmdText flag in the Execute statement
      ADOCommand.CommandType := adCmdText;
      // this will execute the command and return dataset
      ADORecordset := ADOCommand.Execute;
      // get values from a dataset using 0 based indexed field access;
      // notice, that you can't directly concatenate constant strings 
      // with Variant data values
      Name := ADORecordset.Fields(0);
      Surname := ADORecordset.Fields(1);
      CustomerLabel.Caption := Name + ' ' + Surname;
    finally
      ADOConnection.Close;
    end;
  except
    MsgBox(GetExceptionMessage, mbError, MB_OK);
  end;
end;

procedure InitializeWizard;
begin
  ConnectButton := TButton.Create(WizardForm);
  ConnectButton.Parent := WizardForm;
  ConnectButton.Left := 8;
  ConnectButton.Top := WizardForm.ClientHeight - 
    ConnectButton.ClientHeight - 8;
  ConnectButton.Caption := 'Connect';
  ConnectButton.OnClick := @ConnectButtonClick;
  CustomerLabel := TLabel.Create(WizardForm);
  CustomerLabel.Parent := WizardForm;
  CustomerLabel.Left := ConnectButton.Left + ConnectButton.Width + 8;
  CustomerLabel.Top := ConnectButton.Top + 6;
  CustomerLabel.Font.Style := [fsBold];
  CustomerLabel.Font.Color := clMaroon;
end;

Here is my testing SQL script file stored in my case as Script.sql:

BEGIN TRANSACTION;
BEGIN TRY
    CREATE TABLE [dbo].[Customer](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](50) NOT NULL,
        [Surname] [nvarchar](50) NOT NULL,
        [CreatedBy] [nvarchar](255) NOT NULL,
        [CreatedAt] [datetime] NOT NULL,
    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
      ([ID] ASC)
    WITH 
      (
        PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
      ) ON [PRIMARY]
    ) ON [PRIMARY]
    
    ALTER TABLE [dbo].[Customer] 
      ADD CONSTRAINT [DF_Customer_CreatedBy] DEFAULT (suser_sname()) FOR [CreatedBy]
    
    ALTER TABLE [dbo].[Customer] 
      ADD CONSTRAINT [DF_Customer_CreatedAt]  DEFAULT (getdate()) FOR [CreatedAt]
      
    INSERT INTO [dbo].[Customer]
      (Name, Surname)
    VALUES
      ('Dave', 'Lister')
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;