Forums » .NET » .NET »

How to convert linq result as dataset


Posted Date: 30 Jul 2009      Posted By:: ubendiran.th Profile photo    Member Level: Silver    Member Rank: 2606     Points: 1   Responses: 3



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




Responses

#408318    Author: vikram chudasama      Member Level: Gold      Member Rank: 0     Date: 30/Jul/2009   Rating: 2 out of 52 out of 5     Points: 2

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    Author: ABitSmart      Member Level: Gold      Member Rank: 20     Date: 30/Jul/2009   Rating: 2 out of 52 out of 5     Points: 2

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    Author: ubendiran.th      Member Level: Silver      Member Rank: 2606     Date: 30/Jul/2009   Rating: 2 out of 52 out of 5     Points: 2

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




 
Post Reply

 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.



Return to Discussion Forum
Start new thread

Subscribe to Subscribers
Awards & Gifts
Talk to Webmaster Tony John
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India