How do I reference a DataRow element by name in a DataSet retrieved from SQL?

Question

So I am building some XML using a XmlWriter and a DataSet but when it comes time to loop through each DataRow in the DataSet I can't figure out how do reference like "userid" and such that come back from the stored procedure. In page code I see them doing it as Eval("userid") or whatever which I am using the same stored procedure, but I am using it in an ASHX now... see the 'WHAT GOES HERE??' in the code below...

DataSet getData;
        getData = SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString, CommandType.StoredProcedure, "Course_NewReportGet_Get_Sav", objPAra)

            //COUNT NUMBER OF RESULTS FOR COUNT ATTRIBUTE (must add!)

        XmlWriterSettings settings = new XmlWriterSettings();
        settings.Indent = true;
        settings.IndentChars = ("    ");
        using(XmlWriter writer = XmlWriter.Create("data.xml", settings))
        {
            writer.WriteStartElement("changes");
            writer.WriteAttributeString("clientname", foundCompany.CompanyName);
            writer.WriteAttributeString("clientid", foundCompany.Abbreviation);
            //writer... INSERT COUNT ATTRIBUTE

            foreach(DataRow dr in getData.Tables)
            {
                 writer.WriteStartElement("change");
                 writer.WriteStartElement("user");
                 writer.WriteAttributeString("userid", dr... WHAT GOES HERE??;

            }

            writer.WriteEndElement();
        }

Answer 1

dr["userid"]

ref: http://msdn.microsoft.com/en-us/library/system.data.datarow.item.aspx

Answer 2

It is pretty straightforward. An integer column in your datarow (dr) would be accessed with:

int someVal = (int)dr["ColumnName"];

Answer 3

First off, your foreach is wrong. You need to loop over getData.Tables[0].Rows. If there's more than 1 table, you need to loop over getData.Tables.

But, the answer is it's an indexed property. So, dr["userId"] will get you the value.

foreach (DataRow dr in getData.Tables[0].Rows) {
   / * blah, blah */
   writer.WriteAttributeString("userId", dr["userId"]);
}

Answer 4

Your inner foreach is the issue.

Should be

if(getData.Tables.Count > 0){
   foreach(DataRow dr in getData.Tables[0].Rows){
      writer.WriteAttributeString("userid", dr["UserID"]);
   }
}

Answer 5

You're going to want to add an iteration level - you're currently iterating over Tables in your foreach(DataRow dr in getData.Tables), the DataRow objects are one level below.