Writing SQL queries in XML – A support intensive approach

Introduction

Providing real time support in software’s industry is not easy. The experience gets worse when you have to provide support of buggy applications. I am providing support for around 3 years of such an application and the experience leads me to design SQL in such way that requires instant fixes if possible. Goals of the design are:

• We need to minimize the support time. That is if a bug has been identified in the system than this has to be fixed/sent to the client instantly.

• Fix should be sent without changing the code if possible

• Can accommodate missing functionality easily if possible

Problem

To achieve these goals the basic idea comes to mind is to use XML for SQL. Storing complete SQL Templates in SQL would seem an ideal solution for these kinds of applications. But storing Complex SQL in XML is not possible due to special characters restriction. For example I can not do this

<QUERIES>
    <SQL ID="GetBookings">
        SELECT BookingID from BOOKING WHERE BOOKINGDATE >= {0}
    </SQL>
</QUERIES>

And then I thought how easy my life had be if I were able to do it in XML.

Once I finalize structure the rest becomes very easy. This structure helps me in achieving the goal.

• It reduces the compiled code size.

• It makes SQL code readable because otherwise I need to do so many string concatenation operations to make a SQL string.

• SQL debugging becomes very easy because now I don’t need to fetch the large SQL from SQL profiler then indent it properly to understand what it is actually doing

• I now have the flexibility to update the SQL (joins) without actually compiling the code

For example, Lets take a basic scenario (Support persons who understands what priority 1 issue meansJ), we released the product and a priority 1 issue comes that booking status have not been taken care of.

Luckily we have handled this scenario instantly. How, lets look into the solution:

<QUERIES>
    <SQL ID="GetBookings">
    <!-- 
        SELECT BookingID from BOOKING B, BOOKING_STATUS BS
        WHERE B.BOOKINGSTATUSID = BS.BOOKINGSTATUSID
        AND BOOKINGSTATUSBOOKINGSEARCH = 1
        AND BOOKINGDATE >= {0}
    -->
    </SQL>
</QUERIES>

As you can see, we introduced new joins in the condition, added the filter criteria test the code and instantly sent the changed XML to client and the priority 1 has been resolved.

Implementation

Below is the sample code that loads the XML and a sample client that uses it. Though I know this is very easy and lots of developers can code it in much better form. The idea here is to just provide an idea of how to implement the structure.

The CODE

public class SqlManager
{
private StringDictionary Queries;
    private static SqlManager sqlManager = null;
            
    protected SqlManager()
    {
    }

    public static SqlManager Create()
    {
        if (sqlManager == null)
        {
            sqlManager = new SqlManager();
            sqlManager.LoadSqlFile();
        }
        return sqlManager;
    }        

    public void LoadSqlFile()
    {
        string FName=ConfigurationSettings.AppSettings["XMLQuery"];
        XmlTextReader qr = new XmlTextReader(FName);

        if (Queries == null)
            Queries = new StringDictionary();

        string id = "";
        while (queryXMLReader.Read())
        {
            switch (queryXMLReader.NodeType)
            {
                case XmlNodeType.Element:
                    if (qr.Name.Equals("SQL"))
                    {
                        while (qr.MoveToNextAttribute())
                        {
                            if (qr.Name.Equals("ID"))
                            {
                            id = qr.Value;
                                break;
                            }
                        }
                    }
                    break;
                case XmlNodeType.Comment:
                    Queries.Add(id, qr.Value);
                    id = "";
                    break;
            }
        }
    }
}

The code shown above will load all the SQL present in the XML into a dictionary object; this is done to get fastest lookup time.

Below is the sample client code to use the SqlManager class and execute the query.

Public class BookingSearchAgent
{
    public static SqlManager QueryManager;
    public BookingSearchAgent()
    {
        QueryManager = SqlManager.Create();
    }

    Public DataSet GetBookingIds()
    {
        string Query = QueryManager.GetQuery("GetBookings");
        Query = string.Format(Query, DateTime.now);
        DataSet ds = SqlHelper.ExecuteDataset(CommandType.Text, Query);
        Return ds;
    }
}

The Ending

This is a very simple support intensive approach for handling similar situations and i like to have comments on this approach

History