PubliSQLDAL公共数据库访问类 For SQLServer

ClassName:PublicDAL For SQL server
' Author:Eray
' Function:基本数据操作类，提供常用的数据库连接查询执行功能。
' History:2006-10建类,2007-2更新
' 注意：
'   1 如果用此类操作数据库。各业物数据库类的SQL语句参数不再为?,而是@userid的形式
'   2 各实体业务类的获取参数getUserParameter方法也改为Sql形式
'-------------------------------------------------------------------------------------------------------------------------
Imports System.Data
Imports eraysky.Utility
Imports System.Data.SqlClient
Imports System.Configuration

Public Class PublicSQLDAL
    Public Shared ReadOnly constr1 As String = ConfigurationManager.ConnectionStrings.Item("erayskyConstr1").ToString
    Public Shared ReadOnly constr2 As String = ConfigurationManager.ConnectionStrings.Item("erayskyConstr2").ToString
    Public Shared ReadOnly constr3 As String = ConfigurationManager.ConnectionStrings.Item("erayskyConstr3").ToString

    Public Shared ReadOnly Con1 As New SqlConnection(ConStr1)
    Public Shared ReadOnly Con2 As New SqlConnection(ConStr2)
    Public Shared ReadOnly Con3 As New SqlConnection(constr3)

    '执行非查询语句，insert,update
    Public Shared Function EXESQL(ByVal conn As SqlConnection, ByVal SQL As String, ByVal parms() As SqlParameter) As Integer
        Dim result As Integer
        If conn.State = ConnectionState.Closed Then conn.Open()
        Dim cmd As SqlCommand = New SqlCommand
        Try
            PrepareCommand(cmd, conn, Nothing, CommandType.Text, SQL, parms)
            result = cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
        Catch ex As Exception
            result = -1
        End Try
        Return result
    End Function
    '执行查询语句，返回一个dataset
    Public Shared Function GETDataSet(ByVal conn As SqlConnection, ByVal SQL As String, ByVal parms() As SqlParameter) As DataSet
        Dim cmd As SqlCommand = New SqlCommand
        Dim result As New DataSet
        PrepareCommand(cmd, conn, Nothing, CommandType.Text, SQL, parms)
        Try
            Dim myadapter As SqlDataAdapter
            myadapter = New SqlDataAdapter(cmd)
            myadapter.Fill(result)
        Catch ex As Exception
        End Try
        Return result
    End Function
    '执行查询语句，返回reader。
    Public Shared Function GETReader(ByVal conn As SqlConnection, ByVal SQL As String, ByVal parms() As SqlParameter) As SqlDataReader
        Dim cmd As SqlCommand = New SqlCommand
        PrepareCommand(cmd, conn, Nothing, CommandType.Text, SQL, parms)
        Dim result As SqlDataReader
        Try
            result = cmd.ExecuteReader()
        Catch ex As Exception
            result = Nothing
        End Try
        Return result
    End Function
    '执行查询语句，返回查询结果第一行，第一列值。
    Public Shared Function GETScalar(ByVal conn As SqlConnection, ByVal SQL As String) As Object
        Dim cmd As SqlCommand = New SqlCommand
        PrepareCommand(cmd, conn, Nothing, CommandType.Text, SQL, Nothing)
        Dim val As New Object
        Try
            val = cmd.ExecuteScalar()
        Catch ex As Exception
        End Try
        Return val
    End Function
    '准备参数
    Private Shared Sub PrepareCommand(ByVal cmd As SqlCommand, ByVal conn As SqlConnection, ByVal trans As SqlTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms() As SqlParameter)
        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If
        cmd.Connection = conn
        cmd.CommandText = cmdText
        If Not trans Is Nothing Then
            cmd.Transaction = trans
        End If
        cmd.CommandType = cmdType
        If Not cmdParms Is Nothing Then
            Dim parm As SqlParameter
            For Each parm In cmdParms
                cmd.Parameters.Add(parm)
            Next
        End If
    End Sub
End Class