John Mendez

This article will be about how to use XML to keep your Microsoft Access database commands independent from the Access database itself utilizing an XML file to store the Access commands.  Why would someone want to do this? A couple of reasons, 1) as a developer you don’t need to worry about modifying the Access file to change a command, and 2) if someone else developed the Access database and you just needed to write the front-end ASP.NET application, your code and database commands will be totally independent from the Access database.  Below I will show you a simple class and XML file used to communicate to the Microsoft Access Database.

Below is the class that will handle the database commands and retrieving the correct command from the XML file.

public class AccessCommands
{
public static DataSet getAllResources()
    {
        OleDbConnection dbConn = new OleDbConnection();
        OleDbCommand dbCmd = new OleDbCommand();
        DataSet ds = new DataSet();
        OleDbDataAdapter da;

        try
        {
            dbConn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["accessConn"].ToString();
            dbConn.Open();

            string cmd = getDBCommand("qryGetAllResources");

            dbCmd.Connection = dbConn;
            dbCmd.CommandType = CommandType.Text;
            dbCmd.CommandText = cmd;

            da = new OleDbDataAdapter(dbCmd);
            da.Fill(ds);

            return ds;

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            dbCmd.Dispose();
            dbConn.Close();
            dbConn.Dispose();
        }

    }

private static string getDBCommand(string commandName)
    {
        DataSet tmpDs = new DataSet();
        string cmd = string.Empty;

        tmpDs.ReadXml(HttpContext.Current.Server.MapPath("~/dbCommands.xml"));

        for (int i = 0; i < tmpDs.Tables[0].Rows.Count; i++)
        {
            if (tmpDs.Tables[0].Rows[i]["name"].ToString().ToLower() == commandName.ToLower())
            {
                cmd = tmpDs.Tables[0].Rows[i]["commandText"].ToString();
                break;
            }
        }

        return cmd;
    }
}

You will notice there are a couple of things going on here.  First, the public function is what gets called from somewhere inside the web application.  You can have as many as needed.  Second, you will notice that the public function calls a private function named getDBCommand.  This function accepts a string (the name of the query in the XML file) and loops through the XML file to find a match to pull the text for the database command from the XML file.

The XML file (dbCommands.xml) that we are reading from looks like this

<?xml version="1.0" encoding="utf-8" ?>
<DBCommands>
 <command>
  <name>qryGetAllResources</name>
  <commandText>
   <![CDATA[SELECT resource_id
         ,upc_code
         ,title
         ,author
         ,length
         ,copies
         ,in_stock
         ,resource_type
         ,item_decription
      FROM tbl_resources
      ORDER BY title]]>
  </commandText>
 </command>
</DBCommands>

Now with this concept you don’t have to worry about touching or dealing with Microsoft Access databases if you need to change database commands.   You only need to edit or add to your XML file and class file.  Of course, you can still use parameters where needed in your commands.  Any questions or comments feel free to leave them.

ASP.NET xml, microsoft access