In this article I will explain a simple tutorial with example to integrate LINQ to SQL Framework (dbml classes) in ASP.Net using C# and VB.Net.
This article covers the basics of adding and generating dbml classes, connecting to database, adding Table entities and Stored Procedures.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here
Stored Procedures
I am making use of a Stored Procedure that will get the records from the Customers Table of the Northwind database.
CREATE PROCEDURE [GetCustomers]
AS
BEGIN
SELECT [CustomerID]
,[CompanyName]
,[ContactName]
,[City]
,[Country]
FROM [Customers]
END
HTML Markup
Below is an ASP.Net GridView with some fields from the Customers Table of the Northwind Database.
<asp:GridView ID="gvCustomers" CssClass="Grid" runat="server" AutoGenerateColumns="false"
PageSize="10" AllowPaging="true" OnPageIndexChanging="OnPageIndexChanging">
<Columns>
<asp:BoundField DataField="CustomerId" HeaderText="Customer Id" />
<asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>
Configuring and connecting LINQ to SQL Framework to database
Now I will explain the steps to configure and add LINQ to SQL Framework and also how to connect it with the database.
You will need to add LINQ to SQL classes to your project using Add New Item Dialog as shown below.
![Simple Tutorial with example of using LINQ to SQL in ASP.Net Website using C# and VB.Net]()
Now we need to connect to the database and hence you need to right click Data Connections node in Server Explorer and click Add Connection.
![Simple Tutorial with example of using LINQ to SQL in ASP.Net Website using C# and VB.Net]()
The above step will open up a Dialog in which you will need to select the Data Source, in our case it is SQL Server and then click Continue.
![Simple Tutorial with example of using LINQ to SQL in ASP.Net Website using C# and VB.Net]()
In the next dialog you will need to provide the details of the SQL Server i.e. Server Name and authentication details and then select the database i.e. Northwind.
And then click Test Connection to make sure all settings are correct and then click OK.
![Simple Tutorial with example of using LINQ to SQL in ASP.Net Website using C# and VB.Net]()
Now you’ll be able to see the LINQ to SQL (dbml) class created in the App_Code folder of Solution explorer.
![Simple Tutorial with example of using LINQ to SQL in ASP.Net Website using C# and VB.Net]()
And also the Northwind database is available in the Server Explorer.
![Simple Tutorial with example of using LINQ to SQL in ASP.Net Website using C# and VB.Net]()
Now from here you will need to Drag and Drop the Customers Table and the GetCustomers stored procedure (we created earlier) to the LINQ to SQL Framework (dbml) class and the save it.
![Simple Tutorial with example of using LINQ to SQL in ASP.Net Website using C# and VB.Net]()
That’s it and we are now ready to use the LINQ to SQL Framework.
Binding the GridView using LINQ to SQL Framework
Inside the Page Load event, the GridView is populated from the Customers table using the Linq To Sql DataContext class.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.PopulateCustomers();
}
}
private void PopulateCustomers()
{
CustomersDataContext ctx = new CustomersDataContext();
gvCustomers.DataSource = ctx.GetCustomers();
gvCustomers.DataBind();
}
protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvCustomers.PageIndex = e.NewPageIndex;
this.PopulateCustomers();
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.PopulateCustomers()
End If
End Sub
Private Sub PopulateCustomers()
Dim ctx As New CustomersDataContext()
gvCustomers.DataSource = ctx.GetCustomers()
gvCustomers.DataBind()
End Sub
Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
gvCustomers.PageIndex = e.NewPageIndex
Me.PopulateCustomers()
End Sub
Screenshot
![Simple Tutorial with example of using LINQ to SQL in ASP.Net Website using C# and VB.Net]()
Downloads
