sqldataaccess.vb

系统概要: 本系统采用VB.NET开发. 开发平台:Windows XP Professional SP2 (English Version) 开发环境:Visual Studio .NET

'
'    Copyright(C)2006,济南大学材料科学与工程学院
'    All right reserved.
'
'    文件名称:SqlDataAccess.vb
'    文件标识:
'    摘    要:SQL数据库引擎代码
'
'    当前版本:1.0.0
'    作    者:梁  海
'    完成日期:2006-10-3
'
'    取代版本:
'    原作者  :
'    完成日期:
'
'    修改历史:
'

Imports System.IO
Imports System.Data.SqlClient

Public Class SqlDataAccess

    Private _ConnectionString As String         '连接字符串
    Private _Sql As String                      'SQL命令
    Private _Connection As SqlConnection        'SQL数据库连接

    '  
    '    构造方法
    '  
    Sub New()

        _ConnectionString = getParameters("SQL")
        _Sql = Nothing
        _Connection = New SqlConnection(_ConnectionString)

    End Sub

    '  
    '    构造方法
    '    摘    要:参数:strSql是SQL查询语句
    '    
    Sub New(ByVal strSql As String)

        _ConnectionString = getParameters("SQL")
        _Sql = strSql
        _Connection = New SqlConnection(_ConnectionString)

    End Sub

    '  
    '    构造方法
    '    摘    要:参数:strSql是SQL查询语句
    '             参数:ConnectionString是数据库连接字符串
    '    
    Sub New(ByVal strSql As String, ByVal ConnectionString As String)

        _ConnectionString = ConnectionString
        _Sql = strSql
        _Connection = New SqlConnection(_ConnectionString)

    End Sub

    '  
    '    构造方法
    '    摘    要:参数:strSql是SQL查询语句
    '             参数:Connection是数据库连接
    '    
    Sub New(ByVal strSql As String, ByVal Connection As SqlConnection)

        _Sql = strSql
        _Connection = Connection
        _ConnectionString = Connection.ConnectionString()

    End Sub

    '
    '    属性名称:Sql()
    '    摘    要:执行数据库操作的SQL语句
    '  
    Public Property Sql() As String
        Get
            Return _Sql
        End Get
        Set(ByVal Value As String)
            _Sql = Value
        End Set
    End Property

    '
    '    属性名称:ConnectString()
    '    摘    要:SQL数据库连接字符串
    ' 
    Public Property ConnectionString() As String
        Get
            Return _ConnectionString
        End Get
        Set(ByVal Value As String)
            _ConnectionString = Value
        End Set
    End Property

    '
    '    属性名称:Connection()
    '    摘    要:SQL数据库连接
    ' 
    Public Property Connection() As SqlConnection
        Get
            Return getConnection()
        End Get
        Set(ByVal Value As SqlConnection)
            _Connection = Value
        End Set
    End Property

    '
    '    函数名称:getParameters(ByVal s As String) As String
    '    摘    要:私有方法.获取Web.config中设置的键值
    ' 
    Private Function getParameters(ByVal s As String) As String

        Return System.Configuration.ConfigurationSettings.AppSettings(s)

    End Function

    '
    '    函数名称:getConnection()
    '    摘    要:私有方法.创建数据库连接
    '  
    Private Function getConnection() As SqlConnection

        Return New SqlConnection(_ConnectionString)

    End Function

    '
    '    函数名称:Command()
    '    摘    要:私有方法.创建与_Sql相关的SQL命令
    ' 
    Private Function Command() As SqlCommand

        Return New SqlCommand(_Sql, getConnection())

    End Function

    '
    '    函数名称:DataAdapter()
    '    摘    要:私有方法.创建与_Sql相关的适配器
    '  
    Private Function DataAdapter() As SqlDataAdapter

        Return New SqlDataAdapter(Command())

    End Function

    '
    '    方法名称:DataSet()
    '    摘    要:执行_Sql并返回一个数据集
    '  
    Public Function DataSet() As DataSet

        Dim oAda As SqlDataAdapter
        Dim ds As New DataSet

        oAda = DataAdapter()
        Try
            oAda.Fill(ds)
        Catch ex As Exception
            WriteLog("Occure Error on DataSet() " & ex.ToString)
            Return Nothing
            Throw New Exception("Occur Error on Excute DataSet()" & _
                                 "with the SQL string " & _Sql & ex.ToString)
        End Try

        Return ds

    End Function

    '
    '    方法名称:DataView()
    '    摘    要:执行_Sql并返回一个数据视图
    '  
    Public Function DataView() As DataView

        Return DataSet().Tables(0).DefaultView

    End Function


    '
    '    方法名称:DataTable()
    '    摘    要:执行_Sql并返回一个数据表
    '
    Public Function DataTable() As DataTable

        Return DataSet().Tables(0)

    End Function

    '
    '    方法名称:DataReader()
    '    摘    要:执行_Sql并返回一个数据阅读器
    '
    Public Function DataReader() As SqlDataReader

        Dim oCmd As SqlCommand
        Dim oReader As SqlDataReader

        oCmd = Command()
        Try
            If oCmd.Connection.State = ConnectionState.Closed Then
                oCmd.Connection.Open()
            End If
            oReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            WriteLog("Occure Error on DataReader() " & ex.ToString)
            Return Nothing
            Throw New Exception("Occur Error on Excute DataReader()" & _
                                 "with the SQL string " & _Sql & ex.ToString)
        End Try

        Return oReader

    End Function

    '
    '    方法名称:ExecuteNonQuery()
    '    摘    要:执行_Sql并返回一个受影响的记录数
    '
    Public Function ExecuteNonQuery() As Integer

        Dim oCmd As SqlCommand
        Dim intCount As Integer

        oCmd = Command()
        Try
            If oCmd.Connection.State = ConnectionState.Closed Then
                oCmd.Connection.Open()
            End If
            intCount = oCmd.ExecuteNonQuery()
        Catch ex As Exception
            WriteLog("Occure Error on ExecuteNonQuery() " & ex.ToString)
            Return Nothing
            Throw New Exception("Occur Error on Excute ExecuteNonQuery()" & _
                                 "with the SQL string " & _Sql & ex.ToString)
        Finally
            If oCmd.Connection.State = ConnectionState.Open Then
                oCmd.Connection.Close()
            End If
        End Try

        Return intCount

    End Function

    '
    '    方法名称:ExecuteScalar()
    '    摘    要:执行_Sql并返回首行首列
    '
    Public Function ExecuteScalar() As Object

        Dim oCmd As SqlCommand
        Dim oData As Object

        oCmd = Command()
        Try
            If oCmd.Connection.State = ConnectionState.Closed Then
                oCmd.Connection.Open()
            End If
            oData = oCmd.ExecuteScalar()
        Catch ex As Exception
            WriteLog("Occure Error on ExecuteScalar() " & ex.ToString)
            Return Nothing
            Throw New Exception("Occur Error on Excute ExecuteScalar()" & _
                                 "with the SQL string " & _Sql & ex.ToString)

        Finally
            If oCmd.Connection.State = ConnectionState.Open Then
                oCmd.Connection.Close()
            End If
        End Try

        Return oData

    End Function


    '    函数名称:WriteLog(ByVal strError As String)
    '    摘    要:参数:strError 应用程序出错记录
    '             将应用程序出错信息写到web.config中SQL_LOG指定的系统出错日志
    '
    Public Sub WriteLog(ByVal strError As String)

        Dim fs As FileStream
        Dim rw As StreamWriter
        fs = New FileStream(getParameters("SQL_LOG"), FileMode.Append, FileAccess.Write)
        rw = New StreamWriter(fs, System.Text.Encoding.GetEncoding(getParameters("ENCODING")))

        Try
            rw.WriteLine(Now.ToString & Chr(10) & "        " & strError)
        Catch ex As Exception
            'Throw New Exception("Occur Error On Write SQL Log!")
        Finally
            rw.Flush()
            rw.Close()
            fs.Close()
            rw = Nothing
            fs = Nothing
        End Try

    End Sub

    '
    '    析构函数
    '
    Protected Overrides Sub Finalize()

        _Sql = Nothing
        _ConnectionString = Nothing
        _Connection.Dispose()

    End Sub

End Class