query mysql database in asp.net C#

query mysql database in asp.net C#

Step 1) Add reference MySQL Library to asp.net application/Windows Forms/WPF

          MySql.Data  located in "C:\Program Files (x86)\MySQL\MySQL Connector Net 6.6.4\Assemblies\v4.0\MySql.Data.dll"

Step 2) If u don't find MySql.Data.dll

          pls download it from   MySQL Connector  Net 6.6.4 or higher.  from MYSQL NET CONNECTOR

Step 3)  Add GridView to asp.net page

 <div>
        <asp:GridView ID="GridView1" runat="server" ShowFooter="True"
             Caption="<h1 style='background-color:teal;color:white;'>MySQL connection in ASP.NET</h1>"
            BackColor="LightGoldenrodYellow" BorderColor="Tan"
            BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">
            <AlternatingRowStyle BackColor="PaleGoldenrod" />
            <FooterStyle BackColor="Tan" />
            <HeaderStyle BackColor="Tan" Font-Bold="True" />
            <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
                HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
            <SortedAscendingCellStyle BackColor="#FAFAE7" />
            <SortedAscendingHeaderStyle BackColor="#DAC09E" />
            <SortedDescendingCellStyle BackColor="#E1DB9C" />
            <SortedDescendingHeaderStyle BackColor="#C2A47B" />
        </asp:GridView>
    </div>

Step 4)  Connect to MYSQL Database in asp.net C#

Add using Statements

using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;

Connect to MYSQL Database using MySqlConnection

            //MySQL connection String

            MySql.Data.MySqlClient.MySqlConnection mysqlConn = new MySqlConnection(
            @"server=localhost;database=HR;uid=root;pwd=password;port=3306");

Open the Connection

            mysqlConn.Open();
Add MySql data Adapter located in MySql.Data.MySqlClient.MySqlDataAdapter

            MySql.Data.MySqlClient.MySqlDataAdapter ad =
            new MySqlDataAdapter("select * from emp", mysqlConn);
            DataTable tbl = new DataTable();
            ad.Fill(tbl);

Bind to GridView

            GridView1.DataSource = tbl;
            GridView1.DataBind();

Step 6) RUN THE aspx page

OUTPUT:

MySQL connection in ASP.NET

idEMP	ename	Sal
2	shyam	5500.56
3	benegal	5555.00

Complete Source Code

            MySql.Data.MySqlClient.MySqlConnection mysqlConn = new MySqlConnection(
            @"server=localhost;database=HR;uid=root;pwd=password;port=3306");
            mysqlConn.Open();
            MySql.Data.MySqlClient.MySqlDataAdapter ad =
            new MySqlDataAdapter("select * from emp", mysqlConn);
            DataTable tbl = new DataTable();
            ad.Fill(tbl);
            GridView1.DataSource = tbl;
            GridView1.DataBind();
Note: If u get following error
"#1045 - Access denied for user 'root'@'localhost' (using password: NO)"

 Pls follow this link

Tags:query mysql database in asp.net,MYSQL NET COnnector download,MYSQL.DATA.DLL,Connect to MYSQL Database in asp.net C#,MySql connection String in asp.net,mysql connection string in web.config,mysql data adapter.