Monday, April 26, 2004

 

A C# Utility to Test Microsoft Jet Queries

In previous posts, we investigated some of the functions that can be used in Microsoft Jet queries, with particular emphasis on those that can be used in queries executed outside the Microsoft Access environment. For testing purposes, we developed a C# console application that I call “JetTest”.

The original version of JetTest was a fairly crude beast. Both the data source (the path and name of the database) and the name of the query to be executed were hard-coded. To run a test against a different query, or a different database, we had to modify and recompile the code. In the second version, we accepted the name of the query as a command-line parameter. This was an improvement, but still a bit clumsy. If we forgot to specify the query name when running the utility, we had to exit the program and start over. So (in addition to some general tidying up of the code) one of the first changes I wanted to make was to handle this a little more gracefully. The relevant code now looks like this:

if (args.Length == 0)
{
    System.String strPrompt = "Please enter the name " +
        "of the query you wish to test, then press " +
        "Enter";
    System.Console.WriteLine (strPrompt);
    strQueryName = System.Console.ReadLine().Trim();
}
else
    strQueryName = args[0].Trim();

if (strQueryName.Length == 0)
{
    System.Console.WriteLine("No query name specified.");
    return;
}

The program first checks the array “args”, which contains any command-line arguments that were passed to the program when it was executed. If the length of the array is zero, no arguments were passed. As in the first version, the program informs the user of the problem, but this time the user doesn’t have to exit the program and start again. The program will accept a query name from the user and continue. If the length of the string variable “strQueryName” is zero at this point, the user has still failed to enter a query name. The only sensible reason I can think of for this to happen is that the user executed the program by mistake, and does not want to proceed, so the program will simply exit if this happens.

We could handle the data source the same way, but the data source is likely to be a considerably longer string of text, and we probably won’t want to change it very often. In order to avoid any risk to important data, we really should set aside a database that will be used solely for testing purposes. We can always import data from other databases into the test database if we want to run tests against specific data. Entering a long path and name every time we run JetTest would be an unnecessarily inconvenient and error-prone process. Instead, I decided to use an XML configuration file.

The Microsoft .NET framework provides classes to support reading configuration data from an XML file called app.config (for Windows Forms and console applications) or Web.config (for ASP.NET applications). The app.config file for JetTest looks like this:

<?xml version="1.0"?>
<configuration>
    <appSettings>
        <!-- 
            Change the 'value' attribute ('C:\Documents and 
            Settings' etc.) to the path and name of your 
            test MDB. 
        -->
        <add key = "DataSource" value = "C:\Documents and Settings\Brendan Reynolds\My Documents\Blog\Test2.mdb">
        </add>
    </appSettings>
</configuration>

The relevant C# code to read the data source from the configuration file looks like this:

    // Edit the path and name in the configuration file
    // (app.config) to point to your test MDB. See the
    // additional comment in the configuration file.
    System.String strDataSource=
        System.Configuration.ConfigurationSettings.
        AppSettings["DataSource"];

You still need to change the path and name in the configuration file (an XML file is just a text file that includes tags that have special meaning to an XML parser, you can edit it in any text editor, such as Notepad) but you only have to do it occasionally (when the location or name of your test database changes) and you don’t have to recompile the code.

I also tidied up the display a bit by separating the colums with tabs instead of commas. A query with a small number of not-too-wide columns now displays quite neatly. A query with a large number of columns or with very wide columns is never going to look good in a console application. For that you really need features that aren’t available in a console application, like grids and scrollbars.

Here’s the complete source code, with some additional comments.

namespace BJR.JetTest
{
    /// 
    /// JetTest is a console application intended to test the use of 
    /// functions in Microsoft Jet queries.
    /// 
    class JetTest
    {
        /// 
        /// The main entry point for the application. Accepts a 
        /// single argument, the name of the saved Microsoft Jet 
        /// query to be tested.
        /// 
        [System.STAThread]
        static void Main(string[] args)
        {
            System.String strQueryName = System.String.Empty;

            // Did the user specify a query name?
            if (args.Length == 0)
            {
                System.String strPrompt = "Please enter the name " +
                    "of the query you wish to test, then press " +
                    "Enter";
                System.Console.WriteLine (strPrompt);
                strQueryName = System.Console.ReadLine().Trim();
            }
            else
                strQueryName = args[0].Trim();
            if (strQueryName.Length == 0)
            {
                System.Console.WriteLine("No query name specified.");
                return;
            }

            // Connect to the database.
            System.Data.OleDb.OleDbConnection odcn = 
                new System.Data.OleDb.OleDbConnection();
            try
            {
                System.String strProvider = 
                    "Microsoft.Jet.OLEDB.4.0";

                // Edit the path and name in the configuration file
                // (app.config) to point to your test MDB. See the
                // additional comment in the configuration file.
                System.String strDataSource = 
                    System.Configuration.ConfigurationSettings.
                    AppSettings["DataSource"];

                // Concatenate the provider and data source to build 
                // the connection string.
                System.String strConnectionString = 
                    "Provider = " + strProvider + "; " + 
                        "Data Source = " + strDataSource;
                odcn.ConnectionString = strConnectionString;
                odcn.Open();
                System.Data.OleDb.OleDbCommand odcm = 
                    new System.Data.OleDb.OleDbCommand();
                odcm.CommandText = strQueryName;
                odcm.CommandType = 
                    System.Data.CommandType.StoredProcedure;
                odcm.Connection = odcn;

                // Retrieve the data.
                System.Data.OleDb.OleDbDataReader oddr = 
                    odcm.ExecuteReader(System.Data.CommandBehavior.
                    CloseConnection);

                // Loop through the records.
                while (oddr.Read())
                {

                    // Loop through the columns.
                    for (System.Int32 intCol = 0; 
                        intCol < oddr.FieldCount; intCol++)
                    {

                        // Display the result.
                        System.Console.Write(oddr.GetValue(intCol).
                            ToString());
                        if (intCol < (oddr.FieldCount - 1))
                        {
                            System.Console.Write("\t");
                        }
                        else
                        {
                            System.Console.WriteLine();
                        }
                    }
                }
                oddr.Close();
                System.Console.Read();
            }
            catch (System.Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                System.Console.Read();
            }
            finally
            {
                odcn.Dispose();
            }
        }
    }
}


Send feedback on this post to (change the obvious):

Brendan Reynolds
bwnnfb at hotmail dot com

(For the insatiably curious, "bwnnfb" is an acronym for "Blog With No Name Feed-Back")

Copyright © 2004 Brendan Reynolds. All rights reserved.


Comments: Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?