在vb.net 裡面實現對sql server 預存程序的操作

Imports System.Data.SqlClient
Public Class database
    Private con As SqlConnection
    '  /// <summary>
    '/// 執行預存程序
    '/// </summary>
    '/// <param name="procName">預存程序的名稱</param>
    '/// <returns>返回預存程序傳回值</returns>
    Public Function RunProc(ByVal procName As String) As Integer
        Dim cmd As SqlCommand
        cmd = createCommand(procName, Nothing)
        Me.close()
        Dim returnvalue As Integer
        returnvalue = cmd.Parameters("ReturnValue").Value
        Return returnvalue
    End Function
    '   /// <summary>
    '/// 執行預存程序
    '/// </summary>
    '/// <param name="procName">預存程序名稱</param>
    '/// <param name="prams">預存程序所需參數</param>
    '/// <returns>返回預存程序傳回值</returns>
    Public Function RunProc(ByVal procName As String, ByVal params() As SqlParameter) As Integer
        Dim cmd As SqlCommand
        cmd = createCommand(procName, params)
        cmd.ExecuteNonQuery()
        Me.close()
        Dim returnvalue As Integer
        returnvalue = cmd.Parameters("ReturnValue").Value
        Return returnvalue
    End Function
    '/// <summary>
    ' /// 執行預存程序
    ' /// </summary>
    ' /// <param name="procName">預存程序的名稱</param>
    ' /// <param name="dataReader">返回預存程序傳回值</param>
  
    Public Sub RunProc(ByVal procName As String, ByRef dataReader As SqlDataReader)
        Dim cmd As SqlCommand = CreateCommand(procName, Nothing)
        dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
    End Sub

    '/// <summary>
    '/// 執行預存程序
    '/// </summary>
    '/// <param name="procName">預存程序的名稱</param>
    '/// <param name="prams">預存程序所需參數</param>
    '/// <param name="dataReader">預存程序所需參數</param>
    Public Sub RunProc(ByVal procName As String, ByVal prams As SqlParameter(), ByRef dataReader As SqlDataReader)
        Dim cmd As SqlCommand = createCommand(procName, prams)
        dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
    End Sub
    '   /// <summary>
    '/// 建立一個SqlCommand對象以此來執行預存程序
    '/// </summary>
    '/// <param name="procName">預存程序的名稱</param>
    '/// <param name="prams">預存程序所需參數</param>
    '/// <returns>返回SqlCommand對象</returns>
  

    Private Function CreateCommand(ByVal procName As String, ByVal prams As SqlParameter()) As SqlCommand
        Open()
        Dim cmd As SqlCommand = New SqlCommand(procName, con)
        cmd.CommandType = CommandType.StoredProcedure
        If Not (prams Is Nothing) Then
            For Each parameter As SqlParameter In prams
                cmd.Parameters.Add(parameter)
            Next
        End If
        cmd.Parameters.Add(New SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, 0, String.Empty, DataRowVersion.Default, Nothing))
        Return cmd
    End Function

    Private Sub Open()
        If con Is Nothing Then
            con = New SqlConnection(ConfigurationSettings.AppSettings("Constr"))
        End If
        If con.State = System.Data.ConnectionState.Closed Then
            con.Open()
        End If
    End Sub

    Public Sub close()
        If con Is Nothing = False Then
            con.Close()
        End If
    End Sub

    Public Sub Dispose()
        If con Is Nothing = False Then
            con.Dispose()
            con = Nothing
        End If
    End Sub

    '   /// <summary>
    '/// 傳入輸入參數
    '/// </summary>
    '/// <param name="ParamName">預存程序名稱</param>
    '/// <param name="DbType">參數類型</param></param>
    '/// <param name="Size">參數大小</param>
    '/// <param name="Value">參數值</param>
    '/// <returns>新的 parameter 對象</returns>
    Public Function MakeInParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal size As Integer, ByVal value As Object) As SqlParameter
        Return MakeParam(ParamName, DbType, size, ParameterDirection.Input, value)
    End Function
    '/// <summary>
    ' /// 傳入傳回值參數
    ' /// </summary>
    ' /// <param name="ParamName">預存程序名稱</param>
    ' /// <param name="DbType">參數類型</param>
    ' /// <param name="Size">參數大小</param>
    ' /// <returns>新的 parameter 對象</returns>
    Public Function MakeOutParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal size As Integer) As SqlParameter
        Return MakeParam(ParamName, DbType, size, ParameterDirection.Output, Nothing)
    End Function

    '   /// <summary>
    '/// 產生預存程序參數
    '/// </summary>
    '/// <param name="ParamName">預存程序名稱</param>
    '/// <param name="DbType">參數類型</param>
    '/// <param name="Size">參數大小</param>
    '/// <param name="Direction">參數方向</param>
    '/// <param name="Value">參數值</param>
    '/// <returns>新的 parameter 對象</returns>

    '  /// <summary>
    '/// 傳入傳回值參數
    '/// </summary>
    '/// <param name="ParamName">預存程序名稱</param>
    '/// <param name="DbType">參數類型</param>
    '/// <param name="Size">參數大小</param>
    '/// <returns>新的 parameter 對象</returns>
    Public Function MakeReturnParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Integer) As SqlParameter
        Return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, Nothing)
    End Function

    Public Function MakeParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Int32, ByVal Direction As ParameterDirection, ByVal Value As Object) As SqlParameter
        Dim param As SqlParameter
        If Size > 0 Then
            param = New SqlParameter(ParamName, DbType, Size)
        Else
            param = New SqlParameter(ParamName, DbType)
        End If
        param.Direction = Direction
        If Not (Direction = ParameterDirection.Output AndAlso Value Is Nothing) Then
            param.Value = Value
        End If
        Return param
    End Function
End Class