sqldatabase.vb

vb2005数据库完整代码

#End Region

#Region "执行存储过程"
    '<summary>
    ' 执行存储过程
    '</summary>
    '<param name="procName">存储过程的名称</param>
    ' <returns>返回存储过程返回值</returns>
    Public Function RunProc(ByVal ProcName As String) As Integer
        Dim cmd As SqlCommand = CreateCommand(ProcName, Nothing)
        cmd.ExecuteNonQuery()
        Me.Close()
        Return CInt(cmd.Parameters("Returnvalue").Value)
    End Function


#End Region

#Region "执行存储过程"
    '<summary>
    ' 执行存储过程
    '</summary>
    '<param name="procName">存储过程的名称</param>
    '<param name="params">存储过程所需参数</param>
    ' <returns>返回存储过程返回值</returns>
    Public Function RunProc(ByVal ProcName As String, ByVal params() As SqlParameter) As Integer
        Dim cmd As SqlCommand = CreateCommand(ProcName, params)
        cmd.ExecuteNonQuery()
        Me.Close()
        Return CInt(cmd.Parameters("Returnvalue").Value)
    End Function


#End Region


#Region "执行存储过程"
    '<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)
        'Return CInt(cmd.Parameters("Returnvalue").Value)
    End Sub


#End Region


#Region "执行存储过程"
    '''   <summary>
    ''' 执行存储过程
    ''' </summary>
    ''' <param name="procName">存储过程的名称</param>
    ''' <param name="params">存储过程所需参数</param>
    ''' <param name="dataReader">存储过程所需参数</param>
    Public Sub RunProc(ByVal procName As String, ByVal params() As SqlParameter, ByRef datareader As SqlDataReader)
        Dim cmd As SqlCommand = CreateCommand(procName, params)
        datareader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
        'Return CInt(cmd.Parameters("ReturnValue").Value)
    End Sub


#End Region


#Region "生成存储过程参数"
    ' <summary>
    ' 生成存储过程参数
    ' </summary>
    ' <param name="ParamName">存储过程名称</param>
    ' <param name="DbType">参数类型</param>
    ' <param name="Size">参数大小,如果大小不确定，SIZE=-1</param>
    ' <param name="Direction">参数方向</param>
    ' <param name="Value">参数值</param>
    ' <returns>新的 parameter 对象</returns>
    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 = Nothing) Then
            param.Value = Value
        End If
        Return param
    End Function

#End Region



#Region "传入输入参数"

    ''' <summary>
    ''' 传入输入参数
    ''' </summary>
    ''' <param name="ParamName">存储过程名称</param>
    ''' <param name="DbType">参数类型</param>
    ''' <param name="Size">参数大小</param>
    ''' <param name="Value">参数值</param>
    ''' <returns>新的 parameter 对象</returns>
    ''' <remarks></remarks>
    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

#End Region


#Region "传入返回值参数"
    ''' <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

#End Region

#Region "获取类型"
    ''' <summary>
    ''' 通过字符串返回数据库类型
    ''' </summary>
    ''' <param name="DataTypeAsString"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function StringToSqlDbType(ByVal DataTypeAsString As String) As SqlDbType
        Select Case DataTypeAsString

            Case "Bit", "bit"
                Return SqlDbType.Bit

            Case "BigInt", "bigint"
                Return SqlDbType.BigInt

            Case "Binary", "binary"
                Return SqlDbType.Binary

            Case "Char", "char"
                Return SqlDbType.Char

            Case "DateTime", "datetime"
                Return SqlDbType.DateTime

            Case "Decimal", "decimal"
                Return SqlDbType.Decimal

            Case "Float", "float"
                Return SqlDbType.Float

            Case "Int", "int"
                Return SqlDbType.Int

            Case "Image", "image"
                Return SqlDbType.Image

            Case "Money", "money"
                Return SqlDbType.Money

            Case "NText", "ntext"
                Return SqlDbType.NText

            Case "NChar", "nchar"
                Return SqlDbType.NChar

            Case "NVarChar", "nvarchar"
                Return SqlDbType.NVarChar

            Case "Real", "real"
                Return SqlDbType.Real

            Case "SmallDateTime", "smalldatetime"
                Return SqlDbType.SmallDateTime

            Case "SmallInt", "smallint"
                Return SqlDbType.SmallInt

            Case "SmallMoney", "smallmoney"
                Return SqlDbType.SmallMoney

            Case "Text", "text"
                Return SqlDbType.Text

            Case "Timestamp", "timestamp"
                Return SqlDbType.Timestamp

            Case "TinyInt", "tinyint"
                Return SqlDbType.TinyInt

            Case "UniqueIdentifier", "uniqueidentifier"
                Return SqlDbType.UniqueIdentifier

            Case "VarBinary", "varbinary"
                Return SqlDbType.VarBinary

            Case "VarChar", "varchar"
                Return SqlDbType.VarChar

            Case "Variant", "variant"
                Return SqlDbType.Variant

            Case "Xml", "xml"
                Return SqlDbType.Xml

                '--------------------------------------------------添加系统数据格式

            Case "Double", "double"
                Return SqlDbType.Float

            Case "String", "string"
                Return SqlDbType.NVarChar
        End Select
    End Function
#End Region

#Region "存储过程通用处理函数"

    ''' <summary>
    ''' 存储过程通用处理函数
    ''' </summary>
    ''' <param name="inParams">输入参数数组</param>
    ''' <param name="procName">存储过程名称</param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function RunProc(ByVal inParams(,) As Object, ByVal procName As String) As Boolean
        Dim inUpBound As Integer = inParams.GetUpperBound(0)
        Dim params(inUpBound) As SqlParameter
        Dim strname As String = ""
        Dim parameter As Object
        Dim type As SqlDbType
        Dim size As Integer = 0
        For i As Integer = 0 To inUpBound
            strname = "@" & inParams(i, 0)
            parameter = inParams(i, 1)
            type = StringToSqlDbType(inParams(i, 2))
            size = CInt(inParams(i, 3))
            params(i) = MakeInParam(strname, type, size, parameter)
        Next
        Try
            RunProc(procName, params)
            Return True
        Catch ex As Exception
            MsgBox(ex.Message)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 存储过程通用处理函数
    ''' </summary>
    ''' <param name="inParams">输入参数数组</param>
    ''' <param name="outParams">输出参数数组</param>
    ''' <param name="procName">存储过程名称</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RunProc(ByVal inParams(,) As Object, ByVal outParams(,) As Object, ByVal procName As String) As Object
        Dim output(outParams.GetUpperBound(0)) As Object
        Dim inUpBound As Integer = inParams.GetUpperBound(0)
        Dim outUpBound As Integer = outParams.GetUpperBound(0)
        Dim upBound As Integer = inUpBound + outUpBound + 1
        Dim params(upBound) As SqlParameter
        Dim strname As String = ""
        Dim parameter As Object
        Dim type As SqlDbType
        Dim size As Integer
        For i As Integer = 0 To inUpBound
            strname = "@" & inParams(i, 0)
            parameter = inParams(i, 1)
            type = StringToSqlDbType("nvarchar")
            size = CInt(inParams(i, 3))
            params(i) = MakeInParam(strname, type, size, parameter)
        Next
        For i As Integer = 0 To outUpBound
            strname = "@" & outParams(i, 0)
            type = StringToSqlDbType(outParams(i, 1))
            size = outParams(i, 2)
            params(inUpBound + i + 1) = MakeOutParam(strname, type, size)
        Next
        Try
            RunProc(procName, params)
            For i As Integer = 0 To outUpBound
                output(i) = params(inUpBound + 1 + i).Value
            Next
            Return output
        Catch ex As Exception
            If output.Length > 0 Then
                For i As Integer = 0 To outUpBound
                    output(i) = params(inUpBound + 1 + i).Value
                Next
            Else
                output = Nothing
            End If        
            'MsgBox(ex.Message)
        End Try
        Return output
    End Function
#End Region

End Class