You must Sign In to post a response.
  • Category: .NET

    How to convert linq result as dataset

    I create a stored procedure and call that the procedure through on linq.

    databasecontext db=new databasecontext();
    var results=db.sp_insert_logindetails();

    Now i want to convert the results into dataset or datatable. Anybody help me

    sp_insert_logindetails is a procedure name

    it executes this query: - select * from logindetails
  • #408318
    There are three ways to do this :

    1. Create a method which create a datatable, iterate thru all the properties of the 'resuls' & create corresponding columns in datatable, then iterate thru data of the 'result' & insert datarows to datatable. The code for this is as below :

    public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
    DataTable dtReturn = new DataTable();

    // column names
    PropertyInfo[] oProps = null;

    if (varlist == null) return dtReturn;

    foreach (T rec in varlist)
    // Use reflection to get property names, to create table, Only first time, others
    will follow
    if (oProps == null)
    oProps = ((Type)rec.GetType()).GetProperties();
    foreach (PropertyInfo pi in oProps)
    Type colType = pi.PropertyType;

    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
    colType = colType.GetGenericArguments()[0];

    dtReturn.Columns.Add(new DataColumn(pi.Name, colType));

    DataRow dr = dtReturn.NewRow();

    foreach (PropertyInfo pi in oProps)
    dr[pi.Name] = pi.GetValue(rec, null) == null ?DBNull.Value :pi.GetValue

    return dtReturn;

    Use this as :

    DataTable dt = LINQToDataTable(result);

    2. Create a method which gets a sqlcommand from the 'results' and uses dataadapter to fill datatable. The code for this as below :

    public DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query)
    if (query == null)
    throw new ArgumentNullException("query");

    IDbCommand cmd = ctx.GetCommand(query as IQueryable);
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = (SqlCommand)cmd;
    DataTable dt = new DataTable("sd");

    adapter.FillSchema(dt, SchemaType.Source);
    return dt;

    Use this as :

    DataTable dt = LINQToDataTable(db, result);

    3. Extend the LINQ and add extension method. Bit lenghty, mentioned here :

  • #408363
    If you really want to have to results in a DataSet format I would suggest using the ADO.Net way of doing it. There is not point in doing it with LINQ.
    Kind regards,
    DNS Web-master, DNS MVM
    My blog

  • #408387
    Ok Thank U for your reply. I will try those methods.

This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.