Populating custom C # objects from a 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

Use ADO.NET, open SqlDataReader on the SqlCommand object that runs SP with 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 2

You can use ADO.net and create a dataset that will create classes for you so that your queries are executed and read in the classes that store the data you received.

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

This is a great tutorial on creating a data access layer that sounds like you want to do it.

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

Linq to SQL, Entity Framework NHibernate .

Answer 5

Check out the Enterprise library, in particular the Data Access block, from Microsoft templates and methods. Even if you don’t use it, you can steal ... borrow code from it to do what you want.

http://www.codeplex.com/entlib