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…
No comments:
Post a Comment