This asp .net tutorial describes about How to perform insert update delete using gridview in asp .net with ADO .net and without placing controls inside gridview's footer template.
1. Create Database Table
To start with
Gridview Insert update delete in asp .net we need database. For this example I have created simple table named "Employee"
with 4 columns "Id, Name, Email and Age" however you can use your own database Name or Table structure.
CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
2. Create Form
We have created database table. Now lets create simple form to accept input from users for
Gridview Insert update delete in asp .net.
We will create three textboxes for user input and three buttons for Save, Update and Clear operations. Also we have used Label control named "lblAlert" to show
alerts on record Save, update etc.
|
<%--//--- Label to show alerts--%>
|
| Name |
|
| Email |
|
| Age |
|
| |
|
Here we have used Visible="false" inside update button to make it invisible at page load. We will only show update button during update process of gridview.
3. Add connection string
To interact with database we need to add connection string in our web.confing file.
4. Add namespaces.
Add following namespaces.
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Linq;
5. Add New Record
//--- Button Save Click.
protected void btnSave_Click(object sender, EventArgs e)
{
//--- Getting connection string defined in the web.config file. Pointed to the database we want to use.
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
//--- Insert Query.
string cmdText = "INSERT INTO employee (Name,Email,Age) VALUES (@Name,@Email,@Age)";
//--- Providing information to SQL command object about which query to
//--- execute and from where to get database connection information.
SqlCommand cmd = new SqlCommand(cmdText, con);
//--- Adding parameters/Values.
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(txtAge.Text));
//--- To check current state of the connection object. If it is closed open the connection
//--- to execute the insert query.
if (con.State == ConnectionState.Closed)
{
con.Open();
}
//--- Execute Query.
cmd.ExecuteNonQuery();
//--- close the connection.
con.Close();
//--- Clear text from textboxes
resetControls();
//--- Show confirmation message.
lblAlert.Text = "Record successfully saved.";
lblAlert.ForeColor = Color.Green;
//--- Bind data to Gridview so that it will display updated data.
bindAllEmployees();
}
Besides Save operation we have also used two additional methods "resetControls" and "bindAllEmployees". "resetControls" method is used to clear all values from textbox controls and
"bindAllEmployees" method is used to bind Gridview with latest information.
6. Bind Gridview with data "bindAllEmployees()"
//---- Bind all employees in GridView.
public void bindAllEmployees()
{
//--- Getting connection string defined in the web.config file. Pointed to the database we want to use.
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
//--- Select Query.
string cmdText = "SELECT * FROM Employee order by Id desc";
SqlDataAdapter adp = new SqlDataAdapter(cmdText, con);
DataSet ds = new DataSet();
adp.Fill(ds);
grdViewTest.DataSource = ds;
grdViewTest.DataBind();
}
7. Create Gridview
Note: We have used "CommandName" and "CommandArgument" properties of Linkbuttons inside Gridview. "Commandname" property can be used to define operations names Like
Edit, Delete etc and "CommandArgument" can be used to store additional arguments. In this case we have stored Primary key of our records inside "CommandArgument".
Later on we will use these properties inside Gridview's "Rowcommand" Event to distinguish which operation to perform "Edit/Delete" and also we will access primary key from "CommandArgument" property.
8. Bind Gridview with data on Page_Load
Bind gridview inside page_Load event so that gridview can display existing records after page loads.
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
bindAllEmployees();
}
}
9. Reset Controls "resetControls()"
This method is used to clear all the input values from textboxes and reset form to its initial state.
//--- Reset Form input fields.
public void resetControls()
{
txtAge.Text = string.Empty;
txtEmail.Text = string.Empty;
txtName.Text = string.Empty;
//--- Show Save button.
btnSave.Visible = true;
//--- Hide Update button.
btnUpdate.Visible = false;
//--- Reset selected value from View state.
ViewState["selectedRec"] = string.Empty;
}
9. Edit and Delete
Here we have used "OnRowCommand" Event of gridview to perform Edit and Delete operations.
protected void grdViewTest_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Edt")
{
//--- Get primarry key value of the selected record.
int Id = Convert.ToInt32(e.CommandArgument);
//--- Set id in viewstate so that we can use in update process.
ViewState["selectedRec"] = Id.ToString();
//====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
//======= Parameterized select Query.
string cmdText = "SELECT * FROM employee WHERE Id=@Id";
//====== Providning information to SQL command object about which query to
//====== execute and from where to get database connection information.
SqlCommand cmd = new SqlCommand(cmdText, con);
//===== Adding parameters/Values.
cmd.Parameters.AddWithValue("@Id", Id);
//===== To check current state of the connection object. If it is closed open the connection
//===== to execute the insert query.
if (con.State == ConnectionState.Closed)
{
con.Open();
}
//===== Execute Query.
SqlDataReader dr = cmd.ExecuteReader();
//===== Check if data present.
if (dr.HasRows)
{
//===== Read data from datareader.
dr.Read();
//===== Bind values to textboxes.
txtName.Text = dr["Name"].ToString();
txtEmail.Text = dr["Email"].ToString();
txtAge.Text = dr["Age"].ToString();
//==== Store primary key of the selected record in ViewState for future reference.
//==== This will help us when we write update method.
ViewState["selectedRec"] = dr["id"].ToString();
}
dr.Dispose();
//===== close the connection.
con.Close();
//==== Show Update button and hide Save button.
btnSave.Visible = false;
btnUpdate.Visible = true;
}
else if (e.CommandName == "Del")
{
//--- Get primarry key value of the selected record.
int Id = Convert.ToInt32(e.CommandArgument);
//====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
//======= Delete Query.
string cmdText = "DELETE FROM employee WHERE Id=@Id";
//====== Providning information to SQL command object about which query to
//====== execute and from where to get database connection information.
SqlCommand cmd = new SqlCommand(cmdText, con);
//===== Adding parameters/Values.
cmd.Parameters.AddWithValue("@Id", Id);
//===== To check current state of the connection object. If it is closed open the connection
//===== to execute the insert query.
if (con.State == ConnectionState.Closed)
{
con.Open();
}
//===== Execute Query.
cmd.ExecuteNonQuery();
//===== close the connection.
con.Close();
//===== Bind data to FormView so that FormView will display updated data.
bindAllEmployees();
}
}
10. Update Record.
//--- Button Update Click
protected void btnUpdate_Click(object sender, EventArgs e)
{
//--- Get primary key value of the selected record from viewstate.
int Id = Convert.ToInt32(ViewState["selectedRec"]);
//====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
//======= Insert Query.
string cmdText = "UPDATE employee SET Name=@Name,Email=@Email,Age=@Age WHERE Id=@Id";
//====== Providning information to SQL command object about which query to
//====== execute and from where to get database connection information.
SqlCommand cmd = new SqlCommand(cmdText, con);
//===== Adding parameters/Values.
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(txtAge.Text));
cmd.Parameters.AddWithValue("@Id", Id);
//===== To check current state of the connection object. If it is closed open the connection
//===== to execute the insert query.
if (con.State == ConnectionState.Closed)
{
con.Open();
}
//===== Execute Query.
cmd.ExecuteNonQuery();
//===== close the connection.
con.Close();
//===== Bind data to FormView so that FormView will display updated data.
bindAllEmployees();
//--- Reset controls.
resetControls();
//--- Show confirmation message.
lblAlert.Text = "Record successfully updated.";
lblAlert.ForeColor = Color.Green;
}
Insert update delete using gridview in asp .net with ADO .net Demo: