BoyLee's Blog

Imports System.Web
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Namespace BOYLEE
    Public Class clsAdoNet
        Private SqlCon As SqlConnection
        Private SqlCmd As SqlCommand
        Private SqlDta As SqlDataAdapter
        Private SqlTrs As SqlTransaction

        Public Sub New()
            Try
                Me.SqlCon = New SqlConnection
                Me.SqlCon.ConnectionString = getConSql()
            Catch
                HttpContext.Current.Response.Write("数据库连接失败")
                HttpContext.Current.Response.End()
            End Try
        End Sub

        Private Function getConSql() As String
            Dim strCon As String
            Dim strDataSource As String
            Dim strDataBase As String
            Dim strUser As String
            Dim strPassword As String
            Dim strConnection As New System.Text.StringBuilder
            strDataSource = ConfigurationSettings.AppSettings("DB_SERVER")
            strDataBase = ConfigurationSettings.AppSettings("DB_DATABASE")
            strUser = ConfigurationSettings.AppSettings("DB_USER")
            strPassword = ConfigurationSettings.AppSettings("DB_PASSWORD")
            With strConnection
                .Append("server=")
                .Append(strDataSource)
                .Append(";uid=")
                .Append(strUser)
                .Append(";pwd=")
                .Append(strPassword)
                .Append(";database=")
                .Append(strDataBase)
            End With
            strCon = strConnection.ToString
            Return strCon
        End Function

        Public Function ExecuteSQL(ByVal strSQL As String) As Integer
            Dim intRows As Integer
            Me.SqlCmd = New SqlCommand
            Me.SqlCmd.CommandText = strSQL
            Try
                Me.SqlCon.Open()
                Me.SqlCmd.Connection = Me.SqlCon
                intRows = Me.SqlCmd.ExecuteNonQuery()
            Catch
                HttpContext.Current.Response.Write("SQL语句执行不成功!")
                HttpContext.Current.Response.End()
                intRows = -1
            Finally
                If (Me.SqlCon.State.ToString.Equals("Open")) Then
                    Me.SqlCon.Close()
                End If
            End Try
            Return intRows
        End Function

        Public Function SQLBeginTransaction(ByVal strSQL As String()) As Boolean
            Dim tmpFlag As Boolean
            Dim intI As Integer
            Dim strLen As Integer

            strLen = strSQL.Length
            Me.SqlCmd = New SqlCommand
            Try
                Me.SqlCon.Open()
                Me.SqlTrs = Me.SqlCon.BeginTransaction()
                Me.SqlCmd.Connection = Me.SqlCon
                Me.SqlCmd.Transaction = Me.SqlTrs
                For intI = 0 To strLen - 1
                    Me.SqlCmd.CommandText = strSQL(intI)
                    Me.SqlCmd.ExecuteNonQuery()
                Next
                Me.SqlTrs.Commit()
                tmpFlag = True
            Catch
                If Not Me.SqlTrs Is Nothing Then
                    Me.SqlTrs.Rollback()
                End If
                HttpContext.Current.Response.Write("SQL语句执行不成功!")
                HttpContext.Current.Response.End()
                tmpFlag = False
            Finally
                If (Me.SqlCon.State.ToString.Equals("Open")) Then
                    Me.SqlCon.Close()
                End If
            End Try
            Return tmpFlag
        End Function

        Public Function getDataSetFromSQL(ByVal strSQL As String) As DataSet
            Dim dSet As New DataSet
            Try
                Me.SqlCon.Open()
                Me.SqlDta = New SqlDataAdapter(strSQL, SqlCon)
                Me.SqlDta.Fill(dSet, 0)
            Catch
                dSet = Nothing
                HttpContext.Current.Response.Write("数据查询失败!")
                HttpContext.Current.Response.End()
            Finally
                If (Me.SqlCon.State.ToString.Equals("Open")) Then
                    Me.SqlCon.Close()
                End If
            End Try
            Return dSet
        End Function

        Public Function getDataTableFromSQL(ByVal strSQL As String) As DataTable
            Dim dSet As New DataSet
            Dim dTbl As New DataTable
            Try
                Me.SqlCon.Open()
                Me.SqlDta = New SqlDataAdapter(strSQL, SqlCon)
                Me.SqlDta.Fill(dSet, "TempTable")
                dTbl = dSet.Tables("TempTable")
            Catch
                dSet = Nothing
                dTbl = Nothing
                HttpContext.Current.Response.Write("数据查询失败!")
                HttpContext.Current.Response.End()
            Finally
                If (Me.SqlCon.State.ToString.Equals("Open")) Then
                    Me.SqlCon.Close()
                End If
            End Try
            Return dTbl
        End Function

        Public Function getDataReaderFromSQL(ByVal strSQL As String) As SqlDataReader
            Dim DataRead As SqlDataReader
            Me.SqlCmd = New SqlCommand
            Me.SqlCmd.CommandText = strSQL
            Try
                Me.SqlCon.Open()
                Me.SqlCmd.Connection = Me.SqlCon
                DataRead = Me.SqlCmd.ExecuteReader
            Catch
                HttpContext.Current.Response.Write("数据查询失败!")
                HttpContext.Current.Response.End()
            Finally
                If (Me.SqlCon.State.ToString.Equals("Open")) Then
                    Me.SqlCon.Close()
                End If
            End Try
            Return DataRead
        End Function

        Public Function CloseConnection() As Boolean
            Dim SqlConStatus As Boolean = False
            If (Me.SqlCon.State.ToString.Equals("Open")) Then
                Me.SqlCon.Close()
                SqlConStatus = True
            End If
            Return SqlConStatus
        End Function
    End Class
End Namespace