CODEWITHSUNDEEP
c#
MARKAND Bhatt
MARKAND Bhatt

Reputation: 2630

Populate stored procedure result to a List<T>

Is there a way to map the results of a stored procedure to a generic list instead of a dataset/datatable?

Currently I follow these steps:

  1. Execute stored procedure
  2. Take the result in Dataset
  3. Populate list from the Dataset.

Is there a way to eliminate step (2).

OleDbCommand cm = new OleDbCommand();
cm.Connection = AccessConnection();
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "seltblContacts";

OleDbDataAdapter adp = new OleDbDataAdapter(cm);

DataTable dt = new DataTable();
adp.Fill(dt);

List<tblContacts> LstFile = new List<tblContacts>();

if (dt.Rows.Count > 0)
{
    tblContacts t;

    foreach (DataRow dr in dt.Rows)
    {
        t = PopulateContacts(dr);
        LstFile.Add(t);
    }
}

Upvotes: 1

Views: 2779

Answers (1)

marc_s
marc_s

Reputation: 754598

Yes of course you can do that - just execute your command and get back a reader, and then iterate over the rows in the result set and build up your objects:

using (OleDbCommand cm = new OleDbCommand())
{
     cm.Connection = AccessConnection();
     cm.CommandType = CommandType.StoredProcedure;
     cm.CommandText = "seltblContacts";

     List<tblContacts> LstFile = new List<tblContacts>();

     using (OleDbReader reader = cm.ExecuteReader()) 
     {
        while(reader.Read())
        {
            tblContacts contact = new tblContacts();

            // here, set the properties based on your columns from the database
            contact.FirstName = reader.GetString(0);    
            contact.LastName = reader.GetString(1);
            // etc.

            LstFile.Add(contact);
        }

        reader.Close();
     }

     return LstFile;    
}

For details on OleDbReader and how to use it, see this other SO question or find tons of tutorials and samples online using Bing or Google.

Upvotes: 5

Related Questions