In this article I will show you how to execute stored procedure using LINQ.
LINQ is a set of extensions to the .NET Framework that encompass
language-integrated query, set, and transform operations. It extends C# and
Visual Basic with native language syntax for queries and provides class
libraries to take advantage of these capabilities.
The sample database I am using for this article has the following table
Open Visual Studio and create a windows application. Here I have named the
For any database application, the first step is to create connection with the
database. Open "Server Explorer" from the View menu and create a connection.
The steps for creating the connection is shown below:
1. Right click "Data Connections" and select "Add Connection".
2. In connection wizard supply the exact values as you supplied during SQL
Server installation process. Select the database you want to use for the
You can also test the connection from this wizard. If everthing is OK, move to
the next step.
Now we are done with the connection and let's move to our application.
Add "LINQ to SQL Classes" template by right clicking the project and then select
"Add New Item". Name your template. The name of my template is Employee.dbml.
Now you are provided with Object Relational Designer (O/R Desinger) for this
My store procedure will look like this:
ALTER PROCEDURE [dbo].[getempname]
SET NOCOUNT ON;
empdesg, empdept, empadd, empphone
where emp_master.empjoin between @date1 and @date2
Next step is to add the stored procedure to the designer. For this expand the
connection you created earlier and select the stored procedure from the list.
Drag the stored procedure "getempname" to the designer.
Don't forget to save the project.
One thing to note here that, when we added stored procedure to the O/R designer
we are provided with DataContext object for our project. The name of the object
will be same as your tamplate file you created earlier.
Now we can use the DataContext object and try to extact records from our
In my application, I added a listbox for showing the names of the employees.
In this example, I am showing how to get the employee details who joined in the
year 2003. For this I have added a DataGridView.
The code is as follows:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim db As New EmployeeDataContext
DataGridView1.DataSource = db.getempname(#1/1/2003#, #12/31/2003#).ToList()
The result of the above code is as follows:
Here in this article we have seen how to execute stored procedure using LINQ. In
later articles we will see more of LINQ.