調用存儲過程的通用方法

[摘 要] 在VB.NET中存儲過程的調用模式幾乎一樣，不同之處是每個存儲過程的參數不同，所以每調用一次存儲過程重復代碼很多，這在做項目時很麻煩。筆者經過實踐實現了在VB.NET中調用存儲過程的簡單方法，該方法只需要提供要調用的存儲過程名字和提供具體的參數值就可實現任何存儲過程的調用。

[關鍵詞] VB.NET;存儲過程;系統表;信息結構視圖

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2010 . 03 . 038

[中圖分類號]F270.7;TP392 [文獻標識碼]A [文章編號]1673 - 0194(2010)03 - 0095 - 02

筆者在用VB.NET做項目時，很多時候要調用數據庫中的存儲過程，因為使用存儲過程使得程序在調試、升級、維護方面都變得方便，時間久了，筆者發現存儲過程的調用模式幾乎一樣，差別只是在于調用不同的存儲過程的參數不同，所以每調用一次存儲過程都要寫一大堆幾乎一樣的代碼，這在做項目時很麻煩，所以就想找一種簡便的調用存儲過程的方法。筆者通過查找資料和編程實踐實現了在VB.NET中調用存儲過程的通用方法。該方法只需要提供要調用的存儲過程名字和提供具體的參數值就可實現任何存儲過程的調用。

在VB.NET中存儲過程分為有返回值的和無返回值的，不論哪種存儲過程，它的調用方法都可以概括為下面的幾步:

(1) 聲明一個Sql Connection實例。

(2) 聲明一個Sql Command實例，并且設置其Connection屬性為剛聲明的Sql Connection實例，設置CommandName為存儲過程名，CommandType為存儲過程。

(3) 向聲明的Sql Command實例的Parameters集合中添加所有的存儲過程調用需要的參數。

(4) 聲明Sql DataAdapter和DataSet，設置Sql DataAdapter的SelectCommand屬性為2中聲明的Sql Command實例，再調用其Fill方法來把返回的行集填充到DataSet中。

(5) 關閉Sql Connection實例。

(6) 釋放聲明的各個實例。

調用不同的存儲過程，只是第二步的CommandName和第三步中的參數不同。

我們要寫一個通用的調用存儲過程的方法，該方法只需要提供要調用的存儲過程名字和提供具體的參數值就可實現任何存儲過程的調用，那么就要自動實現如何根據存儲過程名來得到它所有的參數信息，再根據這些參數信息自動創建各個參數。在每個SQL Server數據庫中都有自己的系統表。這些系統表被用來保存配置和對象信息。從這些系統表中，可以得到每個存儲過程的所有參數的信息，其中syscolumns表就保存了包括參數名、類型、長度、方向等需要用到的存儲過程的信息。不過，系統表中的字段會隨著SQL Server版本的變化而變化。比如syscolumns中的type和xtype，它們都保存了類型的信息。要讓我們的方法適應SQL Server的版本變化要求，就要用到信息結構視圖。

ANSI-92將信息結構視圖定義為一組提供系統數據的視圖。利用該視圖，可以將實際系統表在應用程序中隱藏起來，這樣系統表的改變就不會影響到應用程序，應用程序就可以獨立于數據庫廠家和版本。

ANSI-92和SQL Server支持用三段命名結構引用本地服務器上的對象。ANSI-92術語稱為catalog.schema.object，而SQL Server稱為database.owner.object。例如，要找到某個存儲過程的所有參數信息，就用:

select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME ='SPname'

那么如何編程實現通用的調用存儲過程的方法呢?其部分程序如下:

首先定義兩個類:一個是用來作為返回值的載體的，用一個DataSet返回查詢出的數據，用一個Hashtable返回存儲過程的返回值和輸出參數;另一個類用來完成聲明Sql Connection、Sql Command、Sql Parameter，創建各個Sql Parameter的功能。

public class SqlResult

Public ds1 As New DataSet

Public hashTable As New Hashtable

End class

public class sqlSPparam

{‘獲得存儲過程的參數

Private Sub getSPparam(ByVal parameters() As SqlParameter)

Dim myparameter As New SqlParameter

Dim mycommand As New SqlCommand

Dim myconnection As New SqlConnection

Dim i As Integer = 0

mycommand.Connection = myconnection

mycommand.CommandText = “select * from INFORMATION_SCHEMA.PARAMETERS where

SPECIFIC_NAME=’\" + this.ProcedureName + \"’ order by ORDINAL_POSITION\"

Dim reader As New SqlDataReader

reader = mycommand.ExecuteReader

myparameter.ParameterName = \"@Value\"

myparameter.SqlDbType = SqlDbType.Int

myparameter.Direction = ParameterDirection.ReturnValue

mycommand.Parameters.Add(myparameter)

Do While (reader.Read())

myparameter = New SqlParameter

myparameter.ParameterName = reader(\"PARAMETER_NAME\").ToString()

If reader(\"PARAMETER_MODE\").ToString() = \"IN\" Then

myparameter.Direction = ParameterDirection.Input

Else

myparameter.Direction = ParameterDirection.Output

End If

Select Case (reader(\"DATA_TYPE\").ToString().ToUpper())

Case \"NVARCHAR\"

myparameter.SqlDbType = SqlDbType.NVarChar

If (myparameter.Direction = ParameterDirection.Input) Then

myparameter.Value = parameters(i).ToString

End If

Exit Select

Case \"INT\"

myparameter.SqlDbType = SqlDbType.Int

If (myparameter.Direction = ParameterDirection.Input) Then

myparameter.Value = Int(parameters(i))

End If

Exit Select '//很多的其他數據類型的處理

MsgBox(\"不支持的數據類型!\")

End Select

i = i + 1

mycommand.Parameters.Add(myparameter)

Loop

End Sub

｝

Private Function sqlresultcall(ByVal parameters() As SqlParameter，byref result as SqlResult)as SqlResult

Dim result As New SqlResult

myConnection = New SqlConnection(ConnectionString)

myCommand = New SqlCommand(ProcedureName， myConnection)

myCommand.CommandType = CommandType.StoredProcedure

SqlDataAdapter(myAdapter = New SqlDataAdapter(myCommand))

myConnection.Open()

getSPparam(parameters)

myAdapter.Fill(result.MyDataSet， \"Table\")

GetOutputValue(result)’//釋放各種連接和對象

Return result

End Function

Private Sub GetOutputValue(ByVal result As SqlResult)

For Each para In myCommand.Parameters’//如果是返回參數或者輸出參數

If (para.Direction! = ParameterDirection.Input) Then

result.ReturnVal.Add(para.ParameterName， para.Value)

End If

Next

End Sub

end class

在VB.NET中常用的返回結果集的類為SqlDataReader和DataSet，而SqlDataReader必須在保持連接的狀態下才可以使用，DataSet卻不需要。在本文的實現方法中，連接應該在調用之后就斷開，因此采用DataSet來保存返回結果集。

這樣用戶只需提供存儲過程的名字和各個參數值就可以調用存儲過程。