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.