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>

Open in new window


Example 2:

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

Open in new window


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

Open 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();
        }

Open in new window

YushellAsked:
Who is Participating?
 
YushellAuthor Commented:
Problem solved. I did not understand why there were references to XMLWriter. I deleted eveyone of them as I would not be writing. Made minor changes and bam, done.

Here's the code for future references:

Response.Expires = -1;
Response.ContentType = "text/xml";
string cCorreo = "";
string cPassword = "";
cCorreo = txtUsername.Text;
cPassword = txtPass.Text;
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();
	xmlArc.MoveToElement();
	while ((xmlArc.Read()) && (!xmlArc.EOF))
	{
		switch (xmlArc.NodeType)
		{
			case XmlNodeType.Element:
				if ((xmlArc.Name == "id") & (cYahayId == ""))
				{
					cCapturaValor = "1";
					cYahayId = "1";
				}
				break;
			case XmlNodeType.CDATA:
				break;
			case XmlNodeType.Text:
				if ((cCapturaValor == "1") & (cYahayId == "1"))
				{
					cUserId = xmlArc.Value;
					cCapturaValor = "";
				}
				cCapturaValor = "";
				break;
			case XmlNodeType.EndElement:
				break;
		}
		if (xmlArc.HasAttributes)
		{
			while (xmlArc.MoveToNextAttribute())
			{
				if (xmlArc.Name == "value")
				{
					cCapturaValor = "2";
				}
				if (cCapturaValor == "2")
				{
					cResponse = xmlArc.Value;
					cCapturaValor = "";

				}
			}
		}
		xmlArc.MoveToElement();
	}
	if (cResponse == "1")
	{
		Session["pUserId"] = "HOLA";
		lblStatus.Text = "<br />Log in exitoso";
		lblStatus.Font.Bold = true;
		lblStatus.ForeColor = System.Drawing.Color.Red;
		lblStatus.Visible = true;
		Response.Redirect("../11ideal/Quiniela.aspx");
	}
	if (cResponse == "2")
	{
		lblStatus.Text = "<br />Datos incorrectos o inexistentes";
		lblStatus.Font.Bold = true;
		lblStatus.ForeColor = System.Drawing.Color.Red;
		lblStatus.Visible = true;
	}
	if (cResponse == "3")
	{
		lblStatus.Text = "<br />Contraseña incorrecta";
		lblStatus.Font.Bold = true;
		lblStatus.ForeColor = System.Drawing.Color.Red;
		lblStatus.Visible = true;
	}
	xmlArc.Close();
	sqlCon.Close();
}

Open in new window


Thanks anyway mwvisa1. See you around. :)
0
 
Kevin CrossChief Technology OfficerCommented:
This appears to be already coded:

cCorreo = Convert.ToString(Request.QueryString["user_id"]);
cPassword = Convert.ToString(Request.QueryString["pass"]);

The above sets the values from the query string of a GET URL. You can replace those with the value from text fields instead.

It appears you already have the parameters to pass the data to SQL and to read the XML response, so advise what is not working exactly?
0
 
YushellAuthor Commented:
You're right. I should change:

cCorreo = Convert.ToString(Request.QueryString["user_id"]);
cPassword = Convert.ToString(Request.QueryString["pass"]);

Open in new window


to:

cCorreo = txtEmail.Text;
cPassword = txtPass.Text);

Open in new window


But after hitting the Log In button, I get an XML error. XML Parsing Error: junk after document element: On this line:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

But that happens only with the code inside:

while ((xmlArc.Read()) && (!xmlArc.EOF))

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Kevin CrossChief Technology OfficerCommented:
Hmm. Try just changing to "text/plain" for troubleshooting.
i.e., Response.ContentType = "text/plain";

We will be able to see what the content is coming back as to determine what exactly is going on.
0
 
YushellAuthor Commented:
Sorry. But where exactly should I put the Response.ContentType = "text/plain";?
0
 
Kevin CrossChief Technology OfficerCommented:
On line #2 in place of Response.ContentType = "text/xml";
Actually, you can put below or above and comment out that line so you don't forget to put it back when you are done troubleshooting.
0
 
YushellAuthor Commented:
I'm not at work right now. I'll try that tomorrow morning. Will get back to you then. Sleep well, thanks for the help.
0
 
YushellAuthor Commented:
Mwvisa1:

I changed the text/xml to text/plain. And I get my html code in screen. What changed after the button click was:

<form name="form1" method="post" action="Registro.aspx" id="form1">
<div>
<input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="/wEPDwUJNTAzMDcxMDQwD2QWAgIDD2QWAgIJDw8WCh4EVGV4dAUgPGJyIC8+RGViZSBpbnRyb2R1Y2lyIHVuIHVzdWFyaW8eCUZvbnRfQm9sZGceCUZvcmVDb2xvcgqNAR4EXyFTQgKEEB4HVmlzaWJsZWdkZBgBBR5fX0NvbnRyb2xzUmVxdWlyZVBvc3RCYWNrS2V5X18WAQUJQ2hlY2tCb3gxdS1JlH4PSkkAa6S3QaTC9Kud+ss=" />
</div>

<div>
	<input type="hidden" name="__EVENTVALIDATION" id="__EVENTVALIDATION" value="/wEWBQL1/8OkAgKl1bK4CQLKw6LdBQKC5Ne7CQKM54rGBtaLEdFlySH6JD7Ev1Lu9/yYTsGS" />
</div>

Open in new window


I'm wondering. Where and how do I specify I want to display these messages in my label?

Example:

Label1.Text = LogInStatusMessage;
Label1.Visible = True;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Hmm. I may need to drill into what the C# is actually doing as I was expecting to see the SQL XML result. As you can see the XML above is not well-formed because it is missing the end </form> tag. Very odd. Anyway, you may have to use a normal ExecuteReader() versus ExecuteXmlReader(). Then what you will have is the entire XML string will be in one cell (first row, first column) that you could then make the text of your label.
0
 
YushellAuthor Commented:
Actually, I missed a few lines. The form tag is closed:

<form name="form1" method="post" action="Registro.aspx" id="form1">
<div>
<input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="/wEPDwUJNTAzMDcxMDQwZBgBBR5fX0NvbnRyb2xzUmVxdWlyZVBvc3RCYWNrS2V5X18WAQUJQ2hlY2tCb3gxNmu9ou7TBlO0BEXPN4AcYJVGj0o=" />
</div>

<div>

	<input type="hidden" name="__EVENTVALIDATION" id="__EVENTVALIDATION" value="/wEWBQKl6MyWCgKl1bK4CQLKw6LdBQKC5Ne7CQKM54rGBkPnrC5OefbABCdVZJyVM3p2JQVb" />
</div>
            <div>
                <center>Usuario:<br />
                <input name="txtUsername" type="text" value="a" id="txtUsername" /><br />
                Contraseña:<br />
                <input name="txtPass" type="text" value="a" id="txtPass" /><br />
                ¿Olvido su contraseña?<br />
                    <input id="CheckBox1" type="checkbox" name="CheckBox1" /> Recordar mi cuenta<br />
                    <input type="submit" name="Button1" value="" id="Button1" class="botonREG" style="border-style:None;height:17px;width:61px;" /><br />
                    </center>
            </div>
            </form>

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Okay makes more sense as the original page, but does not make sense why that is showing and not the response from SQL. I may have read the code too quickly, but the appearance to me was that the Response.ContentType is set to xml inside the code handling the post of data, so was for writing the SQL response which I thought is what you were getting the XML parse errors on. I was trying to get you to display the text returned from SQL to see what it looked like to determine why it was failing to parse, i.e., help you debug the application.
0
 
YushellAuthor Commented:
All I need to do, is catch the Stored Procedure's result (XML). Read it. And display the "Message" on screen depending on the before mentioned "value".

I don't understand what the XMLWriter is for.
0
 
YushellAuthor Commented:
I found the solution to my problem before anyone posted one. Solution was simply to remove XMLWriter references in the code, as I would not be using them, and they were the ones causing the XML parsing errors. Everything works fine now.
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, it sounds like the old developer was writing the XML to the response. If that is not needed, then makes sense to remove it. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.