In this blog we will learn how to extract XML from SQL Server using the
DataSet class.
What is a DataSet?
- DataSet and DataTable are the main components of ADO .NET.
- DataSet is collection of DataTables.
- DataSet can have more than one Table in a single collection.
- It is in System.Data namespace.
- 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.