Bulk Insert using GridView and Sql Server XML

As to my previous article, how to update bulk records. This is how to insert bulk records. More a less, its same only, But some what difference. As in bulk update we fetch records and display in grid. But in bulk insert, we have to first create an empty row for inserting bulk records.

Download source and store procedure script from here.

 

private void InsertEmptyRow()

{

  DataTable dt = new DataTable();

  DataRow dr = null;

 

  dt.Columns.Add(new DataColumn("CustName", typeof(string)));

  dt.Columns.Add(new DataColumn("CustPosition", typeof(string)));

  dt.Columns.Add(new DataColumn("CustCity", typeof(string)));

  dt.Columns.Add(new DataColumn("CustState", typeof(string)));

 

  for (int i = 0; i < 5; i++)

  {

      dr = dt.NewRow();

      dr["CustName"] = string.Empty;

      dr["CustPosition"] = string.Empty;

      dr["CustCity"] = string.Empty;

      dr["CustState"] = string.Empty;

      dt.Rows.Add(dr);

   }

 

    gvCustomer.DataSource = dt;

    gvCustomer.DataBind();

}

 

As, you see above code I am creating five empty rows initially which is empty. This method I am calling at page load event.

Note :- I am creating only five empty rows, you can create as much required for your bulk insert according to your requirement

 

And one more difference between, Bulk update and bulk insert is that as we fetch all records for bulk update, so we don’t have empty rows, but in insert we have five empty rows. One more thing, Suppose we insert three rows and two rows empty what would happen ?. For that I am checking if name field is empty or not, else don’t insert.

 

StringBuilder sb = new StringBuilder();

 sb.Append("<root>"); 

 for (int i = 0; i < gvCustomer.Rows.Count; i++)

 {

    TextBox txtName = gvCustomer.Rows[i].FindControl("txtName") as TextBox;

    TextBox txtPosition = gvCustomer.Rows[i].FindControl("txtPosition") as TextBox;

    TextBox txtCity = gvCustomer.Rows[i].FindControl("txtCity") as TextBox;

    TextBox txtState = gvCustomer.Rows[i].FindControl("txtState") as TextBox;

 

    if(txtName.Text.Length != 0)

      sb.Append("<row Name='" + txtName.Text.Trim() + "' Position='" + txtPosition.Text.Trim() +

         "' City='" + txtCity.Text.Trim() + "' State='" + txtState.Text.Trim() + "'/>");

 }

 sb.Append("</root>");

 

As you see, I am checking txtName if length is more then zero then insert record else skip it.  As other insert detail is simple.

 

string conStr = WebConfigurationManager.ConnectionStrings["BlogConnectionString"].ConnectionString;

 SqlConnection con = new SqlConnection(conStr);

 SqlCommand cmd = new SqlCommand("InsertCustomer", con);

 cmd.CommandType = CommandType.StoredProcedure;

 cmd.Parameters.AddWithValue("@XMLCustomer", sb.ToString());

 

 try

 {

   using (con)

   {

     con.Open();

     cmd.ExecuteNonQuery();

   }

 

   lblError.Text = "Record(s) Inserted successfully";

   lblError.ForeColor = System.Drawing.Color.Green;

 }

 catch (Exception ex)

 {

  lblError.Text = "Error Occured";

  lblError.ForeColor = System.Drawing.Color.Red;

  }

 

Store procedure also simple, just direct insert from XML structure what it consist, this is also difference between bulk update as in which we check in where condition of customer id matches or not

 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

CREATE PROCEDURE [dbo].[InsertCustomer]

(

 @XMLCustomer XML

)

AS

BEGIN

 

      INSERT INTO CUSTOMER

             (CustName,CustPosition,CustCity,CustState)             

      SELECT

            TempCustomer.Item.value('@Name', 'VARCHAR(50)'),

            TempCustomer.Item.value('@Position', 'VARCHAR(50)'),

            TempCustomer.Item.value('@City', 'VARCHAR(50)'),

            TempCustomer.Item.value('@State', 'VARCHAR(50)')

      FROM @XMLCustomer.nodes('/root/row') AS TempCustomer(Item)

 

RETURN 0

END

 

Note :-  In bulk update, we are passing customer id also, here its not required as it is auto increment.

 

  Screen Shot 1 ( Before Insert)

FirstINsert

 

Screen Shot  2 (After Insert)

Third

 

I hope you like this article about how to insert bulk records at a time. Like to have a feedback on this article.

 

Enhancement :- Proper validation is required for inserting bulk records. Example which field should be mandatory and which should not be.
Published Tuesday, January 26, 2010 11:27 AM by Manoj karkera

Comments

# re: Bulk Insert using GridView and Sql Server XML

Thursday, March 11, 2010 11:55 AM by simflex

I love your code Manoj. It is *exactly* what I have been looking now for weeks.

I do have one question, though. I filled up sample data and clicked the Insert button but got the following error:

Sys.Webforms.PageRequestManagerServerErrorExceptions: Object Reference not set to an instance of an object. Do you wish to debug? I clicked yes and it took me here:

           finally {

               if (_this._xmlHttpRequest != null) {

                   _this._xmlHttpRequest.onreadystatechange = Function.emptyMethod;

                   _this._xmlHttpRequest = null;

               }

             }

This leads to my more important question and that is, can I not do this with XML?

I just want direct insert into the database.

Thanks for a great job and I look forward to your response.

# re: Bulk Insert using GridView and Sql Server XML

Sunday, March 14, 2010 9:20 AM by Manoj karkera

follow this link

alpascual.com/.../how-to-fix-sys-webforms-pagerequestmanagerparsererrorexception-in-ajax

# re: Bulk Insert using GridView and Sql Server XML

Monday, March 15, 2010 4:44 PM by simflex

Your awesome code is designed to work specifically with your custom design  XML.

But can you make it work with a stored proc that takes user inputs and inserts into the db, like:

CREATE PROCEDURE [dbo].[InsertCustomer]

(

@CustName nvarchar(50),

@CustPosition nvarchar(50),

@CustCity nvarchar(50),

@CustState

)

AS

BEGIN

     INSERT INTO CUSTOMER

            (CustName,CustPosition,CustCity,CustState)              

    VALUES(@CustName,@CustPosition,@CustCity,@CustState)

END

Can you make your code work with this type of stored proc?

Please help because I really need your code for my church project.

Thanks a lot

# re: Bulk Insert using GridView and Sql Server XML

Thursday, March 18, 2010 11:04 PM by Manoj karkera

Sorry for delay, I am busy with project dates.check below all links

www.codeproject.com/.../BulkEditGridView.aspx

csharpdotnetfreak.blogspot.com/.../edit-multiple-records-gridview-checkbox.html

blogs.msdn.com/.../490868.aspx

# re: Bulk Insert using GridView and Sql Server XML

Wednesday, August 11, 2010 2:13 AM by Rohit Sakalle

Hi,

I am using the same concept. But I am having a issue when I am using special charactor in string or words like "rohit's " with ' then....it is giving me error XML parsing: line 5, character 53, illegal name character. Can I over come this problem as these charactors disturb the xml parsing. Please help. Thanks in advance

# re: Bulk Insert using GridView and Sql Server XML

Wednesday, August 11, 2010 2:23 AM by Manoj karkera

Best thing will be to do is encode and decode special character, which you can google it.

# re: Bulk Insert using GridView and Sql Server XML

Friday, April 08, 2011 9:23 AM by Mohan

Good Tutorial

Thanks a lot.