Language integrated query or LINQ enables you to write SQL like syntax in programming language itself. LINQ contains different types of operators, which is used in language itself. In LINQ, the same query can be used in, an SQL database, a DataSet, an array of objects in memory and so many other data types.
Linq To SQL
Link to SQL is Object Relation Mapping (ORM) framework. It creates the strongly typed dot net classes based on database tables. LINQ to SQL enables you to write Select, Insert, Update, Delete query after after strongly typed dot net classes generated. Behind the seen Link to SQL provider converts LINQ query to normal SQL query, which is understands by SQL server database. LINQ to SQL type only supports SQL server database. It also supports Views, StoredProcedures, and Transactions.
First we will create the database table, name tblEmps in SQL server as:
You can create the table according to your need and provide the table name.
Now Right click on the project (or project folder) and select the option Add New Item.
Select LINQ to SQL Classes Template and Give Name as Emp.dbml. You can provide the name according to application requirement. Emp.dbml file will be added under App_Code folder.
Click on Server Explorer and Right click on the Data Connections and select the option Add Connection.
Add Connection Pop up window will be opened, provide the SQL Server details and click on OK button.
Database will be added under Data Connections as shown below.
Drag the table in the left pane. If primary key & foreign key relations are there then it will automatically displayed. I have not created the foreign key explicitly. Therefore the relation is not shown.
This process generates the Emp.dbml.layout and Emp.designer.cs file under the Emp.dbml
It will also create the connection string automatically in web.config file.
<configuration>
<connectionStrings>
<add name="EmployeeConnectionString" connectionString="Data Source=.;Initial Catalog=Employee;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Click on Emp.designer.cs file, you will see EmpDataContext class that is inherited from DataContext class. Remember that we have created the Emp.dbml file and it will generate the EmpDataContext class. Whatever file name you will provide, it generates the class with class name appended with DataContext as suffix.
using System;
using System.Collections.Generic;
using System.Linq;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if(! IsPostBack)
{
LoadData();
}
}
protected void LoadData()
{
EmpDataContext dbContext = new EmpDataContext();
GridView1.DataSource= from emp in dbContext.tblEmps
select emp;
GridView1.DataBind();
}
}
LINQ have rich set of operators. It provides the operator for Filtering, Sorting, Grouping, Aggregation, Projection, Partitioning, Concatenation etc.
You can use any operators with the LINQ to get the result.
You can create a projection that matches the employee object structure. In the above query it selects all the records. If you want that few column should be display, then you can use linq query as
from emp in dbContext.tblEmps
select new{emp.EmpID , emp.Name};
Controlling column ordering and column name using LINQ
LINQ enables you to change the order of column for display. You can also provide the custom name for column.
from emp in dbContext.tblEmps
select new { DepartmentID=emp.DEPID,EmployeeID=emp.EmpID,EmpName=emp.Name };
Query filters
LINQ provides the where clause to filter the query.
Suppose that you want the record of those employees whose name is starts with letter “R” and salary greater than 20000.
from emp in dbContext.tblEmps
where emp.Name.StartsWith("D") && emp.Salary>20000
select emp;
Insert record using LINQ
For inserting the record into the database using LINQ, first create the object of EmpDataContext class.
EmpDataContext dbContext = new EmpDataContext();
In our example the table name is tblEmps. LINQ automatically generates the tblEmps as a class and the table column as properties.
Now create the object of tblEmps class, so you can access the properties of this class.
InsertOnSubmit() method is used to insert the record. SubmitChanges() method commits all the changes into the database.
Please refer the previous code for populate the Gridview using LINQ. In above example we have used LoadData() method for this purpose.
protected void btnInsert_Click(object sender, EventArgs e)
{
using(EmpDataContext dbContext =new EmpDataContext())
{
tblEmp empObj = new tblEmp
{
EmpID=Convert.ToInt32( txtEmpID.Text),
Name=txtName.Text,
Gender=txtGender.Text,
Salary=Convert.ToDouble( txtSalary.Text),
Address=txtAddress.Text,
DEPID = txtDepID.Text
};
dbContext.tblEmps.InsertOnSubmit(empObj);
dbContext.SubmitChanges();
}
GetData();
}
Update record using LINQ
For updating the record, first we need a unique ID of employee whose record we want to update. For getting single record, SingleOrDefault is used in LINQ. After that SubmitChanges() method is used to commit the updated record in database.
In the given below code, you can easily see that, the table is converted into the class and the column name is converted into the properties of tblEmps class.
protected void btnUpdate_Click(object sender, EventArgs e)
{
using (EmpDataContext dbContext = new EmpDataContext())
{
tblEmp obj=dbContext.tblEmps.SingleOrDefault(em => em.EmpID == Convert.ToInt32(txtEmpID.Text));
obj.Name=txtName.Text;
obj.Gender=txtGender.Text;
obj.Salary=Convert.ToDouble( txtSalary.Text);
obj.Address=txtAddress.Text;
obj.DEPID = txtDepID.Text;
dbContext.SubmitChanges();
}
GetData();
}
Delete record using LINQ
For deleting the record, we also need a single record according to unique ID of employee. Here for deletion DeleteOnSubmit() method is used.
protected void btnDelete_Click(object sender, EventArgs e)
{
using (EmpDataContext dbContext = new EmpDataContext())
{
tblEmp obj = dbContext.tblEmps.SingleOrDefault(em => em.EmpID == Convert.ToInt32(txtEmpID.Text));
dbContext.tblEmps.DeleteOnSubmit(obj);
dbContext.SubmitChanges();
}
GetData();
}
In this given example only seven records are available. Here we have entered an eighth record, when you click on AddRecord button, btnInsert_Click method will be executed and record is added in the database.
Getting the actual sql query generated by LINQ
As you know that SQL server can understand only standard SQL queries and objects. Therefore LINQ to SQL convert LINQ queries into standard SQL query format.
You can easily see the sql query, which is generated by LINQ to SQL.
There are several methods to achieve for the same.
EmpDataContext dbContext = new EmpDataContext();
var selectQuery = from emp in dbContext.tblEmps
select emp;
You can use the log property of DataContest class. It writes the generated sql query at provided I/O.
dbContext.Log = Response.Output;
Second Method:
You can directly use the ToString() method as:
Response.Write(selectQuery.ToString());
Third Method:
GetCommand method of DataContext class provides the information about sql command generated by LINQ to SQL.
string sqlQuery=dbContext.GetCommand(selectQuery).CommandText;
Response.Write(sqlQuery);
Using stored procedures with LINQ to SQL
First we will create the stored procedure in SQL server
Select Stored Procedure
CREATE PROCEDURE GetAllEmployees
AS
BEGIN
SELECT * from tblEmps
END
Now open the Emp.dbml file that we have already created in previous example. Open server explorer and refresh the data connection. Select your stored procedure and drag it on the designer pane. In LINQ to SQL if you drag Stored Procedure on designer pane, it will converted to method.
Here Stored Procedure name is GetAllEmployees, therefore we will get the GetAllEmployees() method.
We can directly call this method using dbContext object. The rest of the code is same as prevoius one.
Example
using System;
using System.Collections.Generic;
using System.Linq;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetData();
}
}
protected void GetData()
{
EmpDataContext dbContext = new EmpDataContext();
var selectQuery = from emp in dbContext.GetAllEmployees()
select emp;
GridView1.DataSource = selectQuery;
GridView1.DataBind();
}
Insert, Update, and Delete using stored procedure
First we create the stored procedures in the SQL server for the same as follows.
Insert Stored Procedure
CREATE PROCEDURE InsertEmployees
@EmpID integer,
@EmpName varchar(100),
@Gender varchar(50),
@Salary money,
@Address varchar(200),
@DepID integer
AS
BEGIN
-- Insert statements for procedure here
insert into tblEmps values(@EmpID,
@EmpName ,
@Gender ,
@Salary ,
@Address ,
@DepID )
END
CREATE PROCEDURE UpdateEmployees
@EmpID integer,
@EmpName varchar(100),
@Gender varchar(50),
@Salary money,
@Address varchar(200),
@DepID integer
AS
BEGIN
update tblEmps set name=@EmpName,
Gender=@Gender ,
Salary=@Salary ,
Address=@Address ,
DepID=@DepID where EmpID=@EmpID
END
CREATE PROCEDURE DeleteEmployees
@EmpID integer
AS
BEGIN
delete tblEmps where Empid=@EmpID
END
Now refresh the DataConnection on server explorer and drag these stored procedure on Emp.dbml desiner pane as we have already done with select stored procedure.
Emp.dbml file
Here you can easily see that all stored procedure in LINQ to SQL are converted into the respective C# functions. You can directly call these functions by using DataContext object.
Before using these functions in C# by DataContext object, we have to map these function with tblemps entity. For doing mapping we have to configure it with tblemps entity in Emp.dbml designer pane.
Right click on tblemps and select configure behavior. A new configure behavior window will open.
Click on customize radio button, select the respective method name from the dropdownlist and click on apply button. You have to perform same thing with all methods related with stored procedure.
We have already done the coding part previously. If you have done the configure behavior, then there is no need to change in the coding section, that we have already done in insert, update, delete operation without using stored procedure.
You can also check that stored procedure is executing in place of normal sql query. For seeing this you have to use sql server tool called “SQL server profiler”. Open the SQL server profiler and execute your web application. Perform the Insert, update, and Delete operations, you will see that Stored Procedure (SP) is executed.
