What's a good way to get data from a datatable to its corresponding object? I'm guessing there's a more efficient and cleaner method than how I'm currently doing it:
var username = dt_user.rows[0].columns[0];
Thanks!
You might benefit from the helper class in this post: http://lozanotek.com/blog/archive/2007/05/09/Converting_Custom_Collections_To_and_From_DataTable.aspx
public class CollectionHelper
{
private CollectionHelper()
{
}
public static DataTable ConvertTo<T>(IList<T> list)
{
DataTable table = CreateTable<T>();
Type entityType = typeof(T);
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);
foreach (T item in list)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(item);
}
table.Rows.Add(row);
}
return table;
}
public static IList<T> ConvertTo<T>(IList<DataRow> rows)
{
IList<T> list = null;
if (rows != null)
{
list = new List<T>();
foreach (DataRow row in rows)
{
T item = CreateItem<T>(row);
list.Add(item);
}
}
return list;
}
public static IList<T> ConvertTo<T>(DataTable table)
{
if (table == null)
{
return null;
}
List<DataRow> rows = new List<DataRow>();
foreach (DataRow row in table.Rows)
{
rows.Add(row);
}
return ConvertTo<T>(rows);
}
public static T CreateItem<T>(DataRow row)
{
T obj = default(T);
if (row != null)
{
obj = Activator.CreateInstance<T>();
foreach (DataColumn column in row.Table.Columns)
{
PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);
try
{
object value = row[column.ColumnName];
prop.SetValue(obj, value, null);
}
catch
{
// You can log something here
throw;
}
}
}
return obj;
}
public static DataTable CreateTable<T>()
{
Type entityType = typeof(T);
DataTable table = new DataTable(entityType.Name);
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);
foreach (PropertyDescriptor prop in properties)
{
table.Columns.Add(prop.Name, prop.PropertyType);
}
return table;
}
}
To see the full code in action, check this sample out:
public class MyClass
{
public static void Main()
{
List<Customer> customers = new List<Customer>();
for (int i = 0; i < 10; i++)
{
Customer c = new Customer();
c.Id = i;
c.Name = "Customer " + i.ToString();
customers.Add(c);
}
DataTable table = CollectionHelper.ConvertTo<Customer>(customers);
foreach (DataRow row in table.Rows)
{
Console.WriteLine("Customer");
Console.WriteLine("---------------");
foreach (DataColumn column in table.Columns)
{
object value = row[column.ColumnName];
Console.WriteLine("{0}: {1}", column.ColumnName, value);
}
Console.WriteLine();
}
RL();
}
#region Helper methods
private static void WL(object text, params object[] args)
{
Console.WriteLine(text.ToString(), args);
}
private static void RL()
{
Console.ReadLine();
}
private static void Break()
{
System.Diagnostics.Debugger.Break();
}
#endregion
}
Check out the extensions in System.Data.DataSetExtensions. It provides a generic extension method Field with various overloads for DataRow.
var username = dt_user.rows[0].columns[0];
//becomes
var username = dt_user.rows[0].Field<String>(0);
The real power comes when accessing nullable primative types:
var integerValue = dataRow.Field<Int32?>("ColumnName");
More information can be found here: http://msdn.microsoft.com/en-us/library/system.data.datarowextensions.aspx
if you know the data type you expect; you can perfectly do this for strings:
string username = dt_user.rows[0][0].ToString();
and this for ints:
int userID = int.Parse(dt_user.rows[0][0].ToString());
Although that's still ugly. I would write a couple extension methods as so:
public static string ObjectToString(this object theObject)
{
if(!string.IsNullOrEmpty(theObject))
return theObject.ToString();
return string.Empty;
}
public static int ObjectToInt(this object theObject)
{
int result = 0;
if(!string.IsNullOrEmpty(theObject) && int.TryParse(theObject, out result))
{
return result;
}
return -1;
}
And then I would call it like this:
string username = dt_user.rows[0][0].ObjectToString();
int userid = dt_user.rows[0][0].ObjectToInt();
One more suggestion: Don't access column names by using the index if you don't have to; you can perfectly say dt_user.rows[0]["username"].ToString();
And that will guard you from unexpected results/errors in case someone changes the position of a particular column in your result set.
DataRowby ordinal is definitely efficient, but not what I'd call "clean". You can reference a column by name:dt_user.rows[0].columns["USER_NAME"], which is cleaner but somewhat less efficient (although this almost certainly won't matter). You can also iterate through all theDataRowobjects in your DataTable usingforeachsyntax.