In this article I will explain with an example, how to call a Stored Procedure using LINQ to SQL 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.
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">
<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" />
Here I am making use of Microsoft’s Northwind Database. You can download it from here
I am making use of a Stored Procedure that will get the records from the Customers Table of the Northwind database.
CREATE PROCEDURE [GetCustomers]
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.
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.
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.
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.
Once above is done you can Test Connection and if it works then click OK.
Now you’ll be able to see the LINQ to SQL (dbml) class created in the App_Code folder of Solution explorer.
And also the Northwind database is available in the Server Explorer.
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.
That’s it and we are now ready to use the LINQ to SQL Framework.
Binding the GridView using LINQ to SQL Framework
This is fairly simple, you will need to create an object of the Data Context of the DBML
protected void Page_Load(object sender, EventArgs e)
private void PopulateCustomers()
CustomersDataContext ctx = new CustomersDataContext();
gvCustomers.DataSource = ctx.GetCustomers();
protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
gvCustomers.PageIndex = e.NewPageIndex;
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Private Sub PopulateCustomers()
Dim ctx As New CustomersDataContext()
gvCustomers.DataSource = ctx.GetCustomers()
Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
gvCustomers.PageIndex = e.NewPageIndex