Table with more then one row

[nsanto17]

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.

[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

 

[jmeckley]

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

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
faq732-7259

 

[nsanto17]

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.

 

[Rhys666]

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

 

[nsanto17]

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

 

[Qik3Coder]

@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

 

[nsanto17]

You are correct sdr is my datareader.

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

 

[Qik3Coder]

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

 

[nsanto17]

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

 

[Qik3Coder]

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

 

[Rhys666]

// 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

 

[Qik3Coder]

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

 

[nsanto17]

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...

      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

 

[Qik3Coder]

<cough>

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

 

[jmeckley]

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

that's why you would apply the xsl template and transfrom your xml into their xml.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[nsanto17]

Jmeckley,

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

 

[jmeckley]

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

it would look something like this

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
faq732-7259

 

[nsanto17]

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

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

 

[Qik3Coder]

nsanto17,

Read EVERY post.

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