To create the tree view, the table's information schema view
is queried and the results are parsed. The table name is created in the format
of schema.tablename. This node is created as a single string and to create a
shorter version of the table name later requires splitting the name at the
decimal point.
Columns are created in the format of name, followed by a
comma, and then the data type/length combination (such as "EventID,varchar(10)").
To get the column name requires splitting by the comma between the name/data
type. That is useful for later.
Listing 9
foreach (DataRow tableRow in _information.Tables["Tables"].Rows)
{
string tableSchema = tableRow["TABLE_SCHEMA"].ToString();
string tableName = tableRow["TABLE_NAME"].ToString();
TreeViewItem tableItem = new TreeViewItem();
tableItem.Header = string.Format("{0}.{1}", tableSchema, tableName);
this.TablesListing.Items.Add(tableItem);
DataView columnsView = _information.Tables["Columns"].DefaultView;
columnsView.RowFilter = string.Format(
"TABLE_NAME = '{1}' and TABLE_SCHEMA = '{0}'", tableSchema, tableName);
for (int i = 0; i < columnsView.Count; i++)
{
DataRow columnRow = columnsView[i].Row;
string columnName = columnRow["COLUMN_NAME"].ToString();
string dataType = columnRow["DATA_TYPE"].ToString();
int precision = (int)(columnRow.IsNull("CHARACTER_MAXIMUM_LENGTH")
? 0
: columnRow["CHARACTER_MAXIMUM_LENGTH"]);
precision = !columnRow.IsNull("NUMERIC_PRECISION")
? (int)columnRow["NUMERIC_PRECISION"]
: precision;
string text = columnName + ", " + dataType;
if (precision > 0)
text += "(" + precision.ToString() + ")";
TreeViewItem columnItem = new TreeViewItem();
columnItem.Header = text;
tableItem.Items.Add(columnItem);
}
}All of the information gets pulled from the information
schema views and creates the appropriate tree view items. To get the
information back, it is collected from these tree view items and parsed
appropriately.
