Click here to Skip to main content
Click here to Skip to main content

Tagged as

Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function

, 6 Jul 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function

In this article I am going to show how you can insert bulk data by using DataTable of C# and OpenXML function available in Sql Server.
I got requirement that "Read data from the Excel file and than after validating data push all record in the database table". Other thing is when inserting data in database if there is failure during insertion of record, I have to rollback all inserted record.
To achieve the task I did as following


OpenXML

I created procedure which make use of OpenXML function of the sql server which allow to insert multiple record in one time. OpenXML require xml string of record to insert data in the database.

ALTER PROCEDURE [dbo].[Ins_Employee]    
(    @XmlString text    )    
AS    
BEGIN    
 SET NOCOUNT ON    
 BEGIN TRANSACTION    
 Begin Try    

  DECLARE @XMLDocPointer INT    
  EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @XmlString    

   INSERT INTO Employee
   (Name, Email, PhoneNo)    
   SELECT Name,Email,PhoneNo   
   FROM OPENXML(@XMLDocPointer,'/ROOT/DATA',2)    
   WITH  (Name VARCHAR(50),-- '@Name',     
         Email VARCHAR(50),-- '@Email',     
         PhoneNo VARCHAR(50) --'@PhoneNo')     

   EXEC sp_xml_removedocument @XMLDocPointer    
   COMMIT TRANSACTION    
   Return 0;     
 End Try    
 Begin Catch    
   ROLLBACK TRANSACTION    
 End Catch    
END 
As you see in above procedure OpenXML make use of xmlDocument as input which is get created by system define procedure sp_xml_preparedocument which take xmlString as input and return XmlDocument.
Once OpenXML done task of insertion sp_xml_removedocument system proceudre is require to remove that element.
All record get inserted in once by the OpenXML function as I used transaction if the one record insertion fails all inserted record get rollback.
Following line of the code used to execute code i.e stored procedure As you see in I am passing Element centric xml to the proceudre.
Exec Ins_Employee
 '<root>
  <data>
    <name>pranay</name>
    <email>pranayamr@gmail.com</email>
    <phoneno>99007007</phoneno>
  </data>
 </root>' 
Note
If you are passing XML string as Attribute centric in it as in procedure than you need to define variable so the select statement in procedure will be
SELECT Name,Email,PhoneNo   
   FROM OPENXML(@XMLDocPointer,'/ROOT/DATA',2)    
   WITH  (Name VARCHAR(50) '@Name',     
         Email VARCHAR(50) '@Email',     
         PhoneNo VARCHAR(50) '@PhoneNo')
Exec Ins_Employee
  '<root>    
   <data phoneno="99007007" name="pranay" email="pranayamr@gmail.com">   
  </data></root>'

Now after done with the database , code part of the application is as below.
Uploaded Excel File which contains Employee data

Presentation layer
Following function in presentation layer read data from the excel file, which is uploaded on server.
private void ReadAndInsertExcelData()
{
     int i;
     bool blValid = true;
     OleDbCommand ocmd;
     OleDbDataAdapter oda;
     DataTable dtDetails;
     DataSet dsDetails;

     OleDbConnection oconn = new OleDbConnection     
          (@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + 
               Server.MapPath("~/Upload/MonthlyActual.xls") + ";Extended 
               Properties='Excel 8.0;HDR=YES;IMEX=1'");
     try
     {
          ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
          oda = new OleDbDataAdapter(ocmd);
          dsDetails = new DataSet();
          oda.Fill(dsDetails, "DATA");
          dtDetails = dsDetails.Tables[0];
          dsDetails.DataSetName = "ROOT";
          i = 0;

          DataRow[] drLst = dtDetails.Select("(Name is null) or (Email is 
                              null) or (PhoneNo is null)");
          if (drLst.Count() > 0)
               blValid = false;
          if (blValid)
          {
               XMLController xMLController = new XMLController();
               xMLController.Ins(BaseLineType, dtDetails);
          }
     }
     catch 
     {
          lblMsg.Text = ex.Message;
          lblMsg.ForeColor = System.Drawing.Color.Red;
     }
     finally
     {
          lblMsg.Text = "Data Inserted Sucessfully";
          oda = null;
          dtDetails = null;
          dsDetails = null;
     }
}
Business Layer
Function below takes DataTable as input and generate XML string, As you see below I used StringWriter which use StringBuilder object, DataTable make use of StringWriter and write XML string in StringBuilder object.
public int Ins(DataTable pImportTable)
{
     int IsSuccess = -100;
     try
     {
          StringBuilder sbXMLString = new StringBuilder();
          System.IO.StringWriter sw = new System.IO.StringWriter
                                                  (sbXMLString);
          pImportTable.WriteXml(sw);

          DALXML dALManualCost = new DALXML();
          dALManualCost.Ins(sbXMLString.ToString());
          IsSuccess = dALManualCost.IsSuccess;
     }
     catch
     {
          throw;
     }
     return IsSuccess;
}
Note:Above method generate Element centric XML string.
Now if you want to write out the Attribute centric xml file you just need to replace the line of datatable.WriteXml with the below code for loop also you dont require to use the StringWriter object.
sbXMLString.Append("<root>");
          for (int i = 0; i < pImportTable.Rows.Count; i++)
          {
            sbXMLString.Append("<DATA ");
            sbXMLString.Append("Name='" + 
                         pImportTable.Rows[i][0].ToString().Trim() + "' ");
            sbXMLString.Append("Email='" + pImportTable.Rows
                         [i][1].ToString().Trim() + "' ");
            sbXMLString.Append("PhoneNo='" + 
                         pImportTable.Rows[i][2].ToString().Trim() + "' ");
            
            sbXMLString.Append(" />");
 
          }
          sbXMLString.Append("</root>");
DataLayer 
Now this layer call the stored procedure which pass the xmlstring of employee to database. Return parameter will tell that its successfull insert or not.
public void Ins(string pXMLString)
{
     try
     {
          Database db = CommonHelper.GetDataBaseInstance();
          DbCommand cmdXML = db.GetStoredProcCommand
                         (SP_INSERT_STAGINGMANUALCOSTMONTHLY);

          db.AddInParameter(cmdXML, "XmlString", DbType.String, 
                                                       pXMLString);
          db.AddParameter(cmdXML, "ret", DbType.Int32,                     
          ParameterDirection.ReturnValue, "", DataRowVersion.Current, 
                                                            IsSuccess);

          db.ExecuteNonQuery(cmdXML);
          IsSuccess = Convert.ToInt32(db.GetParameterValue(cmdXML, "ret"));
     }
     catch
     {
          IsSuccess = -100;
          throw;
     }
}
Note : This is the one technique I found useful to enter bulk amount of data in database in one transaction. There are also other available which might be more efficient than this.  

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pranay Rana
Software Developer (Senior) GMind Solusion
India India

Microsoft C# MVP (12-13)

 
Hey, I am Pranay Rana, working as a ITA in MNC. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5.5 years now.
 
For me def. of programming is : Programming is something that you do once and that get used by multiple for many years
 

You can visit my blog

StackOverFlow - http://stackoverflow.com/users/314488/pranay
My CV :- http://careers.stackoverflow.com/pranayamr
 
Awards:


Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralMy vote of 3 PinprofessionalMithiten10-Oct-13 2:35 
Questionfor winform PinmemberArifkaliyar8-Nov-12 15:31 
AnswerRe: for winform PinmemberPranay Rana8-Nov-12 23:19 
QuestionPlease share the code for this article PinmemberAjay Bhati6-Nov-12 16:19 
AnswerRe: Please share the code for this article PinmemberPranay Rana6-Nov-12 18:42 
QuestionCan i get code of above article. Pinmembersuchita_20064-Sep-12 19:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141015.1 | Last Updated 7 Jul 2012
Article Copyright 2012 by Pranay Rana
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid