VB中访问存储过程的几种办法.doc

VB中访问存储过程的几种办法 使用SQL存储过程有什么好处SQL存储过程执行起来比SQL命令文本快得多。当一个SQL语句包含在存储过程中时，服务器不必每次执行它时都要分析和编译它。调用存储过程，可以认为是一个三层结构。这使你的程序易于维护。如果程序需要做某些改动，你只要改动存储过程即可你可以在存储过程中利用Transact-SQL的强大功能。一个SQL存储过程可以包含多个SQL语句。你可以使用变量和条件。这意味着你可以用存储过程建立非常复杂的查询，以非常复杂的方式更新数据库。最后，这也许是最重要的，在存储过程中可以使用参数。你可以传送和返回参数。你还可以得到一个返回值（从SQL RETURN语句）。环境:WinXP+VB6+sp6+SqlServer2000 数据库:test表:Users CREATE TABLE dbo.users ( id int IDENTITY (1, 1) NOT NULL , truename char (10) COLLATE Chinese_PRC_CI_AS NULL , regname char (10) COLLATE Chinese_PRC_CI_AS NULL , pwd char (10) COLLATE Chinese_PRC_CI_AS NULL , sex char (10) COLLATE Chinese_PRC_CI_AS NULL , email text COLLATE Chinese_PRC_CI_AS NULL , jifen decimal(18, 2) NULL ) ON PRIMARY TEXTIMAGE_ON PRIMARYGO ALTER TABLE dbo.users WITH NOCHECK ADD CONSTRAINT PK_users PRIMARY KEY CLUSTERED ( id ) ON PRIMARY GO 存储过程select_usersCREATE PROCEDURE select_users regname char(20), numrows int OUTPUTAS Select * from users SELECT numrows = ROWCOUNT if numrows = 0 return 0 else return 1GO 存储过程insert_usersCREATE PROCEDURE insert_users truename char(20), regname char(20),pwd char(20),sex char(20),email char(20),jifen decimal(19,2)ASinsert into users(truename,regname,pwd,sex,email,jifen) values(truename,regname,pwd,sex,email,jifen)GO 在VB环境中，添加DataGrid控件，4个按钮，6个文本框代码简单易懂。 引用microsoft active data object 2.X libraryOption ExplicitDim mConn As ADODB.ConnectionDim rs1 As ADODB.RecordsetDim rs2 As ADODB.RecordsetDim rs3 As ADODB.RecordsetDim rs4 As ADODB.Recordset Dim cmd As ADODB.CommandDim param As ADODB.Parameter 这里用第一种方法使用存储过程添加数据Private Sub Command1_Click() Set cmd = New ADODB.Command Set rs1 = New ADODB.Recordset cmd.ActiveConnection = mConn cmd.CommandText = insert_users cmd.CommandType = adCmdStoredProc Set param = cmd.CreateParameter(truename, adChar, adParamInput, 20, Trim(txttruename.Text) cmd.Parameters.Append param Set param = cmd.CreateParameter(regname, adChar, adParamInput, 20, Trim(txtregname.Text) cmd.Parameters.Append param Set param = cmd.CreateParameter(pwd, adChar, adParamInput, 20, Trim(txtpwd.Text) cmd.Parameters.Append param Set param = cmd.CreateParameter(sex, adChar, adParamInput, 20, Trim(txtsex.Text) cmd.Parameters.Append param Set param = cmd.CreateParameter(email, adChar, adParamInput, 20, Trim(txtemail.Text)cmd.Parameters.Append param下面的类型需要注意，如果不使用adSingle,会发生一个精度无效的错误 Set param = cmd.CreateParameter(jifen, adSingle, adParamInput, 50, Val(txtjifen.Text) cmd.Parameters.Append param Set rs1 = cmd.Execute Set cmd = Nothing Set rs1 = Nothing End Sub 这里用第二种方法使用存储过程添加数据Private Sub Command2_Click() Set rs2 = New ADODB.Recordset Set cmd = New ADODB.Command cmd.ActiveConnection = mConn cmd.CommandText = insert_users cmd.CommandType = adCmdStoredProc cmd.Parameters(truename) = Trim(txttruename.Text) cmd.Parameters(regname) = Trim(txtregname.Text) cmd.Parameters(pwd) = Trim(txtpwd.Text) cmd.Parameters(sex) = Trim(txtsex.Text) cmd.Parameters(email) = Trim(txtemail.Text) cmd.Parameters(jifen) = Val(txtjifen.Text) Set rs2 = cmd.Execute Set cmd = Nothing Set rs1 = NothingEnd Sub 这里用第三种方法使用连接对象来插入数据Private Sub Command4_Click() Dim strsql As String strsql = insert_users & Trim(txttruename.Text) & , & Trim(txtregname.Text) & , & Trim(txtpwd.Text) & , & Trim(txtsex.Text) & , & Trim(txtemail.Text) & , & Val(txtjifen.Text) & Set rs3 = New ADODB.Recordset Set rs3 = mConn.Execute(strsql) Set rs3 = NothingEnd Sub 利用存储过程显示数据要处理多种参数，输入参数，输出参数以及一个直接返回值 Private Sub Command3_Click() Set rs4 = New ADODB.Recordset Set cmd = New ADODB.Command cmd.ActiveConnection = mConn cmd.CommandText = select_users cmd.CommandType = adCmdStoredProc 返回值 Set param = cmd.CreateParameter(RetVal, adInteger, adParamReturnValue, 4) cmd.Parameters.Append param 输入参数 Set param = cmd.CreateParameter(regname, adChar, adParamInput, 20, Trim(txtregname.Text) cmd.Parameters.Append param 输出参数 Set param = cmd.CreateParameter(numrows, adInteger, adParamOutput) cmd.Parameters.Append param Set rs4 = cmd.Execute() If cmd.Parameters(RetVal).Value = 1 Then MsgBox cmd.Parameters(numrows).Value Else MsgBox 没有记录 End If MsgBox rs4.RecordCount Set DataGrid1.DataSource = rs4 DataGrid1.Refresh End Sub 连接数据库Private Sub Form_Load() Set mConn = New Connection mConn.ConnectionString = Provider=SQLOLEDB.1;Pe