Export GridView (DataSet / DataTable) to XML File using C# and VB.Net in ASP.Net

Last Reply 25 days ago By dharmendr

Posted 25 days ago

hello,

i have gridview which is connected with sql database.

i want on button click event and export all gridview data to xmlfile

please advice

 

Posted 25 days ago Modified on 25 days ago

Hi nauna,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="EmployeeID" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
<br /><br />
<asp:Button ID="btnSubmit" runat="server" Text="Export To XML" OnClick="btnSubmit_Click" />

Namespaces

C#

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

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindGridView();
    }
}

private void BindGridView()
{
    string strCon = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strCon))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT TOP 5 EmployeeID,FirstName + ' ' + LastName AS 'Name',City,Country FROM Employees";
            using (SqlDataAdapter da = new SqlDataAdapter())
            {
                da.SelectCommand = cmd;
                DataSet ds = new DataSet();
                da.Fill(ds);
                this.gvEmployees.DataSource = ds;
                this.gvEmployees.DataBind();
            }
        }
    }
}

protected void btnSubmit_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable("Employee");
    for (int i = 0; i < this.gvEmployees.Columns.Count; i++)
    {
        dt.Columns.Add(new DataColumn(this.gvEmployees.Columns[i].HeaderText));
    }

    for (int i = 0; i < this.gvEmployees.Rows.Count; i++)
    {
        dt.Rows.Add();
        for (int j = 0; j < this.gvEmployees.Columns.Count; j++)
        {
            dt.Rows[i][j] = this.gvEmployees.Rows[i].Cells[j].Text;
        }
    }

    DataSet ds = new DataSet("Employees");
    ds.Tables.Add(dt);

    ds.WriteXml(Server.MapPath("~/Employees.xml"));
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindGridView()
    End If
End Sub

Private Sub BindGridView()
    Dim strCon As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(strCon)
        Using cmd As SqlCommand = New SqlCommand()
            cmd.Connection = con
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT TOP 5 EmployeeID,FirstName + ' ' + LastName AS 'Name',City,Country FROM Employees"
            Using da As SqlDataAdapter = New SqlDataAdapter()
                da.SelectCommand = cmd
                Dim ds As DataSet = New DataSet()
                da.Fill(ds)
                Me.gvEmployees.DataSource = ds
                Me.gvEmployees.DataBind()
            End Using
        End Using
    End Using
End Sub

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = New DataTable("Employee")
    For i As Integer = 0 To Me.gvEmployees.Columns.Count - 1
        dt.Columns.Add(New DataColumn(Me.gvEmployees.Columns(i).HeaderText))
    Next
    For i As Integer = 0 To Me.gvEmployees.Rows.Count - 1
        dt.Rows.Add()

        For j As Integer = 0 To Me.gvEmployees.Columns.Count - 1
            dt.Rows(i)(j) = Me.gvEmployees.Rows(i).Cells(j).Text
        Next
    Next

    Dim ds As DataSet = New DataSet("Employees")
    ds.Tables.Add(dt)

    ds.WriteXml(Server.MapPath("~/Employees.xml"))
End Sub

Generated XML

<?xml version="1.0" standalone="yes"?>
<Employees>
  <Employee>
    <Id>1</Id>
    <Name>Nancy Davolio</Name>
    <City>Seattle</City>
    <Country>USA</Country>
  </Employee>
  <Employee>
    <Id>2</Id>
    <Name>Andrew Fuller</Name>
    <City>Tacoma</City>
    <Country>USA</Country>
  </Employee>
  <Employee>
    <Id>3</Id>
    <Name>Janet Leverling</Name>
    <City>Kirkland</City>
    <Country>USA</Country>
  </Employee>
  <Employee>
    <Id>4</Id>
    <Name>Margaret Peacock</Name>
    <City>Redmond</City>
    <Country>USA</Country>
  </Employee>
  <Employee>
    <Id>5</Id>
    <Name>Steven Buchanan</Name>
    <City>London</City>
    <Country>UK</Country>
  </Employee>
</Employees>