This article is in continuation of my previous article. Again, we will be using the same sample. So, kindly download the sample from this link.

So here we had a stored procedure named GetEmployeesByDepartment. Notice, the stored procedure has two parameters: first is input parameter which is DepartmentID and second is the output parameter which is DepartmentName.
So, what is this stored procedure going to do? The Stored procedure is going to return to us all the employees by Department so if we give it a Department ID then this stored procedure is going to return to us employees belonging to that department. In addition to that this stored procedure is going to return a department name so we are passing Department name in the output parameter from our department's table where ID equals the one passed in department ID
We need to test this stored procedure, so for that let’s declare a variable:

So, in the above stored procedure we had declared DeptName as nvarchar this is going to return the Deptname. So we will execute the stored procedure. Our stored procedure name is GetEmployees By Department. This stored procedure has two parameters, the first is the input parameter, which is ID, so we passed 1 there, and the second one is output parameter, which is deptName, which we passed using the out keyword, and then we will select the Deptname. Let’s run this.

So we got DeptName as IT when we passed ID as 1. Now let’s see how to integrate this in LINQ to SQL. So flip to VS and refresh the stored procedure folder in server explorer.

Drag and drop the stored procedure from server explorer to LINQ to SQL class designer so it should automatically generate the method. Now,within our code behind, we need to call this method.
First we will add a button and name it Get By Department and add a label to display the department.Double click on the button to generate the event handler

So, this is the simple code. We wrapped the SampleDBContext inside the using clause and gridviewdatasource. We passed our stored procedure which takes two parameters, so we supplied id and deptname which we declared as string, and then we bind that gridview. So, it’s a simple straightforward code. Now let’s run the solution and click on the newly-generated button:

So, ID with 1 is displayed and its respective department is ID.
Now we will see the next part of our article which is








Venkat MungariPosted Feb 10, 2017, 8:08 AM
Sir I have a doubt VS IDE already is there for generation Linq to Sql Classes but why we use SqlMetal? what are more benefits from SqlMetal ratherthen VS IDE?