Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Article

Writing SQL queries in XML – A support intensive approach

Rate me:
Please Sign up or sign in to vote.
4.84/5 (13 votes)
31 Jul 2005CPOL2 min read 101K   28   14
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)


Written By
Product Manager Avanza Solutions
Pakistan Pakistan
Asif embarked on his programming journey back in 1991 when he first encountered 80286 8-16 MHz systems. His programming repertoire began with dBase III+, FoxPro, C, and assembly language, where he honed exceptional skills that have been a cornerstone of his development career.

Over the years, Asif's programming saga has seen him delve into a wide array of technologies and languages, ranging from C++, VC++, Java, Delphi, RPG400, SQL Server, and Oracle, to name just a few. His insatiable curiosity continues to lead him towards newer horizons, such as DOT Net Technologies, SOA architectures, BI, DSL, and beyond. These learning experiences are underscored by a robust theoretical foundation, with a penchant for research.

Beyond the realm of technology, Asif's interests include delving into the pages of fiction, exploring biotechnology, and gazing at the wonders of Astronomy. He finds joy in watching movies, and during his free time, he delights in quality moments spent with his children.

Comments and Discussions

 
QuestionHow to Work with Parameters Pin
Devkranth Kishore Vanja23-Mar-20 5:36
Devkranth Kishore Vanja23-Mar-20 5:36 
QuestionqueryXMLReader Pin
Trupti.Samant26-Mar-15 4:19
Trupti.Samant26-Mar-15 4:19 
AnswerRe: queryXMLReader Pin
AprNgp27-Jan-20 16:56
AprNgp27-Jan-20 16:56 
QuestionGetQuery Pin
sarvanik4-May-13 4:38
sarvanik4-May-13 4:38 
Questionhow to write sql query in xml file Pin
vpooja27-Feb-12 20:19
vpooja27-Feb-12 20:19 
Generalgreat Pin
radioman.lt13-Aug-07 0:47
radioman.lt13-Aug-07 0:47 
GeneralIs it Efficent Pin
sutha10-Mar-08 20:30
sutha10-Mar-08 20:30 
GeneralRe: Is it Efficent Pin
_Asif_23-Mar-08 2:25
professional_Asif_23-Mar-08 2:25 
GeneralRe: Is it Efficent Pin
Bhushan198023-Feb-10 20:30
Bhushan198023-Feb-10 20:30 
QuestionWhy not use CDATA? Pin
Anoop Pillai15-Mar-06 20:48
Anoop Pillai15-Mar-06 20:48 
QuestionWhy don&#180;t you use strored procedures? Pin
machocr1-Aug-05 5:04
machocr1-Aug-05 5:04 
AnswerRe: Why don&#180;t you use strored procedures? Pin
malharone1-Aug-05 12:55
malharone1-Aug-05 12:55 
AnswerRe: Why don&#180;t you use strored procedures? Pin
_Asif_1-Aug-05 19:22
professional_Asif_1-Aug-05 19:22 
GeneralRe: Why don´t you use strored procedures? Pin
Esoteric !23-Feb-09 8:09
Esoteric !23-Feb-09 8:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.