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.
Using LINQ to SQL, we can perform multiple operations like insert, update, delete and get data with minimal coding compared with ADO.NET.
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.
Now select “Asp.Net Empty Web Application” and give the project name “LINQ2SQL” and click OK like shown below.
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.
After adding LINQ to SQL dbml file, that will be as shown below.
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.
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.
Now enter database server details to connect like as shown below.
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.
Once we create the table “EmployeeDetails” in the database, that would be as shown below.
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.
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.
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.
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.
Now open the code behind the file and write the following code in page load.
VB.NET Code
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.
Following is the result of the LINQ to SQL example.
This is how we can use LINQ to SQL in our applications to communicate with the database using LINQ queries in c#, vb.net.