Tuesday, 16 September 2014

How to get Data From Sql Database in Xml Format and from that Xml Fill DataSet



I am trying to get data from sql server in xml format and form that xml filling dataset


here is my Simple 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 .....


No comments:

Post a Comment