Multiple result set from SQL Via LiNQ

Question

I have a stored procedure as

pr___GetArchiveData

Select * from TABLE1

SELECT * FROM TABLE2

SELECT * FROM TABLE 3

I want to get this result set into a dataset. Or access the values of three select queries!! I have a DBML file in which when i drag and drop the stored procedure generates a code as follows:-

global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.pr___GetArchiveData")]
    public ISingleResult<pr___GetArchiveDataResult> pr___GetArchiveData([global::System.Data.Linq.Mapping.ParameterAttribute(DbType="UniqueIdentifier")] System.Nullable<System.Guid> projectID)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), projectID);
        return ((ISingleResult<pr__Project_pr___GetArchiveData>)(result.ReturnValue));
    }

In the code MVC3 Architecture + LINQ i have written a code to get the result set as follows :-

using (HBDataContext hb = new HBDataContext())
                {
                    System.Data.DataSet ds = new System.Data.DataSet();

                    String connString = connString;

                    var conn = new System.Data.SqlClient.SqlConnection(connString);
                    var cmd = conn.CreateCommand();

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = "pr__GetArchiveData";
                    cmd.Connection.Open();

                    var mReader = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
                    //var reader = cmd.ExecuteReader();

                    //using (System.Data.SqlClient.SqlDataReader mReader = cmd.ExecuteReader())
                    //{
                    //  while (mReader.Read())
                    //{
                    // mReader.Read();

                    var tbl1 = hb.Translate<tbl1 >(mReader).ToList();

                  //  mReader = cmd.ExecuteReader();

                    mReader.NextResult();
                    var tbl2 = hb.Translate<tbl2 >(mReader).ToList();

                    mReader.NextResult();
                    var tbl3 = hb.Translate<tbl3>(mReader).ToList();

                  
                    // }
                    // }
                }

But while running it throws error as -

"Invalid attempt to call NextResult when reader is closed."

I am not sure where i am wrong!!

I have tried using it as while

(mReader.Read())

Kindly suggest!!!!

Have you looked into using your SP function pr___GetArchiveData and just returning an ISingleResult? — esastincy
– esastincy, Commented Jun 22, 2011 at 12:15

Stuart · Accepted Answer · 2011-06-23 08:20:26Z

1

The code gen for ISingleResult won't provide multiple result sets

Try adding your own IMultipleResults wrapper - see the tutorial in http://blogs.msdn.com/b/dditweb/archive/2008/05/06/linq-to-sql-and-multiple-result-sets-in-stored-procedures.aspx

answered Jun 23, 2011 at 8:20

Stuart

66.9k7 gold badges116 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shubh Over a year ago

Thank you Stuart. It worked. Just a little work around with your suggested link :)

Shubh · Accepted Answer · 2013-03-20 20:52:50Z

In .dbml file for the stored procedure the code will be like

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.pr__Home_GetArchiveData")] public ISingleResult<pr__Home_GetArchiveData> pr__Home_GetArchiveData([global::System.Data.Linq.Mapping.ParameterAttribute(Name="AlbumID", DbType="UniqueIdentifier")] System.Nullable<System.Guid> albumID) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), albumID); return ((ISingleResult<pr__Album_GetAlbumsFilesResult>)(result.ReturnValue)); }
Replace it with IMultipleResult as below `

[global::System.Data.Linq.Mapping.FunctionAttribute(Name = "dbo.pr__Home_GetArchiveData")] [ResultType(typeof(tbl1))] [ResultType(typeof(tbl2))] [ResultType(typeof(tbl3))] [ResultType(typeof(tbl4))] public IMultipleResults pr__Home_GetArchiveData([global::System.Data.Linq.Mapping.ParameterAttribute(Name = "HOMEID", DbType = "UniqueIdentifier")] System.Nullable hOMEID) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), hOMEID); return ((IMultipleResults)result.ReturnValue); }

in the code .

 using (HBDataContext hb = new HBDataContext())
                {
                    using (System.Data.Linq.IMultipleResults _results = hb.pr__Home_GetArchiveData(model.HomeID))
                    {
                        List<tbl1> _tbl1= _results.GetResult<tbl1>().ToList();
                        List<tbl2> _tbl2= _results.GetResult<tbl2>().ToList();
                        List<tbl3> _tbl3= _results.GetResult<tbl3>().ToList();
                        List<tbl4> _tbl4= _results.GetResult<tbl4>().ToList();}}

You will get the values of the Select queries from theStoredProcedure ...

Collectives™ on Stack Overflow

Multiple result set from SQL Via LiNQ

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related