Generate Parameters Collection Using T-SQL

Generate Parameters Collection Using T-SQL.

"How many times have you written the following lines?
myCommand.Parameters.AddWithValue("@Title",title)
.....
....
....
and so on. If you are attaching only 3-4 parameters then its okay but what about attaching 10-15 parameters. That will be lot of typing and wasting some precious time.
I made a small T-SQL that will generate the parameter collection for you provided the table name.

USE Northwind

-- declare the variables
DECLARE @column_name varchar(20)
DECLARE @ordinal_position int
DECLARE @counter int
DECLARE @totalRows int
DECLARE @table_name varchar(20)
DECLARE @commandObjectName varchar(20)

SET @counter = 1;
SET @table_name = 'Customers'
SET @commandObjectName = 'myCommand'


SELECT @totalRows = COUNT(*) FROM information_schema.columns WHERE
table_name = @table_name

WHILE @counter <= @totalRows
BEGIN

SELECT @column_name = COLUMN_NAME FROM information_schema.columns WHERE
table_name = @table_name AND @counter = ORDINAL_POSITION

Print @commandObjectName+'.Parameters.AddWithValue("@'+@column_name+'",'+LOWER(@column_name)+')'

SET @counter = @counter + 1

END

GO
…"

(SQL leached in full for my future reference, searching, etc, etc. All cred’s to the author, Mohammad Azam…)

I use this technique, using T-SQL to create static VB code, in a number of places so I thought it worth noting and capturing the above…

All my data access is stored procedure based, so with a few minor tweaks my version uses the parameters of a given stored procedure instead of table columns. Next I want to include data types, etc, etc…

USE Northwind

-- declare the variables 
DECLARE @parameter_name varchar(20) 
DECLARE @ordinal_position int 
DECLARE @counter int 
DECLARE @totalRows int 
DECLARE @storedprocedure_name varchar(20) 
DECLARE @commandObjectName varchar(20) 

SET @counter = 1; 
SET @storedprocedure_name = 'SalesByCategory'
SET @commandObjectName = 'myCommand' 

SELECT @totalRows = COUNT(*) 
FROM information_schema.PARAMETERS 
WHERE SPECIFIC_NAME = @storedprocedure_name

WHILE @counter <= @totalRows 
BEGIN 

SELECT @parameter_name = PARAMETER_NAME 
FROM information_schema.PARAMETERS 
WHERE SPECIFIC_NAME = @storedprocedure_name 
AND @counter = ORDINAL_POSITION

Print @commandObjectName
+'.Parameters.AddWithValue("'+ @parameter_name +'",' 
+replace( LOWER( @parameter_name), '@','') +')'

SET @counter = @counter + 1

END 

GO

And here’s the output;
myCommand.Parameters.AddWithValue("@CategoryName",categoryname)
myCommand.Parameters.AddWithValue("@OrdYear",ordyear)

Yeah, I know there’s about a million other ways to also do this, but sometimes quick and dirty is all I need…