What is an Easy Way to Return Results from a CLR Stored Procedure?

Introduction

What is an Easy Way to Return Results from a CLR Stored Procedure? The question sounds simple enough but yet when I went searching for answers I could not find it. This post describes a helper class that I came up with to handle returning values from a CLR stored procedure.

My Solution

When I set out to write my first CLR stored procedure I expected to be able to do something easy, like write a method that returns an array and have SQL Server work out how to display it as a recordset. In the end I found that CLR works sort of like that, except that you have to figure out all the sizes, declare the structure then handle the passing back of each and every cell in each and every row. I guess that is OK, but if you have read any of my other posts you will have noticed a common theme: I am Lazy. Being as lazy as I am, I started digging into Intellisense to see what methods the various classes exposed to make my life easier. Pretty quickly I found SqlMetaData.InferFromValue to define the columns of the result without having to figure out what SQLMetaData type each column in the result set converted to.

Armed with a way to quickly define a column in a recordset I started adding iterative code to walk through various types of objects. I started with walking a DataReader then added DataTables and DataSets, then finally progressed to using reflection to display all of the properties of an object or even all of the properties for all of the objects in an array. I have also added an optional debug flag to output information about the result set to make it easy to define a temporary table to hold the results. Now I have a helper class that I can reference from my CLR stored procedures to quickly return results without very much time spent coding.

Here is an example to show how easy the helper class makes it to code a CLR stored procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
using System;
using System.IO;
using AdventuresInSql;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void FileInfo(String filePath)
    {
        FileInfo fileInfo = new FileInfo(filePath);
        SqlClrHelper.RenderResults(fileInfo);
    }
};

On my systems, I deploy the helper class in it’s own assembly, add the assembly to the server I want to develop against, then open a new project, connect to that server, reference that assembly and write my code. I realize that most people are not using CLR in any distributed manner, making it easiest to just include the class in their project and run with it there.

Warnings: I highly suggest taking the time to deploy this class in it’s own assembly. The assembly this class resides in has to be marked UNSAFE. The database this assembly is deployed to must also be marked TRUSTWORTHY so I highly suggest keeping CLR objects in their own highly secured database. Most importantly, if you do not know what these setttings do stop now and find out before moving any further. UPDATE: Per Adam Machanic’s (Blog|Twitter) comments below, the TRUSTWORTHY setting is not needed if you use certificates.

With that, here is the code for the helper class Updated 7/26/2010 to better handle null values, increase performance and make easier to use. The biggest changes are switching to generic methods rather than using object typed parameters and getting column definitions more efficiently.:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Globalization;
using System.Reflection;
using Microsoft.SqlServer.Server;

public sealed class SqlClrHelper
{

    #region Member Variables

    private const String ARGUMENT_EXCEPTION_STRING = "Extract of property: "{0}" failed with the following message: {1}";
    private const String COLUMN_NAME = "ColumnName";
    private const String COLUMN_SIZE = "ColumnSize";
    private const String DATA_TYPE = "DataType";
    private const String DEBUG_WARNING_MESSAGE = "***Turn off debug before trying to select into a table to avoid conversion exceptions***";
    private const String OBJECT_TYPE_DIFFERENT_EXCEPTION = "All objects in objectsToRender[] must be of the same type.";
    private const String TO_STRING = "ToString()";

    #endregion

    #region Internal Methods

    /// <summary>  
    ///<para>Class will only ever contain static methods.
    ///Added private constructor to prevent compiler from generating default constructor.</para>  
    /// </summary>  
    private SqlClrHelper()
    {
    }

    /// <summary>  
    ///<para>This method takes a column name, type and maximum length, returning the column definition as SqlMetaData.</para>  
    /// </summary>
    /// <param name="System.String">A column name to be used in the returned Microsoft.SqlServer.Server.SqlMetaData.</param>
    /// <param name="System.Type">A column data type to be used in the returned Microsoft.SqlServer.Server.SqlMetaData.</param>
    /// <param name="System.Int32">The maximum length of the column to be used in the returned Microsoft.SqlServer.Server.SqlMetaData.</param>
    private static SqlMetaData ParseSqlMetaData(String columnName, Type type, Int64 maxLength)
    {
        SqlParameter sqlParameter = new SqlParameter();
        sqlParameter.DbType = (DbType)TypeDescriptor.GetConverter(sqlParameter.DbType).ConvertFrom(type.Name);
        if (sqlParameter.SqlDbType == SqlDbType.Char || sqlParameter.SqlDbType == SqlDbType.NChar || sqlParameter.SqlDbType == SqlDbType.NVarChar || sqlParameter.SqlDbType == SqlDbType.VarChar)
        {
            if (maxLength > 8000)
            {
                maxLength = -1;
            }
            return new SqlMetaData(columnName, sqlParameter.SqlDbType, maxLength);
        }
        else if (sqlParameter.SqlDbType == SqlDbType.Text || sqlParameter.SqlDbType == SqlDbType.NText)
        {
            return new SqlMetaData(columnName, sqlParameter.SqlDbType, -1);
        }
        else
        {
            return new SqlMetaData(columnName, sqlParameter.SqlDbType);
        }
    }

    /// <summary>  
    ///<para>This method takes a single object and renders it back to the client.</para>  
    /// </summary>  
    /// <param name="<T>">A populated object.</param>
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1811:AvoidUncalledPrivateCode")]
    private static void RenderResults<T>(T objectToRender)
    {
        RenderResults(objectToRender, false);
    }

    /// <summary>  
    ///<para>This method takes the SqlMetaData created to render an object and renders it back to the client.</para>  
    /// </summary>  
    /// <param name="System.Collections.Generic.List<Microsoft.SqlServer.Server>">A reference to a populated SqlMetaData List.</param>
    private static void WriteRecordStructure(List<SqlMetaData> sqlMetaDataList)
    {
        RenderResults(sqlMetaDataList.ToArray(), false);
    }

    #endregion

    #region Public Methods

    #region RenderResults Overloads

    #region Pass-Through Overloads

    /// <summary>  
    ///<para>This method takes a DataSet and renders it back to the client.</para>  
    /// </summary>  
    /// <param name="System.Data.DataSet">A reference to a populated DataSet.</param>  
    public static void RenderResults(DataSet dataSet)
    {
        RenderResults(dataSet);
    }

    /// <summary>  
    ///<para>This method takes a DataSet and renders it back to the client.</para>  
    /// </summary>  
    /// <param name="System.Data.DataSet">A reference to a populated DataSet.</param>
    /// <param name="System.Boolean">A boolean value indicating whether or not to return information
    /// about the record structure to the client.</param>  
    public static void RenderResults(DataSet dataSet, Boolean isDebugOn)
    {
        foreach (DataTable dataTable in dataSet.Tables)
        {
            RenderResults(dataTable, isDebugOn);
        }
    }

    /// <summary>  
    ///<para>This method takes a DataTable and renders it back to the client.</para>  
    /// </summary>  
    /// <param name="System.Data.DataTable">A reference to a populated DataTable.</param>  
    public static void RenderResults(DataTable dataTable)
    {
        RenderResults(dataTable, false);
    }

    /// <summary>  
    ///<para>This method takes an OleDbDataReader and renders it back to the client.</para>  
    /// </summary>  
    /// <param name="System.Data.OleDb.OleDbDataReader">A reference to a populated OleDbDataReader.</param>
    public static void RenderResults(OleDbDataReader dataReader)
    {
        RenderResults(dataReader, false);
    }

    /// <summary>  
    ///<para>This method takes a single object and renders it back to the client.</para>  
    /// </summary>  
    /// <param name="<T>">A reference to a populated object.</param>
    /// <param name="System.Boolean">A boolean value indicating whether or not to return information
    /// about failed argument exceptions and record structure to the client.</param>
    public static void RenderResults<T>(T objectToRender, Boolean isDebugOn)
    {
        T[] objectsToRender = new T[1];
        objectsToRender[0] = objectToRender;
        RenderResults(objectsToRender, isDebugOn);
    }

    /// <summary>  
    ///<para>This method takes an array of objects and renders it back to the client.</para>  
    /// </summary>  
    /// <param name="System.Object[]">A reference to a populated object.</param>
    public static void RenderResults<T>(T[] objectsToRender)
    {
        RenderResults(objectsToRender, false);
    }

    #endregion

    /// <summary>  
    ///<para>This method takes a DataTable and renders it back to the client.</para>  
    /// </summary>  
    /// <param name="System.Data.DataTable">A reference to a populated DataTable.</param>  
    /// <param name="System.Boolean">A boolean value indicating whether or not to return information
    /// about the record structure to the client.</param>  
    public static void RenderResults(DataTable dataTable, Boolean isDebugOn)
    {
        List<SqlMetaData> sqlMetaDataList = new List<SqlMetaData>();
        for (int i = 0; i < dataTable.Rows[0].ItemArray.Length; i++)
        {
            sqlMetaDataList.Add(ParseSqlMetaData(dataTable.Columns[i].ColumnName, dataTable.Columns[i].DataType, dataTable.Columns[i].MaxLength));
        }
        SqlDataRecord sqlDataRecord = new SqlDataRecord(sqlMetaDataList.ToArray());
        SqlContext.Pipe.SendResultsStart(sqlDataRecord);
        if (SqlContext.Pipe.IsSendingResults)
        {
            foreach (DataRow dataRow in dataTable.Rows)
            {
                sqlDataRecord.SetValues(dataRow.ItemArray);
                SqlContext.Pipe.SendResultsRow(sqlDataRecord);
            }
            SqlContext.Pipe.SendResultsEnd();
        }
        if (isDebugOn)
        {
            WriteRecordStructure(sqlMetaDataList);
        }
    }

    /// <summary>  
    ///<para>This method takes an OleDbDataReader and renders it back to the client.</para>  
    /// </summary>  
    /// <param name="System.Data.OleDb.OleDbDataReader">A reference to a populated OleDbDataReader.</param>
    /// <param name="System.Boolean">A boolean value indicating whether or not to return information
    /// about the record structure to the client.</param>  
    public static void RenderResults(OleDbDataReader oleDBDataReader, Boolean isDebugOn)
    {
        Int64 columnSize = 0;
        List<SqlMetaData> sqlMetaDataList = new List<SqlMetaData>();
        foreach (DataRow dataRow in oleDBDataReader.GetSchemaTable().Rows)
        {
            if (Int64.TryParse(((Int32)dataRow[COLUMN_SIZE]).ToString(CultureInfo.CurrentCulture), out columnSize))
            {
                sqlMetaDataList.Add(ParseSqlMetaData((String)dataRow[COLUMN_NAME], (Type)dataRow[DATA_TYPE], columnSize));
            }
            else
            {
                sqlMetaDataList.Add(ParseSqlMetaData((String)dataRow[COLUMN_NAME], (Type)dataRow[DATA_TYPE], -1));
            }
        }
        SqlDataRecord sqlDataRecord = new SqlDataRecord(sqlMetaDataList.ToArray());
        Object[] objects = new Object[sqlMetaDataList.Count];
        SqlContext.Pipe.SendResultsStart(sqlDataRecord);
        if (SqlContext.Pipe.IsSendingResults)
        {
            while (oleDBDataReader.Read())
            {
                oleDBDataReader.GetValues(objects);
                sqlDataRecord.SetValues(objects);
                SqlContext.Pipe.SendResultsRow(sqlDataRecord);
            }
            SqlContext.Pipe.SendResultsEnd();
        }
        if (isDebugOn)
        {
            WriteRecordStructure(sqlMetaDataList);
        }
        if (oleDBDataReader.NextResult())
        {
            RenderResults(oleDBDataReader, isDebugOn);
        }
    }

    /// <summary>  
    ///<para>This method takes an array of objects and renders it back to the client.</para>  
    /// </summary>  
    /// <param name="<T>[]">A reference to an array of populated objects.</param>
    /// <param name="System.Boolean">A boolean value indicating whether or not to return information
    /// about failed argument exceptions and record structure to the client.</param>
    public static void RenderResults<T>(T[] objectsToRender, Boolean isDebugOn)
    {
        List<SqlMetaData> sqlMetaDataList = new List<SqlMetaData>();
        List<List<Object>> sqlMetaDataValues = new List<List<Object>>();
        SqlDataRecord sqlDataRecord = null;
        Type objectType = null;
        for (int i = 0; i < objectsToRender.Length; i++)
        {
            if (objectsToRender[i] == null)
            {
                continue;
            }
            T objectToRender = objectsToRender[i];
            if (objectType == null)
            {
                objectType = objectToRender.GetType();
            }
            if (objectToRender.GetType() != objectType)
            {
                throw (new InvalidCastException(OBJECT_TYPE_DIFFERENT_EXCEPTION));
            }
            foreach (PropertyInfo property in objectToRender.GetType().GetProperties())
            {
                SqlMetaData sqlMetaData = null;
                if (property.CanRead && property.GetIndexParameters().Length == 0)
                {
                    try
                    {
                        sqlMetaData = SqlMetaData.InferFromValue(property.GetValue(objectToRender, null), property.Name.ToString());
                        for (int j = 0; j < sqlMetaDataList.Count; j++)
                        {
                            if (sqlMetaDataList[j].Name == sqlMetaData.Name)
                            {
                                if (sqlMetaDataList[j].MaxLength < sqlMetaData.MaxLength)
                                {
                                    sqlMetaDataList[j] = sqlMetaData;
                                }
                                sqlMetaData = null;
                                break;
                            }
                        }
                        if (sqlMetaData != null)
                        {
                            sqlMetaDataList.Add(sqlMetaData);
                        }
                        if (sqlMetaDataValues.Count == i)
                        {
                            sqlMetaDataValues.Add(new List<Object>());
                        }
                        sqlMetaDataValues[i].Add(property.GetValue(objectToRender, null));
                    }
                    catch (ArgumentException ex)
                    {
                        if (isDebugOn)
                        {
                            SqlContext.Pipe.Send(String.Format(CultureInfo.CurrentCulture, ARGUMENT_EXCEPTION_STRING, property.Name.ToString(), ex.Message.ToString()));
                        }
                    }
                }
            }
            if (i == 0)
            {
                sqlMetaDataList.Add(SqlMetaData.InferFromValue(objectToRender.ToString(), TO_STRING));
            }
            sqlMetaDataValues[i].Add(objectToRender.ToString());
        }
        sqlDataRecord = new SqlDataRecord(sqlMetaDataList.ToArray());
        SqlContext.Pipe.SendResultsStart(sqlDataRecord);
        if (SqlContext.Pipe.IsSendingResults)
        {
            sqlMetaDataValues.ForEach(sqlMetaDataValue =>
            {
                sqlDataRecord.SetValues(sqlMetaDataValue.ToArray());
                SqlContext.Pipe.SendResultsRow(sqlDataRecord);
            });
            SqlContext.Pipe.SendResultsEnd();
        }
        if (isDebugOn)
        {
            WriteRecordStructure(sqlMetaDataList);
        }

    }

    #endregion

    #endregion

}

Conclusion

I fear that by making it easy to use CLR that I may be opening up a can of worms. I ask that before using CLR that you make sure that it is the best way to accomplish the task you have been given.

As usual, I hope you find this class useful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that code from the internet is like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.