ADO.NET allows you to work with XML data in multiple ways, including storing, retrieving, and manipulating XML within databases or datasets. Here are some key aspects of working with XML in ADO.NET:

1. Reading and Writing XML with DataSet

ADO.NET provides built-in support for working with XML through the DataSet class.

Writing DataSet to XML

using System;
using System.Data;

class Program
{
    static void Main()
    {
        // Create a sample DataSet
        DataSet ds = new DataSet("SampleDataSet");
        DataTable dt = new DataTable("Employees");

        // Add columns
        dt.Columns.Add("ID", typeof(int));
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Department", typeof(string));

        // Add rows
        dt.Rows.Add(1, "John Doe", "HR");
        dt.Rows.Add(2, "Jane Smith", "IT");

        // Add table to DataSet
        ds.Tables.Add(dt);

        // Write to XML file
        ds.WriteXml("employees.xml", XmlWriteMode.WriteSchema);

        Console.WriteLine("XML file created successfully.");
    }
}

Reading XML into DataSet

using System;
using System.Data;

class Program
{
    static void Main()
    {
        DataSet ds = new DataSet();
        ds.ReadXml("employees.xml");

        foreach (DataTable table in ds.Tables)
        {
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine($"ID: {row["ID"]}, Name: {row["Name"]}, Department: {row["Department"]}");
            }
        }
    }
}

2. Storing XML in SQL Server

If your database has an XML column, you can store and retrieve XML data using ADO.NET.

Inserting XML into SQL Server

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string";

        string xmlData = "<Employees><Employee><ID>1</ID><Name>John Doe</Name><Department>HR</Department></Employee></Employees>";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "INSERT INTO EmployeeRecords (XmlData) VALUES (@XmlData)";
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.Add("@XmlData", SqlDbType.Xml).Value = xmlData;
                conn.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("XML data inserted successfully.");
            }
        }
    }
}

Retrieving XML from SQL Server

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "SELECT XmlData FROM EmployeeRecords";
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string xmlData = reader["XmlData"].ToString();
                        Console.WriteLine("XML Data:");
                        Console.WriteLine(xmlData);
                    }
                }
            }
        }
    }
}

3. Using XmlReader with ADO.NET

Instead of working directly with DataSet, you can use XmlReader for better performance when handling large XML data.

Reading XML Using XmlReader

using System;
using System.Data.SqlClient;
using System.Xml;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "SELECT XmlData FROM EmployeeRecords";
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                conn.Open();
                using (XmlReader reader = cmd.ExecuteXmlReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader.ReadOuterXml());
                    }
                }
            }
        }
    }
}

Key Takeaways

• DataSet.WriteXml() and DataSet.ReadXml() allow easy XML data storage and retrieval.
• SQL Server supports XML data type, which can be manipulated using ADO.NET.
• XmlReader provides a more efficient way to read large XML data compared to DataSet.