Automation Helper for Stored Procedures in C#

Download source - 23.98 KB

Introduction

I wrote this class library to help me construct a Visual Studio Addin to insert code into my editor based on the stored procedure and its parameters. The Addin uses XSLT to make the insertion code so the library is serializable and the XML produced can be used easily for XSLT transformations.

Background

The idea of the library is to make a collection of data objects that capture the parameter information from a SQL Server procedure and then use that data programmatically for whatever purpose.

Using the Code

The main class to use is the SQLServerProcProcessor class which takes a connection string to the server in the constructor.

There are two methods to get parameter data from a stored procedure name:

GetParameterCollection(string strProcName), which returns a collection class of detailed information derived from the procedure. The XML file GetParameterCollection_results.xml has the serialize output from this method.
GetParameters(string strProcName), which returns a List of much smaller collection of parameter information from the procedure. The file GetParameter_result.xml has the serialized output from this method.

// Instantiating the main class
SQLServerProcProcessor sqlProcessor = new SQLServerProcProcessor(connStrTextBox.Text);

// Calling the methods
List<ParamInfo> paramInfos = sqlProcessor.GetParameters(procName);
SqlParameterCollection paramCollection = sqlProcessor.GetParameterCollection(procName);