How to get data from SQL Server database in Xml format and from that Xml fill DataSet

Question

I am trying to get data from SQL Server in xml format and from that xml fill a dataset.

Here is my query:

SELECT * 
FROM TblAcademicYear 
FOR XML RAW('AcademicYear'), ELEMENTS;

This query give me following output :

 <AcademicYear>
    <AcademicYearId>3</AcademicYearId>
    <AcademicYearName>دو ‌ہزار ‌پندرہ</AcademicYearName>
    <StartingYear>2015-01-01</StartingYear>
   <EndingYear>2015-12-31</EndingYear>
   <Comments>دو ‌ہزار ‌پندرہ ‌کا ‌تعلیم ‌سال</Comments>
  <RCO>2014-07-02</RCO>
  <UserID>2</UserID>
</AcademicYear>

And my C# code is :

SqlConnection conn = new SqlConnection(ConnectionString);

SqlCommand cmd = new SqlCommand();
System.Xml.XmlReader xmlreader;

try
{
    cmd.Connection = conn;
    conn.Open();
    cmd.CommandText = _Query;
    xmlreader = cmd.ExecuteXmlReader();
    conn.Close();

    DataSet ds = new DataSet();
    dt.Columns.Add("AcademicYearId", typeof(string));
    dt.Columns.Add("AcademicYearName", typeof(string));
    dt.Columns.Add("StartingYear", typeof(string));
    dt.Columns.Add("EndingYear", typeof(string));
    dt.Columns.Add("Comments", typeof(string));
    dt.Columns.Add("RCO", typeof(string));
    dt.Columns.Add("UserID", typeof(string));

    ds.Tables.Add(dt);

    ds.ReadXml(xmlreader);
}
catch (Exception ex)
{
    throw ex;
}
finally
{
    if (conn.State != ConnectionState.Closed)
    {
       conn.Close();
    }
}

and the above code run perfectly but it does not give me the data

Any help would be appreciated .....

Why on earth do you want to output into XML and then read that back in?? You could just fill the dataset directly from the SQL query and not have to worry with XML at all ..... — marc_s
– marc_s, Commented Sep 16, 2014 at 12:40
@marc_s : Actually i am working on Web service(WCF) someone told me that not to use DataTable/DataSet in Web service(WCF) beacuse of interoperability so that's why i thought XML is Best In WCF....Am i right? — Khurram Ali
– Khurram Ali, Commented Sep 19, 2014 at 12:12
No, not really - WCF will easily and happily convert any normal datatype to XML for transfer - having XML to start with is actually more of a hindrance than a benefit! But in a WCF service, I wouldn't use .NET heavyweight types like a DataTable either - use proper POCO (plain old CLR objects) and lists/collections thereof - don't use DataTable or DataSet or any of those.... — marc_s
– marc_s, Commented Sep 19, 2014 at 12:15

Khurram Ali · Accepted Answer · 2014-09-22 11:19:47Z

SqlConnection conn = new SqlConnection(ConnectionString);

SqlCommand cmd = new SqlCommand();
System.Xml.XmlReader xmlreader;
XmlDataDocument xmlDataDoc = new XmlDataDocument();
try
{
    cmd.Connection = conn;
    conn.Open();
    cmd.CommandText = _Query;
    xmlreader = cmd.ExecuteXmlReader();
    DataSet ds = new DataSet();
    dt.Columns.Add("AcademicYearId", typeof(string));
    dt.Columns.Add("AcademicYearName", typeof(string));
    dt.Columns.Add("StartingYear", typeof(string));
    dt.Columns.Add("EndingYear", typeof(string));
    dt.Columns.Add("Comments", typeof(string));
    dt.Columns.Add("RCO", typeof(string));
    dt.Columns.Add("UserID", typeof(string));

    ds.Tables.Add(dt);
    while(xmlreader.Read()
    {
      xmlDataDoc.DataSet.ReadXml(xmlreader);
    }
    ds = xmlDataDoc.DataSet;
    xmlreader.Close();
    conn.Close();
}
catch (Exception ex)
{
    throw ex;
}
finally
{
    if (conn.State != ConnectionState.Closed)
    {
       conn.Close();
    }
}

Krishnraj Rana · Accepted Answer · 2014-09-22 10:10:15Z

0

You have not get data because you closed the connection before read.

DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("AcademicYearId", typeof(string));
dt.Columns.Add("AcademicYearName", typeof(string));
dt.Columns.Add("StartingYear", typeof(string));
dt.Columns.Add("EndingYear", typeof(string));
dt.Columns.Add("Comments", typeof(string));
dt.Columns.Add("RCO", typeof(string));
dt.Columns.Add("UserID", typeof(string));
ds.Tables.Add(dt);

cmd.Connection = conn;
conn.Open();
cmd.CommandText = _Query;
xmlreader.Read();    -- // Add this line
xmlreader = cmd.ExecuteXmlReader();
// conn.Close();  -- Remove this line and add it at the end   
ds.ReadXml(xmlreader);
conn.Close();

You have to invoke read() method of xmlreader.

edited Sep 22, 2014 at 10:10

answered Sep 16, 2014 at 12:28

Krishnraj Rana

6,6583 gold badges33 silver badges37 bronze badges

1 Comment

Krishnraj Rana Over a year ago

@KhurramAli: You have to invoke read() method of xmlreader. I have updated my answer... and sorry for the late reply as busy with other project... :)

Collectives™ on Stack Overflow

How to get data from SQL Server database in Xml format and from that Xml fill DataSet

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related