Click here to Skip to main content
12,292,953 members (67,230 online)
Click here to Skip to main content
Add your own
alternative version

Stats

178.9K views
4.3K downloads
139 bookmarked
Posted

An XML Database Schema And Data Editor

, 29 Sep 2002 CPOL
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)

Share

About the Author

Marc Clifton
United States United States
Marc is the creator of two open source projects, 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.

You may also be interested in...

Comments and Discussions

 
QuestionCSV reader for loading data Pin
Member 47220151-Mar-15 20:30
memberMember 47220151-Mar-15 20:30 
GeneralThanks, this helps A LOT Pin
ivek81cro16-Jan-15 9:39
memberivek81cro16-Jan-15 9:39 
GeneralMy vote of 5 Pin
oohj71032915-Dec-11 6:13
memberoohj71032915-Dec-11 6:13 
GeneralThank You... Pin
Ciuchinobello14-Apr-10 1:16
memberCiuchinobello14-Apr-10 1:16 
Generalfrom DataTable to Query Designer Pin
Atzsea Hiducca13-Nov-09 9:49
memberAtzsea Hiducca13-Nov-09 9:49 
GeneralThanks Pin
was3334-Nov-08 23:17
memberwas3334-Nov-08 23:17 
QuestionNeed help to read My Advanced XML Pin
Prithwish Biswas11-Apr-08 0:41
memberPrithwish Biswas11-Apr-08 0:41 
GeneralFit the Bill Pin
Onskee116-Dec-07 11:21
memberOnskee116-Dec-07 11:21 
GeneralYet another great article! Pin
Preky7-Oct-07 22:11
memberPreky7-Oct-07 22:11 
GeneralHierarchies Pin
Hypnotron13-Sep-07 6:37
memberHypnotron13-Sep-07 6:37 
GeneralVB version Pin
shalan9927-Jun-07 2:31
membershalan9927-Jun-07 2:31 
GeneralLife Saver Pin
Priyank Bolia17-Jun-07 4:24
memberPriyank Bolia17-Jun-07 4:24 
QuestionHow do i change the column width Pin
Sudhanshu Gupta10-Jun-06 1:43
memberSudhanshu Gupta10-Jun-06 1:43 
AnswerRe: How do i change the column width Pin
Jeremy L-T15-Jun-06 19:15
memberJeremy L-T15-Jun-06 19:15 
GeneralCreating database from xml Pin
w3Nima13-May-06 2:12
memberw3Nima13-May-06 2:12 
GeneralXML Dokument Pin
surfman1919-Aug-05 12:39
membersurfman1919-Aug-05 12:39 
GeneralRe: XML Dokument Pin
twostepted30-Oct-05 19:03
membertwostepted30-Oct-05 19:03 
GeneralFrom XML to Relation Database Pin
doxuanhuyen12-Mar-03 2:37
memberdoxuanhuyen12-Mar-03 2:37 
GeneralRe: From XML to Relation Database Pin
Marc Clifton12-Mar-03 8:01
memberMarc Clifton12-Mar-03 8:01 
GeneralProblem with PART Table Pin
fmarchi20-Nov-02 10:39
memberfmarchi20-Nov-02 10:39 
GeneralMalformed XML Pin
Jon Taylor2-Oct-02 22:08
memberJon Taylor2-Oct-02 22:08 
GeneralNice DataSet tutorial Pin
rhoward1-Oct-02 13:05
memberrhoward1-Oct-02 13:05 
GeneralRe: Nice DataSet tutorial Pin
Marc Clifton1-Oct-02 13:10
memberMarc Clifton1-Oct-02 13:10 
GeneralRe: Nice DataSet tutorial Pin
Alexpro2-Oct-02 22:55
memberAlexpro2-Oct-02 22:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.160518.1 | Last Updated 30 Sep 2002
Article Copyright 2002 by Marc Clifton
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid