An XML Database Schema And Data Editor

• Download source files - 15.9 Kb

Introduction

After publishing my recent article on dynamic menus in C#, several people commented that I should implement the menu structure in XML instead of Access. Well...fine, I thought, and began playing around with XML schemas in the VS.NET IDE. I was quickly disappointed with the ability to create schemas and edit their contents. The IDE was not behaving as the documentations said it should, and the behavior appeared inconsistent and buggy. Several of you commented that you were not getting this behavior, but I was. I also wanted an XML schema/data editor that was independent of the IDE (regardless of the 20MB dotnetfx.exe file that needs to be installed!).

After browsing CP and google, I didn�t see anything that fell into the category of a basic schema and data editor, so the following is the result. Even if I�ve duplicated effort, I�ve learned some things on the way, and having no prior XML experience, this is probably where the greatest value lies.

There really isn't anything that's rocket science about this, but I'll show some code anyways.

Loading An XML File

private void mnuOpen_Click(object sender, System.EventArgs e)
{
    DialogResult res=openFileDialog.ShowDialog(this);
    if (res==DialogResult.OK)
    {
        string fn=openFileDialog.FileName;
        DataSet ds=new DataSet();
        ds.ReadXml(fn);
        lbTables.Items.Clear();
        foreach (DataTable dt in ds.Tables)
        {
            lbTables.Items.Add(dt.TableName);
        }
        dataSet=ds;
        lbTables.SelectedIndex=0;
        fnSchema=fn;
        this.Text="XML Database Editor - "+fnSchema;
    }
}

This code, after getting a valid XML filename (to the limits that I test it!), loads the file into a DataSet, which is a built in function of the DataSet. The table ListBox lbTables is then populated with the table name, by iterating through the data set's tables. The first table is selected as default, and the application caption is updated.

Saving A DataSet As An XML File

dataSet.WriteXml(fn, XmlWriteMode.WriteSchema);

This statement writes all the data for all the tables, and with the XmlWriteMode.WriteSchema option, also writes the schema for the DataSet. Thus, the XML file has the complete description of the database and all of its data.

Adding A Table

Tables are added to the DataSet as described below:

private void btnAddTable_Click(object sender, System.EventArgs e)
{
    string tblName=edTableName.Text;
    if (!ValidateTableName(tblName))
    {
        return;
    }
    
    DataTable dt=new DataTable(tblName);
    currentTable=dt;
    lbTables.Items.Add(tblName);
    dataSet.Tables.Add(dt);
    lbTables.SelectedItem=lbTables.Items[lbTables.FindStringExact(tblName)];
}

This code adds a table to the ListBox and to the DataSet, then selects the newly added table in the ListBox.

Adding A Column

Columns are added to the currently selected table:

private void btnAddColumn_Click(object sender, System.EventArgs e)
{
    string colName=edColumnName.Text;
    string colType=cbColumnType.Text;
    if ( (!ValidateColumnNameAndType(colName, colType)) || 
                                (!ValidateSelectedTable()) )
    {
        return;
    }
    ListViewItem lvi=lvColumns.Items.Add(colName);
    lvi.SubItems.Add(colType);
    currentTable.Columns.Add(colName, Type.GetType("System."+colType));
}

In the above code, the column is added to the column ListView and to the selected table. Observe the code:

currentTable.Columns.Add(colName, Type.GetType("System."+colType));

When adding a column, the column name and the type is required. The type is determined by using a great VS.NET function GetType, which converts a textual representation of the string to its actual type. In the code, the string "System." is prefixed onto the column type string so that the conversion routine can find the correct type. The "System." is removed when displaying column types in the ListView with the string Split function, as described below.

Displaying A Table's Columns

void ShowColumns()
{
    lvColumns.Items.Clear();
    if (currentTable != null)
    {
        foreach (DataColumn dc in currentTable.Columns)
        {
            ListViewItem lvi=lvColumns.Items.Add(dc.ColumnName);
            string s=dc.DataType.ToString();
            s=s.Split(new Char[] {'.'})[1];
            lvi.SubItems.Add(s);
        }
    }
}

This code iterates through the columns of the current table, extracting the column name and the column data type, then adding this information to the ListView.

The Data Grid

Probably the most interesting process is setting up the DataGrid. The DataGrid displays data for the selected table:

private void lbTables_SelectedIndexChanged(object sender, 
                                        System.EventArgs e)
{
    string tblName=lbTables.Text;
    currentTable=dataSet.Tables[tblName];
    ShowColumns();
    dgData.SetDataBinding(dataSet, tblName);
    dgData.CaptionText="Table: "+tblName;
}

The SetDataBinding function is used to establish the connection between the DataGrid and the specific table in the DataSet. Quite nicely, whenever you change the name of the table or column, or add/remove columns, the DataSet automatically updates. There is no code required to update the DataGrid. Amazing!

Possible VS.NET bug?

One thing I noticed was that, when deleting a table from the DataSet, the table still "lingers" in the DataGrid. If you add a table with the same name, back into the system, you will notice that it already has columns defined in the DataGrid. Very strange behavior!

Conclusion

This was a very simple utility to put together and demonstrate some nice things about VS.NET, and some quirks. For example, you can't change the data type of a column after it has data. The program could be extended to do this manually, I suppose. But for now, I don't allow it either (which is too bad, because I occasionally need to change the data type with existing data).