filling custom c# objects from data received stored procedure

Question

public class User
{
   public string FirstName { get; set; }
   public string LastName { get; set; }
}

public class Address
{
    public string City { get; set; }
    public string Country { get; set; }
}


/*
 * There are 2 c# objects i have shown 
 * There is a stored procedure in my application which
 * returns data for both objects simultaneously 
 * eg 
 * select FirstName, LasteName from Users where something="xyz"
 * select City,Country from Locations where something="xyz"
 * 
 * both queries are run by single procedure 
 * Now how can i fill both objects with from that stored procedure in asp.net using c#
*/

Answer 1

You could use ADO.net and design a dataset which will create the classes for you so your queries will execute and read into classes which store the data you got.

http://msdn.microsoft.com/en-us/library/aa581776.aspx

That is an excellent tutorial on how to create a data access layer, which is what it sounds like you want to do.

Answer 2

Check out the Enterprise library, specifically the Data Access block from microsoft patterns and practices. Even if you don't use it, you can steal, er... borrow code from it to do what you want.

http://www.codeplex.com/entlib

Answer 3

using(SqlConnection connexion = new Sqlconnection(youconenctionstring))
using(SqlCommand command = conenxion.Createcommand())
{
    command.Commandtext = "yourProcName";
    command.CommandType = CommandType.StoredProcedure;
    command.Paramters.Add("@yourparam",yourparamvalue);
    connexion.Open();
    SqlDataReader reader = command.ExecuteReader();


    List<User> users = new List<User>;
    List<Adress> adresses = new List<User>;
    while(read.Read())
    {
        User user = new User();
        user.firstName = (string) read["FirstName"];
        users.Add(user);
     }
    read.NextResult();
    while(read.Read)
    {
        Address address = new Address();
        address.City = (string) read["Name"];
        adresses.Add(address);
    }
   //make what you want with your both list
}

Answer 4

Use ADO.NET, open a SqlDataReader on a SqlCommand object executing the SP with the parameters. Use the SqlDataReader.NextResult method to get the second result set.

Basically:

SqlConnection cn = new SqlConnection("<ConnectionString>");
cn.Open();

SqlCommand Cmd = new SqlCommand("<StoredProcedureName>", cn);
Cmd.CommandType = System.Data.CommandType.StoredProcedure;

SqlDataReader dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);

while ( dr.Read() ) {
    // populate your first object
}

dr.NextResult();

while ( dr.Read() ) {
    // populate your second object
}

dr.Close();

Answer 5

Linq to SQL, Entity Framework, or NHibernate would be my suggestions.