Executing Stored Procedure using LINQ

In this article I will show you how to execute stored procedure using LINQ.

From MSDN:

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 application LINQaaplinVB.

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 application.



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 template file.

My store procedure will look like this:

ALTER PROCEDURE [dbo].[getempname]
    @date1 DateTime,
    @date2 DateTime
AS
BEGIN
    SET NOCOUNT ON;
    SELECT empname, empdesg, empdept, empadd, empphone
    from emp_master
    where emp_master.empjoin between @date1 and @date2
END

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 database table.

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()
End Sub
The result of the above code is as follows:




Conclusion:

Here in this article we have seen how to execute stored procedure using LINQ. In later articles we will see more of LINQ.