RSS 2.0 Feed
RSS 2.0


Atom 1.0 Feed
Atom 1.0

  Retrieving database independent schema information 


Something that I think is often overlooked in the .NET Framework is the cool stuff you can get at using OleDbSchemaGuid to retrieve database schema information. I just thought of this again earlier today when I was reviewing a C# database application where ADOX was used via interop to retrieve schema. Why use the extra overhead of interop to get to ADOX when you have something built into the framework that will accomplish most of what can be done via ADOX? However, you'll find that ADOX is used frequently for this kind of stuff in .NET and I think it is mainly because people do not know there is a managed alternative.

First of all, let me preface this all with saying that using OleDbSchemaGuid (or ADOX for that matter) is a great way for retrieving database independent schema information. For example, if your application will need to retrieve schema for an unknown OLE DB data source, could be SQL, Oracle, dBase, Access, Excel or even text. Using OleDbSchemaGuid will allow you to do so via OLE DB - completely independent of the underlying source. If you know that your application will always use a specific source, such as SQL Server, then there are always better platform specific ways to retrieve schema rowsets.

So moving on, since the fields in the OleDbSchemaGuid class maps to OLE DB schema rowsets, you won't be surprised that this class only exists in the System.Data.OleDb namespace, not in the SqlClient or other managed provider namespaces (if you need platform specific schema information then use the schema rowsets available in that platform). The syntax might seem a little confusing at first. Basically, you have to have a connected OleDbConnection object. You use the connection's GetOleDbSchemaTable method and pass to it the appropriate static member of the OleDbSchemaGuid class to indicate which type of schema rowset to return along with an object array to include any additional details (such as the table name to return columns for etc). A DataTable is returned that includes the schema rowset data. Take a look at some samples (we'll just bind the returned DataTables to some controls to keep things simple. Also note, in these examples, cn refers to an open OleDbConnection):


//Get the databases from connection & bind to combobox
DataTable t = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs, new object[] {null});
cboCatalogs.DataSource = t;
cboCatalogs.DisplayMember = "CATALOG_NAME";

//Get tables for database (and assoc details) & bind to grid
DataTable t = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {"myDatabase", null, null, null});
dataGrid1.DataSource = t;

//Get columns (and assoc details) for table & bind to grid
DataTable t = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] {"myDatabase", null, "myTable", null});
dataGrid2.DataSource = t;

Easy enough. It all goes way deeper than that, but that gets the point across. You can get to anything from catalogs/databases on a server, tables, columns, procedures, views, constraints, indexes, key information, character sets, object privileges, etc etc etc. The docs to cover what detail is passed in the object array for the various schema rowsets. The columns in the returned DataTable obviously differs from rowset to rowset. I don't believe those are in the docs so you'll need to play around and take a look. Take a look at each of the static fields in the OleDbSchemaGuid class for more details: OleDbSchemaGuid Members

With all that said, however, I do admit that there are some things that ADOX can do that you can't do with the OleDbSchemaGuid class. For example, you can actually create objects with ADOX, you can't do that here. But most of the times I see ADOX used in a .NET application that is not something being done anyway. If all you're doing is retrieving schema data, then this is the way to go. Easy to use, and in the framework.




                   



Leave a comment below.

Comments

  1. makrem 2/6/2006 11:59 PM
    Gravatar
    Salut tout le monde
  2. Brendon 5/9/2006 9:19 AM
    Gravatar
    thanks - have been reading various pages on this and yours so far is the best explained.
  3. dharm 7/10/2006 11:44 PM
    Gravatar
    i want to use ADOX in ASp.NET/C# for retriving text of table views storeprocedure etc.
    I didn't understant about cboCatalog variable.
    Please explain in my email
    dharm@u-tosolutions.com
  4. Ryan Farley 7/19/2006 12:19 PM
    Gravatar
    Hi dharm,

    In the code examples in my post, the "cboCatalogs" represents a ComboBox that I am filling with the database names.

    -Ryan
  5. lnorth 10/21/2006 4:35 PM
    Gravatar
    I'm getting exception on this line when trying to run:

    DataTable t = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {"northwind", null, null, null});


    here is code:
    OleDbConnection cn = new OleDbConnection();

    //Connect to the Northwind database in SQL Server.
    //Be sure to use an account that has permission to list tables.
    cn.ConnectionString = "Provider=SQLOLEDB;Data Source=america;User ID=;"
    + "Password=;Initial Catalog=Northwind";
    cn.Open();

    //Retrieve schema information about tables.
    //Because tables include tables, views, and other objects,
    //restrict to just TABLE in the Object array of restrictions.
    DataTable t = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {"northwind", null, null, null});


    //List the table name from each row in the schema table.
    for (int i = 0; i < t.Rows.Count; i++)
    {
    Console.WriteLine(t.Rows[i].ItemArray[2].ToString());
    }

    //Explicitly close - don't wait on garbage collection.
    cn.Close();

    //Pause
    Console.ReadLine();
  6. lnorth 10/21/2006 4:38 PM
    Gravatar
    sorry, forgot to list exception:

    An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
  7. Jason D. 5/8/2007 10:28 AM
    Gravatar
    Thank you so much!

    I've been racking my brain for the last week trying to find a way to get Database-Independent Schema Information, including using the ODBC API from VC++ 6 with no luck; this works like a charm!


  8. Gary J. 5/17/2007 7:43 AM
    Gravatar
    I have been trying to return Stored Procedures/QueryDefs from Access. I find that I am only able to return them if they take Parameters. Is there a way to return them when they perform more like Views.

    Here is my code:
    OleDbConnection cn = openConnection();
    cn.Open();
    DataTable dataTable = new DataTable();
    cboStoredProcs.Items.Clear();

    dataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, new object[]{null, null, null, null});
    foreach(DataRow dataRow in dataTable.Rows)
    {
    cboStoredProcs.Items.Add(dataRow["PROCEDURE_NAME"].ToString());
    }

    Thanks,

  9. chaitanya 3/18/2008 12:55 AM
    Gravatar
    can we get data from datatable without using datareader and dataadapter
  10. Fidel 9/15/2009 10:03 PM
    Gravatar
    That was a really well written article - thankyou very much for it.

    Quick question though:
    Is it possible to get Access's module code using this mechanism?
Comments have been closed on this topic.



 

News


Also see my CRM Developer blog

Connect:              


Sponsor

Sections