幽灵谷

追求永无止境......
posts - 57, comments - 1, trackbacks - 0, articles - 0
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

[VB.NET2005]SQL数据库连接类

Posted on 2010-04-08 17:48 zhb6022 阅读(...) 评论(...) 编辑 收藏

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