LINQ To SQL Insert,Update,Delete
The process of inserting updating and deleting from LinQ to SQL.
Create Database name (LINQ2SQL)
USE [LINQ2SQL]
CREATE TABLE [dbo].[Department](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DeptName] [varchar](50) NULL,
CONSTRAINT [PK_Department] 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]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Employee] Script Date: 06/08/2012 16:05:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](50) NULL,
[Salary] [varchar](50) NULL,
[DeptId] [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]
GO
SET ANSI_PADDING OFF
GO
/****** Object: ForeignKey [FK_Employee_Department] Script Date: 06/08/2012 16:05:13 ******/
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Department] FOREIGN KEY([DeptId])
REFERENCES [dbo].[Department] ([Id])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Department]
GO
Creating a LINQ 2 SQL class to Map Tables
- Take One Class Library from New Projects name it as DAL.
- Right click on project from solution explorer and Add New Item LINQ to SQL Classes
- Create connection from Server Explorer by right click on the data connection and drag and drop the tables.
- Build The application.
Implementing the LinQ through Code Behind
- Take One Web Application from New Projects.
- Right click on the Project from solution explorer and click on Add References.
- And Select the builded DAL application's DAL.dll file from Browse -> DAL -> Bin -> Debug-> DAL.dll.
in Default.aspx page.
<table border="2" cellspacing="5" cellpadding="5" width="70%">
<tr>
<td align="right">
DepartMent Name
</td>
<td>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td align="right">
</td>
<td>
<asp:Button ID="Button1" runat="server" Text="Save" Width="100"OnClick="Button1_Click" />
</td>
</tr>
<tr>
<td align="right">
Department
</td>
<td>
<asp:DropDownList ID="DropDownList1" runat="server" Width="130px"AutoPostBack="True">
</asp:DropDownList>
</td>
</tr>
<tr>
<td align="right">
Employee Name
</td>
<td>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<asp:HiddenField ID="HiddenField1" runat="server" />
</td>
</tr>
<tr>
<td align="right">
Employee Sal
</td>
<td>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td align="right">
<asp:Button ID="Button2" runat="server" Text="Save" Width="100"OnClick="Button2_Click" />
</td>
<td>
<asp:Button ID="Button3" runat="server" Text="Update" Width="100"OnClick="Button3_Click" />
<asp:Button ID="Button4" runat="server" Text="Delete" Width="100"OnClick="Button4_Click" />
</td>
</tr>
</table>
</br>
<asp:GridView ID="GridView1" runat="server" Width="487px" AutoGenerateSelectButton="True"
OnSelectedIndexChanged="GridView1_SelectedIndexChanged" CellPadding="4"
ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<SortedAscendingCellStyle BackColor="#FDF5AC" />
<SortedAscendingHeaderStyle BackColor="#4D0000" />
<SortedDescendingCellStyle BackColor="#FCF6C0" />
<SortedDescendingHeaderStyle BackColor="#820000" />
</asp:GridView>
in Default.aspx.cs page.
- Add the Namespace DAL
using DAL;
using System.Linq;
public partial class _Default : System.Web.UI.Page
{
ExampleDALDataContext contextVar = new ExampleDALDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fillDropdown();
FillGrid();
}
}
// Adding The Department in DB
protected void Button1_Click(object sender, EventArgs e)
{
Department depart = new Department()
{
DeptName = TextBox1.Text
};
contextVar.Departments.InsertOnSubmit(depart);
contextVar.SubmitChanges();
fillDropdown();
TextBox1.Text = string.Empty;
}
// Inserting the Employee Details in Employee Table
protected void Button2_Click(object sender, EventArgs e)
{
Employee empy = new Employee()
{
DeptId = int.Parse(DropDownList1.SelectedValue),
EmpName = TextBox2.Text,
Salary = TextBox3.Text
};
contextVar.Employees.InsertOnSubmit(empy);
contextVar.SubmitChanges();
TextBox3.Text = string.Empty;
TextBox2.Text = string.Empty;
FillGrid();
}
// Updating the Employee Details in Employee Table
protected void Button3_Click(object sender, EventArgs e)
{
var EditEmp = contextVar.Employees.Single(x => x.Id == int.Parse(HiddenField1.Value));
EditEmp.DeptId = int.Parse(DropDownList1.SelectedValue);
EditEmp.EmpName = TextBox2.Text;
EditEmp.Salary = TextBox3.Text;
contextVar.SubmitChanges();
TextBox3.Text = string.Empty;
TextBox2.Text = string.Empty;
FillGrid();
}
// Deleting the Employee Details in Employee Table
protected void Button4_Click(object sender, EventArgs e)
{
var DeleteEmp = contextVar.Employees.Single(x => x.Id ==int.Parse(HiddenField1.Value));
contextVar.Employees.DeleteOnSubmit(DeleteEmp);
contextVar.SubmitChanges();
TextBox3.Text = string.Empty;
TextBox2.Text = string.Empty;
FillGrid();
}
void fillDropdown()
{
DropDownList1.Items.Clear();
DropDownList1.DataSource = contextVar.Departments.OrderByDescending(x => x.Id);
DropDownList1.DataTextField = "DeptName";
DropDownList1.DataValueField = "Id";
DropDownList1.DataBind();
}
void FillGrid()
{
var gdata = from ep in contextVar.Employees
select new
{
Id = ep.Id.ToString(),
Name = ep.EmpName,
Salary = ep.Salary,
Department = ep.Department.DeptName
};
GridView1.DataSource = gdata;
GridView1.DataBind();
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
HiddenField1.Value = GridView1.SelectedRow.Cells[1].Text;
TextBox2.Text = GridView1.SelectedRow.Cells[2].Text;
TextBox3.Text = GridView1.SelectedRow.Cells[3].Text;
}
}
No comments:
Post a Comment