Csharp/CSharp Tutorial/ADO.Net/DataSet Xml

Adding to DataSet and output to Xml

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);
    }
}

Output the XSD schema for the DataSet

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);
              }
          }

Persist a Dataset to an XML file

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");
  }
}

Pop DataSet

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();
            };
        }
    }

Read an xml file into a dataset

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);
  }
}

Save DataSet to disk with schema

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());
    }
}

Transform Data

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);
  }
}

Write Xml

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();
            } 
        }
    }