vb.net的数据库封装类代码源码

Imports System.data.SqlClient
Imports System.Data

Public Class DbAccess
Dim conn As SqlConnection = New SqlConnection
Dim cmd As SqlCommand = New SqlCommand
Public Sub New()
conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("connstring")
cmd.Connection = conn

End Sub
Public Function GetTable(ByVal sql As String) As DataTable
'conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("connstring")
'cmd.Connection = conn
cmd.CommandText = sql
Dim da As SqlDataAdapter = New SqlDataAdapter
da.SelectCommand = cmd
Dim ds As DataSet = New DataSet

da.Fill(ds)
Return ds.Tables(0)
End Function
Public Function GetState(ByVal sql As String) As Boolean
' conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("connstring")
' cmd.Connection = conn
Dim succ As Boolean = False
cmd.CommandText = sql
'open connection
OpenConnection()

succ = IIf(cmd.ExecuteNonQuery() > 0, True, False)
' CloseConnection()
Return succ

End Function
Public Function GetOne(ByVal sql As String) As String
' conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("connstring")
'cmd.Connection = conn
Dim succ As String = String.Empty
cmd.CommandText = sql
OpenConnection()

succ = Convert.ToString(cmd.ExecuteScalar())
' CloseConnection()
Return succ

End Function
Public Function CheckUser(ByVal sql As String) As Boolean
' conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("connstring")
' cmd.Connection = conn
Dim succ As Boolean = False
OpenConnection()
cmd.CommandText = sql
Dim dr As SqlDataReader = cmd.ExecuteReader
If (dr.Read()) Then
succ = True
End If
' CloseConnection()
Return succ
End Function
Public Function GetTableProc(ByVal procname As String, ByVal pa() As SqlParameter) As DataTable
'conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("connstring")
' cmd.Connection = conn
'For i As Integer = 0 To pa.Length
' cmd.Parameters.Add(pa(i))
'Next
Dim pao As SqlParameter
For Each pao In pa
cmd.Parameters.Add(pao)
Next
OpenConnection()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = procname
Dim da As SqlDataAdapter = New SqlDataAdapter
Dim ds As DataSet = New DataSet
da.SelectCommand = cmd
da.Fill(ds)

' CloseConnection()
Return ds.Tables(0)

End Function
Public Function GetStateProc(ByVal procname As String, ByVal pa() As SqlParameter) As Boolean
Dim succ As Boolean = False
'conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("connstring")
' cmd.Connection = conn
'For i As Integer = 0 To pa.Length
' cmd.Parameters.Add(pa(i))
'Next
Dim pao As SqlParameter
For Each pao In pa
cmd.Parameters.Add(pao)
Next
OpenConnection()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = procname
succ = IIf(cmd.ExecuteNonQuery() > 0, True, False)
' CloseConnection()
Return succ

End Function
Public Sub OpenConnection()
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
End Sub
Public Sub CloseConnection()
If conn.State <> ConnectionState.Closed Then
cmd = Nothing
'cmd.Dispose()
conn.Close()
conn = Nothing
'conn.Dispose()
End If
End Sub

End Class