SQL Server 2005 has brought many challenges to DBAs throughout the years, one of which is CLR (common language runtime). That means some of us who never paid much attention to MS Framework, VB.NET and C# should now open a book and start to learn at least some of these technologies. I think that in order to be successful, every SQL Server DBA should familiarize themselves with MS Framework to a certain degree.

In this article I would like to present and describe the SQL script which produces front-end ADO called a function by a stored procedure name. By default the script will process all user stored procedures and generate an ADO function for each procedure. If you need to specify the stored procedures, just uncomment "-- and name in ('list _proc')" line -- then list the desired procedures.

SELECT name FROM sys.objects
WHERE type = 'p' and name not like 'sp[_]%'
--    and name in ('list_sproc')
ORDER BY name

Be aware that diagram related system stored procedures show up along with user ones, so simply filter them out "name not like 'sp[_]%'". I believe that DBAs should know the rule of thumb that "Do not use 'sp_' as stored procedure's pefix". Now let's look closer at what we need to execute a stored procedure using an ADO SqlCommand object. First, you need to create a connection. To do that we need a SqlConnection object and ConnectionString property. I would recommend a centralized connection string which means creating a function to do this job. I included a sample of such a function. I call this function - GetConnectionStr, and my script uses this function.

Next, does your stored procedure return a result set? If your stored procedures does INSERT, UPDATE, DELETE then the best thing to do is to return TRUE or FALSE, which is a Boolean data type and then use the ExecuteNonQuery method.

To return a result set produced by a stored procedure from a function we have a choice to use either SqlDataReader or SqlDataSet. The SqlDataReader is a faster way to process the stored procedure result, but it has some disadvantages; especially when it is used as a returned object. You need to retain a connection object because if the connection is closed, then SqlDataReader closes as well. In this case I would prefer SqlDataSet object. The SqlDataSet is a disconnected object, so a function could return the result set without worrying about the connection and process further at reference's side. (Note: Every developer is different, so use the idea to modify the script according to your needs.)

To do so I am searching for the "SELECT" clause within the stored procedure code. When "SELECT" is found then the function returns SqlDataSet. For all of the others, the SqlCommand ExecuteNonQuery method is used, which returns True or False. I would recommend that you keep an eye on this and make sure that the proper method is used within a function.

Next, we will talk about parameters. The main purpose for this script is to get a parameter list because some stored procedures could have dozens or even hundreds of them. The script references the sys.parameters view three times. Firstly, when creating the input and output parameters for the function. Second, when setting the Parameters collection; and lastly, when getting output values (if output parameters exist). The function returns a Boolean data type, for DML (INSERT, UPDATE, DELETE) stored procedures or SqlDataSet object to return result set, so if you prefer to return an error descript or rows affected instead, please do so (For instance, I am using ByRef (ref for C#) strError parameter to return error description is any).

Output: The script uses PRINT statement to display the function code. Make sure that the Query Options/Result/Maximum number of characters displayed in each column property is set to its maximum value, which is 8192. For some stored procedures this set still isn't long enough to PRINT all code. In this case, the script creates Visual Studio Source file with an extension: .vb or .cs, depending on which language you going to use - Visual Basic or C#. To create a file from a Management Studio, I would recommend for you to use this technique: use a bcp utility string then execute the string using a xp_cmdshell extended stored procedure. Most values like the database and server name are obtained using intrinsic SQL Server functions, but you need to set up an @outFileTo variable to validate the directory path. (Note: I would recommend that you use the network path, for example \\my_shared\code_storage ...)

Since the most common Framework languages are VB.NET and C#, I created separate scripts for each one. The script is based on SQL Server 2005 system views, but in the comments I explained how to convert the script to SQL Server 2000.

What to replace within the scripts:

VB.NET

• Line 26 – Specify filter (if needed)
• Line 244 – Specify your local or shared directory and file name
• Line 245 – Implicit server name is recommended (optional)
• Line 269 – Execute BCP command (optional. Comment this line if you do not need to create the output file)

C#

• Line 26 – Specify filter (if needed)
• Line 266 – Specify your local or shared directory and file name
• Line 267 – Implicit server name is recommended (optional)
• Line 289 – Execute BCP command (optional. Comment this line if you do not need to create the output file)

Sample call to load DataGridView:

VB.NET

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
     Dim oDATA As New ProcedureClass
     Dim oDS As New DataSet
     Dim strError As String = ""
     oDS = oDATA.GetEmailList(strError)
     DataGridView1.DataSource = oDS.Tables(0)
         If strError <> "" Then
             Dim appEx As System.ApplicationException = New ApplicationException(strError)
             Throw appEx
' or do your error hanling process
         End If
End Sub

C#

private void button1_Click(object sender, EventArgs e)
{
     ProcedureClass oData = new ProcedureClass ();
     DataSet oDS = new DataSet();
     string strError = "";
     oDS = oData.GetEmailList(ref strError);
     dataGridView1.DataSource = oDS.Tables[0];
     if (!strError == "")
     {
       System.ApplicationException appEx = new ApplicationException(strError);
       throw appEx;
       // or do your error hanling process
     }
}

Conclusion

My primary area of expertise is database administration and database development. However, in my professional experience I was often involved in full application development process, where database work merges with front-end. The presented scripts have dramatically cut application development time for me and I hope will for you as well.