Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Sql
Public Class DataBase
Private cnn As SqlConnection
'执行存储过程 procName:存储过程的名字 returns:返回存储过程返回值
Public Function RunProc(ByVal procName As String) As Integer
Dim cmd As SqlCommand
cmd = CreateCommand(procName)
cmd.ExecuteNonQuery()
Return cmd.Parameters("ReturnValue").Value
End Function
'执行存储过程 procName:存储过程的名字 prams:存储过程所需参数 returns:返回存储过程返回值
Public Function RunProc(ByVal procName As String, ByVal prams() As SqlParameter) As Integer
Dim cmd As SqlCommand
cmd = CreateCommand(procName, prams)
cmd.ExecuteNonQuery()
Return cmd.Parameters("ReturnValue").Value
End Function
'执行存储过程 procName:存储过程的名称 dataReader:存储过程所需参数
Public Sub RunProc(ByVal procName As String, ByRef dataReader As SqlDataReader)
Dim cmd As SqlCommand
cmd = CreateCommand(procName)
dataReader = cmd.ExecuteReader
End Sub
'执行存储过程 procName:存储过程的名称 prams:存储过程所需参数 dataReader:存储过程所需参数
Public Sub RunProc(ByVal procName As String, ByVal prams() As SqlParameter, ByRef dataReader As SqlDataReader)
Dim cmd As SqlCommand
cmd = CreateCommand(procName, prams)
dataReader = cmd.ExecuteReader
End Sub
'创建一个SqlCommand对象以此来执行存储过程 procname:存储过程的名称 prams:存储过程所需参数 returns:返回SqlCommand对象
Private Function CreateCommand(ByVal procname As String, ByVal prams() As SqlParameter) As SqlCommand
If cnn.State = ConnectionState.Closed Then
cnn.Open()
End If
Dim cmd As New SqlCommand(procname, cnn)
cmd.CommandType = CommandType.StoredProcedure
If Not (prams Is DBNull.Value) Then
Dim parameter As SqlParameter
For Each parameter 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, System.DBNull.Value))
Return cmd
End Function
'创建一个SqlCommand对象以此来执行存储过程 procname:存储过程的名称 returns:返回SqlCommand对象
Private Function CreateCommand(ByVal procname As String) As SqlCommand
If cnn.State = ConnectionState.Closed Then
cnn.Open()
End If
Dim cmd As New SqlCommand(procname, cnn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, 0, String.Empty, DataRowVersion.Default, System.DBNull.Value))
Return cmd
End Function
'打开数据库连接 strCnn:连接字符串
Public Sub Open(ByVal strCnn As String)
cnn = New SqlConnection(strCnn)
If cnn.State = ConnectionState.Closed Then
cnn.Open()
End If
End Sub
'关闭数据库连接
Public Sub Close()
If cnn.State = ConnectionState.Open Then
cnn.Close()
End If
End Sub
'释放资源
Public Sub Dispose()
If cnn.State = ConnectionState.Closed Then
cnn.Dispose()
ElseIf cnn.State = ConnectionState.Open Then
Close()
cnn.Dispose()
Else
cnn.Dispose()
End If
End Sub
'传入输入参数 ParamName:存储过程名称 DbType:参数类型 Size:参数大小 Value:参数值 returns:新的 parameter 对象
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
'传入输出参数 ParamName:存储过程名称 DbType:参数类型 Size:参数大小 returns:新的 parameter 对象
Public Function MakeOutParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Integer) As SqlParameter
Return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, DBNull.Value)
End Function
'传入返回值参数 ParamName:存储过程名称 DbType:参数类型 Size:参数大小 returns:新的 parameter 对象
Public Function MakeReturnParam(ByVal ParamName As String, ByVal DbType As SqlDbType, ByVal Size As Integer) As SqlParameter
Return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, DBNull.Value)
End Function
'生成存储过程参数 ParamName:存储过程名称 DbType:参数类型 Size:参数大小 Direction:参数方向 returns:新的 parameter 对象
Public Function MakeParam(ByVal ParamName As String, ByVal Dbtype As SqlDbType, ByVal Size As Integer, 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 And (Value Is DBNull.Value))) Then
param.Value = Value
End If
Return param
End Function
End Class
