How to handle Storedprocedure in c# which return xml data. RSS

• • Reply
  

  ganesan2510

  None

  0 Points

  2 Posts

  How to handle Storedprocedure in c# which return xml data.

  Mar 28, 2012 05:43 AM|LINK

  i am having stored procedure which return the below xml

   <TRR>
  <CONTROL>
      <Batch_ID>03/22/2012 20:38:51:527</Batch_ID>
      <Process_Date>03/22/2012</Process_Date>
      <Status>I</Status>
      <Source>sp_MPC_TRR_OutBound</Source>
      <DetailsCount>41</DetailsCount>
    </CONTROL>
   <Detail>
      <RouteId>1</RouteId>
      <OriginProduct>Natural Gasoline</OriginProduct>
      <Segment>
        <SegmentId>3</SegmentId>
        <Orgin>Galena Park, TX - Kinder Morgan Terminal</Orgin>
      </Segment>
      <ToDate>2012-04-30T23:59:00</ToDate>
    </Detail>
     <Detail>
      <RouteId>2</RouteId>
      <OriginProduct>Natural Gasoline</OriginProduct>
      <Segment>
        <SegmentId>4</SegmentId>
        <Orgin>Galena Park, TX - Kinder Morgan Terminal</Orgin>
      </Segment>
      <ToDate>2012-04-30T23:59:00</ToDate>
    </Detail>
    </TRR>

  and i am have 2 class as below

  public class SRADBData
      {
          public virtual int routeId { get; set; }
          public virtual string originProduct { get; set; }
          public virtual Segment segment { get; set; }
          public virtual DateTime toDate { get; set; }
      }

      public class Segment
      {
          public virtual int segmentId { get; set; }
          public virtual string orgin { get; set; }
      }

  and i am having a list object for SRADBData class as below

  List<SRADBData> lstData= new  List<SRADBData> ();

  what i want to do is
  execute the storedprocedure and get the xml and assign the data to  lstData object.

• • Reply
  

  Ashutosh Pat...

  Contributor

  5737 Points

  1105 Posts

  Re: How to handle Storedprocedure in c# which return xml data.

  Mar 28, 2012 06:11 AM|LINK

  please try to use the code below, the ParseXML/XMLFile.xml is the file where ive pasted the content of your xml:

  XDocument doc = XDocument.Load(Server.MapPath("~/ParseXML/XMLFile.xml"));
          List<SRADBData> objList = new List<SRADBData>();
          var listMain = doc.Elements("TRR").Elements("Detail");
          foreach (XElement xll in listMain)
          {
              if (string.Compare(xll.Name.ToString(), "Detail", true) == 0)
              {
                  SRADBData objSRA = new SRADBData();
                  Segment objSeg = new Segment();
                  if (xll.Elements("Segment") != null)
                  {
                      objSeg.orgin = xll.Element("Segment").Element("Orgin").Value.ToString();
                      objSeg.segmentId = Convert.ToInt32(xll.Element("Segment").Element("SegmentId").Value);
                  }
                  objSRA.originProduct = xll.Element("OriginProduct").Value.ToString();
                  objSRA.routeId = Convert.ToInt32(xll.Element("RouteId").Value);
                  objSRA.toDate = Convert.ToDateTime(xll.Element("ToDate").Value);
                  objSRA.segment = objSeg;
                  objList.Add(objSRA);
              }
          }

  but before using it make sure you've used these namespaces:

  using System.Linq;
  using System.Xml.Linq;

  Ashutosh Pathak
  Blog: http://catchcode.blogspot.com
  Please mark it as answer if it helps, as clicking on the button can save time of others :)
  MCP,MCAD,MCSD,MCTS
  • Edited by ashutosh pathak on Mar 28, 2012 06:15 AM
  • Marked as answer by decker dong - msft on Apr 03, 2012 01:24 AM