ASP.NET MySQL Database Class (Visual Studio .Net 2003 - .NET 1.1) |
ASP.NET MySQL Database Class (Visual Studio .Net 2003 - .NET 1.1) สำหรับ Database Class ตัวนี้เป็นของ Framework 1.1 ซึ่งผมได้พัฒนาบน Visual Studio .Net 2003 เหมาะสมกับผู้ที่ต้องการสร้าง Database Class ไว้สำหรับจัดการฐานข้อมูล MySQL ซึ่งผมได้ทำการออกแบบไว้มีฟังก์ชั่นที่ครบครับ สามารถทำการ เพิ่ม/ลบ/แก้ไข/อ่าน ข้อมูลจากฐานข้อมูลได้อย่างง่ายดายครับ
Instance NameSpace
VB.NETImports System.Data
Imports MySql.Data.MySqlClient
ASP.NET & MySql.Data.MySqlClient
Language Code : VB.NET || C#
clsDatabase.vb
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.Configuration
Public Class clsDatabase
Private objConn As MySqlConnection
Private objCmd As MySqlCommand
Private Trans As MySqlTransaction
Private strConnString As String
Public Sub New()
strConnString = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")
End Sub
Public Function QueryDataReader(ByVal strSQL As String) As MySqlDataReader
Dim dtReader As MySqlDataReader
objConn = New MySqlConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
objCmd = New MySqlCommand(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 MySqlDataAdapter
objConn = New MySqlConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
objCmd = New MySqlCommand
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 MySqlDataAdapter
Dim dt As New DataTable
objConn = New MySqlConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
dtAdapter = New MySqlDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
Return dt '*** Return DataTable ***'
End Function
Public Function QueryExecuteNonQuery(ByVal strSQL As String) As Boolean
objConn = New MySqlConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
Try
objCmd = New MySqlCommand
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 MySqlConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
Try
objCmd = New MySqlCommand
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 MySqlConnection
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 MySqlCommand
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
![](/images/adv.jpg)
Default.aspx.vb
Imports System.Data
Imports MySql.Data.MySqlClient
Public Class _Default
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Protected WithEvents lblHeaderCustomerID As System.Web.UI.WebControls.Label
Protected WithEvents lblCustomerID As System.Web.UI.WebControls.Label
Protected WithEvents lblHeaderName As System.Web.UI.WebControls.Label
Protected WithEvents lblName As System.Web.UI.WebControls.Label
Protected WithEvents lblHeaderEmail As System.Web.UI.WebControls.Label
Protected WithEvents lblEmail As System.Web.UI.WebControls.Label
Protected WithEvents lblHeaderCountryCode As System.Web.UI.WebControls.Label
Protected WithEvents lblCountryCode As System.Web.UI.WebControls.Label
Protected WithEvents lblHeaderBudget As System.Web.UI.WebControls.Label
Protected WithEvents lblBudget As System.Web.UI.WebControls.Label
Protected WithEvents lblHeaderUsed As System.Web.UI.WebControls.Label
Protected WithEvents lblUsed As System.Web.UI.WebControls.Label
Protected WithEvents lblText As System.Web.UI.WebControls.Label
Protected WithEvents myData1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents myDataGrid2 As System.Web.UI.WebControls.DataGrid
Protected WithEvents myDataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents myDataGrid3 As System.Web.UI.WebControls.DataGrid
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Dim clsDB As New clsDatabase
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Call myDataReader()
Call myDataSet()
Call myDataTable()
Call myQueryExecuteScalar()
Call myExecuteNonQuery()
Call myExecuteTransaction()
End Sub
'*** DataReader ***'
Sub myDataReader()
Dim strSQL As String
Dim dtReader As MySqlDataReader
strSQL = "SELECT * FROM customer "
dtReader = clsDB.QueryDataReader(strSQL)
Me.myDataGrid1.DataSource = dtReader
Me.myDataGrid1.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.myDataGrid2.DataSource = ds.Tables(0).DefaultView
Me.myDataGrid2.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.myDataGrid3.DataSource = dt
Me.myDataGrid3.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
![](/images/adv.jpg)
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน Source Code 2.0 ของทีมงานไทยครีเอท
|
|
|
By : |
ThaiCreate.Com Team (บทความเป็นลิขสิทธิ์ของเว็บไทยครีเอทห้ามนำเผยแพร่ ณ เว็บไซต์อื่น ๆ) |
|
Score Rating : |
![](/images/resource/startrue.gif) ![](/images/resource/startrue.gif) |
|
|
Create/Update Date : |
2008-10-26 22:59:31 /
2017-03-29 10:17:30 |
|
Download : |
|
|
Sponsored Links / Related |
|
|
|
|
|
|
|