LINQ to SQL (DBML File)

The LINQ to SQL is a component of .NET Framework version 3.5, and it provides a run-time infrastructure to manage relational data as objects. The LINQ to SQL will allow us to access and get the data from the SQL database with LINQ queries. It allows us to perform select, insert, update and delete operations on tables like SQL using LINQ queries.

 

In LINQ to SQL, the data model of a relational database is mapped to an object model. While executing the application, the LINQ queries object model is converted into SQL to get the required data from the database. While returning the data from the database, the LINQ to SQL will convert SQL results to the LINQ objects model.

 

Following is the pictorial representation of LINQ to SQL Architecture.

 

LINQ to SQL Architectural Diagram 

Using LINQ to SQL, we can perform multiple operations like insert, update, delete and get data with minimal coding compared with ADO.NET.

Create LINQ to SQL & Use in Web Application

For example, we will see how to create and use LINQ to SQL (dbml file) in the asp.net web application.

 

 

First, create a new application for that open visual studio à Go to File à New à Project.

 

Create new linq to sql project in visual studio

 

Now select “Asp.Net Empty Web Application” and give the project name “LINQ2SQL” and click OK like shown below.

 

Select Asp.net empty web application for LINQ to SQL Project

 

Now we finished creating a new project. To use LINQ to SQL in our application, we need to create a “DBML” file, and the DBML file will contain source code that will allow us to write LINQ queries to get data from the SQL database.

 

To create a DBML file, right-click on your project à Go to Add à select New Item à select LINQ to SQL template, give name as “EmployeeDB” and click like as shown below.

 

Create newLINQ to SQL DBML File in Asp.Net Application

 

After adding LINQ to SQL dbml file, that will be as shown below.

 

After adding LINQ to SQL File in Asp.Net Web Application

 

Here if you observe, we have two sections in the left side section. We can add or create tables as objects, and on the right side, we can add required stored procedures to get data.

 

Now open Server Explorer, right-click on Data connections and select Add connection like below.

 

Add new database connection in visual studio server explorer

 

Whenever we select Add Connection, a new pop-up will open in that we need to Choose Data Source. Here we will connect from an existing database, so we selected “Microsoft SQL Server” and click Continue like as shown below.

 

select new data source for linq to sql application

 

Now enter database server details to connect like as shown below.

 

Configure database connection in LINQ to SQL Application

 

Once we have entered all the details to connect the database, click the OK button. After adding the database, we will create a new table EmployeeDetails using the following script.

 

CREATE TABLE [dbo].[EmployeeDetails](
[EmpId] INT IDENTITY (1, 1) NOT NULL,
[EmpName] VARCHAR (50) NULL,
[Location] VARCHAR (50) NULL,
[Gender] VARCHAR (20) NULL
PRIMARY KEY CLUSTERED ([EmpId] ASC)
);

Once we create the table “EmployeeDetails” in the database, that would be as shown below.

 

Database with New Table in LINQ to SQL Application

 

After the table is created in the database, enter some dummy to show it in the application. Now drag and drop the required table in the left pane of LINQ to SQL section like as shown below.

 

Drag and Drop Table into LINQ to SQL DBML File

 

Once we drag and drop the required tables in the LINQ to SQL “.dbml” file, the new class files will be added to the project as shown below.

 

After adding LINQ to SQL DBContext Files in Application

 

If you observe “EmployeeDB.dbml”, we have a context file “EmployeeDBDataContext” that will contain a connection to the database, and we have an additional class created for the EmployeeDetail table.

 

If you open the “EmployeeDBDataContext” file containing a database connection string like shown below.

 

LINQ to SQL DBContext File Sample Code

 

Now we will see how to use LINQ to SQL to get EmployeeDetail table data in our application. For that, Right-click on the application --> select Add --> New Item --> Select Web Form --> Give name as Default.aspx and click OK button.

 

Now open the Default.aspx page and write the code as shown below.

 

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Show Employee Details in Gridview</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvDetails" runat="server"></asp:GridView>
</div>
</form>
</body>
</html>

Now open the code behind the file and write the following code in page load.

 

protected void Page_Load(object sender, EventArgs e)
{
  EmployeeDBDataContext db = new EmployeeDBDataContext();
  if (!Page.IsPostBack)
  {
    gvDetails.DataSource = from ed in db.EmployeeDetails
                           select new
                           {
                             EmployeeName = ed.EmpName,
                             Location = ed.Location,
                             Gender = ed.Gender
                           };
    gvDetails.DataBind();
  }
}

VB.NET Code

 

Public Class WebForm1
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim db As New EmployeeDBDataContext()
If Not Page.IsPostBack Then
gvDetails.DataSource = From ed In db.EmployeeDetails Select New With {.EmployeeName = ed.EmpName, .Location = ed.Location, .Gender = ed.Gender}
gvDetails.DataBind()
End If
End Sub
End Class

If you observe the above code, we created an object for the context class “EmployeeDBDataContext” to access the database. Now we will run and see the output as shown below.

Result of LINQ to SQL Example

Following is the result of the LINQ to SQL example.

 

LINQ to SQL Select Query Example Output

 

 This is how we can use LINQ to SQL in our applications to communicate with the database using LINQ queries in c#, vb.net.