|
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
|
