LINQ to SQL (DBML File)

  Total Views : 239
  Comments : 0
 
  Keywords : Linq to sql create dbml file in c# / vb.net with example, Linq to sql tutorial with examples in c# / vb.net, How to use linq to sql in asp.net applications with example using c# / vb.net

Here we will learn LINQ to SQL in c#, vb.net with example and how to create LINQ to SQL dbml file and use it in asp.net web applications with example in c#, vb.net.

LINQ to SQL

The LINQ to SQL is a component of .NET framework version 3.5 and it provides run-time infrastructure to manage relational data as objects. The LINQ to SQL will allow us to access and get the data from 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 relational database is mapped to an object model and while executing application the LINQ queries object model converted into SQL to get required data from database, while returning the data from database the LINQ to SQL will convert SQL results to LINQ objects model.

 

Following is the pictorial representation of LINQ to SQL Architecture.

 

LINQ to SQL Architectural Diagram 

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

Create LINQ to SQL & Use in Web Application

We will see how to create and use LINQ to SQL (dbml file) in asp.net web application with example.

 

 

First create 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 project name as “LINQ2SQL” and click OK like as shown below

 

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

 

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

 

To create DBML file right click on your project → Go to Add → select New Item → select LINQ to SQL template and 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 like as shown below

 

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

 

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

 

Now open Server Explorer and right click on Data connections and select Add connection like as shown 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 are going to connect from existing database so here 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 entered all the details to connect database click OK button. After adding database now we will create new table EmployeeDetails by using following script.

 

CREATE TABLE [dbo].[EmployeeDetails](

[EmpId]  INT IDENTITY (1, 1NOT NULL,

[EmpName]  VARCHAR (50NULL,

[Location] VARCHAR (50NULL,

[Gender] VARCHAR (20NULL

PRIMARY KEY CLUSTERED ([EmpId] ASC)

);

Once we create table “EmployeeDetails” in database that would be like as shown below

 

Database with New Table in LINQ to SQL Application

 

After table created in database enter some dummy to show it in application. Now drag and drop required table in 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 required tables in LINQ to SQL “.dbml” file the new class files will be added to project like 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 connection to the database and we have additional class which is created for EmployeeDetail table.

 

If you open “EmployeeDBDataContext” file that will contain database connection string like as 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 application --> select Add --> New Item --> Select Web Form --> Give name as Default.aspx and click OK button.

 

Now open Default.aspx page and write the code like 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 code behind file and write 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 above code, we created object for context class “EmployeeDBDataContext” to access the database. Now we will run and see the output that would be like as shown below

Output of LINQ to SQL Example

Following is the result of 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 database using LINQ queries in c#, vb.net.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User