Using ASP.NET: Insertion using mysql in asp.net

Introduction: Hello friends, “Welcome back” we come with the new article. In this article i will explain that how we can insert the record inside the mysql database using asp.net.This article is very useful for the all the .net developer.

Implementation: Create a new website add a page insert.aspx page. And paste the below code inside this page. Download the MySql.Data.dll from this link

http://dev.mysql.com/downloads/mirror.php?id=367415 and paste this .dll file inside the bin folder of your application.

Code for insert.aspx page

.style1

{

width: 198px;

}

.style2

{

height: 21px;

width: 198px;

}

.style3

{

height: 22px;

width: 198px;

}

</style>

</head>

<body>

<tr>

Employee Id</td>

</td>

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"

ControlToValidate="TextBox1" ErrorMessage="Required"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

Employee FName</td>

</td>

<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"

ControlToValidate="TextBox4" ErrorMessage="Required"></asp:RequiredFieldValidator></td>

</tr>

<tr>

Employee LName</td>

</td>

<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"

ControlToValidate="TextBox5" ErrorMessage="Required"></asp:RequiredFieldValidator></td>

</tr>

<tr>

Employee Gender</td>

</td>

<asp:TextBox ID="TextBox6" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server"

ControlToValidate="TextBox6" ErrorMessage="Required"></asp:RequiredFieldValidator></td>

</tr>

<tr>

Employee City</td>

</td>

<asp:TextBox ID="TextBox7" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server"

ControlToValidate="TextBox7" ErrorMessage="Required"></asp:RequiredFieldValidator></td>

</tr>

<tr>

Employee Country</td>

</td>

<asp:TextBox ID="TextBox8" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server"

ControlToValidate="TextBox8" ErrorMessage="Required"></asp:RequiredFieldValidator></td>

</tr>

<tr>

Employee PIN</td>

</td>

<asp:TextBox ID="TextBox9" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server"

ControlToValidate="TextBox9" ErrorMessage="Required"></asp:RequiredFieldValidator></td>

</tr>

<tr>

Employee Phone</td>

</td>

<asp:TextBox ID="TextBox10" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server"

ControlToValidate="TextBox10" ErrorMessage="Required"></asp:RequiredFieldValidator></td>

</tr>

<tr>

Employee Bank Name</td>

</td>

<asp:TextBox ID="TextBox11" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator9" runat="server"

ControlToValidate="TextBox11" ErrorMessage="Required"></asp:RequiredFieldValidator></td>

</tr>

<tr>

PAN NO</td>

</td>

<asp:TextBox ID="TextBox12" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator10" runat="server"

ControlToValidate="TextBox12" ErrorMessage="Required"></asp:RequiredFieldValidator></td>

</tr>

<tr>

</td>

<asp:Button ID="btn_submit" runat="server" Text="Submit"

onclick="btn_submit_Click" />    

<asp:Button ID="btn_reset" runat="server" Text="Reset" CausesValidation="false"

onclick="btn_reset_Click" />

</td>

</tr>

<tr>

<td>

<asp:Label ID="lbl_msg" runat="server" Text="" ForeColor="#0000ff"></asp:Label>

</td>

</tr>

</table>

<br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

Width="1326px">

<asp:TemplateField HeaderText="SNO">

<asp:Label id=Emp_Id runat=server Text='<%#Container.DataItemIndex+1%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Employee<br>ID">

<asp:Label id=Emp_Id runat=server Text='<%# DataBinder.Eval(Container.DataItem, "Emp_Id")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Employee<br>FName">

<asp:Label id=Emp_FName runat=server Text='<%# DataBinder.Eval(Container.DataItem, "Emp_FName")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Employee<br>LName">

<asp:Label id=Emp_LName runat=server Text='<%# DataBinder.Eval(Container.DataItem, "Emp_LName")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Emp Gender">

<asp:Label id=Emp_Gender runat=server Text='<%# DataBinder.Eval(Container.DataItem, "Emp_Gender")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Emp City">

<asp:Label id=Emp_P_City runat=server Text='<%# DataBinder.Eval(Container.DataItem, "Emp_P_City")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Emp Country">

<asp:Label id=EmpEmp_P_Country_Id runat=server Text='<%# DataBinder.Eval(Container.DataItem, "Emp_P_Country")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Emp PIN">

<asp:Label id=Emp_P_PIN runat=server Text='<%# DataBinder.Eval(Container.DataItem, "Emp_P_PIN")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Emp Phone">

<asp:Label id=Emp_Phone runat=server Text='<%# DataBinder.Eval(Container.DataItem, "Emp_Phone")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Bank Name">

<asp:Label id=Emp_Bank_Name runat=server Text='<%# DataBinder.Eval(Container.DataItem, "Emp_Bank_Name")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="PAN NO">

<asp:Label id=PAN_NO runat=server Text='<%# DataBinder.Eval(Container.DataItem, "PAN_NO")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</form>

</body>

Code for insert.aspx.cs page

using System;

using System.Data;

using System.Configuration;

using System.Collections;

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.WebControls.Adapters;

using System.Web.UI.HtmlControls;

// This namespace will use to perform the function of insertion for the mysql database

using MySql.Data.MySqlClient;

public partial class insert : System.Web.UI.Page

{

MySqlCommand cmd ;

MySqlConnection con;

MySqlDataAdapter adp;

DataSet ds;

protected void Page_Load(object sender, EventArgs e)

{

// here i am creating a connection with the mysqk database using Appsettings

con = new MySqlConnection(ConfigurationManager.AppSettings["Mysql"]);

con.Open();

// if connection is close during the execution time, this line will open the connection

if (con.State == ConnectionState.Closed)

{ con.Open(); }

con.Close();

if (IsPostBack == false)

{

gvbind();

//grdview();

}

protected void btn_submit_Click(object sender, EventArgs e)

{

con = new MySqlConnection(ConfigurationManager.AppSettings["Mysql"]);

con.Open();

cmd = new MySqlCommand();

// here i am writting the insert query

// In the sql server we are using @ sign with the parameter but in mysql we will use ? sign with the parameter inpace of the

// @ sign

cmd.CommandText = @"insert into emp_master values(?Emp_Id,?Emp_FName,?Emp_LName,?Emp_Gender,?Emp_P_City,?Emp_P_Country,?Emp_P_PIN,?Emp_Phone,?Emp_Bank_Name,?PAN_NO)";

cmd.Connection = con;

// below i an passing the all the parameters which are required to the insert query

cmd.Parameters.AddWithValue("?Emp_Id", Convert.ToInt32(TextBox1.Text));

cmd.Parameters.AddWithValue("?Emp_FName", TextBox4.Text);

cmd.Parameters.AddWithValue("?Emp_LName", TextBox5.Text);

cmd.Parameters.AddWithValue("?Emp_Gender", TextBox6.Text);

cmd.Parameters.AddWithValue("?Emp_P_City", TextBox7.Text);

cmd.Parameters.AddWithValue("?Emp_P_Country", TextBox8.Text);

cmd.Parameters.AddWithValue("?Emp_P_PIN", TextBox9.Text);

cmd.Parameters.AddWithValue("?Emp_Phone", TextBox10.Text);

cmd.Parameters.AddWithValue("?Emp_Bank_Name", TextBox11.Text);

cmd.Parameters.AddWithValue("?PAN_NO", TextBox12.Text);

// ExecuteNonQuery this keyword is use to perform the insertion,deletion or updation, on the table

cmd.ExecuteNonQuery();

// The dispose method will released all the resources used by the component

cmd.Dispose();

// Here i am closing the connection after the insertion of the record

con.Close();

// Here i am calling the method that is binding the gridview, it means when user will insert the record

//inside the database then the inserted record will automatically display inside the gridview,

gvbind();

// After the insertion of the record the message will be display inside the label

lbl_msg.Text = "Record has been submitted successfully";

// Here i am calling the method named clr_rec() this method will clear the all the textboxes

//after the insertion of the record

clr_rec();

}

// the below method is used to clear the all the textboxes after the insertion of the record

private void clr_rec()

{

TextBox1.Text = String.Empty;

TextBox4.Text = String.Empty;

TextBox5.Text = String.Empty;

TextBox6.Text = String.Empty;

TextBox7.Text = String.Empty;

TextBox8.Text = String.Empty;

TextBox9.Text = String.Empty;

TextBox10.Text = String.Empty;

TextBox11.Text = String.Empty;

TextBox12.Text = String.Empty;

}

protected void btn_reset_Click(object sender, EventArgs e)

{

// Here i am calling the method named clr_rec() this method will clear the all the textboxes,

//if any wrong record will be enterd by the user mistacely

clr_rec();

}

// The below function will use to bind the gridview with the inserted record inside the tables by using mysql database

private void gvbind()

{

// here i am creating a connection with the mysqk database using Appsettings

con = new MySqlConnection(ConfigurationManager.AppSettings["Mysql"]);

// here i am writting the select query to fetch the record from the table from the mysql database

adp = new MySqlDataAdapter("select * from emp_master", con);

// if you want to use where claues the you will pass the parameter like below

//adp.SelectCommand.Parameters.AddWithValue("?Emp_Id", 1);

ds = new DataSet();

try

{

adp.Fill(ds);

adp.Dispose();

GridView1.DataSource = ds;

GridView1.DataBind();

con.Close();

ds.Dispose();

}

catch

{ }

}

Database Script- create a table in database named emp_master. Here I am giving you to complete database script of the table of the mysql databse emp_master. See below:

-- phpMyAdmin SQL Dump

-- version 3.5.1

-- http://www.phpmyadmin.net

-- Host: localhost

-- Generation Time: Mar 09, 2013 at 08:58 AM

-- Server version: 5.5.24-log

-- PHP Version: 5.3.13

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

-- Database: `db_practice`

-- --------------------------------------------------------

-- Table structure for table `emp_master`

CREATE TABLE IF NOT EXISTS `emp_master` (

`Emp_Id` int(10) NOT NULL,

`Emp_FName` varchar(50) NOT NULL,

`Emp_LName` varchar(50) NOT NULL,

`Emp_Gender` varchar(50) NOT NULL,

`Emp_P_City` varchar(50) NOT NULL,

`Emp_P_Country` varchar(50) NOT NULL,

`Emp_P_PIN` varchar(50) NOT NULL,

`Emp_Phone` varchar(50) NOT NULL,

`Emp_Bank_Name` varchar(50) NOT NULL,

`PAN_NO` varchar(50) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `emp_master`

INSERT INTO `emp_master` (`Emp_Id`, `Emp_FName`, `Emp_LName`, `Emp_Gender`, `Emp_P_City`, `Emp_P_Country`, `Emp_P_PIN`, `Emp_Phone`, `Emp_Bank_Name`, `PAN_NO`) VALUES

(1, 'Bharat', 'Bhushan', 'male', 'batala', 'India', '143505', '9888504255', 'P.N.B', '1234567'),

(2, 'Sample text', 'hello', 'male', 'batala', 'India', '143505', '9888504256', 'P.N.B', '1234567');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

See the output inside the image below

Conclision: In above code, I have been explained that how we can insert the recod inside the table using mysql database in asp.net. This code is very helpful for every .net developer. Gud bye and take care developers. We will come back shortly with the new article.

Using ASP.NET

Sunday, 10 March 2013

Insertion using mysql in asp.net

1 comment: