by Eranda Niroshan

How many software tasks DON’T involve reading through data? Answer: very few. Developers work all the time with database data, XML data, DataSets, collections, lists, and arrays-all with different syntax and functionality for each one. That is why this series of articles are so important. While LINQ is a large topic I’m going to describe it lesson by lesson. Before this lesson let’s remember what we have discussed last time.

      “LINQ is a set of extensions to the .NET Framework to query different types of data using a common language. The core of LINQ is a set of API with interfaces and classes. Basically the architecture of the Language Integrated Query is divided into three main categories LINQ to Object, LINQ to ADO.Net and LINQ to XML. LINQ to SQL is the main category which maps relational schema into Object oriented model expressed in the programming language.”

In this lesson I’m going to describe basic functionalities of LINQ to SQL category. Same as ADO.Net before perform any function we have to create a connection with the database. But in LINQ its differ than ADO.Net let’s see how to Add a Database Connection to the Project

Add a Database Connection to the Project

First right click on the project title under solution explorer and then select Add à New Item.

it will open a window to add new items into the project (Figure 1).

Then select Data from “Categories:” and LINQ to SQL Classes from “templates:”. Enter a name to the class and press Add button(Figure 2).

Figure 1 – Solution Explorer right click menu

Figure 2 – Add new Item Window

    Double click the “LINQclassName.dbml” file that you created. You can see still it doesn’t contain any item (Figure 3).

            You can insert database table, stored procedure, function, view or whatever you created inside the database.

   Select the database from server explorer and expand the Table node. Then drag and drop tables into the free area (Figure 4).     

Figure 3 – dbml file before Insert Items

Figure 4 – After drag and drop tables into the dbml class

Now that you’ve created the DataContext using LINQ to SQL entity class and ready to use. It’s time to learn basic SQL functionalities in LINQ perspective.

 

    Insert Data

   Update Data

    Delete Data

Insert Data

Previous versions of the visual studio included ADO.Net to communicate with databases. ADO.Net you should have executable SqlCommand and query String which follows the sql insert syntax, but in LINQ the basic concept behind insert data is create an entity object and send it Into DataContext (“DataContext” is the class that we created previously in this lesson which map programming codes to SQL codes).

Example:

 

In this example “db” is the object that created from DataContext class. It provides reference to the entire table in the database. InsertOnSubmit() method will send customer object into entity class. SubmitChanges() method will send intermediate data into the database. Without that method you cannot send data to actual database.

 Update Data

Same as insert function before update we have read data to object, then make whatever changes to the object and finally send that particular object to the database.

Example:

Delete Data

there also we have to read data to object before delete it. DeleteOnSubmit() method will send customer object into entity class to delete. SubmitChanges() method will send intermediate data into the database. Without that method you cannot delete data from actual database.

Example:

 

This is only the basic functions of LINQ to SQL category you can find more details from http://msdn.microsoft.com/en-us/library/bb629289.aspx

Previous Article