SQLSERVER .NET数据库操作编程

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