Csharp/CSharp Tutorial/ADO.Net/DataSet Xml
Содержание
Adding to DataSet and output to Xml
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; public class SaveDiffGram {
private static string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"; public static void Main() { string SQL = "SELECT TOP 3 CategoryID, CategoryName, Description FROM Categories"; SqlConnection con = new SqlConnection(connectionString); SqlCommand com = new SqlCommand(SQL, con); SqlDataAdapter adapter = new SqlDataAdapter(com); DataSet ds = new DataSet("Nortwind"); con.Open(); adapter.FillSchema(ds, SchemaType.Mapped, "Categories"); adapter.Fill(ds, "Categories"); con.Close(); DataRow row = ds.Tables["Categories"].Rows[0]; row["CategoryName"] = "Computer"; row["Description"] = "Java, Oracle"; ds.Tables["Categories"].Rows[1].Delete(); row = ds.Tables["Categories"].NewRow(); row["CategoryName"] = "Tools"; row["Description"] = "JUnit"; ds.Tables["Categories"].Rows.Add(row); ds.WriteXml("mydata.xml" , XmlWriteMode.DiffGram); ds.WriteXml(Console.Out , XmlWriteMode.DiffGram); }
}</source>
Output the XSD schema for the DataSet
<source lang="csharp">using System;
using System.Data; using System.Data.SqlClient; class Program { static void Main(string[] args) { string xsdFileName = "AdventureWorks.xsd"; string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlText = "SELECT * FROM Sales.SalesOrderHeader;SELECT * FROM Sales.SalesOrderDetail;"; SqlDataAdapter da = new SqlDataAdapter(sqlText, sqlConnectString); da.TableMappings.Add("Table", "SalesOrderHeader"); da.TableMappings.Add("Table1", "SalesOrderDetail"); DataSet ds = new DataSet("AdventureWorks"); da.FillSchema(ds, SchemaType.Mapped); ds.Relations.Add("SalesOrderHeader_SalesOrderDetail", ds.Tables["SalesOrderHeader"].Columns["SalesOrderID"], ds.Tables["SalesOrderDetail"].Columns["SalesOrderID"]); ds.WriteXmlSchema(xsdFileName); } }</source>
Persist a Dataset to an XML file
<source lang="csharp">using System; using System.Data; using System.Data.OleDb; class MainClass {
static void Main(string[] args) { OleDbConnection MyConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = c:\\Northwind.mdb"); OleDbDataAdapter MyAdapter = new OleDbDataAdapter("SELECT * FROM orders", MyConnection); DataSet MyDataSet = new DataSet(); MyAdapter.Fill(MyDataSet, "orders"); MyDataSet.WriteXml(@"c:\Sample.xml"); }
}</source>
Pop DataSet
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class PopDataSet { static void Main(string[] args) { string connString = @"server = .\sqlexpress;integrated security = true;database = northwind"; string sql = @"select productname,unitprice from products where unitprice < 20"; SqlConnection conn = new SqlConnection(connString); try { conn.Open(); SqlDataAdapter da = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet(); da.Fill(ds, "products"); DataTable dt = ds.Tables["products"]; foreach (DataRow row in dt.Rows) { foreach (DataColumn col in dt.Columns) Console.WriteLine(row[col]); } } catch (Exception e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); }; } }</source>
Read an xml file into a dataset
<source lang="csharp">using System; using System.IO; using System.Data; class MainClass {
static void Main(string[] args) { string MyXMLDoc = @"<title> MyExample</title>"; StringReader MyStringReader = new StringReader(MyXMLDoc); DataSet MyDataSet = new DataSet(); MyDataSet.ReadXml(MyStringReader); }
}</source>
Save DataSet to disk with schema
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; public class SaveDataSet {
private static string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"; public static void Main() { string SQL = "SELECT CategoryID, CategoryName,Description FROM Categories"; SqlConnection con = new SqlConnection(connectionString); SqlCommand com = new SqlCommand(SQL, con); SqlDataAdapter adapter = new SqlDataAdapter(com); DataSet ds = new DataSet("Nortwind"); con.Open(); adapter.FillSchema(ds, SchemaType.Mapped, "Categories"); adapter.Fill(ds, "Categories"); con.Close(); ds.WriteXmlSchema("mydata.xsd"); ds.WriteXml("mydata.xml"); ds.Reset(); ds.ReadXmlSchema("mydata.xsd"); ds.ReadXml("mydata.xml"); Console.WriteLine(ds.GetXml()); }
}</source>
Transform Data
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; using System.Xml; using System.Xml.Xsl; public class MainClass{
public static void Main(string [] args) { DataSet dataSet = new DataSet("Product"); SqlConnection connection = new SqlConnection("Initial Catalog=Product; Integrated Security=SSPI; User ID=sa"); SqlDataAdapter customersAdapter = new SqlDataAdapter("SELECT * FROM customers", connection); SqlDataAdapter couponsAdapter = new SqlDataAdapter("SELECT * FROM coupons", connection); SqlDataAdapter couponRedemptionsAdapter = new SqlDataAdapter("SELECT * FROM coupon_redemptions", connection); customersAdapter.Fill(dataSet, "customers"); couponsAdapter.Fill(dataSet, "coupons"); couponRedemptionsAdapter.Fill(dataSet, "coupon_redemptions"); XmlDataDocument doc = new XmlDataDocument(dataSet); XmlTextWriter writer = new XmlTextWriter(Console.Out); writer.Formatting = Formatting.Indented; XslTransform transform = new XslTransform(); transform.Load("Coupons.xsl"); transform.Transform(doc, null, writer); }
}</source>
Write Xml
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;
class WriteXML { 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("table.xml"); } catch (Exception e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); } } }</source>