In this article you will learn the basic operations
like Save, Edit, Update and Delete operations in asp.net using LINQ to SQL.
Newly inserting record will get displayed in ListBox control and clicking on
any record from ListBox will fetch the corresponding details from the database
and fills up the appropriate fields on the form, so that we can edit or
delete that record as shown in demo above.
Implementation
Let's create an asp.net application using LINQ to see
it in action.
- First
of all create a SQL SERVER Database and name it "jaya_db" and
also create a table "EMP_TABLE" using the script below:
CREATE TABLE [dbo].[EMP_TABLE]
(
[EMP_ID] [int] IDENTITY(1,1) NOT NULL,
[EMP_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[AGE] [int] NULL,
[DEPT] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[SALARY] [bigint] NULL
)
- Now
open visual studio ->File menu -> New -> Website. New Web site
dialog box will open as shown in image below-> Select Visual C# from
the left pane under Installed Templates and also select ASP.NET Empty Web
Site ->Select the location to save the application e.g.
D:\jaya\LinqDemo and click Ok button.
·
Now from the Website menu -> Add New Item
->Select LINQ to SQL Classes-> rename it from the default name
DataClasses.dbml to emp.dbml as shown in
image below and click on Add button.
- This
window is called ORM (Object-relational mapping).This window has two
parts. On the Left portion of this window we drag the table from
the server explorer and drop here and on Right portion we drag the
Function, procedures etc from the server explorer and drop here.
- Now
connect your database with the visual studio .In Server explorer right
click on the Data Connections and select Add connection as shown in image
below
- Now
from Server explorer expand your connected database and select and drag
and drop the EMP_TABLE to the left portion of the ORM window as shown in
image below.
- It
will automatically create the connection string in the web.config file
<connectionStrings>
<add name="emp_dbConnectionString" connectionString="Data
Source=LocalServer;Initial Catalog=emp_db;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
ASPX page:
<div >
<pre class="brush:xml">
<fieldset style="width: 400px">
<legend>Save,Edit,Update,Delete,Bind Asp.net Listview</legend>
<table style="background-color:AppWorkspace">
<tr>
<td colspan="2">
<asp:HiddenField ID="hdEmpId" runat="server" />
</td>
<td rowspan="6" valign="top">
<asp:ListBox ID="lstEmp" runat="server" AutoPostBack="True" Height="131px"
Width="134px"
Style="margin-left: 0px; margin-bottom: 0px;" OnSelectedIndexChanged="lstEmp_SelectedIndexChanged"></asp:ListBox>
</td>
</tr>
<tr>
<td width="90px">Emp Name</td>
<td>
<asp:TextBox ID="txtEmpName" runat="server" Width="181px"></asp:TextBox>
</td>
</tr>
<tr>
<td>Age</td>
<td>
<asp:TextBox ID="txtAge" runat="server" Width="181px"></asp:TextBox>
</td>
</tr>
<tr>
<td>Department</td>
<td>
<asp:TextBox ID="txtDept" runat="server" Width="181px"></asp:TextBox>
</td>
</tr>
<tr>
<td>Salary</td>
<td>
<asp:TextBox ID="txtSal" runat="server" Width="181px"></asp:TextBox>
</td>
</tr>
<tr>
<td> </td>
<td align="center">
<asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />
<asp:Button ID="btnUpdate" runat="server" Text="Update"
OnClick="btnUpdate_Click" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click" />
<br />
<asp:Button ID="btnDisplay" runat="server" Text="Display"
OnClick="btnDisplay_Click" />
<asp:Button ID="btnCancel" runat="server" Text="Cancel"
OnClick="btnCancel_Click" />
</td>
</tr>
</table>
</fieldset>
</pre>
</div>
Example.aspx.cs
public partial class ListboxwithLinq : System.Web.UI.Page
{
empDataContext obj = new empDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DisplayEmpRecords();
}
btnUpdate.Enabled
= false;
btnDelete.Enabled
= false;
btnDisplay.Enabled
= false;
}
protected void btnSave_Click(object sender, EventArgs e)
{
EMP_TABLE db = new EMP_TABLE
{
EMP_NAME
= txtEmpName.Text,
AGE
= Convert.ToInt32(txtAge.Text),
DEPT
= txtDept.Text,
SALARY
= Convert.ToInt64(txtSal.Text)
};
obj.EMP_TABLEs.InsertOnSubmit(db);
obj.SubmitChanges();
DisplayEmpRecords();
ClearControls();
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
EMP_TABLE db = obj.EMP_TABLEs.First(k => k.EMP_ID == Convert.ToInt32(hdEmpId.Value));
db.EMP_NAME
= txtEmpName.Text;
db.AGE
= Convert.ToInt32(txtAge.Text);
db.DEPT
= txtDept.Text;
db.SALARY
= Convert.ToInt64(txtSal.Text);
obj.SubmitChanges();
DisplayEmpRecords();
ClearControls();
btnSave.Enabled
= true;
}
protected void btnDelete_Click(object sender, EventArgs e)
{
EMP_TABLE db = obj.EMP_TABLEs.First(k => k.EMP_ID == Convert.ToInt32(hdEmpId.Value));
obj.EMP_TABLEs.DeleteOnSubmit(db);
obj.SubmitChanges();
DisplayEmpRecords();
ClearControls();
btnSave.Enabled
= true;
}
protected void btnDisplay_Click(object sender, EventArgs e)
{
DisplayEmpRecords();
btnDisplay.Enabled
= true;
}
private void DisplayEmpRecords()
{
var q = from p in obj.EMP_TABLEs
select new { p1 = p.EMP_ID, p2 = p.EMP_NAME };
lstEmp.DataTextField
= "p2";
lstEmp.DataValueField
= "p1";
lstEmp.DataSource
= q;
lstEmp.DataBind();
}
private void ClearControls()
{
txtEmpName.Text
= string.Empty;
txtAge.Text
= string.Empty;
txtDept.Text
= string.Empty;
txtSal.Text
= string.Empty;
hdEmpId.Value
= string.Empty;
txtEmpName.Focus();
}
protected void btnCancel_Click(object sender, EventArgs e)
{
ClearControls();
lstEmp.Items.Clear();
btnDisplay.Enabled
= true;
btnSave.Enabled
= true;
}
protected void lstEmp_SelectedIndexChanged(object sender, EventArgs e)
{
var q = (from p in obj.EMP_TABLEs
where p.EMP_ID == Convert.ToInt32(lstEmp.SelectedValue)
select p).SingleOrDefault();
if (q != null)
{
hdEmpId.Value
= q.EMP_ID.ToString();
txtEmpName.Text
= q.EMP_NAME;
txtAge.Text
= q.AGE.ToString();
txtDept.Text
= q.DEPT;
txtSal.Text
= q.SALARY.ToString();
btnUpdate.Enabled
= true;
btnDelete.Enabled
= true;
btnSave.Enabled
= false;
}
}
}
}
