Click here to Skip to main content
Click here to Skip to main content
Go to top

Writing SQL queries in XML – A support intensive approach

, 31 Jul 2005
Rate this:
Please Sign up or sign in to vote.
Article that helps writing SQL in XML to provide better support

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Syed Asif Iqbal
Architect Avanza Solutions
Pakistan Pakistan
Asif has started programming back in 1991 on 80286 8-16 MHZ systems. Started from dBase III+, FoxPro, C, assembly (exceptional skills in Assembly language have added a significant confidence in his development career). The programming saga continues exploring new technologies and languages ranges from C++, VC++, Java, Delphi, RPG400, SQL Server, Oracle and many more and the exploration still continues to DOT Net Technologies, SOA architectures, BI, DSL and many more technologies. These learning experiences are backed by strong theoretical background with a flavor of research.
 
Asif show significant interest in reading fiction, bio technology, Astronomy. He Loves watching movies and in free time love to play with his kids.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
QuestionGetQuery Pinmembersarvanik4-May-13 4:38 
Questionhow to write sql query in xml file Pinmembervpooja27-Feb-12 20:19 
Generalgreat Pinmemberradioman@gawab.com13-Aug-07 0:47 
GeneralIs it Efficent Pinmembersutha10-Mar-08 20:30 
GeneralRe: Is it Efficent PinmemberSyed Asif Iqbal23-Mar-08 2:25 
GeneralRe: Is it Efficent PinmemberBhushan198023-Feb-10 20:30 
QuestionWhy not use CDATA? PinmemberAn 'OOP'15-Mar-06 20:48 
QuestionWhy don&#180;t you use strored procedures? Pinmembermachocr1-Aug-05 5:04 
AnswerRe: Why don&#180;t you use strored procedures? Pinmembermalharone1-Aug-05 12:55 
AnswerRe: Why don&#180;t you use strored procedures? PinmemberSyed Asif Iqbal1-Aug-05 19:22 
GeneralRe: Why don´t you use strored procedures? PinmemberEsoteric !23-Feb-09 8:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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 | Mobile
Web04 | 2.8.140926.1 | Last Updated 1 Aug 2005
Article Copyright 2005 by Syed Asif Iqbal
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid