Auto generate class properties by binding to database lookup table

I'm not sure if this is feasible or not but is there a way in C# that will allow me to generate static members or enumrator of all the lookup values in a database table?

For example, if I have a table for countries with 2 columns: code, countryname. I want a way to convert all the rows in this table into a class with properity for each row so I can do the following:

string countryCo开发者_如何学运维de = Country.Egypt.Code

Where Egypt is a generated property from the database table.

When you say "to convert all the rows", do you actually mean "to convert all the columns"?

I so, and if your ADO.NET provider supports it, you can use LINQ to SQL to auto-generate a class that has properties that match the columns in your table. You can follow this procedure:

1. Right-click on your project and Add / New Item / LINQ to SQL Classes. By default, this will generate a DataClasses1.dbml file with DataClasses1DataContext class.
2. Expand the database connection of interest in the Server Explorer, under Data Connections (you may need to add it there first through right-click on Data Connections).
3. Pick the table of interest and drag'n'drop it onto the surface of DataClasses1.dbml.

Assuming your table name was COUNRTY with fields NAME and CODE, you can then use it from your code like this:

using (var db = new DataClasses1DataContext()) {
    COUNRTY egypt = db.COUNRTies.Where(row => row.NAME == "Egypt").SingleOrDefault();
    if (egypt == null) {
        // "Egypt" is not in the database.
    }
    else {
        var egypt_code = egypt.CODE;
        // Use egypt_code...
    }
}

If you actually meant "rows", I'm not aware of an automated way to do that (which doesn't mean it doesn't exist!). Writing a small program that goes through all rows, extracts the actual values and generates some C# text should be a fairly simple exercise though.

But even if you do that, how would you handle database changes? Say, a value is deleted from the database yet it still exists in your program because it existed at the time of compilation? Or is added to the database but is missing from your program?

This cannot be done because Country.Egypt has to be available at compile time. I think your options are:

1. Generate code for Country class from database. Of course, the question then is how will clients use it?

2. Keep the Properties statically declared and read their Code from database during application start-up

3. Keep the properties as well as code statically declared and check them against database during application start-up.

Further to #1 above, if the client code does not depend on individual property names then these are not types but data and you could just as well use a Country.AllCountries property for that is initialized at start-up.