LINQ table enumeration with column enumeration

Question

How do you get a list of all the tables and use that list to enumerate the columns? I've found posts that describe one or the other, but not both.

My net-result is I want to generate a static class which contains names of all the columns in each tables so I could, for example, do:

comboBoxFoo.DisplayMember = SomeNamespace.SomeTable.SomeDisplayColumnName;
comboBoxFoo.ValueMember = SomeNamespace.SomeTable.SomeIDColumnName;
comboBoxFoo.DataSource = dingo;

I'm currently using this method which while it works, it means I have to manually create my tables in a list.

I have a seperate command line project which generates the SomeNameSpace.SomeTable class manually and I add the generated class file in to the project.

Ideally, if I could loop through via a foreach of tables and do something like this:

foreach(var table in someTableNumerationMethodForAGivenContext())
{
    var columnList = databaseContext.ColumnNames<someTable>();
    foreach(var columnData in columnList)
    {
        DoJazz(columnData.Name);
    }
}

Is there a better way to do this other than manually have to do the databaseContext.ColumnNames() ?

Edit 1: We're using LinqToSQL. Moving to ADO.NET also an option on the table, but at the moment we have no pressing need to.

Edit 2: I know L2S does databinding but what I'm after is getting a list of column names as strings from a table. L2S doesn't offer this or it's not apparent on my side. I'd like to do something like: SomeTable.SomeColumn.ToString() or something. SubSonic has this.

Final: Thanks everyone. all are very good answers and lead me to the answer. You guys rock!

Answer 1

What you are describing is essentially an ORM

Linq to SQL is an ORM that will create prototype c# classes for you that contain the information you are describing. It has excellent support for the kind of data binding that you have illustrated.

Answer 2

Nazadus,

Is this what you are looking for?

LINQ to SQL Trick: Get all Table [and Column] Names: http://blogs.msdn.com/jomo_fisher/archive/2007/07/30/linq-to-sql-trick-get-all-table-names.aspx

Answer 3

I think you are looking for this.

DataContext.Mapping.GetTable(yourTable).RowType.DataMemebers()

Answer 4

I can think of two ways to do this.

A) Use SMO to get all the tables / columns in a database. You need to reference:

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Management.Sdk.Sfc

Microsoft.SqlServer.Smo

Then, you can do something like this:

        ServerConnection connection = new ServerConnection(".");
        Server server = new Server(connection);
        Database db = server.Databases["Northwind"];
        foreach (Table table in db.Tables)
        {
            foreach (Column column in table.Columns)
            {
                Console.WriteLine("Table: {0}, Column: {1}",table.Name,column.Name);
            }
        }
    }

B) Use reflection to reflect over your assembly with the dataclasses generated by Linq to Sql. Anything with a Table attribute is a table, and anything with a Column attribute is a column. Let me know if you need a sample...