locked
IDbConnection/DBConnection Schema RRS feed

  • Question

  • Hi,

    I am using this code to get a list of tables,

    IDbConnection conn = Data_Factory.DataProviderFactory.CreateConnection(_dialog.SelectedDataProvider.TargetConnectionType, _dialog.ConnectionString);  
                    try 
                    {  
                        System.Data.Common.DbConnection c = (System.Data.Common.DbConnection)conn;  
                        c.Open();  
                        DataTable schema_tables = c.GetSchema("Tables");  
                    }  
                    catch (Exception ex)  
                    {  
                        MessageBox.Show(ex.Message);  
                    }  
                    finally 
                    {  
                        conn.Close();  
                    } 

     

    I have a couple of questions.

    1) Is there a better way of getting a schema using IDBConnection?

    2) If not, how do I use the restrictions in the get schema. I have tried using 

    new string[] { "", """TABLE" } but keep getting a message "The parameter is incorrect."

    3) Again, if I do choose to use DBConnection, how can I see the Foregin Keys? I have tried setting the collection name to "ForeginKeys" but I get a message saying "The requested collection (ForeginKeys) is not defined." I know I have Foreign Keys in my table.

     

    I would be most gratfull if someone could help.

     

    Thanks,

    Loftty

    Friday, January 23, 2009 11:46 AM

Answers

  • 1) Not that I'm aware of.

    2) You'll have to query the possible restrictions for your provider to see how many you need to pass and which one means what.

    3) Look through the supported schema sets for your provider. Also try getting "MetaDataCollections" and see what you get.

           -Steve

    • Marked as answer by Figo Fei Tuesday, January 27, 2009 8:17 AM
    Friday, January 23, 2009 11:59 AM

All replies

  • 1) Not that I'm aware of.

    2) You'll have to query the possible restrictions for your provider to see how many you need to pass and which one means what.

    3) Look through the supported schema sets for your provider. Also try getting "MetaDataCollections" and see what you get.

           -Steve

    • Marked as answer by Figo Fei Tuesday, January 27, 2009 8:17 AM
    Friday, January 23, 2009 11:59 AM
  • Thanks for your reply. Some usefull stuff there.

    Ok here is my problem.

    I don't know what type of provider the user will be using and I want to be able to get the Tables, PrimaryKeys and ForeignKeys from a database.

    What is the best way to get these?

    Thanks

    Loftty

    Friday, January 23, 2009 12:15 PM
  • A lot of the time, you can use a subset of the supported schema sets that are compatible with all the default providers. I've only retrieved basic table information, so I haven't tried primary/foreign key information. Play around with it and see if you can find where the information is located in one schema set, and then see if the other sets have similar/compatible information (keep in mind "primary key" may also be called an integrity constraint or just an index, and "foreign key" is a type of foreign constraint coupled with a unique index on the foreign table).

    Making it work with any provider may just not be possible. Still, the MS-provided ones are used at least 95% of the time in DB apps, with the vast majority of the remaining market share going to a company called DataDirect Technologies. Any serious developer of ADO.NET providers (like DataDirect) will strive to provide similar metadata as is exposed by the MS providers. So, even though it's not possible to say it'll work with any provider, a "best effort" should go a long way.

            -Steve

    Friday, January 23, 2009 1:56 PM
  • I have been palying around with the DBConnection.GetSchema(). I have been able to get the primary keys, but not been able to get any foreign keys. do you have any ideas?

     

    Thanks

     

    Loftty

    Friday, January 23, 2009 4:03 PM
  • It doesn't look like foreign key information is exposed in a natural way to ADO.NET.

    Also, neither IDataAdapter.Fill nor FillSchema will fill in foreign key information.

    It looks like you'll have to do it differently for each provider. :(

            -Steve

    Friday, January 23, 2009 6:43 PM