SQL Server 2005 introduced the Common Language Runtime (CLR), which has posed new challenges for database administrators (DBAs). As a DBA, it is important to familiarize yourself with the Microsoft Framework, VB.NET, and C# to be successful in managing SQL Server. In this article, we will explore a SQL script that generates front-end ADO functions for stored procedures.

The script provided in this article processes all user stored procedures by default and generates an ADO function for each procedure. If you want to specify specific stored procedures, you can uncomment the relevant line and list the desired procedures. The script filters out system stored procedures related to diagrams by using the condition “name not like ‘sp[_]%'”. It is important to note that using “sp_” as a prefix for stored procedures is not recommended.

Let’s take a closer look at how to execute a stored procedure using an ADO SqlCommand object. First, you need to create a connection using a SqlConnection object and the ConnectionString property. It is recommended to use a centralized connection string by creating a function for this purpose. The article includes a sample function called GetConnectionStr that can be used for this purpose.

Next, you need to determine if your stored procedure returns a result set. If your stored procedure performs INSERT, UPDATE, or DELETE operations, it is best to return a Boolean value (TRUE or FALSE) and use the ExecuteNonQuery method. If your stored procedure returns a result set, you have the option to use either a SqlDataReader or a SqlDataSet. While the SqlDataReader is faster, it has some disadvantages when used as a returned object. It requires the connection object to be retained, as it closes when the connection is closed. In this case, it is recommended to use the SqlDataSet object, which is a disconnected object that can be returned without worrying about the connection.

The script provided in this article searches for the “SELECT” clause within the stored procedure code to determine if a result set is returned. If a “SELECT” clause is found, the function returns a SqlDataSet. For all other cases, the SqlCommand ExecuteNonQuery method is used, which returns a Boolean value (TRUE or FALSE). It is important to ensure that the proper method is used within the function.

Another important aspect to consider is handling parameters. The script’s main purpose is to retrieve a parameter list, as some stored procedures can have dozens or even hundreds of parameters. The script references the sys.parameters view three times: when creating the input and output parameters for the function, when setting the Parameters collection, and when retrieving output values (if output parameters exist).

The script provided in this article uses the PRINT statement to display the generated function code. It is important to ensure that the Query Options/Result/Maximum number of characters displayed in each column property is set to its maximum value (8192) to avoid truncation of the code. In cases where the code exceeds this limit, the script creates a Visual Studio source file with either a .vb or .cs extension, depending on whether you are using Visual Basic or C#.

To create a file from Management Studio, it is recommended to use the bcp utility string and execute it using the xp_cmdshell extended stored procedure. Most values, such as the database and server name, are obtained using intrinsic SQL Server functions. However, you need to set up a variable (@outFileTo) to validate the directory path where the file will be created.

Since the most common languages in the Microsoft Framework are VB.NET and C#, separate scripts are provided for each language. The scripts are based on SQL Server 2005 system views, but the comments in the scripts explain how to convert them for use with SQL Server 2000.

Sample code is provided for loading a DataGridView in both VB.NET and C#. The code demonstrates how to call the generated function and handle any potential errors.

In conclusion, as a DBA, it is important to expand your knowledge beyond just database administration and development. Understanding the CLR in SQL Server and familiarizing yourself with the Microsoft Framework, VB.NET, and C# can greatly enhance your ability to manage SQL Server effectively. The scripts provided in this article can significantly reduce application development time and improve overall efficiency.