×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Table with more then one row

Table with more then one row

Table with more then one row

(OP)
I have created a C# program that pulls data from a Database and dumps it down to XML which is then uploaded to a 3rd party.  I just ran into a snag that i am sure is an easy fix.  

My query will return mutiple rows and in one section of my code i need to pull the data from all of the rows and have it repeat an element in the XML file.

I have tried the if (sdr.HasRows) but i get an error.  If the Query has 10 rows then i need 10 elements in the XML file.  Does this make sense.

CODE

[writer.WriteStartElement("collection");
                            writer.WriteAttributeString("id", "sCondDataSet");

                            //if (sdr.HasRows)
                            //{
                              //  while (sdr.Read())
                                //{

                                    writer.WriteStartElement("record");

                                    writer.WriteStartElement("field");
                                    writer.WriteAttributeString("id", "IsRequired");
                                    writer.WriteString("True");
                                    writer.WriteEndElement();

                                    writer.WriteStartElement("field");
                                    writer.WriteAttributeString("id", "Category");
                                    writer.WriteString(Convert.ToString(sdr["Cond_Cat"]));
                                    writer.WriteEndElement();

                                    writer.WriteStartElement("field");
                                    writer.WriteAttributeString("id", "CondDesc");
                                    writer.WriteString(Convert.ToString(sdr["Cond_Desc"]));
                                    writer.WriteEndElement();

                                    //writer.WriteStartElement("field");
                                    //writer.WriteAttributeString("id", "Notes");
                                    //writer.WriteString("This is a Note");
                                    //writer.WriteEndElement();

                                    writer.WriteStartElement("field");
                                    writer.WriteAttributeString("id", "DoneDate");
                                    writer.WriteString(Convert.ToString(sdr["Cond_Clear_Date"]));
                                    writer.WriteEndElement();

                                    writer.WriteEndElement(); //Record
                                //}
                            //}
                            
                            
                            writer.WriteEndElement(); //Collection

RE: Table with more then one row

you can output a datatable to xml. with the table.WriteXml(), or something like that. and if the default xml doesn't work you could then apply XSLT and transform the xml into whatever form you would like

CODE

var xml = table.ToXml();
var formatted = Xml.XSLTransform(xml, path to template);
File.Create(name of file, formatted);
this is all from memory and I haven't worked with xslt or datatables for quite some time so I'm sure there are plenty of errors, but the idea will work.

Jason Meckley
Programmer

FAQ855-7190: Database Connection Management
FAQ732-7259: Keeping the UI responsive

RE: Table with more then one row

(OP)
I already have the xml portion all worked out.  

Its not as simple as just exporting the entire table to xml b/c i need to follow their schema.  

I just need to figure out a way to move the sql cursor to the next row.  

 

RE: Table with more then one row

Why can't you build a simple business object with appropriate XmlAttribute and XmlElement settings on the properties, a collection object to contain one or many of the business objects and serialize the whole thing in one go?

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

RE: Table with more then one row

(OP)
That is way above my head.  I am just a newbie working on a project.
 

RE: Table with more then one row

@nsanto17,

I'm assuming sdr is SqlDataReader

How I normally handle this is:

foreach(DataRow dr in sdr.Rows)
{
//do stuff to the dr...
myObj.Property1 = dr[1].Value.ToString();
myObj.Property2 = dr["Col1"].Value.ToString();
}

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

RE: Table with more then one row

(OP)
You are correct sdr is my datareader.  

it does not like my sdr.Rows.  i get the red squigly line under "Rows"

 

RE: Table with more then one row

It's what I initially wrote then deleted.


sdr.Read() //This advances to the next row.

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

RE: Table with more then one row

(OP)
So then it would be
foreach (sdr.Read())???
 

RE: Table with more then one row

You had the right bits, in the wrong order.


while (sdr.HasRows)
{
   //do writer stuff
   sdr.Read();
}

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

RE: Table with more then one row

CODE

// SqlDataReader sqlDataReader instantiated etc. somewhere above
if (sqlDataReader.HasRows)
{
    while(sqlDataReader.Read())
    {    // Do Stuff with sqlDataReader, i.e., string s = sqlDataReader["FieldName"].ToString()
    }
}

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

RE: Table with more then one row

Rhys,
From what I remember, the SqlDataReader will have a row "pre-loaded" when you execute the command, so doing a .Read() right off the bat will "skip" the first row.

nsanto,
Attaching the debugger and perusing the will tell you whether or not you need to put the .Read in the While() or as the very last step, like an int counter.

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

RE: Table with more then one row

(OP)
Rhys,

I tried that and i am getting Invalid attempt to read when no data is present.  however i checked the query and there is in fact 3 rows in the dataset.  Not sure what i am doing wrong...

CODE

      writer.WriteStartElement("collection");
                            writer.WriteAttributeString("id", "sCondDataSet");

                            //if (sdr.HasRows)
                            //foreach (sdr.Read())
                            if (sdr.HasRows)
                            {
                                //if (sdr.Read())

                                while (sdr.Read())
                                {

                                    writer.WriteStartElement("record");

                                    writer.WriteStartElement("field");
                                    writer.WriteAttributeString("id", "IsRequired");
                                    writer.WriteString("True");
                                    writer.WriteEndElement();

                                    writer.WriteStartElement("field");
                                    writer.WriteAttributeString("id", "Category");
                                    writer.WriteString(Convert.ToString(sdr["Cond_Cat"]));
                                    writer.WriteEndElement();

                                    writer.WriteStartElement("field");
                                    writer.WriteAttributeString("id", "CondDesc");
                                    writer.WriteString(Convert.ToString(sdr["Cond_Desc"]));
                                    writer.WriteEndElement();

                                    //writer.WriteStartElement("field");
                                    //writer.WriteAttributeString("id", "Notes");
                                    //writer.WriteString("This is a Note");
                                    //writer.WriteEndElement();

                                    writer.WriteStartElement("field");
                                    writer.WriteAttributeString("id", "DoneDate");
                                    writer.WriteString(Convert.ToString(sdr["Cond_Clear_Date"]));
                                    writer.WriteEndElement();

                                    writer.WriteEndElement(); //Record
                                }
                            }


                            writer.WriteEndElement(); //Collection

RE: Table with more then one row

<cough>

Quote:


Qik3Coder (Programmer) 27 Jul 11 11:26  
You had the right bits, in the wrong order.

while (sdr.HasRows)
{   
//do writer stuff   
sdr.Read();
}

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

RE: Table with more then one row

(OP)
Jmeckley,

That is way above my head.  I am working with code that was given to me by the 3rd party.   

RE: Table with more then one row

which part...
exporting a datatable to xml?
transforming xml with xslt?
saving xml (aka a string) to file?

it would look something like this

CODE

var table = GetDataTable();
var xslt = new XslTransform();
xslt.Load(uri to transform file);

using(var stream = new MemoryStream())
using(var writer = XmlWriter.Create(stream)){
using(var output = new XmlTextWriter(file name, encoding))
{
   table.WriteXml(writer);

   var xpathdoc = new XPathDocument(stream);
   xslt.Transform(xpathdoc, null, output, null);
}
and i'm sure there is a cleaner approach than that too.

Jason Meckley
Programmer

FAQ855-7190: Database Connection Management
FAQ732-7259: Keeping the UI responsive

RE: Table with more then one row

(OP)
Alright.  I got it do what i wanted by using the below Code.  Now its just skipping over the 1st row.


CODE


if (sdr.HasRows)
{
   while (sdr.Read())
   {
     //  My stuff Here
   }
}

RE: Table with more then one row

nsanto17,

Read EVERY post.

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close