ZengCode.Com (The Thai Php Framework)  


Home   Download   Manual   About us    

Facebook   


MAIN MENU
News
Php Tips
Android Programming
Design Pattern By PHP
C# using Linq น่าใช้จริงๆ
C# Tips & Technique
C# Design Pattern
Linux Quick Tips
Java & JavaScript Tips
Database & SQL
ZengCode Framework Guide
Zeng Code Code
Programming
IPhone (Tips and Trick)

Download เอกสารที่น่าสนใจ
     (C#) ASP.NET MySQL Database Class   (2009-11-05)

  (C#) ASP.NET MySQL Database Class ตัวอย่างนี้ผมได้ออกแบบ Database Class ไว้สำหรับการจัดการกับฐานข้อมูล ASP กับ MySQL โดยใช้ NameSpace ของ MySql.Data.MySqlClient ซึ่งมีฟังก์ชั่นครบในการใช้งานครับ ไม่ว่าจะเป็นการ เพิ่ม/ลบ/แก้ไข/อ่าน สำหรับตัวอย่างนี้ผมเขียนบน Framework 2.0,3.5 ครับ

Instance NameSpace

1.Using System.Data;
2.Using MySql.Data.MySqlClient;



ASP.NET & MySql.Data.MySqlClient

http://www.thaicreate.com/asp.net/asp.net-mysql.data.mysqlclient.html



Language Code : VB.NET || C#

Web.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="ConnectionString" value="Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false"/>
</appSettings>
</configuration>




App_Code/clsDatabase.cs

view source
print?
001.using System;
002.using System.Data;
003.using System.Configuration;
004.using System.Collections;
005.using System.Web;
006.using System.Web.Security;
007.using System.Web.UI;
008.using System.Web.UI.WebControls;
009.using System.Web.UI.WebControls.WebParts;
010.using System.Web.UI.HtmlControls;
011.using MySql.Data.MySqlClient;
012. 
013.public partial class clsDatabase : System.Web.UI.Page
014.{
015.    private MySqlConnection objConn;
016.    private MySqlCommand objCmd;
017.    private MySqlTransaction Trans;
018.    private String strConnString;
019. 
020.    public clsDatabase()
021.    {
022.        strConnString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
023.    }
024. 
025.    public MySqlDataReader QueryDataReader(String strSQL)
026.    {
027.        MySqlDataReader dtReader;
028.        objConn = new MySqlConnection();
029.        objConn.ConnectionString = strConnString;
030.        objConn.Open();
031. 
032.        objCmd = new MySqlCommand(strSQL, objConn);
033.        dtReader = objCmd.ExecuteReader();
034.        return dtReader; //*** Return DataReader ***//
035.    }
036. 
037.    public DataSet QueryDataSet(String strSQL)
038.    {
039.        DataSet ds = new DataSet();
040.        MySqlDataAdapter dtAdapter = new MySqlDataAdapter();
041.        objConn = new MySqlConnection();
042.        objConn.ConnectionString = strConnString;
043.        objConn.Open();
044. 
045.        objCmd = new MySqlCommand();
046.        objCmd.Connection = objConn;
047.        objCmd.CommandText = strSQL;
048.        objCmd.CommandType = CommandType.Text;
049. 
050.        dtAdapter.SelectCommand = objCmd;
051.        dtAdapter.Fill(ds);
052.        return ds;   //*** Return DataSet ***//
053.    }
054. 
055.    public DataTable QueryDataTable(String strSQL)
056.    {
057.        MySqlDataAdapter dtAdapter;
058.        DataTable dt = new DataTable();
059.        objConn = new MySqlConnection();
060.        objConn.ConnectionString = strConnString;
061.        objConn.Open();
062. 
063.        dtAdapter = new MySqlDataAdapter(strSQL, objConn);
064.        dtAdapter.Fill(dt);
065.        return dt; //*** Return DataTable ***//
066.    }
067. 
068.    public Boolean QueryExecuteNonQuery(String strSQL)
069.    {
070.        objConn = new MySqlConnection();
071.        objConn.ConnectionString = strConnString;
072.        objConn.Open();
073. 
074.        try
075.        {
076.            objCmd = new MySqlCommand();
077.            objCmd.Connection = objConn;
078.            objCmd.CommandType = CommandType.Text;
079.            objCmd.CommandText = strSQL;
080. 
081.            objCmd.ExecuteNonQuery();
082.            return true; //*** Return True ***//
083.        }
084.        catch (Exception)
085.        {
086.            return false; //*** Return False ***//
087.        }
088.    }
089. 
090. 
091.    public Object QueryExecuteScalar(String strSQL)
092.    {
093.        Object obj;
094.        objConn = new MySqlConnection();
095.        objConn.ConnectionString = strConnString;
096.        objConn.Open();
097. 
098.        try
099.        {
100.            objCmd = new MySqlCommand();
101.            objCmd.Connection = objConn;
102.            objCmd.CommandType = CommandType.Text;
103.            objCmd.CommandText = strSQL;
104. 
105.            obj = objCmd.ExecuteScalar();  //*** Return Scalar ***//
106.            return obj;
107.        }
108.        catch (Exception)
109.        {
110.            return null; //*** Return Nothing ***//
111.        }
112.    }
113. 
114.    public void TransStart()
115.    {
116.        objConn = new MySqlConnection();
117.        objConn.ConnectionString = strConnString;
118.        objConn.Open();
119.        Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted);
120.    }
121. 
122. 
123.    public void TransExecute(String strSQL)
124.    {
125.        objCmd = new MySqlCommand();
126.        objCmd.Connection = objConn;
127.        objCmd.Transaction = Trans;
128.        objCmd.CommandType = CommandType.Text;
129.        objCmd.CommandText = strSQL;
130.        objCmd.ExecuteNonQuery();
131.    }
132. 
133. 
134.    public void TransRollBack()
135.    {
136.        Trans.Rollback();
137.    }
138. 
139.    public void TransCommit()
140.    {
141.        Trans.Commit();
142.    }
143. 
144.    public void Close()
145.    {
146.        objConn.Close();
147.        objConn = null;
148.    }
149.}




AspNetDatabase.aspx

view source
print?
001.<%@ Import Namespace="System.Data"%>
002.<%@ Import Namespace="MySql.Data.MySqlClient"%>
003.<%@ Page Language="C#" Debug="true"%>
004. 
005.<script runat="server">
006.     
007.    clsDatabase clsDB = new clsDatabase();
008.    void Page_Load(object sender, EventArgs e)
009.    {
010.        myDataReader();
011.        myDataSet();
012.        myDataTable();
013.        myQueryExecuteScalar();
014.        myExecuteNonQuery();
015.        myExecuteTransaction();
016.    }
017. 
018. 
019.    //*** DataReader ***//
020.    void myDataReader()
021.    {
022.        String strSQL;
023.        MySqlDataReader dtReader;
024.        strSQL = "SELECT * FROM customer ";
025.        dtReader = clsDB.QueryDataReader(strSQL);
026.        this.myDataGrid1.DataSource = dtReader;
027.        this.myDataGrid1.DataBind();
028. 
029.        //*** Bind Rows ***//
030.        /*
031.        if(dtReader.HasRows == true)
032.        {
033.            dtReader.Read();
034.            this.lblCustomerID.Text = dtReader["CustomerID"].ToString();
035.            this.lblName.Text = dtReader["Name"].ToString();
036.            this.lblEmail.Text = dtReader["Email"].ToString();
037.            this.lblCountryCode.Text = dtReader["CountryCode"].ToString();
038.            this.lblBudget.Text = dtReader["Budget"].ToString();
039.            this.lblUsed.Text = dtReader["Used"].ToString();
040.        }
041.        */
042. 
043.        clsDB.Close();
044.    }
045. 
046.    //*** DataSet ***//
047.    void myDataSet()
048.    {
049.        String strSQL;
050.        DataSet ds;
051.        strSQL = "SELECT * FROM customer ";
052.        ds = clsDB.QueryDataSet(strSQL);
053.        this.myDataGrid2.DataSource = ds.Tables[0].DefaultView;
054.        this.myDataGrid2.DataBind();
055. 
056.        //*** Bind Rows ***//
057.        if (ds.Tables[0].Rows.Count > 0)
058.        {
059.            this.lblCustomerID.Text = ds.Tables[0].Rows[0]["CustomerID"].ToString();
060.            this.lblName.Text = ds.Tables[0].Rows[0]["Name"].ToString();
061.            this.lblEmail.Text = ds.Tables[0].Rows[0]["Email"].ToString();
062.            this.lblCountryCode.Text = ds.Tables[0].Rows[0]["CountryCode"].ToString();
063.            this.lblBudget.Text = ds.Tables[0].Rows[0]["Budget"].ToString();
064.            this.lblUsed.Text = ds.Tables[0].Rows[0]["Used"].ToString();
065.        }
066.        clsDB.Close();
067.    }
068. 
069.    //*** DataTable ***//
070.    void myDataTable()
071.    {
072.        String strSQL;
073.        DataTable dt;
074.        strSQL = "SELECT * FROM customer ";
075.        dt = clsDB.QueryDataTable(strSQL);
076.        this.myDataGrid3.DataSource = dt;
077.        this.myDataGrid3.DataBind();
078. 
079.        //*** Bind Rows ***//
080.        if (dt.Rows.Count > 0)
081.        {
082.            this.lblCustomerID.Text = dt.Rows[0]["CustomerID"].ToString();
083.            this.lblName.Text = dt.Rows[0]["Name"].ToString();
084.            this.lblEmail.Text = dt.Rows[0]["Email"].ToString();
085.            this.lblCountryCode.Text = dt.Rows[0]["CountryCode"].ToString();
086.            this.lblBudget.Text = dt.Rows[0]["Budget"].ToString();
087.            this.lblUsed.Text = dt.Rows[0]["Used"].ToString();
088.        }
089.        clsDB.Close();
090.    }
091. 
092.    //*** Execute Scalar ***//
093.    void myQueryExecuteScalar()
094.    {
095.        String strSQL;
096.        strSQL = "SELECT MAX(Budget) FROM customer ";
097.        this.lblText.Text = clsDB.QueryExecuteScalar(strSQL).ToString();
098.        clsDB.Close();
099.    }
100. 
101.    //*** ExecuteNonQuery ***//
102.    void myExecuteNonQuery()
103.    {
104.        String strSQL1, strSQL2, strSQL3;
105. 
106.        //*** Insert ***//
107.        strSQL1 = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " +
108.        " VALUES('C005','Weerachai Nukitram','webmaster@thaicreate.com','TH','200000','100000')";
109.        if (clsDB.QueryExecuteNonQuery(strSQL1) == true)
110.        {
111.            //*** Condition Success ***//
112.        }
113.        else
114.        {
115.            //*** Condition Error ***//
116.        }
117.        clsDB.Close();
118. 
119.        //*** Update ***//
120.        strSQL2 = "UPDATE customer SET Budget = '3000000' WHERE CustomerID = 'C005' ";
121.        if (clsDB.QueryExecuteNonQuery(strSQL2) == true)
122.        {
123.            //*** Condition Success ***//
124.        }
125.        else
126.        {
127.            //*** Condition Error ***//
128.        }
129.        clsDB.Close();
130. 
131.        //*** Delete ***//
132.        strSQL3 = "DELETE FROM customer WHERE CustomerID = 'C005' ";
133.        if (clsDB.QueryExecuteNonQuery(strSQL3) == true)
134.        {
135.            //*** Condition Success ***//
136.        }
137.        else
138.        {
139.            //*** Condition Error ***//
140.        }
141.        clsDB.Close();
142.    }
143. 
144.    //*** Execute Transaction ***'
145.    void myExecuteTransaction()
146.    {
147.        String strSQL1, strSQL2, strSQL3;
148. 
149.        //*** Start Transaction ***//
150.        clsDB.TransStart();
151. 
152.        try
153.        {
154. 
155.            //*** Insert ***//
156.            strSQL1 = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " +
157.            " VALUES('C005','Weerachai Nukitram','webmaster@thaicreate.com','TH','200000','100000')";
158.            clsDB.TransExecute(strSQL1); //*** Execute Query 1 ***//
159. 
160.            //*** Update ***//
161.            strSQL2 = "UPDATE customer SET Budget = '3000000' WHERE CustomerID = 'C005' ";
162.            clsDB.TransExecute(strSQL2); //*** Execute Query 2 ***//
163. 
164.            //*** Delete ***//
165.            strSQL3 = "DELETE FROM customer WHERE CustomerID = 'C005' ";
166.            clsDB.TransExecute(strSQL3); //*** Execute Query 3 **//
167. 
168.            //*** Commit Transaction ***//
169.            clsDB.TransCommit();
170.        }
171.        catch (Exception)
172.        {
173.            //*** RollBack Transaction ***//
174.            clsDB.TransRollBack();
175.        }
176. 
177.        clsDB.Close();
178.    }
179. 
180. 
181.</script>
182.<html>
183.<head>
184.<title>ThaiCreate.Com ASP.NET - Database Class</title>
185.</head>
186.<body>
187.    <form id="form1" runat="server">
188.            <asp:DataGrid id="myDataGrid1" runat="server"></asp:DataGrid>
189.            <br>
190.            <br>
191.            <asp:DataGrid id="myDataGrid2" runat="server"></asp:DataGrid><br>
192.            <br>
193.            <asp:DataGrid id="myDataGrid3" runat="server"></asp:DataGrid><br>
194.            <table style="WIDTH: 300px" border="1">
195.                <tr>
196.                    <td style="WIDTH: 93px">
197.                        <asp:Label id="lblHeaderCustomerID" runat="server" Text="CustomerID"></asp:Label></td>
198.                    <td style="WIDTH: 213px">
199.                        <asp:Label id="lblCustomerID" runat="server"></asp:Label></td>
200.                </tr>
201.                <tr>
202.                    <td style="WIDTH: 93px">
203.                        <asp:Label id="lblHeaderName" runat="server" Text="Name"></asp:Label></td>
204.                    <td style="WIDTH: 213px">
205.                        <asp:Label id="lblName" runat="server"></asp:Label></td>
206.                </tr>
207.                <tr>
208.                    <td style="WIDTH: 93px">
209.                        <asp:Label id="lblHeaderEmail" runat="server" Text="Email"></asp:Label></td>
210.                    <td style="WIDTH: 213px; HEIGHT: 23px">
211.                        <asp:Label id="lblEmail" runat="server"></asp:Label></td>
212.                </tr>
213.                <tr>
214.                    <td style="WIDTH: 93px">
215.                        <asp:Label id="lblHeaderCountryCode" runat="server" Text="CountryCode"></asp:Label></td>
216.                    <td style="WIDTH: 213px; HEIGHT: 23px">
217.                        <asp:Label id="lblCountryCode" runat="server"></asp:Label></td>
218.                </tr>
219.                <tr>
220.                    <td style="WIDTH: 93px">
221.                        <asp:Label id="lblHeaderBudget" runat="server" Text="Budget"></asp:Label></td>
222.                    <td style="WIDTH: 213px; HEIGHT: 21px">
223.                        <asp:Label id="lblBudget" runat="server"></asp:Label></td>
224.                </tr>
225.                <tr>
226.                    <td style="WIDTH: 93px">
227.                        <asp:Label id="lblHeaderUsed" runat="server" Text="Used"></asp:Label></td>
228.                    <td style="WIDTH: 213px; HEIGHT: 21px">
229.                        <asp:Label id="lblUsed" runat="server"></asp:Label></td>
230.                </tr>
231.            </table>
232.            <br>
233.            <asp:Label id="lblText" runat="server"></asp:Label>
234.    </form>
235.</body>
236.</html>






ASP.NET MySql.Data.MySqlClient - Parameter Query

Comment
Name
Comment
Security CodeCAPTCHA Image

easy tracking
avis car rental discount code

This page took 0.559723 seconds to load.