Csharp/CSharp Tutorial/ADO.Net/DataSet Xml — различия между версиями

Материал из .Net Framework эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 15:19, 26 мая 2010

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>