1.新建項目，新建Form頁面，name屬性改為TestProcedures
2.往TestProcedures頁面中拖入控件：TADOConnection，TADODataSet，TDataSource，TADOStoredProc
1雙擊ADOConnection1設置ConnectionString屬性
2修改ADODataSet1的Connection屬性為ADOConnection1
3修改DataSource1的DataSet屬性為ADODataSet1
4修改ADOStoredProc1的Connection屬性為ADOConnection1
3.頁面設置為如圖：
4 代碼如下：
unit Unit1;
Interface usesinterface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids;
type
TTestProcedures = Class (class(TForm)
ADOConnection1: TADOConnection;
ADODataSet1: TADODataSet;
DataSource1: TDataSource;
Selete: TButton;
Edit1: TEdit;
DBGrid1: TDBGrid;
GroupBox1: TGroupBox;
Select: TGroupBox;
Label1: TLabel;
GroupBox2: TGroupBox;
Edit2: TEdit;
Edit3: TEdit;
Label2: TLabel;
Label3: TLabel;
Insert: TButton;
ADOStoredProc1: TADOStoredProc;
GroupBox3: TGroupBox;
Edit4: TEdit;
Label4: TLabel;
Delete: TButton;
GroupBox4: TGroupBox;
Label5: TLabel;
Edit5: TEdit;
Label6: TLabel;
Edit6: TEdit;
Label7: TLabel;
Edit7: TEdit;
Update: TButton;
procedure GetAll;
procedure SeleteClick(Sender: TObject);
procedure InsertClick(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure DeleteClick(Sender: TObject);
procedure UpdateClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
TestProcedures: TTestProcedures;
implementation
{$R *.dfm}
procedure TTestProcedures.SeleteClick(Sender: TObject);
begin
with ADODataSet1 do
begin
Active:=false;
CommandType:=cmdStoredProc;
CommandText:='usp_selectone';
Parameters.Clear;
Parameters.AddParameter;
Parameters[0].Name:='@id';
Parameters[0].Direction:= pdInput;
Parameters[0].DataType:=ftString;
Parameters.Refresh;
Parameters.ParamByName('@id').Value:=Edit1.Text;
Active:=true;
ExecuteOptions;
end;
end;
procedure TTestProcedures.InsertClick(Sender: TObject);
begin
try
AdoConnection1.BeginTrans;
with ADOStoredProc1 do
begin
Close;
ProcedureName:='usp_insertone';
Parameters.Refresh;
Parameters.ParamByName('@aaaaa').Value:=Edit2.Text;
parameters.ParamByName('@bbbbb').Value:=Edit3.text;
ExecProc;
end;
AdoConnection1.CommitTrans;
GetAll;
except
Adoconnection1.RollbackTrans;
end;
end;
procedure TTestProcedures.DeleteClick(Sender: TObject);
begin
try
AdoConnection1.BeginTrans;
with ADOStoredProc1 do
begin
close;
ProcedureName:='usp_deleteone';
Parameters.Refresh;
Parameters.ParamByName('@id').Value:=Edit4.Text;
ExecProc;
end;
Adoconnection1.CommitTrans;
GetAll;
except
Adoconnection1.RollbackTrans;
end;
end;
procedure TTestProcedures.UpdateClick(Sender: TObject);
begin
try
AdoConnection1.BeginTrans;
with ADOStoredProc1 do
begin
close;
Procedurename:='usp_updateone';
Parameters.Refresh;
Parameters.ParamByName('@id').Value:=Edit5.Text;
Parameters.ParamByName('@aaaaa').Value:=Edit6.Text;
Parameters.ParamByName('@bbbbb').Value:=Edit7.Text;
ExecProc;
end;
AdoConnection1.CommitTrans;
GetAll;
except
adoconnection1.RollbackTrans;
end;
end;
procedure TTestProcedures.FormShow(Sender: TObject);
begin
GetAll;
end;
procedure TTestProcedures.GetAll;
begin
with ADODataSet1 do
begin
Active:=false;
CommandType:=cmdStoredProc;
CommandText:='usp_selectall';
Active:=true;
ExecuteOptions;
end;
end;
end.
5.DBScript
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_deleteone]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_deleteone]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_insertone]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_insertone]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_selectall]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_selectall]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_selectone]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_selectone]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_updateone]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_updateone]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO
CREATE TABLE [dbo].[test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[aaaaa] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[bbbbb] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_deleteone
@id int
AS
delete from test where id = @id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_insertone
@aaaaa varchar(50),
@bbbbb varchar(50)
AS
insert into test (aaaaa,bbbbb)values(@aaaaa,@bbbbb)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_selectall
AS
select * from test
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_selectone
@id int
AS
SELECT aaaaa,bbbbb FROM test where id=@id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_updateone
@id int,
@aaaaa varchar(50),
@bbbbb varchar(50)
AS
Update test set aaaaa=@aaaaa,bbbbb=@bbbbb where id=@id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
