Class Generator for SQL Server Stored Procedures

• Download source - 32.1 KB

Introduction

This is a simple program that, given a connection string to SQL Server, and a list of stored procedure names, generates a strong typed class for each stored procedure to invoke it.

Background

No background is necessary.

Using the Code

The program usage is simple. First, you must create a *.txt configuration file. It must be as follows:

###########################################################
# All fields are mandatory, exception for NAMESPACE       # 
###########################################################


#sql server connection string
CONNECTION_STRING=Data Source=.\SQLEXPRESS;
	Initial Catalog=Northwind;Integrated Security=True

#output file name
FILE_NAME=c:\GeneratedStored.cs

#list of stored procedures, comma (,) separated. e.g. CustOrdersDetail,SalesByCategory
#if you want to generate class for ALL stored, set * (ASTERIX)
STORED_LIST_COMMA_SEPARED=CustOrderHist,
	CustOrdersDetail,CustOrdersOrders,SalesByCategory

#namespace (not mandatory)
NAMESPACE=Northwind.StoredClasses

Then launch the program from the command line, providing the configuration file path as the first parameter.

Here's a snippet of a generated class for stored procedure CustOrdersDetail of the Northwind database:

public class SP_CustOrdersDetail
{   
    public static string STORED_NAME = "CustOrdersDetail";
    public static string P_OrderID = "@OrderID";
    public int ReturnValue;
    public Int32? IN_OrderID { ... }
    public DataSet execute(SqlConnection conn, SqlTransaction t) { .. }
    public DataSet execute(String connectionString) { ..}
    public void executeNonQuery(SqlConnection conn, SqlTransaction t) { .. }
    public void executeNonQuery(String connectionString) { ...}
}

Here's an example of a generated class for stored procedure CustOrdersDetail of the Northwind database.

You have a strong typed property for each database parameter, prefixed from "IN_" or "INOUT_" depending on the parameter direction.

To set a parameter to DB NULL, set the related property to null.

If you want that a parameter not be sent to stored, simply don't set related property. (You must ensure that the related stored parameter has a default value, otherwise you will have a runtime error.)

Once the parameters are filled, simply call execute or executeNonQuery methods. After this call, out parameter will be available.

Points of Interest

I hope this simple project helps you to write simple code and in a smaller amount of time.

History

• 18^th September, 2008: Now you can set * to stored list, generating a class for each procedure in the database.