我希望在安装过程中连接到 MS SQL Server 2008 。有一个 类似的问题,提供了使用 isql.exe
工具的解决方案,但它不兼容 SQL Server 2008。
请问你能建议如何连接到 MS SQL Server 2008 吗?
我希望在安装过程中连接到 MS SQL Server 2008 。有一个 类似的问题,提供了使用 isql.exe
工具的解决方案,但它不兼容 SQL Server 2008。
请问你能建议如何连接到 MS SQL Server 2008 吗?
以下是使用ADO连接到Microsoft SQL Server的简单示例:
[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;
这是我测试用的 SQL 脚本文件,我将其存储在我的电脑上,文件名为 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;
这里
以了解如何实现此操作。简而言之,您需要声明另一个变量,例如ADOParameter: Variant;
,然后在您的ADOCommand
对象上调用CreateParameter
并为其分配一个值,最后调用ADOCommand.Parameters.Append(ADOParameter);
。 - TLama