在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