Multiple Tables return by Stotered Procedure using Linq

Question

hi

i m returning multiple tables by stored procedure.where to store these multiple tables,so that i can bind it to diffrent listviw control.

Answer 1

 

"GuptaAlok12" wrote in message news:67e8a0aa-53a8-4343-9aa6-2f81ab4b2ed0...

hi

i m returning multiple tables by stored procedure.where to store these multiple tables,so that i can bind it to diffrent listviw control.

 

my response -------------------------------------------------------------------------------------------------------------------------------------------

You would use a datareader to read the resultset, create custom objects based on the table fields  and populate the objects from the datareader. In a reader loop, you load each custom object into a List<T> from the datareader and bind the List<T> to the control.

You can do a resultset.movenext to read each resultset returned from a multiple resultset sproc, using a datareader.

http://msdn.microsoft.com/en-us/library/w86s7x04(VS.80).aspx

http://en.csharp-online.net/Understanding_Generics%E2%80%94Examining_the_List(T)_Type

Answer 2

Hello,

darnold92's suggestion is excellent! However, if you drag and drop a stored procedure in a Linq to Sql designer, you need to manually modify the code behind to get each table returned by the stored procedure. You need to do this because by default the L2S designer generates single result set for you.

Please open your designer.cs file and find the code for your stored prcedure and add a few more attributes/code snippets to it. The following is a sample on my machine, the orginal code snippet of the stored procedure:
==========================================================
[Function(Name="dbo.sp_MultipleTables")]
public ISingleResult<sp_MultipleTablesResult> sp_MultipleTables()
{
 IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
 return ((ISingleResult<sp_MultipleTablesResult>)(result.ReturnValue));
}
==========================================================
and we modify it like:
==========================================================
[Function(Name = "sp_MultipleTables")]
[ResultType(typeof(Doc))]
[ResultType(typeof(Task))]
[ResultType(typeof(User))]
public IMultipleResults sp_MultipleTables()
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));

    return (IMultipleResults)result.ReturnValue;
}
==========================================================

After that, you could bind the data to different controls as below:
==========================================================
    TestDBDataContext db = new TestDBDataContext();
    IMultipleResults result = db.sp_MultipleTables();

    var docs = result.GetResult<Doc>();
    this.listBox1.DataSource = docs;
    // ...

    var tasks = result.GetResult<Task>();
    this.listBox2.DataSource = tasks;
    // ...

    var users = result.GetResult<User>();
    this.listBox3.DataSource = users;
    // ...
==========================================================

Please have a try and let me know if it does not work.
Best regards

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback, please tell us.
Welcome to the All-In-One Code Framework!