如何以Xml格式从SQL Server数据库以及从该Xml填充数据集中获取数据
本文关键字:数据 Xml 填充 数据集 集中 获取 Server 格式 SQL 数据库 | 更新日期: 2023-09-27 18:22:06
我正试图从SQL Server获取xml格式的数据,并从该xml填充数据集。
这是我的问题:
SELECT *
FROM TblAcademicYear
FOR XML RAW('AcademicYear'), ELEMENTS;
这个查询给我以下输出:
<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>
我的C#代码是:
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();
}
}
上面的代码运行得很好,但它没有给我数据
如有任何帮助,我们将不胜感激。。。。。
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();
}
}
您没有得到数据,因为您在读取之前关闭了连接。
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();
您必须调用xmlreader的read()方法。