An Overview Of LINQ To SQL

If you're new to LINQ, I highly recommend reading Introduction to LINQ before reading this article. 

 

LINQ to SQL is a mapping between the Relational Database Schema and Objects. The relational data can be manipulated using LINQ by its mapped objects. When an object is linked to relational data, it receives attributes of the relational data. The mapping is done by translating the relational database schemas into object definitions and this can be done automatically by using the LINQ to SQL Tools in Visual Studio,  which are called Object Relational Designer (O/R Designer). The Object Relational Designer is an editor which is used to create the LINQ to SQL mapping. The Object Relational Designer has two panels. The Left side panel is the designer panel where we can drag and drop the required tables from the Server explorer and the right side panel is used to perform advanced configurations like mapping to stored procedures or functions from the database. Following are the basic steps to add LINQ to SQL:

1. Open a new project.
   
   
2. Add a new database connection using the Server Explorer.
   
   
3. From Server Explorer, right click on Data Connections and click the Add Connection option. Following is the snapshot of the Server Explorer,
   
   

4. From Add Connection window, select the Server name where the SQL server is installed or enter the complete path of the target SQL server instance name in the Server Name text box.
   
   
5. Now select the target database name from the Select menu or enter a database name dropdown list and click Ok. Following is the snapshot of the Add Connection window,
   
   

6. Now to add LINQ to SQL, right click on the project name from the Solution Explorer and click the Add option then click the New Item option. Following is the snapshot of the Add New Item window,
   
   

7. Now from Add New Item window, click on LINQ to SQL classes. The default name of the LINQ to SQL classes is DataClasses1.dbml. We can also change the default name to any other name. Following is the snapshot,
   
   

8. Now select the required tables from the server Explorer and drag into the DataClasses1.dbml left side panel
   
   
9. The LINQ to SQL classes (DataClasses1.dbml) has a toolbox which we can use to set the associations or relationships between the tables.
   
   
10. When the LINQ to SQL Classes is created and the required tables are added then it creates a DataContext DataClasses1DataContext. The DataClasses1DataContext is the default name of the DataContext and it depends on the name of the DataClasses1.dbml. Following is the snapshot of the DataClasses1DataContext.dbml,
    
    

Now LINQ to SQL is ready to use. Just declare an object from the created DataContext DataClasses1DataContext and performs LINQ query on this object.

Programming Examples of LINQ to SQL

The following programming examples explain LINQ to SQL and demonstrate how to display the Data Model of the .dbml, how to display data from the database using LINQ to SQL and how to manipulate the database data.

Program # 1

Write a program that retrieves the tables list of the DataContext .dbml class.

C# Program

using System;
using System.Linq;
namespace ConsoleApplication1 {
    class Program {
        static void Main(string[] args) {
            DataClasses1DataContext dbcontext = new DataClasses1DataContext();
            var dtm = dbcontext.Mapping;
            foreach(var t in dtm.GetTables()) {
                Console.WriteLine(t.TableName);
            }
            Console.ReadKey();
        }
    }
}

Program # 2

Write a program that retrieves data from a database table using LINQ to SQL.

C# Program

using System;
using System.Data.Linq;
using System.Linq;
namespace ConsoleApplication1 {
    class Program {
        static void Main(string[] args) {
            //Declare an object of the DataContext
            DataClasses1DataContext dbContext = new DataClasses1DataContext();
            //Retrieve data from StudentsInfo table
            var result = from d in dbContext.GetTable < StudentsInfo > ()
            select d;
            foreach(var std in result) {
                Console.WriteLine("Student ID = " + std.StdRegNumber);
                Console.WriteLine("Student Name = " + std.StdName);
                Console.WriteLine("Student Date of Birth = " + std.StdDoBirth);
            }
            Console.ReadKey();
        }
    }
}

Visual Basic Program

Imports System
Imports System.Data.Linq
Module Module1
Sub Main()
'Declare an object of the DataContext
Dim dbContext As DataClasses1DataContext = New DataClasses1DataContext()
' Retrieve data from StudentsInfo table
Dim result = From d In dbContext.GetTable(Of StudentsInfo)()
Select d
For Each std In result
Console.WriteLine("Student ID = " & std.StdRegNumber)
Console.WriteLine("Student Name = " & std.StdName)
Console.WriteLine("Student Date of Birth = " & std.StdDoBirth)
Next
Console.ReadKey()
End Sub
End Module

Program # 3

Write a program that inserts new data into a database table using LINQ to SQL.

C# Program

using System.Linq;
DataClasses1DataContext dbContext = new DataClasses1DataContext();
//Declare an object from the StudentsInfo Table and assign values
//to its attributes
StudentsInfo obj = new StudentsInfo();
obj.StdRegNumber = "008";
obj.StdName = "Hassan Shah";
DateTime dob = new DateTime(1991, 11, 09);
obj.StdDoBirth = dob;
try {
    //Insert new student into StudentsInfo table
    dbContext.GetTable < StudentsInfo > ().InsertOnSubmit(obj);
    //Save changes to the Database
    dbContext.SubmitChanges();
    Console.WriteLine("The record has been inserted");
} catch (Exception e) {
    Console.WriteLine(e.Message);
}

Visual Basic Program

Imports System.Linq
Dim dbContext As DataClasses1DataContext = New DataClasses1DataContext()
'Declare an object from the StudentsInfo Table and assign values to its attributes
Dim obj As StudentsInfo = New StudentsInfo()
obj.StdRegNumber = "008"
obj.StdName = "Hassan Shah"
Dim dob As DateTime = New DateTime(1991, 11, 9)
obj.StdDoBirth = dob
Try
'Insert new student record into StudentsInfo table
dbContext.GetTable(Of StudentsInfo)().InsertOnSubmit(obj)
'Save changes to the Database
dbContext.SubmitChanges()
Console.WriteLine("The record has been inserted")
Catch e As System.Data.SqlClient.SqlException
Console.WriteLine("The Record already Exist")

InsertOnSubmit() Method

This method is used to add a record or entity to the current table of the database. It is used in conjunction with the SubmitChanges() method of DataContext class. The SubmitChanges() method submits and saves the inserted record or entity of the InsertOnSubmit() method.