ASP.NET with MySQL: Feedback Sample

This ASP.NET web application connects to MySQL database, and allows user to submit a feedback form, while the sample Admin allow the Admin to see all the submitted feedback and delete the record as well.

Download this project in ZIP file

The MySQL Database

Phpmyadmin is the tool I use in creating this database (sample_project_db) and its table (feedback)

Below is the screenhot of the design view of the table in phpmyadmin. (click image for a larger view)

Phpmyadmin design view

 

Below is the SQL create query that creates the table.

feedback table CREATE query

CREATE TABLE `feedback` (
  `id` int(10) NOT NULL auto_increment,
  `date` varchar(50) default NULL,
  `name` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `comment` text,
  PRIMARY KEY  (`id`)
)

 

 

Connecting ASP.NET to MySQL

I have to download .NET/MySQL connector from MySQL AB, and this MySql.Data.dll file must be kept in the Bin folder of the ASP.NET web application. Below is the screenshot of the ASP.NET project folders and files. Please refer to the last sample in case you don’t know how to create ASP.NET web project and files.

ASP.NET web application structure

notice the .NET/MySQL connector MySql.Data.dll

 

Web.config

This configuration file contains the connection string to the MySQL database.

Web.config

<?xml version="1.0"?>
<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.0"/>
  </system.web>
  <connectionStrings>
    <add name="ConnectionStringBS" connectionString="server=localhost; user id=root; password=; database=sample_project_db; pooling=false;" providerName="MySql.Data.MySqlClient"/>
  </connectionStrings>
</configuration>

 

Contact web form

This ASP.NET file contains the web form (Contact.aspx) and the code-behind file (Contact.aspx.cs). Let’s see what they contain and see the preview


Contact.aspx

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Contact.aspx.cs" Inherits="_Default" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Feedback</title>    
</head>

<body>
<form id="form1" runat="server">    
<b>Feedback Form </b>
<br/>

<p>
NAME: <asp:TextBox ID="txtName" runat="server" Width="300px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtName" 
    ErrorMessage="Please enter your name"></asp:RequiredFieldValidator>
</p>

<p>
EMAIL: <asp:TextBox ID="txtEmail" runat="server" Width="300px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtEmail" 
    ErrorMessage="Please enter your email"></asp:RequiredFieldValidator>
</p>

<p>
COMMENT: <asp:TextBox ID="txtMsg" runat="server" Height="154px" TextMode="MultiLine" Width="300px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtMsg" 
    ErrorMessage="Please enter comment"></asp:RequiredFieldValidator>
</p>

<p>
<asp:Label ID="lblMsg" runat="server"></asp:Label>
</p>

<p>
 <asp:Button ID="btnSubmit" runat="server" onclick="btnSubmit_Click" Text="SUBMIT" Width="164px" /> 
</p>   

</form>    
    
</body>
</html>

Contact.aspx.cs

    
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;

public partial class _Default : System.Web.UI.Page 
{

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        string name = txtName.Text;
        string email = txtEmail.Text;
        string comment = txtMsg.Text;

        MySqlConnection connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringBS"].ConnectionString);
        string cmdText = "INSERT INTO feedback (date, name, email, comment)" +
            "VALUES (now(), ?name ,?email,?comment);";
        MySqlCommand cmd = new MySqlCommand(cmdText, connection);
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.Add("?name", MySqlDbType.VarChar).Value = name;
        cmd.Parameters.Add("?email", MySqlDbType.VarChar).Value = email;
        cmd.Parameters.Add("?comment", MySqlDbType.VarChar).Value = comment;

        connection.Open();
        cmd.ExecuteNonQuery();

       
        lblMsg.Text = "Thanks for your enquiry, we will get back to you...";

        // clear the form
        txtName.Text = "";
        txtEmail.Text = "";
        txtMsg.Text = "";
    }
}

The Contact.aspx displays the web form as shown in the preview screenshot below, while Contact.aspx.cs responds to the Button click by connecting to the database and inserting the data entered into the contact form to the database table (feedback).

Feedback Form Preview

This contain AdminFeedback.aspx and its code-behind file AdminFeedback.aspx.cs

AdminFeedback.aspx

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="AdminFeedback.aspx.cs" Inherits="_Default"%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Feedback</title>
</head>
<body>
<form id="form1" runat="server"> 

<asp:ListView ID="ListView1" runat="server" DataKeyNames="id" >

    <LayoutTemplate>
        <table cellpadding="2" width="100%" border="1" ID="tbl1" runat="server">
            <tr runat="server">
                <th runat="server">Date Submited</th>
                <th runat="server">Name</th>
                <th runat="server">Email</th>
                <th runat="server">Comment</th>       
                <th runat="server"></th>
            </tr>
            <tr runat="server" id="itemPlaceholder" />
        </table>

        <asp:DataPager ID="DataPager" runat="server" PageSize="10">
            <Fields>
                <asp:NumericPagerField ButtonCount="4" NextPageText="next" PreviousPageText="previous" />
            </Fields>
        </asp:DataPager>
    </LayoutTemplate>

    <ItemTemplate>
        <tr>
            <td><asp:Label ID="Label2" runat="server" Text='<%#Eval("date") %>' /></td>
            <td><asp:Label ID="Label11" runat="server" Text='<%#Eval("name") %>' /></td>						
            <td><asp:Label ID="Label3" runat="server" Text='<%#Eval("email") %>' /></td>
            <td><asp:Label ID="Label4" runat="server" Text='<%#Eval("comment") %>' /></td>
            <td><a href='AdminFeedbackDelete.aspx?id=<%#Eval("id") %>'>Delete</a></td>                            
        </tr>
    </ItemTemplate>

</asp:ListView>
</form>
</body>
</html>

AdminFeedback.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;

public partial class _Default : System.Web.UI.Page 
{

    protected void Page_PreRender(object sender, EventArgs e)
    {
        string strSQL = "SELECT * FROM  feedback";

        MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(strSQL, new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringBS"].ConnectionString));

        DataSet myDataSet = new DataSet();
        myDataAdapter.Fill(myDataSet, "feedback");

        ListView1.DataSource = myDataSet;
        ListView1.DataBind();       
    }   
}

ListView to display record

Above is the preview of AdminFeedback.aspx its uses ListView to display the contact record after retrieving (SELECT) it from the feedback table, I also added a delete link which will posts to AdminFeedbackDelete.aspx for deletion of a record.

Delete Record

This is the continuation of the last step, this page responds to the delete link, it deletes a record and return the user back to AdminFeedback.aspx

AdminFeedbackDelete.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AdminFeedbackDelete.aspx.cs" Inherits="_Default"%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Feedback</title>
</head>
<body>
<form id="form1" runat="server">
</form>        
</body>
</html>

AdminFeedbackDelete.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;

public partial class _Default : System.Web.UI.Page 
{

    protected void Page_PreRender(object sender, EventArgs e)
    {

        String feed_id = Request.QueryString["id"];

        MySqlConnection connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringBS"].ConnectionString);
        string cmdText = "DELETE FROM feedback WHERE id ='" + feed_id + "'";
        MySqlCommand cmd = new MySqlCommand(cmdText, connection);
        cmd.CommandType = CommandType.Text;

        connection.Open();
        cmd.ExecuteNonQuery();

        Response.Redirect("AdminFeedback.aspx");
    }  
   
}

Download this project in ZIP file

 

Next »