Here, I will bind static data to a grid view for a demo but you may bind data to a grid view dynamically.

Step 1: The Table

Create the table as in the following:

  1. CREATE TABLE tblCustomer(
  2. Id int NOT NULL,
  3. name varchar(50) NOT NULL,
  4. gender varchar(6) NOT NULL,
  5. email varchar(30) NOT NULL,
  6. country varchar(30) NOT NULL,
  7. city varchar(30) NOT NULL
  8. )

Step 2: Stored Procedure

Create the Stored Procedure as in the following:

  1. CREATE procedure Pr_SaveCustomer
  2. @CustomerXml xml
  3. as
  4. BEGIN TRANSACTION
  6. BEGIN TRY
  8. DECLARE @index int
  10. EXEC sp_xml_preparedocument @index OUTPUT, @CustomerXml;
  12. insert into tblCustomer
  13. (Id,
  14. name,
  15. gender,
  16. email,
  17. country,
  18. city)
  19. select Id,Name,Gender,Email,Country,City
  20. FROM OPENXML (@index, '/DocumentElement/Table1',2)
  21. WITH (
  22. Id int,
  23. Name varchar(50),
  24. Gender varchar(6),
  25. Email varchar(30),
  26. Country varchar(30),
  27. City varchar(30)
  28. );
  30. EXEC sp_xml_removedocument @index
  32. COMMIT TRANSACTION
  34. END TRY
  36. BEGIN CATCH
  37. ROLLBACK TRANSACTION
  38. END CATCH

Step 3: UI Design

Create the UI Design as in the following:

  1. <body>
  2. <form id="form1" runat="server">
  3. <div>
  4. <table border="1">
  5. <tr>
  6. <td>
  7. <asp:GridView ID="grdBulk" runat="server" AutoGenerateColumns="False" CellPadding="3"
  8. BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
  9. CellSpacing="2">
  10. <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
  11. <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
  12. <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
  13. <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
  14. <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
  15. <SortedAscendingCellStyle BackColor="#FFF1D4" />
  16. <SortedAscendingHeaderStyle BackColor="#B95C30" />
  17. <SortedDescendingCellStyle BackColor="#F1E5CE" />
  18. <SortedDescendingHeaderStyle BackColor="#93451F" />
  19. <Columns>
  20. <asp:BoundField DataField="Id" HeaderText="Id" />
  21. <asp:BoundField DataField="Name" HeaderText="Name" />
  22. <asp:BoundField DataField="Gender" HeaderText="Gender" />
  23. <asp:BoundField DataField="Email" HeaderText="Email" />
  24. <asp:BoundField DataField="Country" HeaderText="Country" />
  25. <asp:BoundField DataField="City" HeaderText="City" />
  26. </Columns>
  27. </asp:GridView>
  28. </td>
  29. </tr>
  30. <tr>
  31. <td align="center">
  32. <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click"
  33. Style="background: #fff; border: 1px solid #000; text-shadow: 0px 0px 0px #000;
  34. text-align: center" />
  35. </td>
  36. </tr>
  37. </table>
  38. </div>
  39. </form>
  40. </body>

Check that the UI design looks as in the following:

gridview

Step 4: Code Behind

Create the Code Behind as in the following:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.Data;//
  8. using System.Data.SqlClient;
  9. using System.IO;//
  11. namespace Demo
  12. {
  13. public partial class BulkSave : System.Web.UI.Page
  14. {
  15. protected void Page_Load(object sender, EventArgs e)
  16. {
  17. if (!IsPostBack)
  18. {
  19. BindCustomer();
  20. }
  21. }
  22. private void BindCustomer()
  23. {
  24. DataTable dt = new DataTable();
  26. dt.Columns.Add(new DataColumn("Id", typeof(int)));
  27. dt.Columns.Add(new DataColumn("Name", typeof(string)));
  28. dt.Columns.Add(new DataColumn("Gender", typeof(string)));
  29. dt.Columns.Add(new DataColumn("Email", typeof(string)));
  30. dt.Columns.Add(new DataColumn("Country", typeof(string)));
  31. dt.Columns.Add(new DataColumn("City", typeof(string)));
  33. dt.Rows.Add(1, "Rajesh", "Male", "[email protected]", "India", "Hyderabad");
  34. dt.Rows.Add(2, "Vijay", "Male", "[email protected]", "India", "Bengluru");
  35. dt.Rows.Add(3, "Rekha", "Female", "[email protected]", "India", "Pune");
  36. dt.Rows.Add(4, "Kiran", "Female", "[email protected]", "India", "Mumbai");
  37. dt.Rows.Add(5, "Suraj", "Male", "[email protected]", "India", "Chennai");
  39. grdBulk.DataSource = dt;
  40. ViewState["vsCustomer"] = dt;
  41. grdBulk.DataBind();
  43. }
  45. protected void btnSubmit_Click(object sender, EventArgs e)
  46. {
  47. if (grdBulk.Rows.Count > 0)
  48. {
  49. DataTable dt = ViewState["vsCustomer"] as DataTable;
  50. if (dt != null)
  51. {
  52. if (dt.Rows.Count > 0)
  53. {
  54. string CustomerXML = DatatableToXml(dt);
  55. if (CustomerXML != null)
  56. {
  57. using (SqlConnection con = new SqlConnection("Data Source=.;Trusted_Connection=true;Database=test"))
  58. {
  59. using (SqlCommand cmd = new SqlCommand())
  60. {
  61. cmd.Connection = con;
  62. cmd.CommandType = CommandType.StoredProcedure;
  63. cmd.CommandText = "Pr_SaveCustomer";
  64. cmd.Parameters.Add("@CustomerXml", SqlDbType.Xml, -1).Value = CustomerXML;
  65. con.Open();
  66. int i = cmd.ExecuteNonQuery();
  67. con.Close();
  68. if (i > 0)
  69. {
  70. ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Record Saved Successfully')", true);
  71. }
  72. }
  73. }
  74. }
  75. }
  76. }
  77. }
  78. }
  79. public string DatatableToXml(DataTable dt)
  80. {
  81. MemoryStream ms = new MemoryStream();
  82. dt.WriteXml(ms, true);
  83. ms.Seek(0, SeekOrigin.Begin);
  84. StreamReader sr = new StreamReader(ms);
  85. string strXML;
  86. strXML = sr.ReadToEnd();
  87. return (strXML);
  88. }
  89. }
  90. }
Check for data in the tblCustomer table in the database.

output

Yes, the bulk records are inserted into the table.

I hope you like this article and understood how to insert bulk data into SQL Server using OPENXML in ASP.NET.