In this article we will see that how we can use a Stored Procedure to generate C# parameter collection for us. This will greatly reduce the amount of coding that we used to write to attach the parameters.
Introduction:
In this article we will see that how we can use a Stored Procedure to generate C# parameter collection for us. This will greatly reduce the amount of coding that we used to write to attach the parameters.
Creating the Stored Procedure:
How many times have to wrote these line?
| myCommand.Parameters.AddWithValue("@CategoryID",CategoryID) myCommand.Parameters.AddWithValue("@CategoryName",CategoryName) myCommand.Parameters.AddWithValue("@Description",Description) myCommand.Parameters.AddWithValue("@Picture",Picture) |
I bet you have written this thousand of times. The above lines are used to attach the parameters to the command object. Wouldn't this be great if you run a stored procedure and it will generate all those parameters for you and the only thing left for you is to copy paste it into the C# code. Well, this is your lucky day because the following stored procedure will just do that:
/* Author: Mohammad Azam Email: azamsharp@gmail.com Description: This stored procedure is used to generate parameters for the C# code. DateCreated: 01-05-2006 DateModified: 01-05-2006 Usage: Create the stored procedure in the database you want to use and then use the EXEC [procedure name] 'Table Name' */ CREATE PROCEDURE [usp_GenerateParams] @table_name nvarchar(20) AS -- declare the variables DECLARE @column_name nvarchar(20) DECLARE @commandObjectName varchar(20) DECLARE @SQLString nvarchar(4000) DECLARE @GenCode nvarchar(4000) SET @GenCode = '' SET @commandObjectName = 'myCommand' Select @GenCode = @GenCode + CHAR(13) + 'myCommand.Parameters.AddWithValue("@'+column_name+'",'+column_name+')' from information_schema.columns where table_name = @table_name Print @GenCode GO |
The stored procedure takes single argument which is the table name. To use the SPROC simply execute it in the target database and use EXEC to execute the stored procedure (EXEC is not necessary).
Here is a small example that demonstrate the Stored Procedure against Northwind database Products table. Use Query Analyzer to run the Stored Procedure.
| USE Northwind EXEC usp_GenerateParams 'Products' |
The Stored Procedure will generate the parameters for the Products table. You can easily modify it to create the parameters with the DataType.
I hope you liked this article, happy coding!
If you are one of the thousands that visit GridViewGuy for your .NET articles and resources, you might be interested in making a donation. Extra cash helps speed things up around here, and makes this website possible. Once, again thank you very much and remember its because of you FINE people that this website is up and running. |
| Export Button is a custom control that let's you export your DataGrid or TextBox data to several different formats. The control is extremely easy to use and also exposes design time features. In this article I will discuss some of the features of the Export Button and how it benefits the developer. |