Imports System.Data.SqlClient
Namespace ReadData
Public Class Database
Private con As SqlClient.SqlConnection
Private Sub Open()
' 打开数据库连接
If con Is Nothing Then
con = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
End If
If con.State = System.Data.ConnectionState.Closed Then
con.Open()
End If
End Sub
Public Sub Close()
'关闭
If Not con Is Nothing Then
con.Close()
End If
End Sub
Public Sub Dispose()
' 确认连接是否已经关闭
If Not con Is Nothing Then
con.Dispose()
con = Nothing
End If
End Sub
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 And Value = Nothing) Then
param.Value = Value
End If
Return param
End Function
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 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
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
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
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, Nothing))
Return cmd
End Function
Public Function RunProc(ByVal procName As String) As Integer
Dim cmd As SqlCommand = CreateCommand(procName, Nothing)
cmd.ExecuteNonQuery()
Me.Close()
Return CType(cmd.Parameters("ReturnValue").Value, Integer)
End Function
'/ <summary>
'/ 执行存储过程
'/ </summary>
'/ <param name="procName">存储过程名称</param>
'/ <param name="prams">存储过程所需参数</param>
'/ <returns>返回存储过程返回值</returns>
Public Function RunProc(ByVal procName As String, ByVal prams() As SqlParameter) As Integer
Dim cmd As SqlCommand = CreateCommand(procName, prams)
cmd.ExecuteNonQuery()
Me.Close()
Return CType(cmd.Parameters("ReturnValue").Value, Integer)
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 Function 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)
'return (int)cmd.Parameters["ReturnValue"].Value;
End Function
End Class
End Namespace