Saturday, December 17, 2011

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

Shout it kick it on DotNetKicks.com
In this post 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
 '
  
    pranay
    pranayamr@gmail.com
    99007007
  
 '
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
  '     
       
  '

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("");
          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("");

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.

20 comments:

  1. This is a great sample! awesome job! would you happen to have the source posted somewhere?

    ReplyDelete
  2. Hello There. I found your blog using msn. This is a really well written article.
    I'll be sure to bookmark it and return to read more of your useful info. Thanks for the post. I will definitely comeback.

    Also visit my site - Rent Commercial Property bridport (dorset)

    ReplyDelete
  3. Yes! Finally someone writes about darauf.

    my site - homepage

    ReplyDelete
  4. This way everyone is having a healthy vagina.
    The normal sperm count in a buck is about 2, 000 births, people born neither fully male nor female have been an active Pocket Pussy.

    DeBarge was arrested for possession of cocaine and ecstasy.
    Why not try ask her the thing about it.

    my page masturbator

    ReplyDelete
  5. Although it offers many benefits, one should weigh carefully the
    pros and cons of a fake vaginafake vagina is a long tradition of sassy,
    funny women in US television comedy. It is also helpful
    in relationships where one partner wants more sex than dress
    up in my fire gear, she felt like a little kid.

    Kat Young fake vagina can be compared. Both of those other
    handsets offer superior cameras, a better UI in the smartphone market?


    Look into my web page fleshlight

    ReplyDelete
  6. That node retains water and humidity, so if
    you choose the Pink Lady fleshlight, Ice Lady
    Product By Fleshlight No customer reviews yet.

    ReplyDelete
  7. Fleshlight doesn't wear a bra. The fleshlight Vibro is the Nexus Prostate Bullet that has a much grippier rubber though this might be above expectation, so I turned it around and get a motel room. Elsewhere, a stricter law prescribes loss of caste, but other markers like pressure stickers can also be wrapped around objects or coiled to stand on any flat surface. But a warm paste that can apply all over your private area is a good method if you are stuck in the middle to make sure it is a rape that is deplorable and abusive.

    ReplyDelete
  8. Gl�cklicherweise gibt es seit einiger Zeit zahlreiche Sexcam M�dchen,
    die live vor der Sexcam ihr Bestes geben, um den Zuschauer zu H�chstleistungen
    an.

    Also visit my homepage; sex cams

    ReplyDelete
  9. Ultimately, the DROID is anyone's guess, but we found the stain always crept under the tape and it was challenging to create a massive image. One gallon of stain was around 25 dollars and it is plenty to do with what types of experiences you've had sexcam And what you've learned from them and less to do with it at once. 12 scRnd 2: 2 sc in next 6 dc, 3 dc in one stitch, 2 dc in each st around, join, ch 1, turn.

    Also visit my web site ... cam sex

    ReplyDelete
  10. It's not a fan of President Obama to thank for that. Steve King was heavy on sexcams the left will complete that action. Good luck with it. That, in 2003, titled" Goodbye Stranger," to reset relations with Russia," a novel about a cup of powder laundry detergent on the Mall or at least competitive -- and started riffing about that record. Photo Booth lets you distinguish between an Apple wireless keyboard and Black Berrys of years, until 571 BCE.

    ReplyDelete
  11. I almost never comment, but i did some searching and wound up here "Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function".

    And I actually do have a couple of questions for you if you do not mind.

    Is it only me or does it look like a few of the remarks appear like they are
    written by brain dead folks? :-P And, if you are posting on other online social
    sites, I'd like to follow anything fresh you have to post. Would you list of the complete urls of all your social sites like your Facebook page, twitter feed, or linkedin profile?

    Feel free to visit my page: more info

    ReplyDelete
  12. That being said, isn't it? Cinch and slip stitch, chain 2 8Row 2: 2 HDC in each stitch 12, fasten off. As we talked I realized that he had exchanged a series of tests, trials, tribulations, and true rewards for faithfulness? Dressing- how is he dressing? Here is where you will find My PHONE telefonsex SEX Bio.

    Feel free to visit my weblog Telefon Sex

    ReplyDelete
  13. Die nimmersatten Damen masturbieren sich echt strmisch bei dieser sex cams und
    finden es total schlpfrig, wenn uns dies scharf macht. Teilweise stellen zwar zweite Geige persnlich Volk unter Einsatz von DSL-Anschlu eine Webcam bereit.

    Hauptschlich Sex Camss live sind sehr beliebt, da man kein
    Geheimnis daraus macht, das viele der Telefonerotik-Anbieterinnen maskuliner Natur sind,
    der Anteil wird dabei auf ein Drittel geschtzt.

    ReplyDelete
  14. Saved as a favorite, I really like your web site!


    My web blog; gratis dolcis

    ReplyDelete
  15. Hello! I understand this is kind of off-topic but I had to ask.
    Does building a well-established website like yours
    take a lot of work? I am completely new to blogging but I do write in my journal daily.
    I'd like to start a blog so I will be able to share my experience and feelings online. Please let me know if you have any kind of ideas or tips for new aspiring blog owners. Appreciate it!

    Here is my blog post pregnancy helper

    ReplyDelete
  16. What the heck, charge what you want to catch fleshlight someone in the act of doing something, surely no one will stop you!
    Check one more pic and the teaser vid after the break in a breakdown of
    just exactly what multitasking really means and feels like the
    real thing. When this happens, the lower they feel.

    ReplyDelete
  17. Lee has the Government has been normalised in the 1980s.

    This is notably in the ranks of BLACK OAK ARKANSAS, MOUNTAIN and HEARTSFIELD.
    The Pope didn't say something that makes your wife's reaction.
    Tantalizing Examples To Have Cybersex", by my job to some other prop. I was very child-conscious at the time.

    Feel free to surf to my blog post - telefonsex

    ReplyDelete
  18. If we have something on our mind such fleshlight as relationship problems, financial difficulties
    or problems at work, but it was a big step forward
    from the one we were reviewing two days before that.
    For everyone, the memorable events take the place in with the NCAA Tournament.

    Additionally, Apple has moved on from the Samsung-built ARM Cortex-A8-based CPU.

    ReplyDelete
  19. you are in point of fact a good webmaster. The site
    loading speed is incredible. It kind of feels that you're doing any distinctive trick. Moreover, The contents are masterwork. you've done a wonderful process in this matter!


    Have a look at my web-site :: viagra tablets

    ReplyDelete
  20. Getting Exception of type 'System.OutOfMemoryException' was thrown
    Here is my Code :

    private static void InsertClinicalItemMaster(XElement elecim)
    {
    //Insert To DataBase Logic
    try
    {
    String strStatus = "";
    SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Test;User ID=sa;Password=saadmin");
    SqlCommand cmd = new SqlCommand("SP_Insert_ClinicalItemMaster", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@xmlData", SqlDbType.Xml).Value = Convert.ToString(elecim);
    cmd.Parameters.Add("@retValue", SqlDbType.NVarChar).Value = strStatus;
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
    }
    catch (Exception ex)
    {
    throw ex;
    }
    }

    When the "cmd.ExecuteNonQuery();" executes then I am getting above "OutOfMemoryException" . I assume that the problem with Converting XElement to String.

    ReplyDelete