LINQ to SQL Introduction with simple example, LINQ SELECT and INSERT Queries in .Net
LINQ to SQL is a component of LINQ and part of ADO.NET that provides a run-time infrastructure for mapping relational data as objects. In this article we discuss about LINQ to SQL with simple example which has LINQ Select and Insert queries.
The .NET Framework provides ADO.NET, a platform for accessing data sources such as SQL Server and XML as well as other data sources exposed through ODBC and OLE DB. Even though it is great technology, it has some limitations, such as sometimes being overly complex. While LINQ to SQL may not offer any speed advantages over previous or existing technology, it does offer the capability to build applications more quickly and efficiently.
Before going to create sample asp.net web application, Open SQL Server management studio and create emp database and empInfo table by using following SQL script.
USE [emp]
GO
/****** Object: Table [dbo].[empInfo] Script Date: 02/19/2012 15:03:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[empInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Role] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[JoiningDate] [datetime] NOT NULL,
CONSTRAINT [PK_empInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Now open visual studio, create asp.net web site. Right click on Solution explorer and select Add New Item => select LINQ to SQL Classes and name it as EmpData.dbml. Open Server Explorer=> Right Click on Server Explorer =>Select Add Connection and mention data base server name and database name just before you created(that is database name is emp here) .
Expand emp database in Server explorer, drag and drop empInfo table on to EmpData.dbml.
Then add below code to Default.aspx page which Employee Input form and Employee grid which displays all employee information from database table empInfo.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>LINQ to SQL Example</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<div>
Name:<asp:TextBox ID="txtName" runat="server"></asp:TextBox><br /><br />
Role:<asp:TextBox ID="txtRole" runat="server"></asp:TextBox><br /><br />
<asp:Button ID="btn" runat="server" Text="Submit" onclick="btn_Click" />
</div>
<br /><br />
<div>
<asp:GridView ID="gvEmp" runat="server"></asp:GridView>
</div>
</div>
</form>
</body>
</html>
Add below code to Default.aspx.cs file which inserts the employee information to empInfo database table and displays all employees’ information from empInfo database table.
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class _Default : System.Web.UI.Page
{
EmpDataDataContext db = new EmpDataDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false)
BindEmpData();
}
protected void btn_Click(object sender, EventArgs e)
{
empInfo objEmp = new empInfo();
objEmp.Name = txtName.Text;
objEmp.Role = txtRole.Text;
objEmp.JoiningDate = DateTime.Now;
db.empInfos.InsertOnSubmit(objEmp);
db.SubmitChanges();
BindEmpData();
}
void BindEmpData()
{
var empData = from emp in db.empInfos
select emp;
gvEmp.DataSource = empData;
gvEmp.DataBind();
}
}
As shown above, create object for database context EmpDataDataContext. To submit employee information to SQL Server Database, create object for empInfo database table which provided as class in EmpDataDataContext.dbml file. Provide the input for Name, Role and JoiningData and submit empInfo object to datacontext EmpDataDataContext object by using InsertOnSubmit() method. Now submit all the data to database by using SubmitChanges() method of EmpDataDataContext object.
To display the all employees information write the simple select query on empInfos class of EmpDataDataContext object as shown below.
var empData = from emp in db.empInfos
select emp;
Provide this data source to gridview DataSource property and the output display as shown below.
