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#

Written By on in C#

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.

Active Data Objects (ADO.Net) Series
  1. What are Active Data Objects?
  2. Connecting to a SQL Server Database with C#
  3. Consuming Data in Windows Forms C# Applications
  4. Using ADO.Net Controls in ASP.Net Pages
  5. Modifying Data and Updating Databases with C#
  6. Connecting to MySql Databases with C# / ASP.Net
  7. Reading Excel Spreadsheets with C# ADO.Net
  8. Creating SQL Server Stored Procedures in C#
  9. Calling SQL Server Stored Procedures from C#
  10. Importing and Exporting XML from a DataSet

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

There are no comments for this post. Be the first!

 

Leave a Reply

Your email address will not be published.





If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.

Copyright © 2001-2018 Tim Trott, all rights reserved. Web Design by Azulia Designs

This web page is licensed for your personal, private, non-commercial use only.

Disclaimer, Privacy & LegalSitemapContact Me