Integrate Message Queue With Excel, XML and ADO.NET In The Enterprise

By Robbe D. Morris

Printer Friendly Version


Robbe & Melisa Morris
The following article demonstrates how to use Message Queues, XML, Excel, and ADO.NET to create a browser based outbound call center type application in .NET (without predictive dialing and telephony of course).  This is really a part II discussion from a previous article entitled Serialize C# Structs To Message Queues which showed the basics of serializing objects to and from message queues.  In that article, I described the ability to use message queues for a small-scale call center type application.  If you are new to message queues, I'd suggest reading it first to familiarize yourself with some of the syntax.  Today, I'll take it one step further and show you how to create one plus demonstrate one way to serialize an XmlDocument to and from the message queue.
 


 
Project Scenario
A client provides you with an Excel spreadsheet of customer data.  By the end of the day, they expect to have their data accessible in read/write mode to over 100 sales agents via a web browser script.  Of course, they'll want the updated customer and call result data in the same spreadsheet and sent back to them on an hourly basis for distribution to their local sales force.  Those of you in the call center business can relate to the technical limitations/desires of most clients.
Project Objective
Develop a web based application that can distribute records to 100 agents simultaneously via some sort of queuing mechanism to guarantee that two agents don't get the same record at the same time.  It should be able to repopulate the calling queues with XmlDocument data from an Excel spreadsheet based on call results.  It should be able to retrieve call results from each agent and update the Excel spreadsheet at regular intervals.  It should serialize an XmlDocument to and from the queue providing a dynamic data structure suitable for each individual call list.  The application definition files should be simple enough for a non-technical person to set up a few database columns and settings to make the call list ready to call.  No database programming in the agent scripts should be required.
Why Excel?
It is critical to note that I chose Excel as the data store for no other reason than to prove it could be done.  Excel is commonly thought to work best in a single user environment and is not well suited for a large number of users.  You could make this statement about many file types.  Thus, I thought getting this to work in Excel would really hammer home the point.  Assuming your IT shop has more time as well as the proper database skill set in house, I would highly recommend utilizing an enterprise RDBMS like SQL Server 2000, Oracle, Informix, etc. or even SQL Server Desktop Engine.  Also, this application is a mere prototype.  It is not intended to be a fully functional production ready application but it will definitely get you off to the right start.
In the call center industry, it is also very common to get customer data sent in Excel spreadsheets and be expected to return the updated data in the exact same format.  Normally, developers have very little programming time and resources available to write robust applications for this type of endeavor.  Thus, a quick and dirty process like the one outlined here is often just what the doctor ordered.
How does it work?
I based the applications basic functionality on the EIS Call Manager system from SER Solutions.  The system retrieves records from each call list and queues them up for agents.  When the agents log on to the call list, they retrieve records one at a time to contact the customer and offer a product.  Upon completion of the call, the record is dispositioned to track call results and the agent moves onto the next record.  In the Utilities.cs class, you'll see a string of dispositions that indicate the record should be recalled at a later date.
There is an administrative page in the /admin folder (Admin.cs in CallCenter.dll and CallCenter Namespace) that displays a list of available call lists, their record counts, and administrative tasks that can be executed against the list.  Each list has a Start, Stop, Recycle, and Process option depending on the current call list state.  Start and Stop is fairly self explanatory.  Recycle, however, is a term that describes resetting all records to be resent to the calling queue.  Whenever a record is sent to the queue, it is flagged so that future queue loads don't pick it up.  This would result in the same customer getting called multiple times a day.  Recycle simply changes the flag for those records that were called but not finalized so they can be called again in the future.  Process allows the administrator to manually process all records in the disposition queue ahead of schedule should it be necessary.
You'll also want to take notice of when the system processes finalized records.  Almost every admin command will trigger this process just prior to or right after the desired command.  At the very least, the admin page refreshes itself every 60 seconds so the calling queues are always populated and the most recent records in the disposition queue have been processed.
In the /admin folder you'll find a folder named ListConfig.  It contains the master calllists.xml file that manages which call lists are in production or not.  It also contains individual calllistdef xml files (ex. calllist_1.xml).  These calllistdef xml files contain the column and data type definitions for each call list.  You can add custom attributes to each column for data validation purposes or dynamic HTML generation based on data type (ex. state data type might automatically display a drop down list of states with the current answer populated).  Each call list should be given a unique number and the file naming convention maintained (ex. calllist_1.xls, calllistdef_1.xml, and so on).  You'll undoubtedly want to password protect the admin folder so normal users can't download your call list data.
For simplicity sake, I've created separate agent scripts for each of the three test call lists in the /home folder called calllist_1.aspx, calllist_2.aspx, and calllist_3.aspx.  Typically, one script could handle multiple call lists for the same offer.  For testing purposes, just browse to the prospective page to view a record from the designated queue, update the record, and submit it for processing.
One last note of interest.  I've seen quite a few requests on various forums asking how to serialize an XmlDocument.  I managed to find an article on MSDN describing how to create your own custom formatting class for use with message queues.  You'll find an example of this in the CustomMessageFormatter.cs class source code below.
How do I run the test code?
If you already have the Message Queue service running, then all that is necessary prior to downloading the code is to add a reference in your machine.config file to the assemblies node if it isn't already there.  Just copy one of the existing nodes and change the name to System.Messaging.  I've included test call lists under the /admin folder as well as the solution called SiteDebugger in the root of the website.  This will allow you to launch the solution and step through the standard C# class libraries used for both the admin page and agent script methods.  The /admin folder will need write permissions if you haven't already given that to your anonymous IIS user account.
Of course, you'll want to launch the admin page first so that you can start at least one of the call lists.  Then, launch one or more browser instances of a specific call list page to watch the system in action.
Adding a new call list to the mix is simple.  Put the spreadsheet in the /admin/lists folder.  Make sure it has the required columns added to it: queue, fdisp, curdate, curtime, callbackdate, callbacktime, agentid, and recordid.  Create a calllistdef xml file in the /admin/listconfig folder and adjust it's column/field name definitions.  Then, open /admin/listconfig/calllists.xml and add a node for your calllist.  That's it!
Download the complete solution and test call lists: Call Center Solution
Summary
There you have it.  The basic groundwork for a browser based outbound call center application.  Feel free to take the bits and pieces you need to create your own production ready application.  As always, please take a moment to rate this article.  Rate Article (opens in separate window).

Admin.cs Source Code
using System;
using System.Data;
using System.Data.OleDb;  
using System.Xml;
using System.Messaging;
using System.Runtime.Serialization;


namespace CallCenter
{
	 
  public class Admin : CallCenter.Utilities 
  {
        
    public XmlDocument XmlListConfig = new XmlDocument();

    public Admin()
    {
       // Load up the admin systems Xml config file.  We'll use
       // this to determine which lists are open/closed.
      try
      {
        XmlListConfig.Load(this.ListCfgPath + @"\listconfig\calllists.xml");
      }
      catch (Exception e) { this.RW(e.Message + "<br>"); }
    }

   public void StartList(string sID)
   {
      // Activate a specific list and save the updated node back to calllists.xml
      // We redirect back to the same page to avoid page a refresh processing
      // a list start/stop/recycle when it shouldn't.

     try
     {
       XmlNode oNode = this.XmlListConfig.SelectSingleNode("//LIST[@id='" + sID + "']");
       oNode.Attributes.GetNamedItem("openforcalling").Value = "1";    
     }
     catch (Exception e) { this.RW(e.Message + "<br>"); }
     this.XmlListConfigSave(); 
     this.SiteRedirect("default.aspx"); 
   }


   public void StopList(string sID)
   {
     string sFile=""; 
     string sList="";
     string sQueue="";
            
     // Stop a list.  Process any remaining records in the disposition queue.
    // Even though a list is stopped, as long as it is not "hidden", future loads
    // of the admin page will finalize any records still left over from agents still
    // on the call when the list was stopped.
    // Also clears all unretrieved messages from the calling queue.  This does
    // not make records flagged as in the queue immediately callable again.
    // The list must be recycled for this.

      try
      {
        XmlNode oNode = this.XmlListConfig.SelectSingleNode("//LIST[@id='" + sID + "']");
        sFile = oNode.Attributes.GetNamedItem("filename").Value;                     
        oNode.Attributes.GetNamedItem("openforcalling").Value = "0";    
        sList = this.CallList + sID;
        sQueue = this.QueuePath + sList; 
        this.ProcessFinalizedRecords(sID,sFile); 
        ClearQueue(sQueue);       
              
      }
      catch (Exception e) { this.RW(e.Message + "<br>"); }
      this.XmlListConfigSave(); 
      this.SiteRedirect("default.aspx"); 
   }



   public void RecycleList(string sID)
   {

     string sFile="";
     string sList="";
     string sSQL ="";
     string sQueue="";
             
     // Reset all records that had been previously dialed that were dispositioned
    // to be called again in the future.  Processes any finalized records before
    // initiating the reset.

     try
     {

      XmlNode oNode = this.XmlListConfig.SelectSingleNode("//LIST[@id='" + sID + "']");
      sFile = oNode.Attributes.GetNamedItem("filename").Value;                     
      sFile = this.ListCfgPath + @"\lists\" + sFile;               
      sList = this.CallList + sID;
      sQueue = this.QueuePath + sList;
      this.ProcessFinalizedRecords(sID,sFile); 
      ClearQueue(sQueue);
      sSQL = "update [" + sList + "$] set queue='0' where fdisp in (" + this.FdispCallable + ")";
      this.DBExecute(sSQL,sFile);
     }
     catch (Exception e) { this.RW(e.Message + "<br>"); }
             
     this.XmlListConfigSave(); 
     this.SiteRedirect("default.aspx"); 
  }



 public void XmlListConfigSave()
 {
   try
    {
      XmlListConfig.Save(this.ListCfgPath + @"\listconfig\calllists.xml");
    }
    catch (Exception e) { this.RW(e.Message + "<br>"); }
 }

 public bool FlagRecordInQueue(string sRecordID,string sFile,string sList)
 {

  bool fRet=false;
  string sSQL ="";
            
  // Set the queue column in the spreadsheet so the record won't be
  // pulled up again in queue load.

  try
  {

    sSQL = "update [" + sList + "$] set queue='1',callcnt = callcnt + 1 ";
    sSQL += " where recordid in (" + sRecordID + ")";
              
    this.DBExecute(sSQL,sFile);
    fRet=true;

  }
  catch (Exception e) { this.RW("Flag In Queue: " + e.Message + "<br>"); }
  return fRet;

 }

 public bool SaveRecordToExcel(string sXML,string sID,string sFile,string sList)
 {
   string sRecordID="";
   string sSQL="";
   string sField="";
   bool fRet=false;
   XmlDocument oXML = new XmlDocument();
   XmlNodeList oList;
               
   // Typically called from ProcessFinalizedRecords().  Loads the Xml
  // document deserialized from the message queue.  Builds a SQL update
  // string and saves the record back to Excel.

  try
  {
       
    oXML.LoadXml(sXML);

    sRecordID = oXML.SelectSingleNode("//DEF[@fieldname='recordid']").InnerText.ToString();

    oList = oXML.SelectNodes("//DEF"); 
   
    foreach(XmlNode oNode in oList)
    {
      sField=oNode.Attributes.GetNamedItem("fieldname").Value.ToString();

      switch(sField)
      {

        case "recordid":

              break;
        case "queue":
              sSQL += "queue='2',"; 
              break;

        default:
              sSQL += this.CreateSqlPair(oNode);
              break;
      }
                    
                       
    }

      sSQL = "update [" + sList + "$] set " + sSQL.Substring(0,sSQL.Length -1);
      sSQL += " where recordid in (" + sRecordID + ")";
      this.DBExecute(sSQL,sFile);
             
      fRet=true;

  }
  catch (Exception e) {this.RW(e.Message + "<br>");  }
 
  return fRet;

 }


 public void SetRecordCounts(string sID,string sFile)
 {

   int lTotal=0;
   int lCallable=0;
   string sList="";
   string sSQL="";
   DataSet oDS;

   // Update the admin display to reflect the various records counts for
   // the call list.

   try
   {

    sFile = this.ListCfgPath + @"\lists\" + sFile;
    sList = this.CallList + sID;
 
    oDS = this.GetDataSetFromExcel(sFile,"select * from [" + sList + "$]"); 

    lTotal = oDS.Tables[0].Rows.Count;

    sSQL = "select * from [" + sList + "$] where fdisp in (" + this.FdispCallable + ")";
    oDS = this.GetDataSetFromExcel(sFile,sSQL); 

    lCallable = oDS.Tables[0].Rows.Count;
   
    XmlNode oNode = this.XmlListConfig.SelectSingleNode("//LIST[@id='" + sID + "']");
    oNode.Attributes.GetNamedItem("totalrecords").Value = lTotal.ToString();     
    oNode.Attributes.GetNamedItem("callablerecords").Value = lCallable.ToString();     
    this.XmlListConfigSave(); 
   }
   catch (Exception e) { this.RW(e.Message + "<br>"); }
        
 }

    public void LoadCallingQueue(string sID,string sFile)
     {
 
          string sQueue="";
          string sList="";
          string sCol="";
          string sRecordID="";
          string sSQL="";
          int lRecords=0;
          int lMessageCount=0;
          DataSet oDS;
          XmlDocument oRecord;
          XmlNodeList oList;
         
          try
           {
                 
                sList = this.CallList + sID;
                sQueue = this.QueuePath + sList;
                sFile = this.ListCfgPath + @"\lists\" + sFile;
                
                // Subtract the number of messages in the queue
                // from our MaxRecordsInQueue variable.  This
                // allows to keep the desired number of callable records
                // at all times.

                lMessageCount = this.GetQueueMessageCount(sQueue);
               
                if (this.MaxRecordsInQueue >=  lMessageCount)
                  {
                    lRecords = this.MaxRecordsInQueue - lMessageCount;
                   }
                else { lRecords = 0; }

                if (lRecords<1) { return; }

                // Query the top x records based on lowest number of call attempts.
                // Notice the ORDER BY clause.  It appears that either Excel or ADO.NET
                // has a small bug when you use ORDER BY with Excel.  If the column values are not
                // unique enough, it causes the query to bring back improper results.  Very
                // strange.  I've added the unique id "recordid" to guarantee uniqueness.
                // Since "callcnt" is really the column we want to order by, this keeps the
                // problem from occurring.

                sSQL = "select TOP " + lRecords.ToString() + " * from [" + sList + "$] ";
                sSQL += " where queue in (0) and fdisp in (" + this.FdispCallable + ")   ";
                sSQL += "         order by callcnt,recordid ASC  ";

                oDS = this.GetDataSetFromExcel(sFile,sSQL); 

                if (oDS==null) { return; }

               // Load the call list defintion blank shell document with column settings

                oRecord = new XmlDocument();
                oRecord.Load(this.ListCfgPath + @"\listconfig\" + this.CallListDef + sID + ".xml");

               	foreach(DataRow oRow in oDS.Tables[0].Rows)
				{ 

                    oList = oRecord.SelectNodes("//DEF"); 
  
                    // Populate the shell XmlDocument with actual data from
                    // the spreadsheet using the "fieldname" attribute to determine
                    // which column data each node should get.

                    foreach(XmlNode oNode in oList )
				   {
                      sCol = oNode.Attributes.GetNamedItem("fieldname").Value.ToString();  
                      if (sCol=="recordid") { sRecordID = oRow[sCol].ToString(); }  
                      oNode.InnerText =  oRow[sCol].ToString();   
                   }
               
                // Flag this record in the spreadsheet so future queue loads won't
               // add this record.

                 if (this.FlagRecordInQueue(sRecordID,sFile,sList)==true)
                 {
                   // Serialize the XmlDocument and add it to the message queue

                   if (this.SendRecordToQueue(sQueue,oRecord.InnerXml)==false) { return; }
                  }
                }
                 
            }
          catch (Exception e) { this.RW(e.Message + "<br>"); }
        
     }
 


     public void ProcessFinalizedRecords(string sID,string sFile)
     {
          string sQueue="";
          string sList="";
          string sXML="";
          int lCount=0;  
          int lMessageCount=0;
        
          Message oMessage;
          
          // Go through the disposition queue for this call list
         // and save the updated record back to the Excel sheet.

          try
          {

                sList = this.CallList + sID;
                sQueue = this.QueuePath + this.CallListDisp + sID;
                sFile = this.ListCfgPath + @"\lists\" + sFile;
              
                lMessageCount = GetQueueMessageCount(sQueue);
              
                for (lCount=0;lCount<lMessageCount;lCount++)
                {

                     oMessage = this.GetRecordFromQueue(sQueue);

                     if (oMessage!=null)
                      {
                         sXML = (string) oMessage.Body;
                         this.SaveRecordToExcel(sXML,sID,sFile,sList);
                      }
                }

           }
          catch (Exception e) { this.RW(e.Message + "<br>"); }
          
     }
         
        public void WriteMainMenu()
        {

             XmlNodeList oList;
             string sID="";
             string sDesc="";
             string sFile="";
             string sOpen="";
             string sHide="";
             string sTotal="";
             string sCallable="";
             string sQueue="";
             string sList="";
             int lCallableQueue=0;
          
          try
          { 

            oList = this.XmlListConfig.SelectNodes("//LIST");  
				
            foreach(XmlNode oNode in oList )
            {

              sID = oNode.Attributes.GetNamedItem("id").InnerText.ToString();
              sFile = oNode.Attributes.GetNamedItem("filename").InnerText.ToString();
              sDesc = oNode.Attributes.GetNamedItem("desc").InnerText.ToString();
              sOpen = oNode.Attributes.GetNamedItem("openforcalling").InnerText.ToString();
              sHide = oNode.Attributes.GetNamedItem("hide").InnerText.ToString();
                    
              sList = this.CallList + sID;
              sQueue = this.QueuePath + sList;
               
              if (sHide!="1") 
              {
  
                this.ProcessFinalizedRecords(sID,sFile); 
                if (sOpen=="1") { this.LoadCallingQueue(sID,sFile); }
                this.SetRecordCounts(sID,sFile);
                         
                sTotal = oNode.Attributes.GetNamedItem("totalrecords").InnerText.ToString();
                sCallable = oNode.Attributes.GetNamedItem("callablerecords").InnerText.ToString();

                lCallableQueue=this.GetQueueMessageCount(sQueue); 
                   
                this.RW("<tr>");
                this.RW("<td align=left width=200 class=BodyText8 >" + sDesc + "</td>");

                if (sOpen=="1")
                {
                  this.RW("<td align=center nowrap><a href=default.aspx?action=stop&id=" + sID);
                  this.RW(" class=BodyLink8>Stop List</a></td>");
                  this.RW("<td align=center>   </td>");
                  this.RW("<td align=center><a href=default.aspx class=BodyLink8>Process</a></td>");
                }
                else
                {
                  this.RW("<td align=center nowrap><a href=default.aspx?action=start&id=" + sID); 
                  this.RW(" class=BodyLink8>Start List</a></td>");
                  this.RW("<td align=center nowrap><a href=default.aspx?action=recycle&id=" + sID);
                  this.RW(" class=BodyLink8>Recycle List</a></td>");
                  this.RW("<td align=center><a href=default.aspx class=BodyLink8>Process</a></td>");
                }
  
                  this.RW("<td align=right class=BodyText8 nowrap>" + lCallableQueue.ToString() + "</td>");
                  this.RW("<td align=right class=BodyText8 nowrap>" + sTotal + "</td>");
                  this.RW("<td align=right class=BodyText8 nowrap>" + sCallable + "</td>");
                  this.RW("</tr>");

              }
 
            }

           }
        catch (Exception e) { this.RW("<tr><td align=left class=BodyText10>" + e.Message + "</td></tr>"); }
 
        }

 }
}

Utilities.cs Source Code
using System;
using System.Data;
using System.Data.OleDb;  
using System.Xml;
using System.Messaging;
using System.Runtime.Serialization;

namespace CallCenter
{
	 
   public class Utilities 
   {
 
        public string CallList = "calllist_";
        public string CallListDef = "calllistdef_";
        public string CallListDisp = "calllistdisp_";
        public string QueuePath = ".\\PRIVATE$\\";
        public string ListCfgPath=System.Web.HttpContext.Current.Server.MapPath("/admin"); 
        public string FdispCallable = "0,30,31,32,33,34,35,36,37,38,39,40";
        public string ExcelCon = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;";
        public int MaxRecordsInQueue=20;

        public Utilities()
        {
          System.Web.HttpContext.Current.Response.Buffer = true;
        }

       public string RequestObject(string sName)
       {
         string sRet="";
         try { sRet = System.Web.HttpContext.Current.Request[sName].ToString().Trim(); }
         catch (Exception) { sRet = "";}  
         return sRet;
       }

       public void SiteRedirect(string sURL)
       {
         System.Web.HttpContext.Current.Response.Redirect(sURL);
       }

        public string HTMLName(string sFieldName)
        {
          return " name='" + sFieldName + "' id='" + sFieldName + "'";
        }
 
        public void RW(string sVal)
        {  
          try
          {	
            if (System.Web.HttpContext.Current !=null)
            {
              System.Web.HttpContext.Current.Response.Write(sVal +'\n');
            }
          }
          catch(Exception) {}
        } 

		 
      

       public int GetQueueMessageCount(string sQueue)
       {

             int nRet=0;
             MessageQueue oQueue;
             Message[] oMessages;

             try
             {
                if (!MessageQueue.Exists(sQueue)) { MessageQueue.Create(sQueue); }
                oQueue = new MessageQueue(sQueue);
                oMessages = oQueue.GetAllMessages();
                nRet = oMessages.GetLength(0); 
                oQueue.Close(); 
              }
             catch (Exception) {} 
             return nRet;
       }


       public Message GetRecordFromQueue(string sQueue)
          {

            MessageQueue oQueue;
            Message oMessage;

            try
            { 

              if (!MessageQueue.Exists(sQueue)) { MessageQueue.Create(sQueue); }

              oQueue = new MessageQueue(sQueue);
                
                // Utilize our custom message formatter to deserialize the XmlDocument stored
                // in the message queue.

              oQueue.Formatter = new CallCenter.CustomMessageFormatter(); 
             
              oMessage = oQueue.Receive(new TimeSpan(0,0,5));

              try {oQueue.Close(); } catch (Exception) { }
              oQueue.Close();
              return oMessage;
            }
            catch (Exception) { }
            return null;
         }

     

         public bool SendRecordToQueue(string sQueue,object oRecord)
         {
            bool fRet=false;
            MessageQueue oQueue;
            
            try
            {

              if (!MessageQueue.Exists(sQueue)) { MessageQueue.Create(sQueue); }

              oQueue = new MessageQueue(sQueue);
 
              // Utilize our custom message formatter to serialize oRecord which is
              // itself an XmlDocument.

              oQueue.Formatter = new CallCenter.CustomMessageFormatter(); 
              oQueue.Send(oRecord);

              fRet = true;
              oQueue.Close(); 
             }
           catch (Exception e) { this.RW("Send Queue Error:" + e.Message + "<br>"); }
          
           return fRet;
       }

     

         public bool ClearQueue(string sQueue)
         {
            bool fRet=false;
             
            MessageQueue oQueue;
            
            try
            {

              if (!MessageQueue.Exists(sQueue)) { MessageQueue.Create(sQueue); }

              oQueue = new MessageQueue(sQueue);
 
              oQueue.Purge();  

              fRet = true;
              oQueue.Close(); 
             }
           catch (Exception e) { this.RW("Clear Queue Error:" + e.Message + "<br>"); }
          
           return fRet;
       }

        public bool DBExecute(string sSQL,string sFile)
        {
            bool fRet=false;
            OleDbConnection oConn = new OleDbConnection();

            try
            {
                oConn.ConnectionString = this.ExcelCon + "Data Source=" + sFile + ";";
                oConn.Open();
                OleDbCommand oCmd = new OleDbCommand(sSQL,oConn);
                oCmd.ExecuteNonQuery();  
                oConn.Close();
                fRet=true;   
            }
            catch (Exception e) { this.RW("DBExecute: " + e.Message + "<br>"); }
            finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); }}
            return fRet;
        }

        public string CreateSqlPair(XmlNode oNode)
        {
            string sCol="";
            string sColVal="";
         
            sCol=oNode.Attributes.GetNamedItem("fieldname").Value.ToString(); 
            sColVal = oNode.InnerText.ToString();
            if (sColVal.Length <1) { return ""; }
            sColVal = sColVal.Replace("'","");
            return sCol + "='" + sColVal + "',";  
        }

        public DataSet GetDataSetFromExcel(string sFile,string sSQL)
        {

            DataSet oDS = new DataSet();
            OleDbConnection oConn = new OleDbConnection();

            try
            { 
                oConn.ConnectionString = this.ExcelCon + "Data Source=" + sFile + ";";
                oConn.Open();
                OleDbDataAdapter oCmd = new OleDbDataAdapter(sSQL,oConn);
                oCmd.Fill(oDS);
            }
            catch (Exception) {  }
            finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); }  }
            return oDS;
        }
		  
	}
}

CustomMessageFormatter.cs Source Code
using System;
using System.IO;
using System.Text;
using System.Messaging;


namespace CallCenter
{
	 
   public class CustomMessageFormatter : IMessageFormatter
    {

       public CustomMessageFormatter() { }
       public object Clone() { return new CustomMessageFormatter();  }
       public bool CanRead (System.Messaging.Message oMessage){ return true;}

       public void Write (System.Messaging.Message oMessage, object oMessageObject)
       {
          try
           {
             byte[] buff;
             buff = Encoding.UTF8.GetBytes (oMessageObject.ToString());
             Stream oStream = new MemoryStream(buff);
             oMessage.BodyStream = oStream;
           }
           catch (Exception e){System.Web.HttpContext.Current.Response.Write(e.Message + "<br>");}
      } 

      public object Read (System.Messaging.Message oMessage)
      {
         try
          {
            Stream oStream = oMessage.BodyStream;
            StreamReader oReader = new StreamReader (oStream);
            return oReader.ReadToEnd();
          }
           catch (Exception e){System.Web.HttpContext.Current.Response.Write(e.Message + "<br>");}
           return null;
      }


 }
}

CallList_3.aspx Source Code (Sample Agent Script)
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Messaging" %>
<%@ Import Namespace="CallCenter" %>

<script Language="C#" runat="server">
 
 protected CallCenter.Utilities oSite = new  CallCenter.Utilities();
 protected XmlDocument oRecord = new XmlDocument();
 protected string sCallListID="3";
 protected string sRecordID="";
 protected bool ShowRecord=false;

 protected void Page_Load(object sender, EventArgs e) { ProcessPage();  }
           
 public void ProcessPage()
{

     string sXML="";
     string sRecordQueue="";
     string sRecordDispositionQueue="";
     Message oMessage;

     sRecordQueue=oSite.QueuePath + "calllist_" + sCallListID;
     sRecordDispositionQueue=oSite.QueuePath + "calllistdisp_" + sCallListID;

     try
     {

        if (oSite.RequestObject("action")=="post") { SaveCustomerRecord(sRecordDispositionQueue); }

        oMessage = oSite.GetRecordFromQueue(sRecordQueue);

        sXML = (string) oMessage.Body;

        if (sXML.Length >1) 
          {
              oRecord.LoadXml(sXML);
              sRecordID = oRecord.SelectSingleNode("//DEF[@fieldname='recordid']").InnerText.ToString();
              ShowRecord=true;
          }
        
     }
      catch (Exception) {  }
     
}



public void SaveCustomerRecord(string sQueue)
{

      XmlDocument oXML = new XmlDocument();
 
     oXML.Load(oSite.ListCfgPath + @"\listconfig\" + oSite.CallListDef + sCallListID + ".xml");
 
     oXML.SelectSingleNode("//DEF[@fieldname='fdisp']").InnerText = oSite.RequestObject("FDISP");
     oXML.SelectSingleNode("//DEF[@fieldname='recordid']").InnerText = oSite.RequestObject("RECORDID");
     oXML.SelectSingleNode("//DEF[@fieldname='phone']").InnerText = oSite.RequestObject("PHONE");
     oXML.SelectSingleNode("//DEF[@fieldname='fname']").InnerText = oSite.RequestObject("FNAME");
     oXML.SelectSingleNode("//DEF[@fieldname='lname']").InnerText = oSite.RequestObject("LNAME");
     oXML.SelectSingleNode("//DEF[@fieldname='addr1']").InnerText = oSite.RequestObject("ADDR1");
     oXML.SelectSingleNode("//DEF[@fieldname='addr2']").InnerText = oSite.RequestObject("ADDR2");
     oXML.SelectSingleNode("//DEF[@fieldname='city']").InnerText = oSite.RequestObject("CITY");
     oXML.SelectSingleNode("//DEF[@fieldname='state']").InnerText = oSite.RequestObject("STATE");
     oXML.SelectSingleNode("//DEF[@fieldname='zip']").InnerText = oSite.RequestObject("ZIP");
    
     oSite.SendRecordToQueue(sQueue,oXML.InnerXml);
     
}



public string GetDispositionList()
{

        string sH="";

        sH = "<select id=FDISP name=FDISP>";
        sH += "<option value='0'>Not Dialed</option>";
        sH += "<option value='30' selected>Unavailable</option>";
        sH += "<option value='20'>Refused</option>";
        sH += "<option value='01'>Sale</option>";
        sH +="</select>";

        return sH;
}

 
public void DisplayRecord()
{
    string sFieldName="";
    string sFieldValue="";
    string sFieldDesc="";
    string sName="";
    
    try
    {
        oSite.RW("<table border=0 align=left cellspacing=2 cellpadding=2>");
         
        if (ShowRecord==true)
        {

            XmlNodeList oList = oRecord.SelectNodes("//DEF[@showinscript='true']");  
   
  				 
            foreach(XmlNode oNode in oList )
            {

                 sFieldName=oNode.Attributes.GetNamedItem("fieldname").InnerText.ToString();
                 sFieldDesc=oNode.Attributes.GetNamedItem("desc").InnerText.ToString();
                 sFieldValue=oNode.InnerText.ToString();
                 sName = oSite.HTMLName(sFieldName);

                 oSite.RW("<tr><td align=left class=BodyText8><b>" + sFieldDesc + "</b></td>");
                 oSite.RW("<td align=left class=BodyText8>");

                 switch(sFieldName)
                 {
                       case "fdisp":
                                    oSite.RW(GetDispositionList() + "---" + sFieldValue);
                                    break;

                       case "phone": 
                                    oSite.RW(sFieldValue);
                                    break;

                       case "recordid": 
                                    oSite.RW(sFieldValue);
                                    break;
                       case "callcnt": 
                                    oSite.RW(sFieldValue);
                                    break;

                       case "fname":
                                    oSite.RW("<input " + sName + " type=text size=40 maxlength=40 value='" + sFieldValue + "'>");
                                    break;
                       case "lname":
                                    oSite.RW("<input " + sName + " type=text size=40 maxlength=40 value='" + sFieldValue + "'>");
                                    break;
                       case "addr1":
                                    oSite.RW("<input " + sName + " type=text size=40 maxlength=40 value='" + sFieldValue + "'>");
                                    break;
                       case "addr2":
                                    oSite.RW("<input " + sName + " type=text size=40 maxlength=40 value='" + sFieldValue + "'>");
                                    break;
                       case "city":
                                    oSite.RW("<input " + sName + " type=text size=40 maxlength=40 value='" + sFieldValue + "'>");
                                    break;
                       case "state":
                                    oSite.RW("<input " + sName + " type=text size=2 maxlength=2 value='" + sFieldValue + "'>");
                                    break;
                       case "zip":
                                    oSite.RW("<input " + sName + " type=text size=5 maxlength=5 value='" + sFieldValue + "'>");
                                    break;

                 }
               
                 oSite.RW("</td></tr>");
           }

          oSite.RW("<tr><td align=left> </td><td align=left class=BodyText8>");
          oSite.RW("<input type=button name=Submit value='Submit' onclick=SubmitForm();></td></tr>");


        }
        else
        { 
          oSite.RW("<tr><td align=left colspan=2 class=BodyText8>");
          oSite.RW("There are no records in call list: " + sCallListID + ".  ");
          oSite.RW("<a href=javascript:ReloadPage(); class=BodyLink8 target=_self>Retry record query</a></td></tr>");
         }

        oSite.RW("</table>");

    }
    catch (Exception e) { Response.Write("Display Record Error: " + e.Message + "<br>");  }
}
 
</script>


 <HTML><TITLE>Call Center Agent Demo</TITLE>   
 <HEAD>
  <LINK href=/home/global.css rel=Stylesheet>
 <SCRIPT language=JavaScript>

    var fSubmitted=false;  

          function ReloadPage()
          {
             document.frmSubmit.Action.value='reload';
             document.frmSubmit.submit();
          }

          function SubmitForm()
           {
               if (fSubmitted==false)
                 {
                   document.frmSubmit.Action.value='post';
                   document.frmSubmit.submit();
                   fSubmitted=true;
                 }
           }

    </SCRIPT>

  </HEAD>

  <BODY bottomMargin=0 bgColor=#ffffff leftMargin=10 topMargin=0 rightMargin=0 marginwidth=0 marginheight=0  >
  <FORM  method=post action=calllist_<%=sCallListID%>.aspx id=frmSubmit name=frmSubmit>

  <table valign=top align=left class=ColorTable border=0 cellPadding=2 cellSpacing=2 width='90%' >

    
  <tr><td align=left class=BodyText8><br></td></tr>
  <tr><td align=left class=ColorText10><b>Call List <%=sCallListID%></b></td></tr>
  <tr><td><hr size=1 color=#CCCCCC noshade></td></tr>
  <tr><td align=left class=BodyText9>Hello, my name is _________ and I'm calling for the Happy Gardener Magazine!</td></tr>
  <tr><td align=left class=BodyText8><br></td></tr>
  <tr><td align=left class=BodyText9>We'd like to offer you a full one year subscription for $1.  Would you like to buy a subscription?</td></tr>

  <tr><td align=left class=BodyText8><br></td></tr>
  <tr><td align=left><% DisplayRecord(); %></td></tr>
  
      
  </table>
  <input type=hidden name=Action id=Action value='0'>        
  <input type=hidden name=RECORDID id=RECORDID value='<%=sRecordID%>'>        
  </form></body></html>


Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of EggHeadCafe which provides .NET articles, book reviews, software reviews, and software download and purchase advice.