Click here to Skip to main content
11,928,078 members (38,291 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


13 bookmarked

SqlCommand Generator

, 6 Feb 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
A static method that returns a SqlCommand object with all the parameters for a specific stored procedure


In this article, I'm going to show you how to make a SqlCommandGenerator class using SMO.

I came up with this idea when I was assigning all the parameters of my stored procedures to the SqlCommand one by one. Defining datatypes, parameter name, etc. was a real pain. So I decided to write a SqlCommandGenerator so I wouldn't have to write all the tedious code but get them all in one line of code.


The code is pretty straightforward and you wouldn't have any problems if you already have knowledge of ADO.NET and a bit of SMO. But just for the record, you might want to check this article out by Kanasz Robert if you want to know about SMO.

Getting Started

When you want to use SMO, you must add references of it to Visual Studio.They are usually placed here:

  • C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

And then add these 4 references:

  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoExtended
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Mircorost.SqlServer.ConnectionInfo

And to get started with, add these three namespaces to your code :

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;

Now we're all set and ready to go to the most interesting part.

The Static Method

In this part, we want to add a static method to our class that will accept datasource, database name, uid, pwd for the connection string and stored procedure's name and its schema as input parameters. The returning value will be a SqlCommand object. So the signature of our method will look as follows:

public static SqlCommand GenerateSqlCommand(
    string dataSource, string database, string uid, string pwd,
    string storedProcName, string storedProcSchema,bool hasReturnValue=false)

Next thing we would like to do is to make a connection to our database with the information provided to us, make a Database object and a SqlCommand object with the ConnectionString provided for it. So till this moment, our code will look as the following:

public static SqlCommand GenerateSqlCommand(
string dataSource, string database, string uid, string pwd,
string storedProcName, string storedProcSchema,bool hasReturnValue=false)
SqlConnection connection = new SqlConnection(string.Format
("data source={0};database={1};uid={2};pwd={3}", dataSource, database, uid, pwd));
        ServerConnection con = new ServerConnection();
        con.ConnectionString = connection.ConnectionString;
        Database db = new Server(con).Databases[database];
        SqlCommand cmd = new SqlCommand(storedProcName, connection);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

Now that we have a connection to our database and its stored procedures, we should only iterate through the parameters of the specific stored procedure that had been provided to us by the user previously, with a foreach loop :

foreach (Microsoft.SqlServer.Management.Smo.Parameter param
         in db.StoredProcedures[storedProcName, storedProcSchema].Parameters)


The next thing we want to do is to retrieve the data type of the parameter and convert it to a System.Data.SqlDbType enum (by the help of Enum.Parse). Since we have the parameter object in the foreach loop, we can use the following code to get it done :

System.Data.SqlDbType paramDbType = (System.Data.SqlDbType)Enum.Parse
    (typeof(System.Data.SqlDbType), param.DataType.SqlDataType.ToString(), true);

The last thing we should do is to add parameters to the SqlCommand object. Now normally, we use two overloads of SqlCommand.Parameters.Add, one takes parameter name and its data type and another one takes parameter name, its data type and the size of it for string parameters like VARCHAR, CHAR, etc.

The trick is to check before applying parameters if we should provide the size of it or not. It's a bit tricky and you can achieve it by the following piece of code:

if (param.DataType.NumericScale == 0 && param.DataType.NumericPrecision == 0)
    cmd.Parameters.Add(param.Name,paramDbType , param.DataType.MaximumLength);

    cmd.Parameters.Add(param.Name, paramDbType)

Tip: The '@' character is already included in the name of the parameter, so you don't need to add it manually.

There's a problem with this code above. Image and Binary data types have length and you don’t really want to provide the size of them for the SqlCommand object unless you want to end up with an exception. So all you got to do is to check for these data types at the beginning of if statement as follows:

if(paramDbType == System.Data.SqlDbType.Image || 
    cmd.Parameters.Add(param.Name, paramDbType);

else if (param.DataType.NumericScale == 0 && param.DataType.NumericPrecision == 0)
    cmd.Parameters.Add(param.Name,paramDbType , param.DataType.MaximumLength);

    cmd.Parameters.Add(param.Name, paramDbType);

At the end, we should check if the parameter is an output one. If so, we want to add an output direction for that parameter. It's done by checking the Properties["IsOutputParameter"].Value of our param object :

if ((bool)param.Properties["IsOutputParameter"].Value)
    cmd.Parameters[param.Name].Direction = System.Data.ParameterDirection.Output;

And then, we check if there's a return value from the stored procedure, we add the parameter @ReturnValue for it:

if (hasReturnValue)
    System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;

Now all you’ve got to do is disconnecting from the server and returning the SqlCommand object:

return cmd;

Using Code in a Demo

In this demo, we just want to demonstrate (using ASP.NET) that we are actually getting the SqlCommand object with all the parameters attached to it using the code that we had written previously. Imagine that we have a database as follows:

USE test

And we've created a stored procedure in it. It doesn’t matter what it does. In our case, it has a lot of input parameters with different data types and the last one is an output.

@1 VARCHAR (50),
@3 NVARCHAR(100),
@4 BIT,
@5 INT,
@8 DATE,
@9 TIME,
@11 FLOAT,
@12 NCHAR(10),
@13 REAL,

Now add an ASP.NET page to your website and write the following code for the page load event of it:

protected void Page_Load(object sender, EventArgs e)
SqlCommand cmd = SqlcommandGenerator.GenerateSqlCommand
    ("localhost", "test", "sa", "123456", "USP_INS_myTable", "dbo");
foreach (System.Data.SqlClient.SqlParameter item in cmd.Parameters)
Response.Write(item.ParameterName + "<br/>");
Response.Write(item.SqlDbType + "<br/>");
Response.Write(item.Direction + "<br/>");
Response.Write(item.Size + "<br/><br/><br/>");

Don't forget to change the connection string as it suits your connection to the SQL server. Now if you check the rendered page, you'll see that @ID parameter has output direction and all the other parameters just seem as they should. All you got to do is to add their values and execute it.

At the end, I must add that no code is bug free and I'm just an "intermediate" developer. Your comments are already appreciated.

Points of Interest

While I was writing this code, I came up with another Idea.The idea was to write a SQL stored procedure that will create Insert, Delete and Update procedures for all the tables in a specific database using Dynamic-SQL. So depending on your feedback, I might write another article about SqlStoredProcedureGenerator.


  • 31st January, 2012: Initial version


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Web Developer
Ukraine Ukraine
No Biography provided

You may also be interested in...

Comments and Discussions

QuestionHOW Pin
Ali Javani13-Dec-12 1:35
memberAli Javani13-Dec-12 1:35 
GeneralHi Ashkan Pin
Ali Javani19-Feb-12 8:05
memberAli Javani19-Feb-12 8:05 
QuestionAlternative Pin
Richard Deeming15-Feb-12 5:39
memberRichard Deeming15-Feb-12 5:39 
QuestionFantastic idea. But how to set values to the returned parameters from my object class Pin
vkuttyp6-Feb-12 12:21
membervkuttyp6-Feb-12 12:21 
AnswerRe: Fantastic idea. But how to set values to the returned parameters from my object class Pin
Ashkan.hosseini7-Feb-12 1:39
memberAshkan.hosseini7-Feb-12 1:39 
QuestionTip Pin
Shahin Khorshidnia6-Feb-12 11:18
memberShahin Khorshidnia6-Feb-12 11:18 
GeneralMy vote of 5 Pin
Abinash Bishoyi4-Feb-12 0:42
memberAbinash Bishoyi4-Feb-12 0:42 
QuestionThoughts Pin
PIEBALDconsult3-Feb-12 3:50
memberPIEBALDconsult3-Feb-12 3:50 
AnswerRe: Thoughts Pin
Ashkan.hosseini3-Feb-12 4:35
memberAshkan.hosseini3-Feb-12 4:35 
GeneralRe: Thoughts Pin
PIEBALDconsult3-Feb-12 12:05
memberPIEBALDconsult3-Feb-12 12:05 
GeneralRe: Thoughts Pin
EgyptianRobot28-Aug-12 8:54
memberEgyptianRobot28-Aug-12 8:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.151126.1 | Last Updated 6 Feb 2012
Article Copyright 2012 by Ashkan.hosseini
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid