Read XML and bulk insert into Database table using C# and VB.Net

lejogeorge
 
on Nov 17, 2022 09:43 PM
303 Views

Bulk inserting by passing as xml and insert into two table master and child table.

Insert first row in first table and first row into second table with primary key of master table like that insert each record one by one in both table.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Shivam.M
 
on Nov 18, 2022 02:27 AM
on Nov 18, 2022 02:49 AM

Hi lejogeorge,

Please refer below sample.

Database

CREATE TABLE Students
(
	[Id] Int Primary Key IDENTITY,
	[Name] Varchar(50),
)
GO
CREATE TABLE Student_Details
(
	[Id] int FOREIGN KEY REFERENCES Students(Id),
	[Country] Varchar(50)
)
GO
CREATE PROCEDURE [InsertStudentDetails]
      @Name varchar(50),
      @Country varchar(50)
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @Id INT
      INSERT INTO Students ([Name]) VALUES (@Name)
      SELECT @Id = SCOPE_IDENTITY()
      INSERT INTO Student_Details ([Id], [Country]) VALUES (@Id, @Country)
END

XML

<?xml version="1.0" encoding="utf-8" ?>
<Students>
  <Student>
    <Id>1</Id>
    <Name>John Hammond</Name>
    <Country>United States</Country>
  </Student>
  <Student>
    <Id>2</Id>
    <Name>Mudassar Khan</Name>
    <Country>India</Country>
  </Student>
  <Student>
    <Id>3</Id>
    <Name>Suzanne Mathews</Name>
    <Country>France</Country>
  </Student>
  <Student>
    <Id>4</Id>
    <Name>Robert Schidner</Name>
    <Country>Russia</Country>
  </Student>
</Students>

HTML

<asp:GridView ID="gvStudents" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>

Namespaces

C#

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

VB.Net

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

Code

C#

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

private void BindGrid()
{
    DataSet ds = new DataSet();
    ds.ReadXml(Server.MapPath("~/Students.xml"));
    gvStudents.DataSource = ds;
    gvStudents.DataBind();
    foreach (DataRow row in ds.Tables[0].Rows)
    {
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand("InsertStudentDetails", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Name", row["Name"]);
                cmd.Parameters.AddWithValue("@Country", row["Country"]);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

VB.Net

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

Private Sub BindGrid()
    Dim ds As DataSet = New DataSet()
    ds.ReadXml(Server.MapPath("~/Students.xml"))
    gvStudents.DataSource = ds
    gvStudents.DataBind()

    For Each row As DataRow In ds.Tables(0).Rows
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(conString)
            Using cmd As SqlCommand = New SqlCommand("InsertStudentDetails", con)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@Name", row("Name"))
                cmd.Parameters.AddWithValue("@Country", row("Country"))
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    Next
End Sub

Screenshot