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()
    ==typeof(Nullable<>)))
    {
    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
    (rec,null);
    }

    dtReturn.Rows.Add(dr);
    }
    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");

    try
    {
    cmd.Connection.Open();
    adapter.FillSchema(dt, SchemaType.Source);
    adapter.Fill(dt);
    }
    finally
    {
    cmd.Connection.Close();
    }
    return dt;
    }

    Use this as :

    DataTable dt = LINQToDataTable(db, result);



    3. Extend the LINQ and add extension method. Bit lenghty, mentioned here : http://blogs.msdn.com/aconrad/archive/2007/09/07/science-project.aspx

  • #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,
    ABitSmart
    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.