I wanted to write generic code that returns Sql Server procedure results as DataTable
objects. I have then written the code below, but, I stumbled across the problem of DataTable.Rows.Add()
expecting a params object[]
parameter, while I what I have to pass is a IEnumerable<string>
.
public static DataTable GetProcedureResults(int id)
{
return GetQueryResultAsDataTable<MyProcedure_Result>(_sqlServerDB.MyProcedure(id));
}
private static DataTable GetQueryResultAsDataTable<T>(ObjectResult<T> objectResult)
{
DataTable resultAsDataTable = new DataTable("Result");
var columns = typeof(T).GetProperties();
foreach (var column in columns)
{
resultAsDataTable.Columns.Add(column.Name, typeof(string));
}
foreach (var resultRecord in objectResult)
{
var fields = resultRecord.GetType().GetProperties().Select(p => p.GetValue(resultRecord).ToString());
resultAsDataTable.Rows.Add(fields);
}
return resultAsDataTable;
}
For those who are familiar with this issue, you may have guessed that my datatable at the end looks like:
The values are all treated as a single object, thus are all being inserted in the 1st column.
What is the workaround here, if any ? I read in other posts that seems C#6 will fix this issue, other than that did not find a clear and concise solution.