Update Delete data using Linq to Sql From Asp.net GridView Control
What is Linq to Sql in .Net Framework 3.5
LINQ to SQL is a new feature in .NET Framework version 3.5 that provides a way to manage relational data as objects. In LINQ to SQL, the relational database is mapped to an object model for the developer. At run time LINQ to SQL translates Language Integrate Queries into SQL sends them to the database for the execution. When database returns the result, LINQ to SQL convert them back to objects so that developer can work in his programming language.
Edit Data In GridView using Linq to Sql
In this example I am showing how to update and delete data in database using Linq to Sql Feature of .Net Framework 3.5. GridView Control is used to Display and Edit the data in Database using Linq to Sql. Source Code of the example is also attatched with this post.
Nortwind Database’s Tables name Category and Products are used in this Example
Modeling Database using Linq to Sql
Go to
Website-> Add New Item-> Select Linq to Sql Classes
Name this class Northwind.dbml
Visual Studio Automatically opens the designer of norhtwind.dbml class.
Now open solution explorer and drag drop Category and Products table of northwind database
Linq to sql will automatically gets the relation of the tables draged in it’s designer window.
Drag drop a DropDownList, button and GridView control to your web page or the code is given below.
Following namespaces are used in this example
Following Method is used to bind the DropDownList control with the category table of northwind database .
private void get_categories()
{
NorthwindDataContext
db = new NorthwindDataContext();
db = new NorthwindDataContext();
var cat
= from p in
db.Categories select p;
= from p in
db.Categories select p;
DropDownList1.DataSource = cat.ToList();
DropDownList1.DataValueField = "CategoryID";
DropDownList1.DataTextField = "CategoryName";
DropDownList1.DataBind();
}
Call the above mehtod in the page load event of web page
protected void Page_Load(object
sender, EventArgs e)
sender, EventArgs e)
{
if
(Page.IsPostBack == false)
(Page.IsPostBack == false)
get_categories();
}
Binding the gridview control with products of selected category in the DropDownList
protected void Button1_Click(object
sender, EventArgs e)
sender, EventArgs e)
{
List<Product> prd = grid_bind();
GridView1.DataSource = prd;
GridView1.DataBind();
}
The grid_bind() method is used to get all the products of the selected category from the Product table of the databae using Linq to sql.
private List<Product>
grid_bind()
grid_bind()
{
int
catid = Convert.ToInt32(DropDownList1.SelectedValue);
catid = Convert.ToInt32(DropDownList1.SelectedValue);
NorthwindDataContext
db = new NorthwindDataContext();
db = new NorthwindDataContext();
var
products = from p in
db.Products where p.CategoryID == catid select p;
products = from p in
db.Products where p.CategoryID == catid select p;
List<Product> prd = products.ToList();
return
prd;
prd;
}
protected void GridView1_RowEditing(object
sender, GridViewEditEventArgs e)
sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
GridView1.DataSource = grid_bind();
GridView1.DataBind();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs
e)
e)
{
GridView1.EditIndex = -1;
GridView1.DataSource = grid_bind();
GridView1.DataBind();
}
Updating the Selected Record in Database (Product table) using Linq to Sql From GridView
protected void
GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int
prdid,prdinstock;
prdid,prdinstock;
string
prdname;
prdname;
decimal
prdunitprice;
prdunitprice;
prdname = ((TextBox)(GridView1.Rows[e.RowIndex].FindControl("ename"))).Text;
prdunitprice =Convert.ToDecimal(
((TextBox)(GridView1.Rows[e.RowIndex].FindControl("eprice"))).Text);
((TextBox)(GridView1.Rows[e.RowIndex].FindControl("eprice"))).Text);
prdinstock = Convert.ToInt32(((TextBox)(GridView1.Rows[e.RowIndex].FindControl("estock"))).Text);
//Product prd
= dc.Products.Single(p => p.ProductID == prdid);
= dc.Products.Single(p => p.ProductID == prdid);
//or
Product
prd = (from p in
dc.Products where p.ProductID == prdid select p).Single();
prd = (from p in
dc.Products where p.ProductID == prdid select p).Single();
prd.ProductName = prdname;
prd.UnitPrice = prdunitprice;
prd.UnitsInStock =Convert.ToInt16(prdinstock);
//updating the
database with the changes
database with the changes
dc.SubmitChanges();
GridView1.DataSource = grid_bind();
GridView1.DataBind();
}
Deleting A product from Product table using Linq to Sql in Asp.Net.
protected void GridView1_RowDeleting(object
sender, GridViewDeleteEventArgs e)
sender, GridViewDeleteEventArgs e)
{
int
prdid = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("id"))).Text);
prdid = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("id"))).Text);
NorthwindDataContext db = new NorthwindDataContext();
Product
prd = (from p in
db.Products where p.ProductID == prdid select p).Single();
prd = (from p in
db.Products where p.ProductID == prdid select p).Single();
db.Products.DeleteOnSubmit(prd);
db.SubmitChanges();
List<Product> product = grid_bind();
GridView1.DataSource = product;
GridView1.DataBind();
