Opening/Closing a Database connection in an apsx page for an IVR system

Question

I am currently developing an IVR system, my question is more on a development side of basic architecture and opening/closing a database connection. As you can see from the code below, in the page_load I am opening a connection, passing variables, and then closing the connection. My problem lies in the fact that the variables are not set during the page load, they are set as the call comes in, which is in the Boolean ParseXML section. I need to know what is the best way of opening the connection during page load, pass the variables after they are collected, and then finally close the connection. And most important how to do this, I have tried several different methods, with no success.

My initial thought process and approach is to split up the database connection code, and place them in different parts of the page life cycle. But I have had zero success with where to exactly put it.

The Boolean parse, writes to a text file. But I want it to also write to a database.

<%@ Page Language="C#" aspcompat="true" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Net" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System.Web.SessionState" %>
<%@ Import Namespace="System.Web.UI" %>
<%@ Import Namespace="System.Web.UI.WebControls" %>
<%@ Import Namespace="System.Web.UI.HtmlControls" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>



   <script language="C#" runat="server">


       Boolean ParseXML(string XMLContent)
       {
           try
           {
               XmlDocument doc = new XmlDocument();
               doc.LoadXml(XMLContent);

               String MenuID, Duration, CallerID, CallID, DateAndTime, VoiceFileName;
               XmlNode TempNode;
               Byte[] VoiceFile;

               XmlElement root = doc.DocumentElement;
               XmlAttributeCollection attrColl = root.Attributes;

               //parse inbound values
               MenuID = attrColl["menuid"].Value;
               Duration = attrColl["duration"].Value;
               CallID = attrColl["callid"].Value;
               CallerID = attrColl["callerid"].Value;

               //writed parsed values to file
               StreamWriter w = File.AppendText(Request.MapPath("summaryincall.txt"));

               w.Write(String.Format("\"{0:MM/dd/yyyy}\",\"{0:HH:mm:ss}\"", DateTime.Now));

               XmlNodeList NodeCount = doc.SelectNodes("/campaign/prompts/prompt");
               foreach (XmlNode node in NodeCount)
               {
                   attrColl = node.Attributes;

                  w.WriteLine("Prompt ID: " + attrColl["promptid"].Value);
                  w.WriteLine("Keypress : " + attrColl["keypress"].Value);
                  w.Write(attrColl["keypress"].Value);


                   if (node.HasChildNodes)
                   {
                       TempNode = node.FirstChild;
                       attrColl = TempNode.Attributes;

                       //convert file to binary
                       VoiceFile = System.Convert.FromBase64String(TempNode.InnerText);
                       VoiceFileName = attrColl["filename"].Value;

                       //save file in application path
                       FileStream fs = new FileStream(Request.MapPath(VoiceFileName), FileMode.OpenOrCreate);
                       BinaryWriter bw = new BinaryWriter(fs);
                       bw.Write((byte[])VoiceFile);
                       bw.Close();
                       fs.Close();

                       w.WriteLine("Filename : " + VoiceFileName);
                   }
               }


               w.Close();
               return true;
           }
           catch (Exception e)
           {
               Response.Write(e.Message);
               return false;

           }
       }


       void Page_Load(object sender, System.EventArgs e)
       {

           string connectionString = "server=abc;database=abc;uid=abc;pwd=1234";
           SqlConnection mySqlConnection = new SqlConnection(connectionString);
           string procedureString = "Call_Import";
           SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
           mySqlCommand.CommandText = procedureString;
           mySqlCommand.CommandType = CommandType.StoredProcedure;
           mySqlCommand.Parameters.Add("@CDate", SqlDbType.DateTime).Value = DateTime.Now;
           mySqlCommand.Parameters.Add("@CTime", SqlDbType.DateTime).Value = DateTime.Now;
           mySqlCommand.Parameters.Add("@ID", SqlDbType.Int).Value = keypress;
           mySqlCommand.Parameters.Add("@CType", SqlDbType.Int).Value = CallID;
           mySqlConnection.Open();
           mySqlCommand.ExecuteNonQuery();
           SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
           mySqlDataAdapter.SelectCommand = mySqlCommand;
           mySqlConnection.Close();


           try
          {
             String xmlcontent, PostResponse, campaign;
             Byte[] Bindata = Request.BinaryRead(Request.TotalBytes);

             string XML;
             XML = System.Text.Encoding.ASCII.GetString(Bindata);
             StreamWriter w = File.AppendText(Request.MapPath("xmlsummaryincall.txt"));
             w.WriteLine("--- "  + DateTime.Now + " ------------------------------------------------------");
             w.WriteLine(XML.Replace("<?xml version=\"1.0\"?>", ""));  //needed so ?xml tag will display as text
             w.WriteLine("");
             w.WriteLine("");          
             w.Close();

             if (!ParseXML(XML)) Response.Write("Failed");

           }
           catch (Exception error)
           {
             Response.Write(error.Message);
           }
       }

   </script>

Answer 1

I am assuming that the SqlDataAdapter is being used after the ParseXml method is called. Try the following:

• Declare a field for SqlConnection and SqlCommand.
• Open the connection in Page_Load.
• Set the command parameters in ParseXml.
• Close the connection in Page_Unload.

<script language="C#" runat="server">
    private SqlConnection mySqlConnection;
    private SqlCommand mySqlCommand;
           
    Boolean ParseXml(string XMLContent){

        // Do other work

        mySqlCommand.Parameters["@CDate"].Value = DateTime.Now; 
        mySqlCommand.Parameters["@CTime"].Value = DateTime.Now; 
        mySqlCommand.Parameters["@ID"].Value = keypress; 
        mySqlCommand.Parameters["@CType"].Value = CallID; 

        // Do other work

    }

     void Page_Load(object sender, System.EventArgs e)
     {
        string connectionString = "server=abc;database=abc;uid=abc;pwd=1234"; 
        mySqlConnection = new SqlConnection(connectionString); 
        string procedureString = "Call_Import"; 
        mySqlCommand = mySqlConnection.CreateCommand(); 
        mySqlCommand.CommandText = procedureString; 
        mySqlCommand.CommandType = CommandType.StoredProcedure; 
        mySqlCommand.Parameters.Add(new SqlParameter("@CDate", SqlDbType.DateTime));
        mySqlCommand.Parameters.Add(new SqlParameter("@CTime", SqlDbType.DateTime));
        mySqlCommand.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
        mySqlCommand.Parameters.Add(new SqlParameter("@CType", SqlDbType.Int));
        mySqlConnection.Open(); 
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); 
        mySqlDataAdapter.SelectCommand = mySqlCommand;
     }

     void Page_UnLoad(object sender, System.EventArgs e){
        if (mySqlConnection.State != ConnectionState.Closed)
            mySqlConnection.Close();
     }
</script>

Here is a link that will help you understand the life cycle of an ASP.NET page.

Answer 2

I don't have a lot of IVR experience, but here's how it worked on one system I worked on (which used VXML).

The call was answered by the IVR. This caused the IVR Voice Browser to issue an HTTP request to the web server.

The web server received the request, along with a port number, to identify the unique caller. In my case, we were using standard ASPX pages that output VMXL (rather than HTML or XHTML), so all processing had to be done in the Page_Load method. If the page needed additional information about the call, for example the callers number, we would issue a web services call to the IVR, including the port number.

All of the user interaction with the IVR - button presses, etc - were handled on the IVR, and the web server would only get involved when a different VXML document was requested.