C#: LINQ to SQL

Updated on September 21, 2015     Kisan Patel

LINQ to SQL is a component of .Net Framework, and is specifically designed for working with SQL Server database. It provides a run-time infrastructure for managing relational data as objects.

It allows you to write queries to retrieve and manipulate data from the SQL Server. Using LINQ to SQL, you can retrieve the data from the database, insert, update and delete rows from the table.

LINQ to SQL also supports transactions, views and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model.

LINQ to SQL is an object-relational mapping (ORM) framework that allows the direct one-to-one mapping of a microsoft SQL Server database to .Net classes. Using LINQ to SQL ORM mapping, the classes that match the database table are created automatically from the database itself and you can start using the classes immediately.

Let’s take one example, LINQ to SQL, in which you can work with LINQ to SQL.

Here, we will create console application named “LINQtoSQLDemo”.

Now, Right-click the Solution name in Solution Explorer and select the Add New Item option from the context menu. This opens the Add New Item dialog box.

In this dialog box, select the LINQ to SQL Classes template named as “NorthwindClasses.dbml”. Click the Add button. The Object Relational Designer opens.

Now, Drag and drop the tables from Server Explorer. This action adds tha table to your application, and you can retrieve any data from the table. In this case, the Customers table from the Northwind database is added to ORM Designer as shown in below screenshot.

Now, let’s open Program.cs page and add below line of code.

using System;

namespace LINQtoSQLDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            NorthwindClassesDataContext nwContext = new NorthwindClassesDataContext();

            var Customers = nwContext.Customers;

            foreach (var Customer in Customers)
            {
                Console.WriteLine(Customer.CompanyName + " - " + Customer.Country);
            }
        }
    }
}

In the example, an instance of the data context NorthwindClassesDataContext is created, and then a query is formed to get all of the values from the “Customers” table.

The NorthwindClassesDataContext class was generated by the Visual Studio Object Relational Designer.

Now, let’s run the application by pressing F5 key on the keyboard. The output of the application appears as shown in below screenshot.