ASP.NET(vb.net) & Microsoft Access Database Class (Visual Studio 2005,2008,2010 - .NET 2.0,3.5,4.0) - This is example scripts how to use ASP.NET and Microsoft Access (.mdb) database on Visual Studio 2005,2008,2010 (.NET 2.0,3.5,4.0)
ShotDev Focus:
- ASP.NET(vb.net) & Microsoft Access Database Class (Visual Studio 2005,2008,2010 - .NET 2.0,3.5,4.0)
Example
clsDatabase.vb
- Imports Microsoft.VisualBasic
- Imports System.Data
- Imports System.Data.OleDb
- Imports System.Configuration
- Public Class clsDatabase
- Private objConn As OleDbConnection
- Private objCmd As OleDbCommand
- Private Trans As OleDbTransaction
- Private strConnString As String
- Public Sub New()
- strConnString = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")
- End Sub
- Public Function QueryDataReader(ByVal strSQL As String) As OleDbDataReader
- Dim dtReader As OleDbDataReader
- objConn = New OleDbConnection
- With objConn
- .ConnectionString = strConnString
- .Open()
- End With
- objCmd = New OleDbCommand(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 OleDbDataAdapter
- objConn = New OleDbConnection
- With objConn
- .ConnectionString = strConnString
- .Open()
- End With
- objCmd = New OleDbCommand
- 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 OleDbDataAdapter
- Dim dt As New DataTable
- objConn = New OleDbConnection
- With objConn
- .ConnectionString = strConnString
- .Open()
- End With
- dtAdapter = New OleDbDataAdapter(strSQL, objConn)
- dtAdapter.Fill(dt)
- Return dt '*** Return DataTable ***'
- End Function
- Public Function QueryExecuteNonQuery(ByVal strSQL As String) As Boolean
- objConn = New OleDbConnection
- With objConn
- .ConnectionString = strConnString
- .Open()
- End With
- Try
- objCmd = New OleDbCommand()
- 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 OleDbConnection
- With objConn
- .ConnectionString = strConnString
- .Open()
- End With
- Try
- objCmd = New OleDbCommand()
- 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 OleDbConnection
- 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 OleDbCommand()
- 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
Default.aspx.vb
- Imports System.Data
- Imports System.Data.OleDb
- 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 OleDbDataReader
- 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','webmaster@thaicreate.com','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','webmaster@thaicreate.com','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
Screenshot
2undeceive…
…