001.Imports System.Data
002.Imports System.Data.SqlClient
003.Imports System.Configuration
004.Public Class clsDatabase
005. Private objConn As SqlConnection
006. Private objCmd As SqlCommand
007. Private Trans As SqlTransaction
008. Private strConnString As String
009.
010. Public Sub New()
011. strConnString = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")
012. End Sub
013.
014. Public Function QueryDataReader(ByVal strSQL As String) As SqlDataReader
015. Dim dtReader As SqlDataReader
016. objConn = New SqlConnection
017. With objConn
018. .ConnectionString = strConnString
019. .Open()
020. End With
021. objCmd = New SqlCommand(strSQL, objConn)
022. dtReader = objCmd.ExecuteReader()
023. Return dtReader
024. End Function
025.
026. Public Function QueryDataSet(ByVal strSQL As String) As DataSet
027. Dim ds As New DataSet
028. Dim dtAdapter As New SqlDataAdapter
029. objConn = New SqlConnection
030. With objConn
031. .ConnectionString = strConnString
032. .Open()
033. End With
034. objCmd = New SqlCommand
035. With objCmd
036. .Connection = objConn
037. .CommandText = strSQL
038. .CommandType = CommandType.Text
039. End With
040. dtAdapter.SelectCommand = objCmd
041. dtAdapter.Fill(ds)
042. Return ds
043. End Function
044.
045. Public Function QueryDataTable(ByVal strSQL As String) As DataTable
046. Dim dtAdapter As SqlDataAdapter
047. Dim dt As New DataTable
048. objConn = New SqlConnection
049. With objConn
050. .ConnectionString = strConnString
051. .Open()
052. End With
053. dtAdapter = New SqlDataAdapter(strSQL, objConn)
054. dtAdapter.Fill(dt)
055. Return dt
056. End Function
057.
058. Public Function QueryExecuteNonQuery(ByVal strSQL As String) As Boolean
059. objConn = New SqlConnection
060. With objConn
061. .ConnectionString = strConnString
062. .Open()
063. End With
064. Try
065. objCmd = New SqlCommand
066. With objCmd
067. .Connection = objConn
068. .CommandType = CommandType.Text
069. .CommandText = strSQL
070. End With
071. objCmd.ExecuteNonQuery()
072. Return True
073. Catch ex As Exception
074. Return False
075. End Try
076. End Function
077.
078. Public Function QueryExecuteScalar(ByVal strSQL As String) As Object
079. Dim obj As Object
080. objConn = New SqlConnection
081. With objConn
082. .ConnectionString = strConnString
083. .Open()
084. End With
085. Try
086. objCmd = New SqlCommand
087. With objCmd
088. .Connection = objConn
089. .CommandType = CommandType.Text
090. .CommandText = strSQL
091. End With
092. obj = objCmd.ExecuteScalar()
093. Return obj
094. Catch ex As Exception
095. Return Nothing
096. End Try
097. End Function
098.
099. Public Function TransStart()
100. objConn = New SqlConnection
101. With objConn
102. .ConnectionString = strConnString
103. .Open()
104. End With
105. Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted)
106. End Function
107.
108. Public Function TransExecute(ByVal strSQL As String) As Boolean
109. objCmd = New SqlCommand
110. With objCmd
111. .Connection = objConn
112. .Transaction = Trans
113. .CommandType = CommandType.Text
114. .CommandText = strSQL
115. End With
116. objCmd.ExecuteNonQuery()
117. End Function
118.
119. Public Function TransRollBack()
120. Trans.Rollback()
121. End Function
122.
123. Public Function TransCommit()
124. Trans.Commit()
125. End Function
126.
127. Public Sub Close()
128. objConn.Close()
129. objConn = Nothing
130. End Sub
131.
132.End Class
