title: How to merge two stored procedures into one method and return one result?
body: <p>Here in the code I'm trying to get all data from SQL Server and the method is used to list an users with their rolls in grid form. My question here is how to call/merge two stored procedures and return one result and am I in right way ? If not please explain it and show me the right way so I can learn it how to do it in future. Thanks.</p>
  
  <pre><code>public IEnumerable&lt;ApplicationUser&gt; GetAllUsers()
  {
      try
      {
          List&lt;ApplicationUser&gt; userList = new List&lt;ApplicationUser&gt;();
  
          using (SqlConnection con = new SqlConnection(connectionString))
          {
              SqlCommand cmd = new SqlCommand("sp_UsersReadAll", con);
              cmd.CommandType = System.Data.CommandType.StoredProcedure;
  
              SqlCommand cmd2 = new SqlCommand("sp_GetUserRolls", con);
              cmd.CommandType = System.Data.CommandType.StoredProcedure;
  
              con.Open();
              SqlDataReader reader = cmd.ExecuteReader();
  
              while (reader.Read())
              {
                  ApplicationUser apUser = new ApplicationUser();
  
                  apUser.Id = Convert.ToString(reader["Id"]);
                  apUser.UserName = reader["Username"].ToString();
                  apUser.FirstName = reader["FirstName"].ToString();
                  apUser.LastName = reader["LastName"].ToString();
                  apUser.Email = reader["Email"].ToString();
  
                  userList.Add(apUser);
              }
  
              con.Close();
          }
  
          return userList;
      }
      catch (Exception)
      {
          throw;
      }
  }
  </code></pre>