working With Linq in asp.net(Insert,Update,delete,Bind gridview using LINQ)
Work with Linq(language Integrated
query) In asp.net
This article is about LINQ which I think is one of
the most exciting features in Orcas. LINQ makes the concept of querying a
first-class programming concept in .NET. The data to be queried can take the
form of XML (LINQ to XML), databases (LINQ-enabled ADO.NET: LINQ to SQL, LINQ
to Dataset and LINQ to Entities) and objects (LINQ to Objects). LINQ is also
highly extensible and allows you to build custom LINQ enabled data providers
(e.g.: LINQ to Amazon, LINQ to NHibernate, LINQ to LDAP).
In this articles we
mainly do insert, update ,delete ans show data in gridview using Linq.
For working with Linq
we initially design our page with some input controls.
So lets proceed now….
Step 1: Design Database tables.
Create table in your
Database..
CREATE TABLE [dbo].[GridView](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
[age] [int] NULL,
[salary] [decimal](18, 0) NULL,
[country] [varchar](50) NULL,
[city] [varchar](50) NULL,
CONSTRAINT
[PK_GridView] 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]
Here my table name is gridview.
Step 2 :
design Your page with
some input controls..
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
Name : <asp:TextBox ID="txt_name"
runat="server"></asp:TextBox>
<br />
Age : <asp:TextBox ID="txt_age"
runat="server"></asp:TextBox>
<br />
Salary : <asp:TextBox ID="txt_sal"
runat="server"></asp:TextBox>
<br />
Country :
<asp:TextBox ID="txt_country"
runat="server"></asp:TextBox>
<br />
City : <asp:TextBox ID="txt_city"
runat="server"></asp:TextBox>
<br />
<asp:Button ID="btn_submit"
runat="server"
Text="Submit"
onclick="btn_submit_Click"
/>
</div>
</form>
</body>
</html>
It normally look like …
Step 3: Add ling to Sql class from your solution Explore..
I used here its name as
linqtest.
Now in your linqtest.dbml drag yor working tables
here.
Save it..
Step 3: Work on .cs
Uses Some Namespaces
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
for insert new record in table
public partial class linq1 :
System.Web.UI.Page
{
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["usgConnectionString"].ToString());
protected void
Page_Load(object sender, EventArgs e)
{
}
protected void
btn_submit_Click(object sender, EventArgs e)
{
SaveCustomerInfo();
}
private void
SaveCustomerInfo()
{
using (linqtestDataContext
context = new linqtestDataContext(con))
{
GridView cust = new
GridView();
cust.name = txt_name.Text;
cust.age = Convert.ToInt32(txt_age.Text);
cust.salary = Convert.ToInt32(txt_sal.Text);
cust.country = txt_country.Text;
cust.city = txt_city.Text;
//Insert the new record in grdview object
context.GridViews.InsertOnSubmit(cust);
//Sumbit changes to the database
context.SubmitChanges();
//Display Message for successful operation
LiteralMessage.Text = "<p
style='color:Green;'>Information Successfully saved!</p>";
}
}
}
Bind Dropdownlist Using Linq
<asp:LinqDataSource ID="LinqDataSource1"
runat="server"
ContextTypeName="linqtestDataContext"
TableName="GridViews"
EnableDelete="True"
EnableInsert="True" >
</asp:LinqDataSource>
select
name for delete ::
<asp:DropDownList ID="DropDownList1"
runat="server"
DataSourceID="LinqDataSource1" DataTextField="name" DataValueField="id">
</asp:DropDownList>
<br />
<br />
<asp:Button ID="btn_up" runat="server"
Text="update"
onclick="btn_up_Click"
/>
Here I use LinqDataSource efor this purpose.
Update record …
I am using
dropdown id for record reference ,to which row I am going to update.
Write it on Update
button Click event.
protected void
btn_up_Click(object sender, EventArgs e)
{
using (linqtestDataContext
context = new linqtestDataContext(con))
{
int Username = Convert.ToInt32(DropDownList1.SelectedValue);
////Update a user
GridView editUser = context.GridViews.Single(u
=> u.id == Username); //To edit user that matches
the Username
LiteralMessage.Text = "Upadate :"+DropDownList1.SelectedItem.Text;
editUser.name = txt_name.Text;
editUser.age = Convert.ToInt32(txt_age.Text);
editUser.salary = Convert.ToInt32(txt_sal.Text);
editUser.country = txt_country.Text;
editUser.city = txt_city.Text;
context.SubmitChanges();
}
}
For Delete Record from table.
Write this
code on delete button event.
protected void
btn_del_Click(object sender, EventArgs e)
{
using (linqtestDataContext
context = new linqtestDataContext(con))
{
//Create a new instance of the Customer object
int UsernameDelete = Convert.ToInt32(DropDownList1.SelectedValue);
GridView
toDelete = context.GridViews.Single(p => p.id == UsernameDelete); //Delete user that
matches Username
context.GridViews.DeleteOnSubmit(toDelete);
context.SubmitChanges();
LiteralMessage.Text = DropDownList1.SelectedItem.Text + " Deleted succesfully";
}
}
Bind Gridview using Linq
Design
gridview on .aspx
<span class="style2">Bind gridview<br />
<br />
<asp:GridView ID="gr" runat="server"
HeaderStyle-BackColor="AntiqueWhite">
</asp:GridView>
Bind
gridview on Page_load of .cs
protected void Page_Load(object sender, EventArgs
e)
{
using (linqtestDataContext
context = new linqtestDataContext(con))
{
////bind gridview
//
myContextDataFile db = new myContextDataFile();
////simple query
var results =
from users in
context.GridViews
select users;
//database command object
DbCommand dc = context.GetCommand(results);
gr.DataSource = results;
gr.DataBind();
}
}
The whole page look like this
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["usgConnectionString"].ToString());
ReplyDeletethis is giving me an error, abject reference not set to an instance of an object.
Please check.
ssssssss
Deletethank you solved
ReplyDelete