Csharp/CSharp Tutorial/ADO.Net/ResultSet to Xml
Содержание
Convert the result from a query to XML and output
<source lang="csharp">using System; using System.Xml; using System.Data; using System.Data.SqlClient; class MainClass {
public static void Main(string[] args)
{
using (SqlConnection con = new SqlConnection()) {
con.ConnectionString = @"Data Source = .\sqlexpress;Database = Northwind; Integrated Security=SSPI";
using (SqlCommand com = con.CreateCommand()) {
com.rumandType = CommandType.Text;
com.rumandText = "SELECT ID, FirstName" +
" FROM Employee FOR XML AUTO";
con.Open();
using (XmlReader reader = com.ExecuteXmlReader())
{
while (reader.Read())
{
Console.Write("Element: " + reader.Name);
if (reader.HasAttributes)
{
for (int i = 0; i < reader.AttributeCount; i++)
{
reader.MoveToAttribute(i);
Console.Write(" {0}: {1}",
reader.Name, reader.Value);
}
reader.MoveToElement();
Console.WriteLine(Environment.NewLine);
}
}
}
}
}
}
}</source>
Convert the result from a query to XML and output (Disconnected mode)
<source lang="csharp">using System; using System.Xml; using System.Data; using System.Data.SqlClient; class MainClass {
public static void Main(string[] args)
{
XmlDocument doc = new XmlDocument();
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
SqlCommand com = con.CreateCommand();
com.rumandType = CommandType.Text;
com.rumandText = "SELECT ID, FirstName FROM Employee FOR XML AUTO";
con.Open();
XmlReader reader = com.ExecuteXmlReader();
doc.LoadXml("<results></results>");
XmlNode newNode = doc.ReadNode(reader);
while (newNode != null)
{
doc.DocumentElement.AppendChild(newNode);
newNode = doc.ReadNode(reader);
}
}
Console.WriteLine(doc.OuterXml);
}
}</source>
Save data stored in table to xml file
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {
static void Main(string[] args)
{
string connString = @"server = .\sqlexpress;integrated security = true;database = northwind";
string qry = @"select productname,unitprice from products";
SqlConnection conn = new SqlConnection(connString);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(qry, conn);
conn.Open();
DataSet ds = new DataSet();
da.Fill(ds, "products");
ds.WriteXml(@"c:\productstable.xml");
}
catch(Exception e)
{
Console.WriteLine("Error: " + e);
}
finally
{
conn.Close();
}
}
}</source>
SqlCommand.ExecuteXmlReader
<source lang="csharp">using System; using System.Xml; using System.Data; using System.Data.SqlClient; class MainClass {
public static void ConnectedExample() {
using (SqlConnection con = new SqlConnection()) {
con.ConnectionString = @"Data Source = .\sqlexpress;Database = Northwind; Integrated Security=SSPI";
using (SqlCommand com = con.CreateCommand()) {
com.rumandType = CommandType.Text;
com.rumandText = "SELECT CustomerID, CompanyName FROM Customers FOR XML AUTO";
con.Open();
using (XmlReader reader = com.ExecuteXmlReader()) {
while (reader.Read()) {
Console.Write("Element: " + reader.Name);
if (reader.HasAttributes) {
for (int i = 0; i < reader.AttributeCount; i++) {
reader.MoveToAttribute(i);
Console.Write(" {0}: {1}",reader.Name, reader.Value);
}
reader.MoveToElement();
}
}
}
}
}
}
public static void Main(string[] args) {
ConnectedExample();
}
}</source>
