Stored Procedure With Output Parameters In LINQ To SQL And What SQLMetal Is

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. 

 

The first step is to create the stored procedure so let’s flip to SSMS.



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

• What is SqlMetal?
• How do we use SqlMetal?

What is SqlMetal?

SqlMetal is a command-line code generation tool used to generate LINQ-to-SQL classes. There are 2 ways to generate LINQ-to-SQL classes.

1. Using Visual Studio OR
2. Using SqlMeta

In previous articles and in this article we have seen how to generate LINQ to SQL classes using VS .IN this part we will see how to use SqlMetal to generate Linq to SQL classes.

What is the windows path where I can find SqlMetal.exe?

On my machine SqlMetal.exe is present in the following location

C\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin



How to use SqlMetal.exe to generate the LINQ-to-SQL classes

Step 1

Run Visual Studio Command prompt as an Administrator Type sqlmetal.exe /?



Step 2

In C\ create a new folder. Name it SqlMetalGeneratedFiles. This is the folder where we will store the generated dbml file.

Step 3

Type the following command and press enter

SqlMetal.exe /serverDESKTOP-QOMPPF7 /databaseTEST /dbmlC\SqlMetalGeneratedFiles\Sample.dbml /namespaceDemo /ContextSampleDataContext

In this example we are using the following options

• server
  Database server name. In our example, the database server is a local server, hence we specified localhost.
  
  
• database
  database name
  
  
• dbml
  The name of the generated dbml file
  
  
• namespace
  Namespace for the generated classes
  
  
• context
  Name of the data context class

For the full list of all available options that can be used with SqlMetal.exe, please check the following MSDN article





So our sample.dbml file has been created you can give any name to the folder.

Now let us see how use this generated dbml file in an .NET application. So create an Empty Web application project. Add the connection string in your web.config file and add a form

Connection string



Add that sample.dbml file to our project right click on the project ->Add->Existing Item ->c/foldername and add the sample.dbml file.

Add a gridview control to our form and in the code behind page we will write some code: 



So we are reading the connectionstring, and we had created an instance of SampleDataContext, and in our LINQ query we are displaying all employees again.  It’s a straightforward code

Now let's run the app and see the output



Conclusion

So, this article was all about stored procedure with output parameters in LINQ to SQL and what SQLMetal is. Hope this article was helpful.