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.
Make a Donation
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.
BUY IT
NOW |