SqlCommand Generator

• Download demo project - 4.7 KB
• Download source - 918 B

Introduction

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.

Background

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);

else
    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 || 
        paramDbType==System.Data.SqlDbType.Binary)
    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);

else
    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)
cmd.Parameters.Add("@ReturnValue", 
    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:

con.Disconnect();
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:

CREATE DATABASE test
GO
USE test
GO

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.

CREATE PROC USP_myProc
(
@1 VARCHAR (50),
@2 MONEY,
@3 NVARCHAR(100),
@4 BIT,
@5 INT,
@6 IMAGE,
@7 DATETIME,
@8 DATE,
@9 TIME,
@10 DECIMAL,
@11 FLOAT,
@12 NCHAR(10),
@13 REAL,
@14 BINARY,
@ID INT OUTPUT
)
AS
BEGIN
SET @ID=1
END

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.

History

• 31^st January, 2012: Initial version