none
C# Linq to SQL Need list of tables in current open database RRS feed

  • Question

  • I have a c# solution (VS 2010) and I have added the LINQ to SQL classes. I am using SQL Server 2012 and I am testing locally. I have the same Data out in Azure where I'll go next.

    I have NOT added any tables. My application has the user configure his/her own data source, userid, and password which the app uses to build a connection string.

    Here is what I have so far:

    if (Properties.Settings.Default.UserConnectionString != "")             {                 Variables.LessonsThisUser = newUserLessonsDataContext(Properties.Settings.Default.UserConnectionString);                 bool giveatry = Variables.LessonsThisUser.DatabaseExists();                 var TableList = Variables.LessonsThisUser.GetType().GetProperties()                     .Where(propertyInfo => propertyInfo.PropertyType == typeof(Table<>))                     .Select(propertyInfo => propertyInfo.GetValue(Variables.LessonsThisUser, nullas ITable);                 foreach (var Table in TableList)                 {                     var looker = Table;                 }                              }

    I create a DataContext and the 'giveatry'tells me the database exists (and the connection is open).

    I need to present my user with a list of tables in his/her DataBase because each table is a different lesson.

    (The DataBase and lessons are created outside the app.)

    The query that follows was the best prospect I found for listing the names of the tables in the user's own DataBase,

    but the foreach loop comes up empty. (This is the third query I've found and tried. They all come up empty.

    SQL Server Management Studio lists the tables just fine. There are 10 tables.)

    Once I get a list of tables, the user will select the one he/she wants and I load that table locally.

    But that's the next step. I need to get this step to work first. It seems like something lots of people would want

    to do, but I cannot find a working example!

      

    Jim Kay

    Monday, October 22, 2012 2:56 PM

Answers

All replies

  • Hi Jim0607,

    Welcome to MSDN Forum.

    If you want to get all the tables of the DataContext, the code below can achieve the purpose.

    var TableList = context.Mapping.GetTables();

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, October 23, 2012 2:47 AM
    Moderator
  • Hi Jim0607,

    Welcome to MSDN Forum.

    If you want to get all the tables of the DataContext, the code below can achieve the purpose.

    var TableList = context.Mapping.GetTables();

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Been there, done that. The 'foreach' loop is entered zero times (I step through it) indicating the result is an empty list.

    The DataContext also show the connection is closed, which seems odd. But 'the database exists!'

    ==============

    In desperation, to get some more work done, I opened the .dbml file and opened the Server Explorer. The Database and all the Tables appear just fine so I dragged a table over to the .dbml and at least I can work with that for now.

    I very intentionally configured the Server Explorer to connect with the exact same credentials I was using in the DataContext and that all seems to be correct. So why I cannot list the tables... at this point, I still don't know.


    Jim Kay


    • Edited by Jim0607 Tuesday, October 23, 2012 4:58 AM
    Tuesday, October 23, 2012 4:15 AM
  • Hi Jim0607,

    Do you mean you didn't add any tables to the dbml file before? If yes, it certainly couldn't get the tables. In your code, it uses reflection to get tables from the DataContext, if there's no such table properties in the DataContext class, reflection will get nothing. With the code I posted, I have successfully get all the tables in DataContext.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, October 24, 2012 2:33 AM
    Moderator
  • Hi Jim0607,

    Do you mean you didn't add any tables to the dbml file before? If yes, it certainly couldn't get the tables. In your code, it uses reflection to get tables from the DataContext, if there's no such table properties in the DataContext class, reflection will get nothing. With the code I posted, I have successfully get all the tables in DataContext.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    I mean exactly that. I did not add the tables to the .dbml because that would not serve my needs.

    My application currently uses a collection of local .xml files, one file for each unit of learning. The units are created and destroyed by the user any time he/she wishes to do that. It's a user determined function.

    Now I am trying to move the local .xml file(s) function to SQL (and eventually to the Cloud.) I understand that LINQ to SQL provides for table create and table destroy functions. So my current design is to have a database for each user and allow the user to create and destroy tables (equal to learning units) at will. It would be most convenient if I could list out the tables in the user's database. So I am trying to find a technique for accessing metadata within the SQL database that will give me a list of tables.

    I'm reluctant to believe this cannot be done since the SQL Server Management Studio does exactly this. The quetion is HOW?

    At the next stage of development, after I can list the tables, I will want to use a DataContext to dynamically access any appropriate table in the database as the 'current' table. This has to include newly created tables as well as old tables and it has to avoid trying to access tables that were just deleted. In no case would I be happy having to add and delete tables in the .dbml and rebuild the project.

    It really should be possible to access a database, list the current tables, pick one, and process that one without having to add everything to the .dbml file.


    Jim Kay

    Wednesday, October 24, 2012 3:08 AM
  • Well, you could use other stuff instead of the datacontext or you can manipulate the dbml files at will, after all they are xml files with the db schema.

    One of the ways you could obtain the names would be:

    http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbschemaguid.tables.aspx

    However remember to filter the result to only get the tables using DataTable.GetOleDbSchemaTable

    An article about this with some interesting comments 

    http://www.codeproject.com/Articles/2738/How-to-get-list-of-tables-in-a-database 

    Regards

    • Marked as answer by Jim0607 Thursday, October 25, 2012 2:22 AM
    Wednesday, October 24, 2012 3:22 PM
  • Dear Serguey123,

    The msdn link is about as uninformative as anything can be. The CodeProject article provided a workable, if very ugly, solution. Not having worked with a DataTable before, it took some tedious picking my way through things to find what I actually wanted.

    Modifying dbml files would, I suspect, mean a rebuild of the application which an end-user would not be doing. So it seems Linq to SQL, pretty as it appears, is also rather inflexible. I can use it for parts or my app, but not this part.

    I'll go back and look at the tags on the CodeProject article as I'd like to know why I didn't find it. (Guessing the right search terms is always so much fun.) And, the associated demo project is SO OLD that most of the technology is no longer supported by .NET!

    It's interesting that Linq to SQL has a function to create a new table, but unless you are recreating a table you just deleted, Linq to SQL cannot access the new table! Good thing there is still OleDB around.

    Getting the DataTable was simple enough but picking my way through it to get the Table Names I want to show to my user, that was tricky.

                   for (int x = 0; x < Variables.UserTables.Rows.Count; x++)
                   {
                       string working = Variables.UserTables.Rows[x].ItemArray[2].ToString();
     
                       if ((working.Length == 7) && (working == "Default"))
                           continue;
     
                       if ((working.Length > 8) && (working.Substring(working.Length - 8,8) == "_Setting"))
                           continue;
     
                       listBox1.Items.Add(working);
                   }

    Jim Kay

    Thursday, October 25, 2012 2:22 AM