2

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!

2
  • 2
    Referencing fields in a DataRow by 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 the DataRow objects in your DataTable using foreach syntax. Commented Sep 16, 2011 at 14:05
  • 1
    not sure because you are not giving too many details but if you are loading only 1 value (scalar), use SqlCommand.ExecuteScalar and don't fill a DataTable. Commented Sep 16, 2011 at 14:05

3 Answers 3

3

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
}
Sign up to request clarification or add additional context in comments.

Comments

2

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

Comments

2

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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.