Freigeben über

LINQ to SQL Trick: Get all Table Names

  • Artikel

It's often useful to be able to list all the tables or columns in your DataContext. One example is if you want to write a report generation system that works for any DataContext class. LINQ to SQL has an API called the MetaModel which allows you to get at this information and a lot more. Here's the simplest example, it lists all the table names in the context:

        var model = new AttributeMappingSource().GetModel(typeof(Northwind));

        foreach (var mt in model.GetTables())

            Console.WriteLine(mt.TableName);

The MetaModel holds all of the information the LINQ to SQL uses to map between the CLR type system and the SQL database. The above code doesn't require an instance of the DataContext. If you happen to have an instance then you could also,

        Northwind db = ...from somewhere

        var model = db.Mapping;

        foreach (var mt in model.GetTables())

            Console.WriteLine(mt.TableName);

In a comment below, Max asks how you get the column names for a table. Here it is:

         var model = new AttributeMappingSource().GetModel(typeof(Northwind));
        foreach (var mt in model.GetTables()) {
            Console.WriteLine(mt.TableName);
            foreach (var dm in mt.RowType.DataMembers) 
                Console.WriteLine(" "+dm.MappedName);

The metamodel represents everything that LINQ to SQL knows about the .NET type system and its mapping into the database. So you can find columns, associations, SQL types, inheritance information, and others.

If you're ambitious you could even replace the MetaModel with your own implementation--for example, maybe you want to generate your mapping or the fly, or read it from a web service.

This posting is provided "AS IS" with no warranties, and confers no rights.

Comments

  • Anonymous
    July 30, 2007
    PingBack from http://www.universityupdate.com/Technology/C-Sharp/4222002.aspx

  • Anonymous
    August 30, 2007
    Once you have that how would you go about getting all of the Columns for each of these tables?

  • Anonymous
    August 31, 2007
    Max, I added an example for column names above.

  • Anonymous
    January 08, 2008
    How would you get a particular table using a dropdown to select the table name?

  • Anonymous
    February 02, 2009
    hi, Im trying to code a webservice that gets the name of a table from a control and returns an array that fills a dropdownlist, something like this:"but something is wrong, could u help me out? [WebMethod] public ListItemDupla[] GetColumnList(string TableName) { var model = new AttributeMappingSource().GetModel(typeof(MyDataContext)); foreach (var mt in model.GetTables()) {  if (mt.TableName.ToString() == TableName)  {   foreach(var dm in mt.RowType.DataMembers)   {    var fields= from c in dm.MappedName                select new                ListItemDupla(c.ToString(),                  c.GetType());                return campos.ToArray();   }  } } }

  • Anonymous
    June 21, 2010
    Hi Very useful article, do you know how to or if it is possible to get the column names for a lINQ function as apposed to a table, I have tried replacing GetTables() with GetFunctions in the example above this returns my stored procedure names but than am totally stuck on how to get the column names from my stored procedure if at all possible? Thanks in advance!!!