?
Solved

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

Posted on 2011-10-18
14
Medium Priority
?
229 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:Yushell
  • 8
  • 6
14 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36990148
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
 

Author Comment

by:Yushell
ID: 36990278
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36990308
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Yushell
ID: 36990323
Sorry. But where exactly should I put the Response.ContentType = "text/plain";?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36990362
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
 

Author Comment

by:Yushell
ID: 36990419
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
 

Author Comment

by:Yushell
ID: 36993976
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36994082
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
 

Author Comment

by:Yushell
ID: 36994130
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36994200
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
 

Author Comment

by:Yushell
ID: 36994366
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
 

Accepted Solution

by:
Yushell earned 0 total points
ID: 36995183
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
 

Author Closing Comment

by:Yushell
ID: 37016610
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36995253
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question