Retrieve data in asp.net MVC from complex stored procedure which return multiple actual table

Question

User Defined Stored Procedure which returns multiple actual table as result set.

CREATE  PROCEDURE uspDemo(
@UserID BIGINT=0,
 @IsAdmin bit=0,
 @Title varchar(120)=''
)AS
BEGIN 
------Retrieve Posts------

SELECT * FROM tblPost AS MP INNER JOIN tblUserProfile AS UP ON UP.ID=MP.UserID
WHERE UP.ID=@UserID AND ((@IsAdmin=0 AND MP.IsDeleted=0 AND MP.IsApproved=1)OR (@IsAdmin=1 OR MP.IsDeleted=0 OR MP.IsApproved=1))

----- Retrieve Tags------

SELECT *  FROM tblTagMasters AS MT INNER JOIN tblPostTags AS MP ON MT.TagID= MP.TagID

--------Retrieve User likes-----

SELECT * FROM tblUserLikes AS UV INNER JOIN tblPost AS MP ON MP.PostId=UV.PostId

END

I want to convert into list format of all returned actual table from stored procedure according to model in asp.net MVC.

 public List<PostView> GetPosts(int userID = 0, string s = "")
    {
        IEnumerable<PostView> query = null;
        using (var db = new MVCDatabase())
        {
            var cmd = db.Database.Connection.CreateCommand();
            cmd.CommandText = "[dbo].[uspDemo]";
            cmd.CommandType = CommandType.StoredProcedure;                             
            cmd.Parameters.Add(new SqlParameter("@UserID", userID));
            cmd.Parameters.Add(new SqlParameter("@IsAdmin", 0));                
            cmd.Parameters.Add(new SqlParameter("@Title", s));
            try
            {
                db.Database.Connection.Open();
                
                using (var result = cmd.ExecuteReader())
                {
                    var Posts = ((IObjectContextAdapter)db).ObjectContext.Translate<PostView>(result).ToList();
                    result.NextResult();
                    var tags = ((IObjectContextAdapter)db).ObjectContext.Translate<TagView>(result).ToList();
                    result.NextResult();
                    var uservotes = ((IObjectContextAdapter)db).ObjectContext.Translate<UserVoteView>(result).ToList();
                    Posts.ForEach(z =>
                    {
                        z.TagMaster = tags.Where(x => x.PostId == z.PostId).ToList();
                        z.UserLike = uservotes.Where(x => x.PostId == z.PostId).ToList();

                    });
                    query = Posts;
                }
            }
            catch (Exception ex)
            {
                MSError.Trace(ex);
            }
            finally
            {
                db.Database.Connection.Close();
                cmd.Dispose();
            }
            return query.ToList();
        }
    }

Throwing an ArgumentNullException

Help me to find out the solution.

Use `ado.net` and `Dataset` to get multiple table. Then you can convert `dataset` to `datatable` or `List` with `LINQ` . — Nagib Mahfuz, Dec 18 '21 at 12:30
but, I want to translate ` result ` into model, and convert into list. then what should i do. — MANISH SHARMA, Dec 18 '21 at 13:45
You can use somethin like AutoMapper. Otherwise you will have to loop through (for each) the result sets (Posts) and add items into IEnumerable query. — Avantha Siriwardana, Dec 18 '21 at 16:22

Nagib Mahfuz · Accepted Answer · 2021-12-19T05:55:06.843

Here is a demo how to do it. Using System.data and System.Linq you can do below.

        var cmd = db.Database.Connection.CreateCommand();
        cmd.CommandText = "[dbo].[uspDemo]";
        cmd.CommandType = CommandType.StoredProcedure;                             
        cmd.Parameters.Add(new SqlParameter("@UserID", userID));
        cmd.Parameters.Add(new SqlParameter("@IsAdmin", 0));                
        cmd.Parameters.Add(new SqlParameter("@Title", s));

        SqlDataAdapter da = new SqlDataAdapter(); //adapter
        DataSet ds = new DataSet(); //dataset
        cmd.CommandType = CommandType.StoredProcedure;
        da = new SqlDataAdapter(cmd);
        da.Fill(ds);  //fill dataset with multiple select


        var Posts = (from DataRow row in ds.Tables[0].Rows  //0 means 1st select
                           select new Posts  //Posts model to map
                           {
                               test = row["test"].ToString(), //test is the column name from select
                               test1 = Convert.ToDecimal(row["test1"])
                           }).ToList();

        var Tags = (from DataRow row in ds.Tables[1].Rows //1 means 2nd select
                           select new Tags //Tags model to map
                           {
                               test = row["test"].ToString(), //test is the column name from select
                               test1 = Convert.ToDecimal(row["test1"])
                           }).ToList();
        var User  = (from DataRow row in ds.Tables[2].Rows //2 means 3rd select
                           select new User //User model to map
                           {
                               test = row["test"].ToString(), //test is the column name from select
                               test1 = Convert.ToDecimal(row["test1"])
                           }).ToList();

If we do not want to read one by one data then what should I do for converting to list of table. — MANISH SHARMA, Dec 20 '21 at 07:53
Then you need `EntityFramework` . And Follow this [Link](https://stackoverflow.com/questions/23172888/entity-framework-stored-procedures-multiple-result-sets-with-codefirst) for more details. Single select is very easy but multi select need some work around. — Nagib Mahfuz, Dec 20 '21 at 07:59

Retrieve data in asp.net MVC from complex stored procedure which return multiple actual table

1 Answers1