In this blog we will learn how to extract XML from SQL Server using the DataSet class.

What is a DataSet?

  1. DataSet and DataTable are the main components of ADO .NET.
  2. DataSet is collection of DataTables.
  3. DataSet can have more than one Table in a single collection.
  4. It is in System.Data namespace.
  5. The DataSet object represents a disconnected data source state.

Coding

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
 namespace ExtracingXML

{

public partial class frmExtractxml : Form

{
public frmExtractxml()
{
InitializeComponent();
}
private void frmExtractxml_Load(object sender, EventArgs e)
{
string connstr = "Data Source =ServerName;"
+ " Initial Catalog =DatabaseName;User Id =UserName;Password = password";
using(SqlConnection conn = new SqlConnection(connstr))
{
SqlCommand cmd = new SqlCommand("SELECT * FROM EMP",conn);
conn.Open();
DataSet ds = new DataSet();
ds.DataSetName = "Employees";
ds.Load(cmd.ExecuteReader(),LoadOption.OverwriteChanges,"Employee");
ds.WriteXml("D:\\File.xml");
}

}

}
}

Explanation

string connstr = "Data Source =ServerName;" + " Initial Catalog =DatabaseName;User Id =UserName;Password = password";

This is the Connection String Information to the Database.

using(SqlConnection conn = new SqlConnection(connstr))

To create SqlConnection to the database.

SqlCommand cmd = new SqlCommand("SELECT * FROM EMP",conn);

Creating a SqlCommand to retrieve Employee Details with the connection defined.

conn.Open();

Opens the Connection

DataSet ds = new DataSet();

This code snippet creates an Instance of DataSet Class.

ds.DataSetName = "Employees";

Here we set the DataSet Name to Employees.

ds.Load(cmd.ExecuteReader(),LoadOption.OverwriteChanges,"Employee");

This DataSet Load Method Loads the DataSet from the DataSource, using an array of DataTable instances.

The LoadOption Enumeration controls how the values are applied to existing rows. The LoadOption enumeration has OverwriteChanges, PreserveChanges, andUpsert.ds.WriteXml("D:\\File.xml");

Finally, writing the data to the XML in a Location.

The Root node of the document is Employees, which corresponds to the DataSetName property.