Solved

using datareader to output xml in C#

Posted on 2015-02-16
3
131 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 35

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 35

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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 antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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