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.
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>
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
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.
![Call Stored Procedure using LINQ to SQL in ASP.Net 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.
![Call Stored Procedure using LINQ to SQL in ASP.Net 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.
![Call Stored Procedure using LINQ to SQL in ASP.Net 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.
Once above is done you can Test Connection and if it works then click OK.
![Call Stored Procedure using LINQ to SQL in ASP.Net 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.
![Call Stored Procedure using LINQ to SQL in ASP.Net using C# and VB.Net]()
And also the Northwind database is available in the Server Explorer.
![Call Stored Procedure using LINQ to SQL in ASP.Net 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.
![Call Stored Procedure using LINQ to SQL in ASP.Net 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
This is fairly simple, you will need to create an object of the Data Context of the DBML
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
![Call Stored Procedure using LINQ to SQL in ASP.Net using C# and VB.Net]()
Downloads
