Import XML file to database in ASP.Net using C# and VB.Net

Hi Somen,

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

XML File

<?xml version="1.0" encoding="utf-8" ?>
<cities>
 <city>
    <id>1</id>
    <name>Mumbai</name>
 </city>
 <city>
    <id>2</id>
    <name>Delhi</name>
 </city>
 <city>
    <id>3</id>
    <name>Chennai</name>
 </city>
 <city>
    <id>4</id>
    <name>Kolkatta</name>
 </city>
</cities>

HTML

<asp:FileUpload ID="fuUpload" runat="server" />
<asp:Button Text="Upload" runat="server" OnClick="OnUpload" />
<hr />
<asp:GridView runat="server" ID="gvCities"></asp:GridView>

Namepsaces

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 OnUpload(object sender, EventArgs e)
{
    if (fuUpload.HasFile)
    {
        using (DataSet ds = new DataSet())
        {
            ds.ReadXml(fuUpload.PostedFile.InputStream);
            if (ds.Tables[0].Rows.Count > 0)
            {
                gvCities.DataSource = ds;
                gvCities.DataBind();

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                    string query = "INSERT INTO Cities VALUES (@Id,@Name)";
                    using (SqlConnection con = new SqlConnection(conString))
                    {
                        SqlCommand cmd = new SqlCommand(query, con);
                        cmd.Parameters.AddWithValue("@Id", ds.Tables[0].Rows[i]["id"].ToString());
                        cmd.Parameters.AddWithValue("@Name", ds.Tables[0].Rows[i]["name"].ToString());
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }
        }
    }
}

VB.Net

Protected Sub OnUpload(ByVal sender As Object, ByVal e As EventArgs)
    If fuUpload.HasFile Then
        Using ds As DataSet = New DataSet()
            ds.ReadXml(fuUpload.PostedFile.InputStream)
            If ds.Tables(0).Rows.Count > 0 Then
                gvCities.DataSource = ds
                gvCities.DataBind()
                For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
                    Dim query As String = "INSERT INTO Cities VALUES (@Id,@Name)"
                    Using con As SqlConnection = New SqlConnection(conString)
                        Dim cmd As SqlCommand = New SqlCommand(query, con)
                        cmd.Parameters.AddWithValue("@Id", ds.Tables(0).Rows(i)("id").ToString())
                        cmd.Parameters.AddWithValue("@Name", ds.Tables(0).Rows(i)("name").ToString())
                        con.Open()
                        cmd.ExecuteNonQuery()
                        con.Close()
                    End Using
                Next
            End If
        End Using
    End If
End Sub

Screenshot