Bogart BogartAutoCode.vb

Imports System.Data.SqlClient
Imports System.Data

Public Class BogartAutoCodeDataBase

    Private Conn As SqlClient.SqlConnection = SqlConnect
    Sub New()
        'Conn = New BogartAutoCodeConnection
    End Sub

    ' 保存的错误！
    Private _StrError As String = ""
    Friend ReadOnly Property StrError() As String
        Get
            Return _StrError
        End Get
    End Property

#Region "执行命令，返回影响的行数"
    '执行但行命令
    Function ExecuteNonQuery(ByVal SqlCommandText As String, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As Integer
        Return ExecuteNonQuery(SqlCommandText, Nothing, SqlCommandType)
    End Function

    '执行但行命令
    Function ExecuteNonQuery(ByVal SqlCommandText As String, ByVal SqlParameters As ArrayList, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As Integer
        Dim ret As Integer = -1
        Try
            Dim SqlComm As SqlCommand = CreateSqlComm(SqlCommandText, SqlParameters, SqlCommandType)
            If SqlComm Is Nothing Then
                Return 0
            End If
            ret = DataAccess.ExectueCmd(SqlComm)
            Return ret
        Catch ex As System.Exception
            ret = -1
            _StrError = ex.Message & vbCrLf & ex.StackTrace
        End Try
        Return ret
    End Function
#End Region

#Region "执行命令，返回影响的行数"
    '执行但行命令
    Function ExecuteBool(ByVal SqlCommandText As String, ByVal SqlParameters As ArrayList, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As Boolean
        Dim ret As Integer = 0
        ret = ExecuteNonQuery(SqlCommandText, SqlParameters, SqlCommandType)
        If ret >= 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    '执行但行命令
    Function ExecuteBool(ByVal SqlCommandText As String, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As Boolean
        Return ExecuteNonQuery(SqlCommandText, Nothing, SqlCommandType) >= 0 And Len(Me.StrError)
    End Function

#End Region

#Region "执行命令，返回单值"
    '执行但行命令
    Function ExecuteScalar(ByVal SqlCommandText As String, ByVal SqlParameters As ArrayList, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As Object

        Dim SqlComm As SqlCommand = CreateSqlComm(SqlCommandText, SqlParameters, SqlCommandType)
        If SqlComm Is Nothing Then
            Return Nothing
        End If
        Try
            Return SqlComm.ExecuteScalar
        Catch ex As Exception
            _StrError = ex.Message & vbCrLf & ex.StackTrace
        End Try

    End Function
    '执行但行命令
    Function ExecuteScalar(ByVal SqlCommandText As String, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As Object
        Return ExecuteScalar(SqlCommandText, Nothing, SqlCommandType)
    End Function
#End Region

#Region "加载数据集合"
    Function LoadDateSet(ByVal SqlCommandText As String, ByVal SqlParameters As ArrayList, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As DataSet
        Dim SQLCMD As SqlClient.SqlCommand = CreateSqlComm(SqlCommandText, SqlParameters, SqlCommandType)
        Return DataAccess.ExectueSQL(SQLCMD)
    End Function
    Function LoadDateSet(ByVal SqlCommandText As String, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As DataSet
        Return LoadDateSet(SqlCommandText, Nothing, SqlCommandType)
    End Function
#End Region

#Region "创建SqlCommand对象"
    '创建命令对象
    Function CreateSqlComm(ByVal SqlCommandText As String, ByVal SqlParameters As ArrayList, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As SqlCommand
        Dim comm As New SqlCommand

        If Not OpenConn(Conn) Then
            Me._StrError = "Sorry, system not found DataServer or DataServer Closed!"
            Return Nothing
        End If
        With comm
            .Connection = Conn
            .CommandType = SqlCommandType
            .CommandText = SqlCommandText
            .CommandTimeout = 5
        End With
        AddParameters(comm, SqlParameters)
        Return comm
    End Function
    Function CreateSqlComm(ByVal SqlCommandText As String, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As SqlCommand
        Return CreateSqlComm(SqlCommandText, Nothing, SqlCommandType)
    End Function
    Function CreateSqlComm()
        Return CreateSqlComm("", Nothing, CommandType.Text)
    End Function

#End Region

#Region "处理参数"

    '这里处理参数
    Sub AddParameters(ByVal SqlComm As SqlCommand, ByVal SqlParameters As ArrayList)
        If Not SqlParameters Is Nothing AndAlso SqlParameters.Count > 0 Then
            For Each Sqlp As SqlParameter In SqlParameters
                SqlComm.Parameters.Add(Sqlp)
            Next
        End If
    End Sub

#End Region

#Region "创建Datareader对象"
    '创建Datareader对象
    Function CreateSqlDataReader(ByVal SqlCommandText As String, ByVal SqlParameters As ArrayList, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As SqlDataReader
        Dim comm As SqlCommand = Me.CreateSqlComm(SqlCommandText, SqlParameters, SqlCommandType)
        If comm Is Nothing Then
            Return Nothing
        End If
        Return comm.ExecuteReader(CommandBehavior.CloseConnection)
    End Function
    Function CreateSqlDataReader(ByVal SqlCommandText As String, Optional ByVal SqlCommandType As CommandType = CommandType.Text) As SqlDataReader
        Return Me.CreateSqlDataReader(SqlCommandText, Nothing, SqlCommandType)
    End Function
#End Region

End Class