Solved

using datareader to output xml in C#

Posted on 2015-02-16
3
120 Views
Last Modified: 2015-02-16
Hi Experts,

I'm using Visual Studio 2013 and Sql Server 2008.
I created an example in ASP.net using VB and that one works fine.
Now I'm translating this example to use C# and I'm having problem in the translation.
Below I describe my VB example and then describe the error i'm getting in my C# example.



I'm using the Employees table of the Northwind sql server database.

The Employees table returned by my query in my code looks like this:

employees table
VB Version

I have a ASP.NET page called TestEmployees.aspx. In this example I used Visual Basic as the language.

The page queries the Employees table and returns an (XML with Attributes) representation of that table.

This is my code for it.

TestEmployees.aspx

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="TestEmployees.aspx.vb" Inherits="Map2.TestEmployees" %>

Open in new window


TestEmployees.aspx.vb
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml

Public Class TestEmployees
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


        Dim dbConnString As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
        Dim dbConn As New SqlConnection(dbConnString)
        Dim strSQL As String, objCommand As SqlCommand
        Dim objDataReader As SqlDataReader
        Dim eventsXMLDoc As XmlTextWriter = New XmlTextWriter(Response.OutputStream, Encoding.UTF8)

        strSQL = "SELECT EmployeeID, LastName, FirstName" _
        & " FROM Employees " _
        & " ORDER BY EmployeeID"

        dbConn.Open()
        objCommand = New SqlCommand(strSQL, dbConn)
        objDataReader = objCommand.ExecuteReader()
        'generate XML
        eventsXMLDoc.Formatting = Formatting.Indented
        Response.ContentType = "text/xml"
        eventsXMLDoc.WriteStartDocument()
        eventsXMLDoc.WriteStartElement("Employees")
        While objDataReader.Read()
            eventsXMLDoc.WriteStartElement("Employee")
            eventsXMLDoc.WriteAttributeString("EmployeeID", objDataReader("EmployeeID"))
            eventsXMLDoc.WriteAttributeString("LastName", objDataReader("LastName"))
            eventsXMLDoc.WriteAttributeString("FirstName", objDataReader("FirstName"))
            eventsXMLDoc.WriteEndElement()
        End While
        eventsXMLDoc.WriteEndElement()
        eventsXMLDoc.Flush()
        eventsXMLDoc.Close()
        objDataReader.Close()
        dbConn.Close()

    End Sub

End Class

Open in new window


So when I run my page it runs just fine and looks like this:

my VB asp page when its run


C# Version

So now I'm doing the same example using C# as the language. This example is where I have a question.


TestEmployeesCS.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="TestEmployeesCS.aspx.cs" Inherits="Map2.TestEmployeesCS" %>

Open in new window


TestEmployeesCS.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Configuration;
using System.Text;


    public partial class TestEmployeesCS : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            string strConn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

            SqlConnection dbConn = new SqlConnection(strConn);
            string strSQL = null;
            SqlCommand objCommand = default(SqlCommand);

            SqlDataReader objDataReader = default(SqlDataReader);
            XmlTextWriter employeesXMLDoc = new XmlTextWriter(Response.OutputStream, Encoding.UTF8);

            strSQL = "SELECT EmployeeID, LastName, FirstName" + " FROM [Northwind].[dbo].[Employees] " + " ORDER BY EmployeeID";

            dbConn.Open();
            objCommand = new SqlCommand(strSQL, dbConn);
            objDataReader = objCommand.ExecuteReader();
            //generate XML
            employeesXMLDoc.Formatting = Formatting.Indented;
            Response.ContentType = "text/xml";
            employeesXMLDoc.WriteStartDocument();
            employeesXMLDoc.WriteStartElement("Employees");
            while (objDataReader.Read())
            {
                employeesXMLDoc.WriteStartElement("Employee");
                
                employeesXMLDoc.WriteAttributeString("EmployeeID", objDataReader["EmployeeID"]);
                employeesXMLDoc.WriteAttributeString("LastName", objDataReader["LastName"]);
                employeesXMLDoc.WriteAttributeString("FirstName", objDataReader["FirstName"]);


                // tested good but not correct formate
                //employeesXMLDoc.WriteStartElement("EmployeeID");
                //employeesXMLDoc.WriteValue(objDataReader["EmployeeID"]);


                employeesXMLDoc.WriteEndElement();
            }
            employeesXMLDoc.WriteEndElement();
            employeesXMLDoc.Flush();
            employeesXMLDoc.Close();
            objDataReader.Close();
            dbConn.Close();


        }
    }

Open in new window


I'm having problem with my C# example. I'm getting the errors underlined in red in the C# image below.

code with error
The output xml should exactly the same as the VB example above.

Anyone know what I'm doing wrong in my c# example?
0
Comment
Question by:maqskywalker
  • 2
3 Comments
 
LVL 34

Accepted Solution

by:
gr8gonzo earned 500 total points
ID: 40612933
Try casting the values as strings. For example:

employeesXMLDoc.WriteAttributeString("EmployeeID", (string)objDataReader["EmployeeID"]);
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 40613105
Use (int) instead of (string). If it still has trouble because it's expecting a string, just use the ToString() method after casting:

((int)value).ToString()
0
 
LVL 1

Author Comment

by:maqskywalker
ID: 40613110
thanks gonzo your solution worked.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now