LINQ

 aktripathi.wordpress.com/2009/01/08/linq-for-beginners/
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

LINQ Vs Without LINQ

3.0 What is LINQ entity class?

A .NET class(s) which maps you to\ 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. Instance of the entity class acts as a single row.

4.0 How to generate LINQ Entity class?

Using Visual Studio IDE:

If you are using visual studio IDE then its very simple to create LINQ entity classes. Follow the steps below to create LINQ entity classes in your .NET Winform project

1. Go to Start –> Microsoft Visual Studio 2008
2. Once VS 2008 IDE is launched. Go to File –> New –> Project
3. A “New Project” dialog would open. Select Windows Form Application templates from the templates listed right side and click ‘OK’. (Make sure you have selected right language from left panel and .NET Framework 3.5 is selected on top right)
   
4. This action will create a new windows form project with name “Windows Form Application1″ having default form Form1.
5. Now in order to generate LINQ entity class Right click on project i.e. WindowsFormApplication1 node available on the right side tree.
   
6. Select Add–> New Item
7. A new dialog “Add New Item” would be opened. Select “LINQ to SQL Classes” from the various templates listed on right side and Click on Add button.
   
8. Above action will bring Object Relational Designer for you. Click on Server Explorer Link available. This will bring a Server Explorer on left side. Right Click on “Data Connection” and select “Add Connection..”
   
9. Now you will see a new dialog “Add Connection”. Provide your database information i.e. Server/ Username/ Password/ Database Name ad hit OK button.
   
10. Above action will bring your desired database connection as a child node into “Data Connection” tree available on left.
11. Select all the tables available and drag them to the middle area.
    
12. You might get a dialog regarding saving sensitive information. You may choose ‘No’.
    
    
13.  Now you will see the database diagram on the center panel. Save the .dbml file and build if required.
14. Now, you are done with your entity class creation.

Without Using Visual Studio IDE

In case you don not have Visual Studio IDE, Mi.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-

1. Start – > Run
2. Write cmd and click on “OK” button.
3. Go to the location drive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin
4. 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

SQLMetal to generate LINQ entity class

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

Relational Database Diagram For Sample Database

• 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.

1.0 How to select a record?
As explained earlier that in order to access database first of all we need to create instance of entity class. Below is the lin of code which creates the instance of the entity class,

DataClasses1DataContext objEntityClass= new DataClasses1DataContext();

1.1 How to select all Columns and all the records?


1.2 How to use where clause?

Same LINQ Sql may be written with the help of Lambda Expression as well in only one line of code

Employee employee = _dataContext.Employees.Single(emp => emp.FirstName == “First Name”);
1.3 How to select particular columns only?

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

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

1.5 How to use alias for any column name?

1.6 How to bind LINQ data with GridView?

1.7 How to use Joins?

2.0 How to Update a Record?


3.0 How to Delete a Record?

4.0 How to use Transactions with LINQ?

5.0 How to Iterate / Loop through the records?

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 location drive:\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