Creating Web Application using MySQL and Asp.Net with C# 2005
I am creating Asp.Net Web Application with MySQL Database. In this Article I am going to Explain about Select, Insert, Update and delete Operation in ASP.Net and MySQL. Display Records in Gridview from MySQL Database using DataAdapter.
Introduction
I am creating one ASP.NET Web application with C# using MySQL Database. MySQL Database is open source and easy to work in asp.net and MySQL Database.
In this Application I am going to explain about Select, Insert, Update and Delete Operation in ASP.Net and MySQL Database. Here I am displaying records in Gridview from MySQL Database.Display Records in Gridview from MySQL Database using DataAdapter.
I am creating Web application in ASP.Net named WorkingwithMySql. In this application I am using Three Textbox, Gridview, Four Button controls and Four Labels.
If you want to connect Asp.Net with MySQL Database, You need install MySQL Connector/ODBC 3.51. You can Download it from MySql.com.
To add a MySQL.Data in to your Web Application
In Solution Explorer, Right Click of WorkingwithMySql Application, Click Add Reference.
In the Add Reference Dialog Box, Click the tab indicating the type of component you want to select.
You can see MySql.Data, Select MySql.Data and Click ok.
Now you can Connect Asp.net with MySQL Data base.
Write Below code in .ASPX Page
<%@ 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>Untitled Page</head>
<body>
<form id="form1" runat="server">
<div align="Center">
<asp:Label ID="Label4" runat="server" Text="Simple DML Statements in ASP.Net and MySQL "></asp:Label><br />
<br />
<asp:Label ID="Label1" runat="server" Text="UserId"></asp:Label>
<asp:TextBox ID="txtUserId" runat="server">
<br />
<br />
<asp:Label ID="Label2" runat="server" Text="UserName"></asp:Label>
<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label3" runat="server" Text="Age"></asp:Label>
<br />
<br />
<br />
<asp:Button ID="btnAdd" runat="server" Text="Insert" OnClick="btnAdd_Click" />
<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" /><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<br />
<br />
<asp:Label ID="lblMsg" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
Web.config file
Write Below code in Web.config file Configuration Section.
<configuration>
<appSettings>
<connectionStrings>
<add name="testConnectionString" connectionString="server=localhost;User Id=root;password=mysql;Persist Security Info=True;database=test" providerName="MySql.Data.MySqlClient">
</connectionStrings>
<system.web>
<!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.
-->
<compilation debug="true"/>
<authentication mode="Windows"/>
<!--
The <customErrors> section enables configuration
of what to do if/when an unhandled error occurs
during the execution of a request. Specifically,
it enables developers to configure html error pages
to be displayed in place of a error stack trace.
<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->
</system.web>
</configuration>
if you are configure the data base connection string in web.config file, you can access the connection string from all over the application.Here You want to set your Server name, Userid, Password and Data base name.
You want to use MySql.Data.MySqlClient Namespace for Connecting Asp.net with MySQL Database.MySql.Data.MySqlClient Namespace contain class and method for working with MySQL Data base.
MySqlConnection :it is used connect Visual Studio.Net with MySQL Data Base.
MySqlCommand : It is used to execute the command in your data base.
MySqlDataAdapter : it is acting as bridge between MySQL and Visual Studio.Net.
Namespace For MySQL Database
using MySql.Data.MySqlClient;
MySqlConnection
MySqlConnection is class. MySqlConnection is used to connect Asp.Net with mySQL.
Connection String For MySQL Database
MySqlConnection MySqlConn = new MySqlConnection(ConfigurationManager.ConnectionStrings["testConnectionString"].ConnectionString);
Write Below code in Page load Event. Here Display records in Gridview from MySQL Data base.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SetData();
}
}
Here I creating a method SetData. SetData method is used to display records in Gridview.
public void SetData()
{
MySqlCommand MySqlCmd = new MySqlCommand("SELECT * FROM userreg", MySqlConn);
DataSet ds = new DataSet();
MySqlDataAdapter MySqlDa = new MySqlDataAdapter();
MySqlConn.Open();
MySqlDa.SelectCommand = MySqlCmd;
MySqlDa.Fill(ds, "Product");
GridView1.DataSource = ds;
GridView1.DataBind();
MySqlConn.Close();
}
Insert Record
When you click Insert button, The Records will insert into MySQL Data base.
protected void btnAdd_Click(object sender, EventArgs e)
{
String Query;
int i;
MySqlConn.Open();
Query = "INSERT INTO userreg(idUserReg,UserName, Age)VALUES(";
Query = Query + txtUserId.Text + ",'" + txtUserName.Text + "'," + txtAge.Text + ")";
MySqlCommand MySqlCmd = new MySqlCommand(Query, MySqlConn);
i = MySqlCmd.ExecuteNonQuery();
if(i>0)
{
lblMsg.Text = "Record is Successfully Inserted";
}
else
{
lblMsg.Text = "Record is not Inserted";
}
MySqlConn.Close();
SetData();
}
Update Records
When you click Update button, The Particular Records will update in MySQL Database.
protected void btnUpdate_Click(object sender, EventArgs e)
{
String Query;
int i;
MySqlConn.Open();
Query = "UPDATE userreg SET UserName ='" + txtUserName.Text + "',";
Query = Query + "Age = " + txtAge.Text;
Query = Query + " WHERE idUserReg = " + txtUserId.Text;
MySqlCommand MySqlCmd = new MySqlCommand(Query, MySqlConn);
i = MySqlCmd.ExecuteNonQuery();
if (i > 0)
{
lblMsg.Text = "Record is Successfully Updated";
}
else
{
lblMsg.Text = "Record is not Updated";
}
MySqlConn.Close();
SetData();
}
Delete Records
When you click Delete button, The Particular Records will Delete in MySQL Database.
protected void btnDelete_Click(object sender, EventArgs e)
{
String Query;
int i;
MySqlConn.Open();
Query = "Delete FROM userreg WHERE idUserReg =" + txtUserId.Text;
MySqlCommand MySqlCmd = new MySqlCommand(Query, MySqlConn);
i = MySqlCmd.ExecuteNonQuery();
if (i > 0)
{
lblMsg.Text = "Record is Successfully Deleted";
}
else
{
lblMsg.Text = "Record is not Deleted";
}
MySqlConn.Close();
SetData();
}
See the Output :
Conclusion :
Finally We Create ASP.Net Web Application in C# with MySQL Database. Here Insert, update, Delete and display Records in grid view.Creating Web application using MySQL Database Connection in C#.Net.In this Application I am using MySqlConnection, MySqlCommand, MySqlDataAdapter and Dataset
Thanks for spending your valuable time.if you have any doubts or any Suggestion , Let Me know. I am Happy to update here.
Hope you will enjoy this article and provide your valuable suggestion and feedback.
if you want to know about How to Create Login Page in ASP.Net Applications using Session. Please Refer Following Link.
How to Create Login Page in ASP.Net Applications using Session.
if you want to know about How to Upload Excel file in ASP.Net Applications. Please Refer Following Link.
Excel file upload in asp.net 2.0 display in grid view
Thanks
Happy Coding.....
Hi,
Thanks for this great post! it is useful for me.
How to create Stored procedure in MySQL and Use in ASP.Net.
Thanks
Vinodh.