CODEWITHSUNDEEP
c#linqstored-proceduresdatatable
elbillaf
elbillaf

Reputation: 1984

Converting a stored procedure LINQ result list to a DataTable

I am modifying some working code to convert some code to using LINQ calls to a stored procedure. It makes the C# look neater for the most part.

This current code invokes the stored procedure to create a data table which is then sent to openxml to create an excel spreadsheet for download. This approach works.

I want to modify this to invoke the SP from LINQ. I am invoking a stored procedure from LINQ to get a list. However, the openxml requires a DataTable.

The code I have looks something like this:

using (TEMPDataContext dbc = new TEMPDataContext(connectionString))
{
    GetTestPlanquestionnaireResult tq  = (GetTestPlanquestionnaireResult) dbc.GetTestPlanquestionnaire(tbStartDate.Text, tbEndDate.Text);       

    DataTable tbl = tq.ConvertToDataTable();        

    MemoryStream ms = new MemoryStream();
    using (ExcelPackage pck = new ExcelPackage(ms))
    {
        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Results");


        ws.Cells["A1"].LoadFromDataTable(tbl, true);

        ws.Cells[1, 1, 1, tbl.Columns.Count].Style.Font.Bold = true;
        ws.Cells[1, 1, 1, tbl.Columns.Count].AutoFitColumns();

        pck.Save();
    }
    byte[] buffer = ms.ToArray();

    Context.Response.ClearContent();
    Context.Response.AddHeader("content-disposition", "attachment; filename=\"downloadfilename.xlsx\"");  
    Context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Context.Response.OutputStream.Write(buffer, 0, buffer.Length);
    Context.Response.End();
}

My issue is with that line in the code which invokes the method that does not yet exist; namely DataTable tbl = tq.ConvertToDataTable();

I haven't written any code for ConvertToDataTable() as it's inconceivable to me that something doesn't already exist to do this. If that's the level of irritation, then I'm probably going to just leave the stuff as as-is and not worry about conversion to LINQ. Is there a way to do this that is simple and easy to read?

Upvotes: 0

Views: 1142

Answers (1)

NetMage
NetMage

Reputation: 26917

In case you are still interested, this is from my LINQPad extensions file:

public static DataTable AsDataTable<T>(this IEnumerable<T> rows) {
    var dt = new DataTable();
    var infos = typeof(T).GetProperties();
    foreach (var info in infos)
        dt.Columns.Add(new DataColumn(info.Name, info.PropertyType));

    foreach (var r in rows) {
        var nr = dt.NewRow();
        for (int i = 0; i < infos.Length; ++i) {
            nr[i] = infos[i].GetValue(r);
        }
        dt.Rows.Add(nr);
    }
    return dt;
}

public static EnumerableRowCollection<DataRow> AsDataRows<T>(this IEnumerable<T> rows) => rows.AsDataTable().AsEnumerable();

Upvotes: 2

Related Questions