Post
Ask Question

Insert Bulk Data into SQL Server in ASP.NET using OPENXML

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                       
  5.                         
  6. BEGIN TRY      
  7.       
  8. DECLARE @index int        
  9.       
  10. EXEC sp_xml_preparedocument @index OUTPUT, @CustomerXml;       
  11.       
  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.  );       
  29.       
  30. EXEC sp_xml_removedocument @index            
  31.       
  32. COMMIT TRANSACTION                        
  33.                     
  34. END TRY                      
  35.                     
  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;//  
  10.   
  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();  
  25.   
  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)));  
  32.   
  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");  
  38.   
  39.             grdBulk.DataSource = dt;  
  40.             ViewState["vsCustomer"] = dt;  
  41.             grdBulk.DataBind();  
  42.   
  43.         }  
  44.   
  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.


Similar Articles