Work with an XML file as if it was a database table...

There is a cool XML database client (Open-Source ADO.NET XML Provider -- WilsonXmlDbClient v1.0)written by Paul Wilson that allows you to work directly with a well formed XML file as if it was a database.

By: Andrey Lifshiz Date: March 20, 2005 Download the code.

There is a cool XML database client (Open-Source ADO.NET XML Provider -- WilsonXmlDbClient v1.0)written by Paul Wilson that allows you to work directly with a well formed XML file as if it was a database. Here we will use this XML database client to code some simple examples using 'SELECT', 'INSERT', 'UPDATE', and 'DELETE' SQL commands directly against an XML file. To present and edit data we are going to use a DataList server control. I liked the article - Use the DataList Control to Present and Edit Data..., and I wanted to use it in this example. Also, we are going to use a couple of nifty objects like ViewState and SessionState. ViewState is used to track the value between post backs and SessionState is used to store data specific to a single client within a Web application on the server.

We start by dropping a DataList control on our page from the Visual Studio UI ToolBox. Then we create an ItemTemplate and an EditItemTemplate We can do this in two different ways: One way is to write it ourselves using the Visual Studio Html view and the other is to use the Visual Studio designer to help us out. To do this, we right-click on the DataList, chose the 'Edit Template' option and then choose the appropriate desired option.

We are working primarily with two files; the Default.aspx page is used to hold our html and the Default.aspx.cs used as a code behind file. In this example we will be concentrating on the code-behind file.

In the Page_Load() event handler we check to see if our XML file exist. If so, we show the ItemTemplate, otherwise, we show the EditItemTemplate. This is only done the first time the page is requested.

private void Page_Load(object sender, System.EventArgs e)
{
  if (!this.IsPostBack)
  {
    if (System.IO.File.Exists(Connection))
    {
      loadDataList();
    }
    else
    {
      UserAction = "insert";
      dtlcustomers.EditItemIndex = 0;
      loadEmptyDataList(Connection);
    }
  }
}

First we will take a look at the loadEmptyDataList(string connection) method. This is called when we want to add an item. In this method we create a DataSet object, create a DataTable object, define and add columns to the DataTable, and finally, add the table to a DataSet. Note that the CustomerID field will be unique and act as the identity field. In order to follow constraint rules when attempting any update operation we set the EnforceConstraints property to true for the DataSet. Next, we do a quick validation of the input parameter to ensure it is not an empty string and then we call the WriteXmlSchema(connection) method to create our xml file.

private void loadEmptyDataList(string connection)
{
  DataSet ds = new DataSet();
  DataTable dt = new DataTable("Customers");
  DataRow dr = null;
  dt.Columns.Add("CustomerID", System.Type.GetType("System.Int32"));
  dt.Columns["CustomerID"].AutoIncrement = true;
  dt.Columns["CustomerID"].Unique = true;
  dt.Columns.Add("CompanyName", System.Type.GetType("System.String"));
  dt.Columns.Add("ContactName", System.Type.GetType("System.String"));
  dt.Columns.Add("ContactTitle", System.Type.GetType("System.String"));
  dt.Columns.Add("Address", System.Type.GetType("System.String"));
  dt.Columns.Add("City", System.Type.GetType("System.String"));
  dt.Columns.Add("PostalCode", System.Type.GetType("System.String"));
  dt.Columns.Add("Country", System.Type.GetType("System.String"));
  dt.Columns.Add("Phone", System.Type.GetType("System.String"));
  dt.Columns.Add("Fax", System.Type.GetType("System.String"));
  for (int i = 1; i < 10; i++)
  {
    if (i== 1)
    {
      dr = dt.NewRow();
    }
    dr[i] = "";
  }
  dt.Rows.Add(dr);
  ds.Tables.Add(dt);
  ds.EnforceConstraints = true;
  if (connection != "")
  {
    ds.WriteXmlSchema(connection);
  }
  dtlcustomers.DataSource = ds.Tables[0].DefaultView;
  dtlcustomers.DataBind();
}

The loadDataList() method is self explanatory. We are using an SQL 'SELECT' statement to get all of the rows from the customers table in the same fashion as a call to a database.

private void loadDataList()
{
  string SQL = "SELECT * FROM Customers";
  try
  {
    XmlDbConnection connect= new XmlDbConnection(Connection);
    IDbDataAdapter adapter = new XmlDbDataAdapter(new XmlDbCommand(SQL, connect));
    DataSet ds = new DataSet();
    adapter.Fill(ds);
    dtlcustomers.DataSource = ds.Tables[0].DefaultView;
    dtlcustomers.DataBind();
  }
  catch{}
}

Now let's take a look at the ExecuteCommand() method, that also mimics working with a database. You use this method to update, insert and delete records in your well formed XML file by passing in an SQL statement.

private bool ExecuteCommand(string SQL)
{
  XmlDbConnection connect = null;
  bool result = false;
  try
  {
    connect = new XmlDbConnection(Connection);
    IDbCommand command = connect.CreateCommand();
    command.CommandText = SQL;
    connect.Open();
    result = (command.ExecuteNonQuery() > 0);
  }
  finally
  {
    if (connect != null) {connect.Close();}
  }
  return result;
}

There are four events that are associated with the SQL commands. The DataList fires the appropriate Edit, Update, Delete and Cancel events when the associated buttons are clicked and we must to handle each of these events. Since we would like to use the EditItemTemplate for inserting and updating records, we use the EDIT command’s name property of the 'INSERT' and 'EDIT' command buttons to fire the event. Also, we use an optional CommandArgument parameter for 'INSERT' command button to compliment the 'EDIT' command. Inside the Edit event handler we do three things:

  1. We check the number of allowed inserts the user is permitted to do.
  2. We set command action to the UserAction property and save it to the ViewState.
  3. We set the index number of the selected item in the DataList control.
public void dtlcustomers_Edit(object source, DataListCommandEventArgs e)
{
  if (e.CommandArgument.ToString() == "insert")
  {
    int insertCount = Int32.Parse(this.Session["insertcount"].ToString());
    int maxCount =
      Int32.Parse(System.Configuration.ConfigurationSettings.AppSettings["MaxInserts"].ToString());
    if (insertCount == maxCount)
    {
      lblUserAction.Text = "You have max out number of inserts allowed! " +
      "Delete some items tocontinue.";
      dtlcustomers.EditItemIndex = -1;
      loadDataList();
      return;
    }
    UserAction = "insert";
    dtlcustomers.EditItemIndex = 0;
    loadEmptyDataList();
  }
  else
  {
    UserAction = "update";
    dtlcustomers.EditItemIndex = e.Item.ItemIndex;
    loadDataList();
  }
}

The Update event fires when the update command button is clicked but remember the same event handler is also used to handle inserts. This is where the UserAction property comes handy. It reads the ViewState and builds the appropriate SQL statement. In the case of an 'INSERT', we increment the insert counter.

public void dtlcustomers_Update(object source,
  System.Web.UI.WebControls.DataListCommandEventArgs e)
{
  string SQL = "";
  if (UserAction == "insert")
  {
    SQL = "INSERT INTO Customers " +
      "(CompanyName, ContactName, ContactTitle, Address, " +
      "City, PostalCode, Country, Phone, Fax) VALUES (" +
      "'" + ((TextBox)e.Item.FindControl("txtCompanyName")).Text +
      "', '" + ((TextBox)e.Item.FindControl("txtContactName")).Text +
      "', '" + ((TextBox)e.Item.FindControl("txtContactTitle")).Text +
      "', '" + ((TextBox)e.Item.FindControl("txtAddress")).Text +
      "', '" + ((TextBox)e.Item.FindControl("txtCity")).Text +
      "', '" + ((TextBox)e.Item.FindControl("txtPostalCode")).Text +
      "', '" + ((TextBox)e.Item.FindControl("txtCountry")).Text +
      "', '" + ((TextBox)e.Item.FindControl("txtPhone")).Text +
      "', '" + ((TextBox)e.Item.FindControl("txtFax")).Text + "')";
  }
  else
  {
    SQL = "Update Customers " +
      "Set CompanyName = '" + ((TextBox)e.Item.FindControl("txtCompanyName")).Text +
      "', ContactName = '" + ((TextBox)e.Item.FindControl("txtContactName")).Text +
      "', ContactTitle = '" + ((TextBox)e.Item.FindControl("txtContactTitle")).Text +
      "', Address = '" + ((TextBox)e.Item.FindControl("txtAddress")).Text +
      "', City = '" + ((TextBox)e.Item.FindControl("txtCity")).Text +
      "', PostalCode = '" + ((TextBox)e.Item.FindControl("txtPostalCode")).Text +
      "', Country = '" + ((TextBox)e.Item.FindControl("txtCountry")).Text +
      "', Phone = '" + ((TextBox)e.Item.FindControl("txtPhone")).Text +
      "', Fax = '" + ((TextBox)e.Item.FindControl("txtFax")).Text +
      "' WHERE CustomerID = " + ((Label)e.Item.FindControl("lblCustomerID")).Text;
  }
  bool success = ExecuteCommand(SQL);
  if (UserAction == "insert" && success)
  {
    int insertCount = Int32.Parse(this.Session["insertcount"].ToString());
    insertCount ++;
    this.Session["insertcount"] = insertCount;
  }
  dtlcustomers.EditItemIndex = -1;
  loadDataList();
}

The Delete event fires when the delete command button is clicked. Inside the delete event handler we do the following:

  1. We set the index number of the deleted item in the DataList control.
  2. We build the SQL statement.
  3. We call the ExecuteCommand() method.
  4. We set the index number of the DataList control to -1 and load our data.
  5. We decrement the Insert counter.
private void dtlcustomers_Delete(object source, System.Web.UI.WebControls.DataListCommandEventArgs e)
{
  dtlcustomers.EditItemIndex = e.Item.ItemIndex;
  int insertCount = Int32.Parse(this.Session["insertcount"].ToString());
  string SQL = "DELETE FROM Customers WHERE CustomerID = '" +
  ((Label)e.Item.FindControl("lblID")).Text + "'";
  ExecuteCommand(SQL);
  dtlcustomers.EditItemIndex = -1;
  loadDataList();
  insertCount = insertCount > 0 ? insertCount - 1 : 0;
  this.Session["insertcount"] = insertCount;
}

The Cancel event fires when the cancel command button is clicked. Inside this event handler we just setting the index number of the DataList control to -1 and loading our data.

public void dtlcustomers_Cancel(object source, DataListCommandEventArgs e)
{
  dtlcustomers.EditItemIndex = -1;
  loadDataList();
}

The last thing I want to mention is the use of session state values to store the insert counter specific to a single client. The maxCount is a setting value maintained in the web.config file along with the connection string.

<appSettings>
  <add key="DataListXML.DBXMLConnectionString" value="File Path Here" />
  <add key="MaxInserts" value="3" />
</appSettings>

Conclusion:

In this article we see how easy you can work with well formed XML files as if they were a database. This permits you to use standard SQL statements instead of XPath. It is very straight-forward to use a DataList to present and edit data. Last but not least, we used some neat features that asp.net offers including a session state variable to store data specific to a single client and a view state variable to track the values between posts back.

You may download the code here.