Geeks With Blogs
AzamSharp Some day I will know everything. I hope that day never comes.

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

I am using T-SQL you can easily use the STORED PROCEDURE.

This T-SQL query will generate the following parameter collection.

myCommand.Parameters.AddWithValue("@CustomerID",customerid)
myCommand.Parameters.AddWithValue("@CompanyName",companyname)
myCommand.Parameters.AddWithValue("@ContactName",contactname)
myCommand.Parameters.AddWithValue("@ContactTitle",contacttitle)
myCommand.Parameters.AddWithValue("@Address",address)
myCommand.Parameters.AddWithValue("@City",city)
myCommand.Parameters.AddWithValue("@Region",
region)
myCommand.Parameters.AddWithValue("@PostalCode",postalcode)
myCommand.Parameters.AddWithValue("@Country",country)
myCommand.Parameters.AddWithValue("@Phone",phone)
myCommand.Parameters.AddWithValue("@Fax",fax)

Now you can simply copy and paste the above generated parameter collection into your C# code.

I hope this helps you in your future projects.


powered by IMHO 1.3

Posted on Saturday, December 31, 2005 10:18 PM | Back to top


Comments on this post: Generate Parameters Collection Using T-SQL

# re: Generate Parameters Collection Using T-SQL
Requesting Gravatar...
Another option might be to use SqlCommandBuilder to extract parameter information and add it to DataAdapter's command object to perform insert/update. Just a thought

private SqlCommand GetCommandForStoredProcedure(string spName, SqlConnection sqlConn)
{
SqlConnection cn = sqlConn;
SqlCommand cmd = new SqlCommand(spName, cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
SqlCommandBuilder.DeriveParameters(cmd);
cn.Close();
foreach (SqlParameter sqlparameter in cmd.Parameters)
{
sqlparameter.SourceVersion = DataRowVersion.Current;
sqlparameter.SourceColumn = ((string)(sqlparameter.ParameterName)).Remove(0, 1);
}
return cmd;
}
catch(SqlException sqlEx)
{
Trace.WriteLine(sqlEx.Message);throw;

}
catch(Exception ee)
{
Trace.WriteLine(ee.Message);throw;
}
finally
{
if(cn.State != ConnectionState.Closed)
cn.Close();
}

}
Left by Vijay on Jan 09, 2006 6:45 AM

Your comment:
 (will show your gravatar)


Copyright © Mohammad Azam | Powered by: GeeksWithBlogs.net