Link to LINQ

1.0 What is LINQ?

LINQ stands for Language INtegrated Query. Means query language integrated with Microsoft .NET supporting languages i.e. C#.NET, VB.NET, J#.NET etc. Need not to write\ use explicit Data Access Layer.

Writing Data Access Layer require much proficiency as a Data Access Layer should be capable of at least

1. Efficient Extraction (Select) \ Add\ Update\ Delete of data.
2. Support to multiple database, ORACLE\ SQL Server\ MySQL etc.
3. Transaction Management.
4. Logging\ Tracing
5. And many more.

LINQ enables you to use all the above features in very simple and efficient way with very less codes.

2.0 Why LINQ? \ What are benefits of LINQ?

A simple architecture of any software is like

3.0 What is LINQ entity class?

A Microsoft DOT NET class which map you to or from database. This class provides you flexibility to access database in very efficient way.

Usually LINQ entity class contains that many number of partial classes how many tables are present into the database. Each partial class contains properties same as columns present into the database table. Each instance of the entity class acts as a single row.

4.0 How to generate LINQ Entity class?

.NET provides a simple utility SQLMetal.exe to generate the LINQ Entity class.

By default, the SQLMetal file is located at

Drive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin

Follow the steps below to generate LINQ entity class-

• Start - > Run
• Write cmd and click on "OK" button.
• Go to the locationdrive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin
• Type

sqlmetal /server:<SERVER NAME> /database:<DATABASE NAME> /namespace:<NAMESPASE> /code:<GENERATED CODE LOCATION> / language:csharp

Example:

sqlmetal /server:myserver /database:northwind /namespace:nwind /code:nwind.cs /language:csharp

Start using LINQ:

Suppose we have a simple database containing three tables with Structure/ Relations as follows-

Generate the LINQ Entity class for the above database (Use step 4.0 to generate entity class)

Add the newly created entity class to your project. For a better architecture LINQ Entity class should be placed into separate class library.

Create instance of the LINQ Entity class. There are various overloads of LINQ entity class.

Use the overload of the entity class which accepts Connection String as an argument.

{codecitation class="brush: csharp; gutter: true;" width="650px"}

LINQEntityClass objEntityClass = new LINQEntityClass(Configurations.ConnectionString);

{/codecitation}

1.0 How to select a record?

Create instance of LINQ Entity Class

{codecitation class="brush: csharp; gutter: true;" width="650px"}

LINQEntityClass objEntityClass = new LINQEntityClass(Configurations.ConnectionString);

{/codecitation}

1.1 How to select all Columns and all the records?

{codecitation class="brush: csharp; gutter: true;" width="650px"}

var employees =   from emp in objEntityClass.Employees

select emp;

{/codecitation}

1.2 How to use where clause?

{codecitation class="brush: csharp; gutter: true;" width="650px"}

var employees =   from emp in objEntityClass.Employees

where emp.FirstName == "Ashish"

select emp;

{/codecitation}

1.3 How to select particular columns only?

{codecitation class="brush: csharp; gutter: true;" width="650px"}

var employees = from emp in objEntityClass.Employees

select new {emp.EmpId, emp.FirstName};

{/codecitation}

1.4 Display EmpId, FirstName, Designation and Department of the employee.

If we want to select the above record without using LINQ then we will have to Join Employee table with Department and Designation tables and the Sql will look like

{codecitation class="brush: csharp; gutter: true;" width="650px"}

Select emp.EmpId, emp.Firstname, designation.Designation, dept.Deptname

FROM Employees emp, Designations designation,  Departments dept

WHERE emp.DesignationId = designation.DesignationId

AND emp.DeptId = dept.DeptId

{/codecitation}

With the help of LINQ we can obtain the same result very easily

{codecitation class="brush: csharp; gutter: true;" width="650px"}

var employees = from emp in objEntityClass.Employees

select new

{emp.EmpId, emp.FirstName, emp.Departments.DeptName,

emp.Designations.Designation};

{/codecitation}

1.5 How to use alias for any column name?

{codecitation class="brush: csharp; gutter: true;" width="650px"}

var employees = from emp in objEntityClass.Employees

select new

{ID = emp.EmpId, Name  = emp.FirstName};

{/codecitation}

1.6 How to bind LINQ data with ASP.NET GridView?

{codecitation class="brush: csharp; gutter: true;" width="650px"}

var employees = from emp in objEntityClass.Employees

select new

{ID = emp.EmpId, Name  = emp.FirstName};

//Bind the data with Grid View

gvData.DataSource = employees;

{/codecitation}

1.7 How to use joins?

{codecitation class="brush: csharp; gutter: true;" width="650px"}

var employees = from emp in objEntityClass.Employees

join dept in objEntityClass.Departments

on emp.DeptId equals  dept.DeptId

join desig in objEntityClass.Designations

on emp.DesignationId equals desig.DesignationId

select new {ID = emp.EmpId, Name  = emp.FirstName};

{/codeciation}

2.0 How to Update a Record?

{codecitation class="brush: csharp; gutter: true;" width="650px"}

Employees employee = objEntityClass.Employees.Single(emp => emp.EmpId == 1);

employee.FirstName = "Ashish";

objEntityClass.SubmitChanges();

{/codecitation}

3.0 How to Delete a Record?

{codecitation class="brush: csharp; gutter: true;" width="650px"}

//Select the record want to Delete

Employees employee = objEntityClass.Employees.Single(emp => emp.EmpId == 6);

objEntityClass.Employees.DeleteOnSubmit(employee);

objEntityClass.SubmitChanges();

{/codecitation}

4.0 How to use Transactions with LINQ?

{codecitation class="brush: csharp; gutter: true;" width="650px"}

DbTransaction trx = null;

trx = GetDBTransaction();

objEntityClass.Transaction = trx;

//Add new Department

Departments department = AddDepartments();

//Add New Designation

Designations designation = AddDesignations();

//Use the IDs of Newly Created Dept and Designation to create a New Employee

Employees emp = new Employees();

emp.EmpCode = "EMP00T";

emp.FirstName = "Transaction";

emp.LastName = "Employee";

emp.Email = "FirstName.LastName[at]company.com";

emp.DesignationId = designation.DesignationId;

emp.DeptId = department.DeptId;

emp.ManagerId = 0;

emp.Address = "Transaction Enabled Employee, India";

try

{

objEntityClass.Employees.InsertOnSubmit(emp);

objEntityClass.SubmitChanges();

trx.Commit();

}

catch

{

if(trx != null)

trx.Rollback();

}

{/codecitation}

5.0 How to Iterate / Loop through the records?

Iteration of record is very much needed for any developer to implement any software or requirement. Main drawback with the traditional iteration is the dependency on the database table structure. If any columns position (index) is changed entire iteration logic is affected. Using LINQ for iteration help us to overcome from the explained problem.

{codecitation class="brush: csharp; gutter: true;" width="650px"}

var employees = from emp in objEntityClass.Employees

select emp;

foreach (var employee in employees)

{

if (employee.FirstName != "")

{

//Do Operation

}

}

{/codecitation}

6.0 How to execute or use Stored Procedures?

6.1 Generate Entity Class for Stored Procedures

In order to use Stored Procedures using LINQ you need to create entity classes for the stored procedures in the same way created the entity class for the tables.

Follow the steps below to create LINQ Entity class for Stored Procedures

• Start - > Run
• Write cmd and click on "OK" button.
• Go to the locationdrive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin
• Type

sqlmetal /server:<SERVER NAME> /database:<DATABASE NAME> /sprocs /namespace:<NAMESPASE> /code:<GENERATED CODE LOCATION> / language:csharp

Note:

1. If you have created Database Diagram then above command will fail to generate the entity class for the Stored Procedures. You need to create a new table into your database with the name dtproperties. This table will contain following columns
2. Above class will contain system stored procedures also. So far it was not possible avoid including system Stored Procedures. May be into recent releases of SQLMetal.exe we may get this flexibility.
3. Using /sprocs will generate the complete entity class which will include Stored Procedures as well as database tables.

6.2 Execute Stored Procedures

Now your newly created entity class will contain a method with the name same as the stored procedure name. You simply need to call the method

{codecitation class="brush: csharp; gutter: true;" width="650px"}

var employees = objEntityClass.GetEmployeeByName("");

foreach (var emp in employees)

{

if (emp.EmpCode != "")

{

//Do Operation

}

}

{/codecitation}

In the example shown above GetEmployeeByName is the name of the Stored Procedure which accepts name of the employee as an argument.

7.0 How to use Source Code Provided?

Follow the steps below to use the Source Code provided along with the article to understand LINQ

1. Restore SQL Server Database Backup with the name LINQ
2. Modify App.Config file with the suitable connection string

3. Now you can double click LINQSample.sln file to open the solution.

Feel free to ask any query by writing mail at ak.tripathi@yahoo.com with subject line LINQ

Thank you

Ashish