Introduction
This is an ASP.NET Linq demo.
Background
If you have knowledge of bind datagrid
and add, update and delete record through the ASP.NET application and no idea about the Linq integration, then this article will give you an idea of how to use Linq.
Using the Code
This is a sample application for beginner users who want to start the Linq implementation with ASP.NET.
Step 1
Create a new Web application project:
Step 2
Create a Web Form:
<asp:Button ID="btnView" runat="server" Text="View" OnClick="btnView_Click" />
<asp:Button ID="btnAddNew" runat="server" Text="Add New" OnClick="btnAddNew_Click" />
<table id="tblForm" runat="server">
<tr>
<td>Country Name:</td>
<td><asp:TextBox ID="txtCountryName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Country Code:</td>
<td><asp:TextBox ID="txtCountryCode" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="btnSave" runat="server" Text="Add"
OnClick="btnSave_Click" />
<asp:Button ID="btnUpdate" runat="server" Text="Update"
OnClick="btnUpdate_Click" />
<asp:Button ID="btnCancel" runat="server" Text="Cancel"
OnClick="btnCancel_Click" />
</td>
</tr>
</table>
<table id="tblGrid" runat="server">
<tr>
<td><asp:Label ID="lblID" runat="server" Visible="false"></asp:Label>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"
OnRowCommand="GridView1_RowCommand">
<Columns>
<asp:BoundField DataField="Countryid" HeaderText="ID" />
<asp:BoundField DataField="CountryName"
HeaderText="Country Name" />
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lnkEdit" runat="server"
CommandArgument='<%# Eval("Countryid")%>'
CommandName="cmdEdit" Text="Edlit"></asp:LinkButton>
---
<asp:LinkButton ID="lnkDelete" runat="server"
CommandArgument='<%# Eval("Countryid")%>'
CommandName="cmdDelete" Text="Delete">
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
Step 3
Add a new item – Linq to SQL classes:
Step 4
Create a database table -- tblCountry
:
CREATE TABLE [dbo].[tblCountry](
[Countryid] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CountryCode] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Status] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_tblCountry] PRIMARY KEY CLUSTERED
(
[Countryid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Step 5
Connect the database to the project:
Step 6
Select the table for Linq table integration:
Step 7
Set database connection string in web.config file:
<appSettings>
<add key="constr" value="Data Source = machinename\SQLEXPRESS;
Initial Catalog=test;User ID=tuser; password=tpass"/>
</appSettings>
Step 8
Write Insert record method:
protected void btnSave_Click(object sender, EventArgs e)
{
using(DLCountryiesDataContext countries = new DLCountryiesDataContext())
{
tblCountry country = new tblCountry
{
CountryName = txtCountryName.Text.Trim(),
CountryCode = txtCountryCode.Text.Trim(),
Status = "Active"
};
countries.Connection.ConnectionString =
System.Configuration.ConfigurationManager.AppSettings["constr"];
countries.tblCountries.InsertOnSubmit(country);
countries.SubmitChanges();
ViewData();
}
}
Step 9
Write method View Records in Gridview
:
private void ViewData()
{
ClearForm();
DLCountryiesDataContext db = new DLCountryiesDataContext();
db.Connection.ConnectionString =
System.Configuration.ConfigurationManager.AppSettings["constr"];
var country =
from c in db.tblCountries
select c;
GridView1.DataSource = country;
GridView1.DataBind();
}
Step 10
Write methods for edit and delete records:
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "cmdEdit")
{
ClearForm();
DLCountryiesDataContext db = new DLCountryiesDataContext();
db.Connection.ConnectionString =
System.Configuration.ConfigurationManager.AppSettings["constr"];
var country =
from c in db.tblCountries
where (c.Countryid == Convert.ToInt32(e.CommandArgument.ToString()))
select c;
lblID.Text = e.CommandArgument.ToString();
foreach (tblCountry cntry in country)
{
txtCountryName.Text = cntry.CountryName;
txtCountryCode.Text = cntry.CountryCode;
}
btnSave.Visible = false;
btnUpdate.Visible = true;
}
if (e.CommandName == "cmdDelete")
{
DLCountryiesDataContext db = new DLCountryiesDataContext();
db.Connection.ConnectionString =
System.Configuration.ConfigurationManager.AppSettings["constr"];
var country =
from c in db.tblCountries
where (c.Countryid == Convert.ToInt32(e.CommandArgument.ToString()))
select c;
db.tblCountries.DeleteAllOnSubmit(country);
db.SubmitChanges();
ViewData();
}
}
Step 11
Write method for update record:
protected void btnUpdate_Click(object sender, EventArgs e)
{
DLCountryiesDataContext db = new DLCountryiesDataContext();
db.Connection.ConnectionString =
System.Configuration.ConfigurationManager.AppSettings["constr"];
var country = db.tblCountries.Single
(p => p.Countryid == Convert.ToInt32(lblID.Text));
country.CountryName = txtCountryName.Text.Trim();
country.CountryCode = txtCountryCode.Text.Trim();
db.SubmitChanges();
ViewData();
}
Step 12
Common methods for page (it's not mandatory to use in page):
protected void btnView_Click(object sender, EventArgs e)
{
ViewData();
HideForm();
}
protected void btnAddNew_Click(object sender, EventArgs e)
{
ShowForm();
btnSave.Visible = true;
btnUpdate.Visible = false;
}
protected void btnCancel_Click(object sender, EventArgs e)
{
ViewData();
HideForm();
}
private void HideForm()
{
tblForm.Visible = false;
tblGrid.Visible = true;
btnView.Visible = false;
btnAddNew.Visible = true;
}
private void ShowForm()
{
tblForm.Visible = true;
tblGrid.Visible = false;
btnView.Visible = true;
btnAddNew.Visible = false;
}
private void ClearForm()
{
txtCountryCode.Text = "";
txtCountryName.Text = "";
}
Step 13
Run the project.
End.
History
- 29th April, 2011: Initial version