how to create a class automatically from datatable

Question

I know there's a cleaner way to do this than the way I am. Basically, I retrieve a datatable from my sql database. I now want to have that information in a class that is globally accessible. I don't want to have to go through each column like so: txtFirstName.Text = dt[0].ToString(). I want to create a "User" class, and assign txtFirstName.Text = User.FirstName. BUT, I don't want to manually map dt[0] to FirstName, and dt[1] to LastName...I want the object to be created automagically! Or, at least once the class is created, and the elements match the names of the dt columns, the mapping should happen automatically.

Thanks!

Answer 1

Why not use Linq to SQL? I like it alot.

Once you draw out your tables you can then create a separate partial .cs class file for individual tables in this case. DO NOT EDIT THE GENERATED CODE :) If you saved your dbml file in lets say "App_Code/Linq_customer/" Folder and mapped a table you named "Customer", your class may look like this (untested code):

using System.Linq;

namespace Linq_customer
{
    public partial class Customer
    {
        public static Customer GetCustomerByID(int customerID, CustomerDataContext dc)
        {

            return (from s0 in dc.Customers
                   where s0.customerID== customerID
                   select s0).firstOrDefault();
        }
    }
}

Then from your page or other class do something like:

using Linq_customer;
...
CustomerDataContext dc = new CustomerDataContext() //(Generated data context)
Customer myCustomerDude = Customer.GetCustomerByID(5, dc);
if(myCustomerDude == null) return error..
txtFirstName.Text = myCustomerDude.firstName;

Optionally if you wanted to create a new user:

myCustomerDude = new Customer();

And saving any new info is fairly easy too:

...

myCustomerDude.City = txtCity.Text;
myCustomerDude.favoriteSlogan = "I believe in Science";
dc.Customers.insertOnSubmit(myCustomerDude); //If not already existing, if is already in table then omit this line.
dc.submitChanges();

Answer 2

You're basically asking for an ORM (object relational mapping). Two of them that come with .NET are LINQ to SQL and the Entity Framework. Others also exist, such as NHibernate. The topic is pretty involved, so you may want to investigate all of your options and select which would work best in your scenario.

Answer 3

Don't roll your own. It's far too much work. Use LINQ-to-SQL. The command-line tool sqlmetal will convert an existing schema into a C# file that you can use directly in LINQ queries. As a matter of fact, I was doing precisely this for our FogBugz database when this question popped up:

sqlmetal /server:<our-server> /database:fogbugz /views /functions /sprocs /code:fogbugz.cs

Answer 4

you can use reflection,like this: I'm sorry the comments are chinaese you can invoke like this: ConvertDataRowToModel(User object,dt,0);

    #region 根据反射机制将dataTable中指定行的数据赋给obj对象
    /// <summary>
    /// 根据反射机制将dataTable中指定行的数据赋给obj对象
    /// </summary>
    /// <param name="obj">obj对象</param>
    /// <param name="dataTable">dataTable</param>
    /// <param name="rowIndex">指定行</param>
    public static void ConvertDataRowToModel(object obj, DataTable dataTable, int rowIndex)
    {
        //指定行不存在
        if (dataTable.Rows.Count < (rowIndex + 1))
        {
            throw new Exception("指定行不存在!");
        }

        //DataTable列为空!
        if (dataTable.Columns.Count < 1)
        {
            throw new Exception("DataTable列为空!");
        }

        Type type = obj.GetType();
        PropertyInfo[] pInfos = type.GetProperties();

        try
        {
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                for (int j = 0; j < pInfos.Length; j++)
                {
                    //全部转换为小写的作用是防止数据库列名的大小写和属性的大小写不一致
                    if (dataTable.Columns[i].ColumnName.ToLower() == pInfos[j].Name.ToLower())
                    {
                        PropertyInfo pInfo = type.GetProperty(pInfos[j].Name);  //obj某一属性对象

                        object colValue = dataTable.Rows[rowIndex][i]; //DataTable 列值

                        #region 将列值赋给object属性
                        if (!Comm_Object.ObjectIsNull(colValue))
                        {
                            if (pInfos[j].PropertyType.FullName == "System.String")
                            {
                                pInfo.SetValue(obj, Convert.ToString(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Int32")
                            {
                                pInfo.SetValue(obj, Convert.ToInt32(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Int64")
                            {
                                pInfo.SetValue(obj, Convert.ToInt64(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Single")
                            {
                                pInfo.SetValue(obj, Convert.ToSingle(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Double")
                            {
                                pInfo.SetValue(obj, Convert.ToDouble(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Decimal")
                            {
                                pInfo.SetValue(obj, Convert.ToDecimal(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Char")
                            {
                                pInfo.SetValue(obj, Convert.ToChar(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Boolean")
                            {
                                pInfo.SetValue(obj, Convert.ToBoolean(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.DateTime")
                            {
                                pInfo.SetValue(obj, Convert.ToDateTime(colValue), null);
                            }
                            //可空类型
                            else if (pInfos[j].PropertyType.FullName == "System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]")
                            {
                                pInfo.SetValue(obj, Convert.ToDateTime(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Nullable`1[[System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]")
                            {
                                pInfo.SetValue(obj, Convert.ToDateTime(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Nullable`1[[System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]")
                            {
                                pInfo.SetValue(obj, Convert.ToInt32(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Nullable`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]")
                            {
                                pInfo.SetValue(obj, Convert.ToInt32(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Nullable`1[[System.Int64, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]")
                            {
                                pInfo.SetValue(obj, Convert.ToInt64(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Nullable`1[[System.Int64, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]")
                            {
                                pInfo.SetValue(obj, Convert.ToInt64(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Nullable`1[[System.Decimal, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]")
                            {
                                pInfo.SetValue(obj, Convert.ToDecimal(colValue), null);
                            }
                            else if (pInfos[j].PropertyType.FullName == "System.Nullable`1[[System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]")
                            {
                                pInfo.SetValue(obj, Convert.ToDecimal(colValue), null);
                            }
                            else
                            {
                                throw new Exception("属性包含不支持的数据类型!");
                            }
                        }
                        else
                        {
                            pInfo.SetValue(obj, null, null);
                        }
                        #endregion

                        break;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    #endregion

Answer 5

You can use an ORM which will map your database tables to objects. Other options include

1. Create a T4 template to read from the database and create your objects with "mapping" code. (http://msdn.microsoft.com/en-us/library/bb126445.aspx)
2. Create the classes and use AutoMapper to map the IDataReader to your object.