ASP.NET SQL Server Database Class (Visual Studio 2005,2008,2010 - .NET 2.0,3.5,4.0)

Imports System.Data 
Imports System.Data.SqlClient 

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class clsDatabase
    Private objConn As SqlConnection
    Private objCmd As SqlCommand
    Private Trans As SqlTransaction
    Private strConnString As String

    Public Sub New()
        strConnString = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")
    End Sub

    Public Function QueryDataReader(ByVal strSQL As String) As SqlDataReader
        Dim dtReader As SqlDataReader
        objConn = New SqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        objCmd = New SqlCommand(strSQL, objConn)
        dtReader = objCmd.ExecuteReader()
        Return dtReader '*** Return DataReader ***'
    End Function

    Public Function QueryDataSet(ByVal strSQL As String) As DataSet
        Dim ds As New DataSet
        Dim dtAdapter As New SqlDataAdapter
        objConn = New SqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        objCmd = New SqlCommand
        With objCmd
            .Connection = objConn
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        dtAdapter.SelectCommand = objCmd
        dtAdapter.Fill(ds)
        Return ds   '*** Return DataSet ***'
    End Function

    Public Function QueryDataTable(ByVal strSQL As String) As DataTable
        Dim dtAdapter As SqlDataAdapter
        Dim dt As New DataTable
        objConn = New SqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        dtAdapter = New SqlDataAdapter(strSQL, objConn)
        dtAdapter.Fill(dt)
        Return dt '*** Return DataTable ***'
    End Function

    Public Function QueryExecuteNonQuery(ByVal strSQL As String) As Boolean
        objConn = New SqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Try
            objCmd = New SqlCommand()
            With objCmd
                .Connection = objConn
                .CommandType = CommandType.Text
                .CommandText = strSQL
            End With
            objCmd.ExecuteNonQuery()
            Return True '*** Return True ***'
        Catch ex As Exception
            Return False '*** Return False ***'
        End Try
    End Function

    Public Function QueryExecuteScalar(ByVal strSQL As String) As Object
        Dim obj As Object
        objConn = New SqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Try
            objCmd = New SqlCommand()
            With objCmd
                .Connection = objConn
                .CommandType = CommandType.Text
                .CommandText = strSQL
            End With
            obj = objCmd.ExecuteScalar()  '*** Return Scalar ***'
            Return obj
        Catch ex As Exception
            Return Nothing '*** Return Nothing ***'
        End Try
    End Function

    Public Function TransStart()
        objConn = New SqlConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted)
    End Function

    Public Function TransExecute(ByVal strSQL As String) As Boolean
        objCmd = New SqlCommand()
        With objCmd
            .Connection = objConn
            .Transaction = Trans
            .CommandType = CommandType.Text
            .CommandText = strSQL
        End With
        objCmd.ExecuteNonQuery()
    End Function

    Public Function TransRollBack()
        Trans.Rollback()
    End Function

    Public Function TransCommit()
        Trans.Commit()
    End Function

    Public Sub Close()
        objConn.Close()
        objConn = Nothing
    End Sub

End Class

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page
    Dim clsDB As New clsDatabase

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Call myDataReader()
        Call myDataSet()
        Call myDataTable()
        Call myQueryExecuteScalar()
        Call myExecuteNonQuery()
        Call myExecuteTransaction()
    End Sub

    '*** DataReader ***'
    Sub myDataReader()
        Dim strSQL As String
        Dim dtReader As SqlDataReader
        strSQL = "SELECT * FROM customer "
        dtReader = clsDB.QueryDataReader(strSQL)
        Me.myGridView1.DataSource = dtReader
        Me.myGridView1.DataBind()

        '*** Bind Rows ***'
        'If dtReader.HasRows = True Then
        '    Me.lblCustomerID.Text = dtReader.Item("CustomerID")
        '    Me.lblName.Text = dtReader.Item("Name")
        '    Me.lblEmail.Text = dtReader.Item("Email")
        '    Me.lblCountryCode.Text = dtReader.Item("CountryCode")
        '    Me.lblBudget.Text = dtReader.Item("Budget")
        '    Me.lblUsed.Text = dtReader.Item("Used")
        'End If
        clsDB.Close()
    End Sub

    '*** DataSet ***'
    Sub myDataSet()
        Dim strSQL As String
        Dim ds As DataSet
        strSQL = "SELECT * FROM customer "
        ds = clsDB.QueryDataSet(strSQL)
        Me.myGridView2.DataSource = ds.Tables(0).DefaultView
        Me.myGridView2.DataBind()

        '*** Bind Rows ***'
        'If ds.Tables(0).Rows.Count > 0 Then
        '    Me.lblCustomerID.Text = ds.Tables(0).Rows(0)("CustomerID")
        '    Me.lblName.Text = ds.Tables(0).Rows(0)("Name")
        '    Me.lblEmail.Text = ds.Tables(0).Rows(0)("Email")
        '    Me.lblCountryCode.Text = ds.Tables(0).Rows(0)("CountryCode")
        '    Me.lblBudget.Text = ds.Tables(0).Rows(0)("Budget")
        '    Me.lblUsed.Text = ds.Tables(0).Rows(0)("Used")
        'End If
        clsDB.Close()
    End Sub

    '*** DataTable ***'
    Sub myDataTable()
        Dim strSQL As String
        Dim dt As DataTable
        strSQL = "SELECT * FROM customer "
        dt = clsDB.QueryDataTable(strSQL)
        Me.myGridView3.DataSource = dt
        Me.myGridView3.DataBind()

        '*** Bind Rows ***'
        If dt.Rows.Count > 0 Then
            Me.lblCustomerID.Text = dt.Rows(0)("CustomerID")
            Me.lblName.Text = dt.Rows(0)("Name")
            Me.lblEmail.Text = dt.Rows(0)("Email")
            Me.lblCountryCode.Text = dt.Rows(0)("CountryCode")
            Me.lblBudget.Text = dt.Rows(0)("Budget")
            Me.lblUsed.Text = dt.Rows(0)("Used")
        End If
        clsDB.Close()
    End Sub

    '*** Execute Scalar ***'
    Sub myQueryExecuteScalar()
        Dim strSQL As String
        strSQL = "SELECT MAX(Budget) FROM customer "
        Me.lblText.Text = clsDB.QueryExecuteScalar(strSQL)
        clsDB.Close()
    End Sub

    '*** ExecuteNonQuery ***'
    Sub myExecuteNonQuery()
        Dim strSQL1, strSQL2, strSQL3 As String

        '*** Insert ***'
        strSQL1 = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
        " VALUES('C005','Weerachai Nukitram','[email protected]','TH','200000','100000')"
        If clsDB.QueryExecuteNonQuery(strSQL1) = True Then
            '*** Condition Success ***'
        Else
            '*** Condition Error ***'
        End If
        clsDB.Close()

        '*** Update ***'
        strSQL2 = "UPDATE customer SET Budget = '3000000' WHERE CustomerID = 'C005' "
        If clsDB.QueryExecuteNonQuery(strSQL2) = True Then
            '*** Condition Success ***'
        Else
            '*** Condition Error ***'
        End If
        clsDB.Close()

        '*** Delete ***'
        strSQL3 = "DELETE FROM customer WHERE CustomerID = 'C005' "
        If clsDB.QueryExecuteNonQuery(strSQL3) = True Then
            '*** Condition Success ***'
        Else
            '*** Condition Error ***'
        End If
        clsDB.Close()
    End Sub

    '*** Execute Transaction ***'
    Sub myExecuteTransaction()
        Dim strSQL1, strSQL2, strSQL3 As String

        '*** Start Transaction ***'
        clsDB.TransStart()

        Try
            '*** Insert ***'
            strSQL1 = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
            " VALUES('C005','Weerachai Nukitram','[email protected]','TH','200000','100000')"
            clsDB.TransExecute(strSQL1) '*** Execute Query 1 ***'

            '*** Update ***'
            strSQL2 = "UPDATE customer SET Budget = '3000000' WHERE CustomerID = 'C005' "
            clsDB.TransExecute(strSQL2) '*** Execute Query 2 ***

            '*** Delete ***'
            strSQL3 = "DELETE FROM customer WHERE CustomerID = 'C005' "
            clsDB.TransExecute(strSQL3) '*** Execute Query 3 ***

            '*** Commit Transaction ***'
            clsDB.TransCommit()

        Catch ex As Exception
            '*** RollBack Transaction ***'
            clsDB.TransRollBack()
        End Try
        clsDB.Close()
    End Sub

End Class