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.
