How can I send data to my Stored Procedure, and read the XML output it gives?

I have a Stored Procedure called usp_getWAPFanLogin. Which takes two parameters (@email and @password), and returns XML with log in info.

Example 1:

<data>
  <Response value="1" description="Registered user" />
  <Registry>
    <id>1</id>
    <email><![CDATA[a@a.com]]></email>
    <username><![CDATA[Alan G.]]></username>
    <firstname><![CDATA[Alan]]></firstname>
    <lastname><![CDATA[Gonzalez]]></lastname>
    <birthday>1970-10-22</birthday>
    <thumb>THMFP00000001.jpg</thumb>
    <location lat="41.61258670" long="-87.66332387">
      <city><![CDATA[Harvey]]></city>
      <street><![CDATA[122 West 153rd Street]]></street>
      <state code="IL"><![CDATA[ILLINOIS]]></state>
    </location>
    <team id="37"><![CDATA[Guerreros]]></team>
  </Registry>
</data>

Select allOpen in new window

Example 2:

<data>
        <response value="2" description="User not found" />
        <registry>empty<registry>
</data>

Select allOpen in new window

Example 3:

<data>
        <response value="3" description="Incorrect password" />
        <registry>empty<registry>
</data>

Select allOpen in new window

I have an ASP.NET log in, with 2 textboxes, one for email, one for password. What I intend to do, is execute the stored procedure, along with its 2 parameters. It returns the before mentioned XML, depending on the users credentials.

Questions:

1. How do I execute the Stored Procedure with the textbox parameters?

2. How do I read and display the XML output?

3. How do I redirect if user exists?

I need to display the Description attribute value on screen to inform the user about his/her log in status. Then, if log in is successful, redirect to www.sample.com/page.aspx.

I'm supposed to work with the code a co-worker left behind.

Response.Expires = -1;
        Response.ContentType = "text/xml";
        string cCorreo = "";
        string cPassword = "";
        cCorreo = Convert.ToString(Request.QueryString["user_id"]);
        cPassword = Convert.ToString(Request.QueryString["pass"]);
        string cCapturaValor = "";
        string cYahayId = "";
        string cUserId = "";
        string cResponse = "";
        if (cCorreo == null)
        {
            cCorreo = "";
        }
        if (cPassword == null)
        {
            cPassword = "";
        }
        string sConection = WebConfigurationManager.ConnectionStrings["sqlCoolCast"].ConnectionString;
        using (SqlConnection sqlCon = new SqlConnection(sConection))
        {
            SqlCommand sqlCom = new SqlCommand("exec usp_getWAPFanLogin @email, @password", sqlCon);
            sqlCom.Parameters.Add("@email", SqlDbType.VarChar);
            sqlCom.Parameters["@email"].Value = cCorreo;
            sqlCom.Parameters.Add("@password", SqlDbType.VarChar);
            sqlCom.Parameters["@password"].Value = cPassword;
            sqlCon.Open();
            System.Xml.XmlReader xmlArc = sqlCom.ExecuteXmlReader();
            System.Xml.XmlTextWriter xmlPage = new System.Xml.XmlTextWriter(Response.Output);
            xmlPage.WriteStartDocument();
            xmlArc.MoveToElement();
            while ((xmlArc.Read()) && (!xmlArc.EOF))
            {
                switch (xmlArc.NodeType)
                {
                    case XmlNodeType.Element:
                        if ((xmlArc.Name == "id") & (cYahayId == ""))
                        {
                            cCapturaValor = "1";
                            cYahayId = "1";
                        }
                        xmlPage.WriteStartElement(xmlArc.Name);
                        break;
                    case XmlNodeType.CDATA:
                        xmlPage.WriteCData(xmlArc.Value);
                        break;
                    case XmlNodeType.Text:
                        if ((cCapturaValor == "1") & (cYahayId == "1"))
                        {
                            cUserId = xmlArc.Value;
                            cCapturaValor = "";
                        }
                        xmlPage.WriteString(xmlArc.Value);
                        cCapturaValor = "";
                        break;
                    case XmlNodeType.EndElement:
                        xmlPage.WriteEndElement();
                        break;
                }
                if (xmlArc.HasAttributes)
                {
                    while (xmlArc.MoveToNextAttribute())
                    {
                        if (xmlArc.Name == "value")
                        {
                            cCapturaValor = "2";
                        }
                        if (cCapturaValor == "2")
                        {
                            cResponse = xmlArc.Value;
                            cCapturaValor = "";

                        }
                        xmlPage.WriteAttributeString(xmlArc.Name, xmlArc.Value);
                    }
                }
                xmlArc.MoveToElement();
            }
            xmlPage.Flush();
            if (cResponse == "1")
            {
                Session["pUserId"] = cUserId;
            }
            xmlPage.Close();
            xmlArc.Close();
            sqlCon.Close();
        }

Select allOpen in new window