VB中訪問(wèn)存儲(chǔ)過(guò)程的幾種辦法.doc

VB中訪問(wèn)存儲(chǔ)過(guò)程的幾種辦法 使用SQL存儲(chǔ)過(guò)程有什么好處SQL存儲(chǔ)過(guò)程執(zhí)行起來(lái)比SQL命令文本快得多。當(dāng)一個(gè)SQL語(yǔ)句包含在存儲(chǔ)過(guò)程中時(shí)，服務(wù)器不必每次執(zhí)行它時(shí)都要分析和編譯它。調(diào)用存儲(chǔ)過(guò)程，可以認(rèn)為是一個(gè)三層結(jié)構(gòu)。這使你的程序易于維護(hù)。如果程序需要做某些改動(dòng)，你只要改動(dòng)存儲(chǔ)過(guò)程即可你可以在存儲(chǔ)過(guò)程中利用Transact-SQL的強(qiáng)大功能。一個(gè)SQL存儲(chǔ)過(guò)程可以包含多個(gè)SQL語(yǔ)句。你可以使用變量和條件。這意味著你可以用存儲(chǔ)過(guò)程建立非常復(fù)雜的查詢，以非常復(fù)雜的方式更新數(shù)據(jù)庫(kù)。最后，這也許是最重要的，在存儲(chǔ)過(guò)程中可以使用參數(shù)。你可以傳送和返回參數(shù)。你還可以得到一個(gè)返回值（從SQL RETURN語(yǔ)句）。環(huán)境:WinXP+VB6+sp6+SqlServer2000 數(shù)據(jù)庫(kù):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 存儲(chǔ)過(guò)程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 存儲(chǔ)過(guò)程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環(huán)境中，添加DataGrid控件，4個(gè)按鈕，6個(gè)文本框代碼簡(jiǎn)單易懂。 引用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 這里用第一種方法使用存儲(chǔ)過(guò)程添加數(shù)據(jù)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,會(huì)發(fā)生一個(gè)精度無(wú)效的錯(cuò)誤 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 這里用第二種方法使用存儲(chǔ)過(guò)程添加數(shù)據(jù)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 這里用第三種方法使用連接對(duì)象來(lái)插入數(shù)據(jù)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 利用存儲(chǔ)過(guò)程顯示數(shù)據(jù)要處理多種參數(shù)，輸入?yún)?shù)，輸出參數(shù)以及一個(gè)直接返回值 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 輸入?yún)?shù) Set param = cmd.CreateParameter(regname, adChar, adParamInput, 20, Trim(txtregname.Text) cmd.Parameters.Append param 輸出參數(shù) 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 沒(méi)有記錄 End If MsgBox rs4.RecordCount Set DataGrid1.DataSource = rs4 DataGrid1.Refresh End Sub 連接數(shù)據(jù)庫(kù)Private Sub Form_Load() Set mConn = New Connection mConn.ConnectionString = Provider=SQLOLEDB.1;Pe