Click here to Skip to main content
Click here to Skip to main content

An XML Database Schema And Data Editor

, 29 Sep 2002
Rate this:
Please Sign up or sign in to vote.
View and edit your XML database schema and data

Sample Image - xmlSchemaEditor.jpg

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).

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Marc Clifton

United States United States
Marc is the creator of two open source projets, MyXaml, a declarative (XML) instantiation engine and the Advanced Unit Testing framework, and Interacx, a commercial n-tier RAD application suite.  Visit his website, www.marcclifton.com, where you will find many of his articles and his blog.
 
Marc lives in Philmont, NY.

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberoohj71032915-Dec-11 6:13 
GeneralThank You... PinmemberCiuchinobello14-Apr-10 1:16 
Generalfrom DataTable to Query Designer PinmemberAtzsea Hiducca13-Nov-09 9:49 
GeneralThanks Pinmemberwas3334-Nov-08 23:17 
QuestionNeed help to read My Advanced XML PinmemberPrithwish Biswas11-Apr-08 0:41 
GeneralFit the Bill PinmemberOnskee116-Dec-07 11:21 
GeneralYet another great article! PinmemberPreky7-Oct-07 22:11 
GeneralHierarchies PinmemberHypnotron13-Sep-07 6:37 
GeneralVB version Pinmembershalan9927-Jun-07 2:31 
GeneralLife Saver PinmemberPriyank Bolia17-Jun-07 4:24 
QuestionHow do i change the column width PinmemberSudhanshu Gupta10-Jun-06 1:43 
AnswerRe: How do i change the column width PinmemberJeremy L-T15-Jun-06 19:15 
GeneralCreating database from xml Pinmemberw3Nima13-May-06 2:12 
GeneralXML Dokument Pinmembersurfman1919-Aug-05 12:39 
GeneralRe: XML Dokument Pinmembertwostepted30-Oct-05 19:03 
GeneralFrom XML to Relation Database Pinmemberdoxuanhuyen12-Mar-03 2:37 
GeneralRe: From XML to Relation Database PinmemberMarc Clifton12-Mar-03 8:01 
GeneralProblem with PART Table Pinmemberfmarchi20-Nov-02 10:39 
GeneralMalformed XML PinmemberJon Taylor2-Oct-02 22:08 
GeneralNice DataSet tutorial Pinmemberrhoward1-Oct-02 13:05 
GeneralRe: Nice DataSet tutorial PinmemberMarc Clifton1-Oct-02 13:10 
GeneralRe: Nice DataSet tutorial PinmemberAlexpro2-Oct-02 22:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 30 Sep 2002
Article Copyright 2002 by Marc Clifton
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid