We'll add a DataGridView object to the form, which will be used to display data when an option is selected. We'll also add a button to the form and add the following code, which retrieves the metadata collections for the database connection we selected.
Visual Basic
Private Sub button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles button1.Click If comboBox1.Text.Trim().Length = 0 Then MessageBox.Show("Select a connection") Return End If dataGridView1.DataSource = GetSchemaDataTable(Nothing, Nothing) End Sub Public Function GetSchemaDataTable(ByVal collectionName As String, _ ByVal restrictions As String()) As DataTable Dim cnSettings As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings(comboBox1.Text) Dim provider As DbProviderFactory = _ DbProviderFactories.GetFactory(cnSettings.ProviderName) Using cn As DbConnection = provider.CreateConnection() cn.ConnectionString = cnSettings.ConnectionString cn.Open() If (collectionName Is Nothing) Then Return cn.GetSchema() End If Return cn.GetSchema(collectionName, restrictions) End Using End Function
This code simply uses the name of the current connection string to create and open a connection to the database. The GetSchema method runs next, and the resultant DataTable object is assigned directly to the DataSource property of the DataGridView object. The GetSchema method cannot be called with a null collectionName parameter, so the collectionName is checked; if it is null, the parameterless overload is executed. Figure 11-1 shows the form when the code is run and the SqlClient and Access versions are selected.
Figure 11-1: The SQL Server metadata collections using the SqlClient versus the Microsoft Access metadata collection
Figure 11-2 shows the form when the code is run and the OLEDB and ODBC versions are selected. Notice that the SQL Server database has the most metadata available and the Access database has the least. Every provider has different metadata collections, but you can start here and drill down into each collection as needed.
Figure 11-2: The SQL Server metadata collections using the OleDb provider versus the Odbc provider.
Navigating the Schema
If every provider has a different schema, you might have a problem navigating the various metadata collections. In the ADO.NET schema API, regardless of the provider you select to query, the following common collections must be implemented by each provider.
MetaDataCollections Contains a list of all the available collections and is the default collection when you execute GetSchema with no parameters, as shown earlier in Figure 11-1. This also returns the number of restrictions (filters) that can be applied to each collection.
DataSourceInformation Contains a single row that provides the name, version, and other details about the database.
DataTypes Contains a list of all the supported data types. Also provides metadata about each data type, such as the size and whether it is incrementing, case sensitive, and nullable.
ReservedWords Contains a list of all the words reserved for use by the database and code that runs within it, including code in stored procedures and SQL statements.
Restrictions Contains a list of the restrictions you can apply to filter the results when querying any of the metadata collections.
If you look at the four connection samples, you will see that they contain these five metadata collections. Other than these five collections, no other collections are required. This means provider writers can implement other collections as they see fit, but there is no standard even for common collections such as the table collection.
Navigating a Metadata Collection
We added a CellDoubleClick event handler to the DataGridView object that reads the collection name and navigates into the collection if the row that was clicked has a CollectionName column. The following code shows the use of GetSchema when passing a collection name.
Visual Basic
Private Sub dataGridView1_CellDoubleClick(_ ByVal sender As System.Object, _ ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _ Handles dataGridView1.CellDoubleClick If dataGridView1.Columns("CollectionName") Is Nothing Then MessageBox.Show("No CollectionName column") Return End If Dim collectionName As String = CStr(dataGridView1.Rows(_ e.RowIndex).Cells("CollectionName").Value) dataGridView1.DataSource = GetSchemaDataTable(collectionName, Nothing) End Sub
This code simply gets the collection that matches the collection name in the row that was double-clicked. Figure 11-3 shows the table collection when you use the NorthwindSqlClient connection string. Notice that this collection contains the user-defined tables and views, not the system tables and views. Also notice that the SqlClient provider returns only four columns: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and TABLE_TYPE. Notice that TABLE_CATALOG, which is the database name, contains the complete path and database filename. This is the default naming when you use SQL Server Express and set UserInstance=True in the connection string.
Figure 11-3: When you use the SqlClient provider, the table collection contains a list of user-defined tables and user-defined views.
If you switch to the NorthwindOleDb connection string and list the tables, you see that this collection returns the list of system tables, system views, user-defined tables, and user-defined views. Figure 11-4 shows this collection. Some of the columns can't be seen in the figure, but nine columns are returned: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_GUID, DESCRIPTION, TABLE_PROPID, DATE_CREATED, and DATE_MODIFIED. This connection string references a mounted copy of the Northwind database, so TABLE_CATALOG simply says Northwind.
Figure 11-4: When you use the OLEDB provider, the table collection contains a list of system tables, system views, user-defined tables, and user-defined views.
Working with the Restrictions
Retrieving the metadata is easy, as long as you know how to open a connection and you know the name of the metadata collection name to pass to the GetSchema method, but you must be wondering about the restriction parameter that has not been set when you call the GetSchema method. The restriction parameter is an array of string values that are used to filter the collection rows so you get only the information you want.
When you get the list of metadata collection names, you also get the number of restrictions you can specify. If the number is zero, you cannot apply a filter to this collection. If the number is greater than zero, you can query the restriction collection for the available filters for this collection. You must provide a restriction array to the GetSchema method that has the same number of elements as filters. The filters are position sensitive, so unused filter values should be set to null (Visual Basic Nothing).
In the sample application, when you double-click a cell, instead of immediately displaying the collection, the code is modified to display a filter prompt that can be used to narrow the displayed results. We want to build this form dynamically, displaying a combo box for each restriction that will be populated with valid filter values. We'll add a new method called Get-Restrictions that dynamically populates a form and prompts the user for filter restrictions. We'll also add a new method called GetComboValues that gets all of the existing values in a column for display in a combo box. The code looks like the following.
Visual Basic
Dim restrictionDataTable As DataTable Dim unselected As String = "<unselected>" Public Function GetRestrictions(ByVal collectionName As String) _ As String() 'Display form with filter selections Dim frm As New Form() Dim filters As New ArrayList() Dim completeCollection As DataTable = _ GetSchemaDataTable(collectionName, Nothing) restrictionDataTable.DefaultView.RowFilter = _ "CollectionName='" + collectionName + "'" restrictionDataTable.DefaultView.Sort = "RestrictionNumber ASC" Dim labelX As Integer = 10 Dim comboX As Integer = 150 Dim labelWidth As Integer = comboX - labelX Dim comboWidth As Integer = 150 Dim formWidth As Integer = comboWidth + comboX + 10 Dim y As Integer = 20 frm.Text = "Select Filter Values" frm.Width = formWidth Dim containsFilters As Boolean = False For Each dv As DataRowView In restrictionDataTable.DefaultView Dim lbl As New Label() lbl.Location = New Point(labelX, y) lbl.Width = labelWidth lbl.Text = CStr(dv("RestrictionName")) Dim cmb As New ComboBox() cmb.DropDownStyle = ComboBoxStyle.DropDownList cmb.Location = New Point(comboX, y) cmb.Width = comboWidth cmb.Anchor = AnchorStyles.Left Or AnchorStyles.Right _ Or AnchorStyles.Top cmb.Items.Add(unselected) cmb.Items.AddRange(GetComboValues(_ dv, completeCollection)) frm.Controls.Add(lbl) frm.Controls.Add(cmb) filters.Add(cmb) cmb.Text = unselected cmb.Visible = True lbl.Visible = True y += 30 containsFilters = True Next If (containsFilters) Then Dim ok As New Button() ok.Text = "&Ok" ok.DialogResult = Windows.Forms.DialogResult.OK ok.Location = New Point(labelX, y) frm.Controls.Add(ok) ok.Visible = True frm.AcceptButton = ok Dim cancel As New Button() cancel.Text = "&Cancel" cancel.DialogResult = Windows.Forms.DialogResult.Cancel cancel.Location = New Point(comboX, y) frm.Controls.Add(cancel) cancel.Visible = True frm.CancelButton = cancel Else Return Nothing End If frm.Height = y + 60 If (frm.ShowDialog() <> Windows.Forms.DialogResult.OK) Then Return Nothing End If Dim results(filters.Count - 1) As String For i As Integer = 0 To filters.Count - 1 Dim cmb As ComboBox = CType(filters(i), ComboBox) If (cmb.Text = unselected) Or (cmb.Text Is Nothing) _ Or (cmb.Text.Length = 0) Then results(i) = Nothing Else results(i) = cmb.Text End If Next Return results End Function Public Function GetComboValues(ByVal dv As DataRowView, _ ByVal list As DataTable) As Object() Dim ret As New Hashtable() Dim columnName As String columnName = CStr(dv("RestrictionName")) For Each dr As DataRow In list.Rows Dim currentValue As String = CStr(dr(columnName)) ret(currentValue) = currentValue Next Dim comboValues As New ArrayList(ret.Keys) comboValues.Sort() Return comboValues.ToArray() End Function
C#
DataTable restrictionDataTable; string unselected = "<unselected>"; public string[] GetRestrictions(string collectionName) { //Display form with filter selections Form frm = new Form(); ArrayList filters = new ArrayList(); DataTable completeCollection = GetSchemaDataTable(collectionName, null); restrictionDataTable.DefaultView.RowFilter = "CollectionName='" + collectionName + "'"; restrictionDataTable.DefaultView.Sort = "RestrictionNumber ASC"; int labelX = 10; int comboX = 150; int labelWidth = comboX - labelX; int comboWidth = 150; int formWidth = comboWidth + comboX + 10; int y = 20; frm.Text = "Select Filter Values"; frm.Width = formWidth; bool containsFilters = false; foreach (DataRowView dv in restrictionDataTable.DefaultView) { Label lbl = new Label(); lbl.Location = new Point(labelX, y); lbl.Width = labelWidth; lbl.Text = (string)dv["RestrictionName"]; ComboBox cmb = new ComboBox(); cmb.DropDownStyle = ComboBoxStyle.DropDownList; cmb.Location = new Point(comboX, y); cmb.Width = comboWidth; cmb.Anchor = AnchorStyles.Left | AnchorStyles.Right | AnchorStyles.Top; cmb.Items.Add(unselected); cmb.Items.AddRange(GetComboValues( dv, completeCollection)); frm.Controls.Add(lbl); frm.Controls.Add(cmb); filters.Add(cmb); cmb.Text = unselected; cmb.Visible = true; lbl.Visible = true; y += 30; containsFilters = true; } if (containsFilters) { Button ok = new Button(); ok.Text = "&Ok"; ok.DialogResult = DialogResult.OK; ok.Location = new Point(labelX, y); frm.Controls.Add(ok); ok.Visible = true; frm.AcceptButton = ok; Button cancel = new Button(); cancel.Text = "&Cancel"; cancel.DialogResult = DialogResult.Cancel; cancel.Location = new Point(comboX, y); frm.Controls.Add(cancel); cancel.Visible = true; frm.CancelButton = cancel; } else { return null; } frm.Height = y + 60; if (frm.ShowDialog() != DialogResult.OK) { return null; } string[] results = new string[filters.Count]; for (int i = 0; i < filters.Count; i++) { ComboBox cmb = (ComboBox)filters[i]; if (cmb.Text == unselected || cmb.Text == null || cmb.Text.Length == 0) { results[i] = null; } else { results[i] = cmb.Text; } } return results; } public object[] GetComboValues(DataRowView dv, DataTable list) { Hashtable ret = new Hashtable(); string columnName; columnName = (string)dv["RestrictionName"]; foreach (DataRow dr in list.Rows) { string currentValue = (string)dr[columnName]; ret[currentValue] = currentValue; } ArrayList comboValues = new ArrayList(ret.Keys); comboValues.Sort(); return comboValues.ToArray(); }
This code starts by declaring a DataTable variable called restrictionDataTable, which is used to hold the restriction list when you get the metadata collection list for a connection. A variable called unselected holds the value to be displayed when you don't want to make a selection on a filter.
The GetRestrictions method accepts a collection name parameter and returns an array of strings that contains the filters the user selected. In this method, a form is dynamically created and populated with a combo box for each restriction filter. A call is made to get the complete collection, even though we might want a filtered list. This is done in this example to get a list of valid values to use when populating the combo boxes.
Next, the restrictionDataTable object's default data view is filtered and sorted so it shows only the restrictions for the currently selected collection, sorted by restriction number. We define variables for the layout of the restriction labels and combo boxes and loop through the restriction values, creating a label and combo box for each restriction and populating the combo box's values by calling the GetComboValues method. The GetComboValues method typically expects to find the filter column name in the RestrictionName value.
After the loop has completed, the code checks to see if any filters were added to the form; if so, an OK and a Cancel button are added to the form and the form is displayed. If no filters were added to the form, this method returns with no restrictions.
After the OK button is clicked, the results are collected from each combo box and selected values are copied into a string array of restrictions, which is returned.
We need to make a couple of changes to the existing code: the button's click event handler must fill the restrictionDataTable object, and the cell's double-click event handler must be modified to prompt the user for restriction filters. We'll modify these methods as follows.
Visual Basic
Private Sub button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles button1.Click restrictionDataTable = GetSchemaDataTable("Restrictions", Nothing) dataGridView1.DataSource = GetSchemaDataTable(Nothing, Nothing) End Sub Private Sub dataGridView1_CellDoubleClick(_ ByVal sender As System.Object, _ ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _ Handles dataGridView1.CellDoubleClick If dataGridView1.Columns("CollectionName") Is Nothing Then MessageBox.Show("No CollectionName column") Return End If Dim collectionName As String = CStr(dataGridView1.Rows(_ e.RowIndex).Cells("CollectionName").Value) dataGridView1.DataSource = GetSchemaDataTable(collectionName, _ GetRestrictions(collectionName)) End Sub
Run the application and get the metadata collection list for a connection such as the NorthwindSqlClient. Double-click the Tables collection and you will be prompted for restriction filters, as shown in Figure 11-5.
Figure 11-5: The restriction filters for the Tables collection with the Employees table selected
In the figure, the Employees table is selected. Clicking OK causes the filter to be applied to the GetSchema query, and only Employees table data is displayed.
Note
Depending on the release of the .NET Framework, you might receive an Argument-Exception in the GetComboValues method when you double-click on some of the collections. The problem is that this sample code assumes that the restriction names matches the collection's field names. Microsoft did not match these names, which means that you cannot link a restriction to a collection field. For example, the Tables collection has a field that is called TABLE_CATALOG, but its restriction is called Catalog. The following section shows how you can change the names to make them match.
Changing and Extending the Metadata
The metadata that you have been using comes from embedded XML resources in the System.Data.dll file. This information can be extracted very easily by launching the IL Disassembler (ILDasm.exe) from the Visual Studio 2005 Command Prompt, and dumping its contents to an empty folder. You can try the following steps, or you can use the XML files that are included in the sample code.
Choose Start | Programs | Microsoft Visual Studio 2005 | Visual Studio Tools | Visual Studio 2005 Command Prompt. At the command prompt, type ILDasm and press Enter. Open the System.Data.dll file, which is located in the %WinDir%\Microsoft.NET\Framework\ vX.X.XXXXX folder, where X.X.XXXXX is the latest version of the .NET Framework that is installed on your machine.
Dump the contents of this assembly to a new folder by choosing File | Dump | OK. When the SaveAs dialog is displayed, click the New Folder icon to create a new folder, give the folder a name, double-click on the folder to navigate into it, and finally save the contents of the assembly as System.Data.IL. Close ILDasm.exe and use Windows Explorer to navigate to this new folder. Notice that the following XML files exist, which contain the metadata information that you have been using.
System.Data.Odbc.OdbcMetaData.xml
System.Data.OleDb.OleDbMetaData.xml
System.Data.SqlClient.SqlMetaData.xml
If you open these files, you'll find that these files contain a serialized DataSet object with its schema embedded. Scrolling through the file you'll find that each metadata collection is defined in the XML file using the following format:
<MetaDataCollections> <CollectionName>Databases</CollectionName> <NumberOfRestrictions>1</NumberOfRestrictions> <NumberOfIdentifierParts>1</NumberOfIdentifierParts> <PopulationMechanism>SQLCommand</PopulationMechanism> <PopulationString>select name as database_name, dbid, crdate as create_date from master..sysdatabases where (name = @Name or (@Name is null))</PopulationString></MetaDataCollections>
You can see that the CollectionName, NumberOfRestrictions, and NumberOfIdentifierParts properties are directly defined in this file. To populate the metadata collection, the PopulationMechanism specifies how this will be done, and the PopulationString is the actual command to populate the collection.
If the PopulationMechanism is set to DataTable then the PopulationString must be the name of one of the DataTable objects that are in this DataSet object. For example, the collection called MetaDataCollections has its PopulationMechanism set to DataTable and the PopulationString is set to MetaDataCollections to return the MetaDataCollections DataTable object in this DataSet object.
If the PopulationMechanism is set to SQLCommand, the PopulationString must be a SQL query that will return a DataTable object as shown in the XML snippet for the Databases collection. Notice that the SELECT statement aliases (renames) the name column as database_name, which causes an exception to be thrown when you double-click the Databases collection because the restriction for the Databases collection is called name, not database_name.
If the PopulationMechanism is set to PrepareCollection, the PopulationString is not used; instead, the data for the collection is embedded in the XML document.
You edit these files to make the names match and fix other problems that may exist, and you can extend the metadata by adding your own collection. The following XML snippet could be inserted into the System.Data.SqlClient.SqlMetaData.xml file to add a new SystemMessages collection, which contains information about the SQL Server's error messages.
Note
The sample code contains the XML files. The System.Data.SqlClient.SqlMetaData.xml file has been modified to match the collection field names to the restriction names, and the SystemMessages collection is included. You should use these files instead of keying this snippet into the file. Remember that only the SqlClient metadata file has been fixed, so if you use the ODBC or OLEDB files, you may still get errors.
XML - SystemMessages Collection
<!--new SystemMessages collection --><MetaDataCollections> <CollectionName>SystemMessages</CollectionName> <NumberOfRestrictions>5</NumberOfRestrictions> <NumberOfIdentifierParts>2</NumberOfIdentifierParts> <PopulationMechanism>SQLCommand</PopulationMechanism> <PopulationString> SELECT * FROM sys.messages WHERE ((message_id=@message_id) OR (@message_id is null)) AND ((language_id = @language_id) OR (@language_id is null)) AND ((severity = @severity) OR (@severity is null)) AND ((is_event_logged = @is_event_logged) OR (@is_event_logged is null)) AND (([text] = @text) OR (@text is null)) </PopulationString></MetaDataCollections><Restrictions> <CollectionName>SystemMessages</CollectionName> <RestrictionName>message_id</RestrictionName> <ParameterName>@message_id</ParameterName> <RestrictionDefault>message_id</RestrictionDefault> <RestrictionNumber>1</RestrictionNumber></Restrictions><Restrictions> <CollectionName>SystemMessages</CollectionName> <RestrictionName>language_id</RestrictionName> <ParameterName>@language_id</ParameterName> <RestrictionDefault>language_id</RestrictionDefault> <RestrictionNumber>2</RestrictionNumber></Restrictions><Restrictions> <CollectionName>SystemMessages</CollectionName> <RestrictionName>severity</RestrictionName> <ParameterName>@severity</ParameterName> <RestrictionDefault>severity</RestrictionDefault> <RestrictionNumber>3</RestrictionNumber></Restrictions><Restrictions> <CollectionName>SystemMessages</CollectionName> <RestrictionName>is_event_logged</RestrictionName> <ParameterName>@is_event_logged</ParameterName> <RestrictionDefault>is_event_logged</RestrictionDefault> <RestrictionNumber>4</RestrictionNumber></Restrictions><Restrictions> <CollectionName>SystemMessages</CollectionName> <RestrictionName>text</RestrictionName> <ParameterName>@text</ParameterName> <RestrictionDefault>text</RestrictionDefault> <RestrictionNumber>5</RestrictionNumber></Restrictions>
You will need to get your application to read the data in this file instead of the XML resource file that is embedded in the System.Data.dll assembly. To do this, these XML files must be located in the %WinDir%\Microsoft.NET\Framework\vX.X.XXXXX\CONFIG folder, where X.X.XXXXX is the latest version of the .NET Framework that is installed on your machine.
After the XML files are saved to the proper location, edit your app.config and add commands to tell the runtime to load your XML files as follows.
If you run the sample code, you will find that the new XML files are used instead of the internal resource files, and when using the SqlClient, all of the metadata functions will work properly. Remember that you can also modify the other XML files as well.
Understanding the Unique Identifier Parts
The metadata collection list also contains a column called NumberOfIdentifierParts, which is the number of identifier parts that are required to make up a unique restriction on each metadata collection. This number will be either the same as the NumberOfRestrictions count or lower. If you look at the number of restrictions for the Tables collection, you'll see that it's 4, but the number of identifier parts is 3. This is because the last restriction filter is just a filter on table type BASE TABLE or VIEW but you can't have a table and a view that have the same name in a single database. This means you only need to use the first three restriction filters to uniquely identify or retrieve a table or view.
You can still filter based on any combination of the restrictions, including those that are not part of the set of unique identifiers. In Chapter 6, we created a stored procedure to get a list of the base tables, but we could have also retrieved the list of base tables by supplying a filter restriction based on the table type being BASE TABLE.
