Web Design that taps into the haromny and vision of your dreams.

Connecting to MySql Databases with C# / ASP.Net

Connecting to MySql with C#

By on in Coding

840 words, estimated reading time 4 minutes.

ADO.Net works best when connecting to Microsoft SQL Server sources, but it can access data from other sources such as MySql. In this tutorial, I will show you how to connect a sample ASP.Net page to MySql data source and utilise the provided data controls.

ADO Connection to MySQL

MySql connections can be established using the free software MySql Connector Net. Once installed this product provides the .Net Framework classes to interact with a MySql Server.

You can use the standard ADO.Net data components; however you will have to override the default functions such as Sort. The code provided is designed for an ASP page, but it can easily be converted to Windows Forms. The code will need to be changed to connect to your database and tables. The grid view will need changing to match columns of the table, as will the sort expressions.

ASP.Net Webpage (.aspx)

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>MySql Test</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" Style="position: relative" AllowSorting="True" GridLines="Vertical" CaptionAlign="Left" AutoGenerateColumns="False" OnSorting="GridView1_Sorting" EmptyDataText="<strong>There are no records to show.</strong>">
          <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
          <RowStyle ForeColor="Black" BackColor="#EEEEEE" />
          <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
          <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
          <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
          <AlternatingRowStyle BackColor="Gainsboro" />
          <Columns>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Button ID="btnDelete" CommandName="Delete" runat="server" Text="Delete" CssClass="okbutton" OnClientClick="return confirm('Are you sure you wish to delete this item?');" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:boundfield datafield="productID" readonly="True" headertext="ID" SortExpression="productID"/>
            <asp:boundfield datafield="productName" readonly="True" headertext="Style" SortExpression="productName"/>
            <asp:hyperlinkfield datatextfield="URL" datatextformatstring="view" datanavigateurlfields="URL" datanavigateurlformatstring="{0}" headertext="URL" target="_blank" />
          </Columns>
        </asp:GridView>
    </form>
</body>
</html>
 

Code Behind (.cs)

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
 
public partial class _Default : System.Web.UI.Page 
{
    private MySqlConnection conn;
    private DataTable data;
    private MySqlDataAdapter da;
    private MySqlCommandBuilder cb;
    private string SortString = "";
    private string SortOrder;
    private string SortExpression;
 
    protected void Page_Load(object sender, EventArgs e)
    {
        SortOrder = (string)Session["SortOrder"];
        SortExpression = (string)Session["SortExpression"];
 
        if (SortOrder == null)
            SortOrder = "ASC";
 
        if (SortExpression == null)
            SortExpression = "productID";
 
        if (!IsPostBack)
            QueryServer("SELECT * FROM database.table ORDER BY " + SortExpression + " " + SortOrder);
    }
 
    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
        SortString = e.SortExpression;
 
        QueryServer("SELECT * FROM database.table ORDER BY " + SortString + " " + SortOrder);
 
        if (SortOrder == "ASC")
          SortOrder = "DESC";
        else  
          SortOrder = "ASC";
 
        Session["SortOrder"] = SortOrder;
        Session["SortExpression"] = e.SortExpression;
    }
 
    protected void QueryServer(string cmd)
    {
 
        if (cmd == "")
            cmd = "SELECT * FROM database.table";
 
        string connStr = String.Format("server={0};user id={1}; password={2}; database=mysql; pooling=false", "localhost", "username", "password");
        try
        {
            conn = new MySqlConnection(connStr);
            conn.Open();
 
            MySqlDataReader reader = null;
 
            try
            {
                da = new MySqlDataAdapter(cmd, conn);
                data = new DataTable();
                da.Fill(data);
 
                GridView1.DataSource = data;
                GridView1.AllowSorting = true;
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                throw new Exception("Failed to populate database list: " + ex.Message);
            }
            finally
            {
                if (reader != null) reader.Close();
            }
 
        }
        catch (MySqlException ex)
        {
            throw new Exception("Error connecting to the server: " + ex.Message);
        }
    }
 
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int index = e.RowIndex;
        string id = GridView1.Rows[index].Cells[1].Text;
 
        string command = "DELETE FROM database.table WHERE logID = " + id + " LIMIT 1;";
        command += "SELECT * FROM database.table ORDER BY " + SortExpression + " " + SortOrder;
 
        QueryServer(command);
    }
}
 

Other data sources can be used by changing the Ado.Net command strings.

Last updated on: Friday 23rd June 2017

 

Comments

Have a question or suggestion? Please leave a comment to start the discussion.

 

Leave a Reply

Please keep in mind that all comments are moderated according to our privacy policy, and all links are nofollow. Do NOT use keywords in the name field. Let's have a personal and meaningful conversation.

Your email address will not be published.