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

Last Reply 8 months ago By dharmendr

Posted 8 months 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 8 months ago Modified on 8 months 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>