Converting DataReader to Generic Collection

I'm a big fan of using Collections in .NET; I find them cleaner and easier to use than datasets and datatables. There are multiple examples on converting a datatables into a generic collection (a good one here), but I couldn't find many examples of converting the results of a DataReader into one. I did find one excellent sample: AzamSharp has a great post on this here. While that definitely works, the only thing I didn't like was having to define the list of fields every time. It may be a little more flexible, but it seemed to defeat the purpose.

Borrowing largely from his example, I came up with this:


public static void ConvertTo<T>(SqlDataReader sqlDr, IList<T> list, Type type)
{
while (sqlDr.Read())
{
T item = (T)Activator.CreateInstance(type);

// Get all the properties of the type
PropertyInfo[] properties = ((Type)item.GetType()).GetProperties();

for (int j = 0; j < sqlDr.FieldCount; j++)
{
if (sqlDr.GetName(j) == properties[j].Name)
{
properties[j].SetValue(item, sqlDr[j], null);
}
}
list.Add(item);
}
}

Much simpler, although I have no idea if his way is more efficient or not. To use, just use the following code against your custom class:

public class UserProfile
{
public int UserId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}

public List<UserProfile> FillUserProfileList()
{
SqlCommand sqlCmd;
List<UserProfile> upList = new List<UserProfile>();

try
{
sqlCmd = (SqlCommand)db.GetSqlStringCommand("SELECT UserId, FirstName, LastName FROM MyUserTable WHERE UserId = 1");
SqlDataReader sqlDr = (SqlDataReader)db.ExecuteReader(sqlCmd);
Helpers.CollectionHelper.ConvertTo(sqlDr, upList, typeof(UserProfile));
}
catch
{
}
return upList;
}

5 comments:

Anonymous said...

Hi Tim,

Is it possible to have a trial code on using generic to retrieve data from database basically to do the basic database operations like retrieve, delete,update.... basically using datareader. Someone told me it is easy and quick to use generics but can't find my way around it.

Kind regards

Armstrong said...

Hi Tim,
I was looking for Datareader to generic List, and found your code very helpful.
Thanks alot..

I did some changes to the same and have posted the code, this might help some one..

private static IList ReturnSQLDataReaderResponse(SqlDataReader sqlDr)
{
IList list = null;
list = new List();
while (sqlDr.Read())
{
T item = default(T);
item = Activator.CreateInstance();

// Get all the properties of the type
PropertyInfo[] properties = ((Type)item.GetType()).GetProperties();

for (int j = 0; j < sqlDr.FieldCount; j++)
{
if (sqlDr.GetName(j) == properties[j].Name)
{
properties[j].SetValue(item, sqlDr[j], null);
}
}
list.Add(item);
}
return list;
}

raja said...

a Good One.


Regards
Raja

Anonymous said...

its not fetching the first row value. any idea how to get the first row value?