Suyati Technologies
  • Fluid Solutions
    • Buyer Rhythms Engine
    • Lead Prioritization
    • Customer LifeTime Value
    • Chatbot
    • Account Based Marketing
  • Platforms
    • CRM
      • Salesforce
      • Dynamics
    • CMS
      • Sitecore
      • Drupal
      • Episerver
      • Sitefinity
    • Ecom
      • Magento
      • Sitecore commerce
    • RPA
      • UiPath
    • Analytics
    • Martech
  • Services
    • CX Consulting
    • DSaaS
    • Product Engineering
  • Intel
    • Blog
    • eBooks
    • Webinars
    • Case Studies
  • About Us
    • Management Team
    • Advisory Board
    • Our Story
    • Testimonials
  • Careers
Suyati Technologies
  • Fluid Solutions
    • Buyer Rhythms Engine
    • Lead Prioritization
    • Customer LifeTime Value
    • Chatbot
    • Account Based Marketing
  • Platforms
    • CRM
      • Salesforce
      • Dynamics
    • CMS
      • Sitecore
      • Drupal
      • Episerver
      • Sitefinity
    • Ecom
      • Magento
      • Sitecore commerce
    • RPA
      • UiPath
    • Analytics
    • Martech
  • Services
    • CX Consulting
    • DSaaS
    • Product Engineering
  • Intel
    • Blog
    • eBooks
    • Webinars
    • Case Studies
  • About Us
    • Management Team
    • Advisory Board
    • Our Story
    • Testimonials
  • Careers
Suyati Technologies > Blog > SQL Bulk copy with trigger in ASP.NET

SQL Bulk copy with trigger in ASP.NET

by Hamid Narikkoden August 1, 2013
by Hamid Narikkoden August 1, 2013 5 comments

I recently built a new website and started getting startling responses from customers, which exceeded all my expectations. As a startup website, the incredible customer inflow was totally overwhelming. My customer base kept expanding, and everything worked fantastic until – the “Delay” possessed my till-then awesome website! Gradually, I noticed my website degrading in performance and response time, along with an increasing customer base. I had no other option except for performing an exorcism – completely refactoring its code.
What the Exorcism revealed:
I used ASP.NET MVC4 along with Entity framework 5 and SQL 2012 for my website. I followed LINQ query expressions and looping through the entire user base to deliver specific content to millions of users. The website seemed to take years to respond because of this and its bulky coding – my website was behaving truly possessed. It took ages to load; exhibited performance degrades and gave a terrible experience.
LINQ query consumed much time owing to its syntax validation and conversion of LINQ queries to SQL queries. I thought executing stored procedure in my database wasn’t a wise decision either, as it lacks debugging of values. All I wanted was a simple and fast operation.
How to make my website faster? – The question puzzled me like crazy!  This aroused in me the idea to implement SQLBulkCopy method. The method can be used to insert large amount of data very efficiently. Let me illustrate the scenario.
I have two tables: Profile and Content
tables
After inserting to Content table I have to update the delivery date of the corresponding user profile.

string conn = ConfigurationManager.ConnectionStrings["bulkcopy"].ToString();
SQLConnection con = new SQLConnection(conn);
con.Open();
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn() { ColumnName = "Id", AutoIncrement = true });
dt.Columns.Add new DataColumn() { ColumnName = "Title" });
dt.Columns.Add new DataColumn() { ColumnName = "Text" });
dt.Columns.Add new DataColumn() { ColumnName = "UserId" });
SQLDataReader rd;
//Loop through and create rows of data and add to datatable
{
dr = dt.NewRow();
dr["Title"] = Title
dr["Text"] = Text
dr["UserId"] = UserId
dt.Rows.Add(dr);
}
rd.Close();
SQLBulkCopy copy = new SQLBulkCopy(conn,SQLBulkCopyOptions.FireTriggers);
copy.DestinationTableName = "Content";
copy.WriteToServer(dt);
con.Close();

Suppose it may be around millions of rows but I need profile update on delivery date column after each insertion on Content table. So it should be fare if I go for an insert trigger in database. But as we are using SQLBulkCopy insert so just an update on the profile table would update only the first row in the profile. So I have to go for a cursor inside the trigger.
Create AFTER INSERT trigger:

Create trigger [updatedeliverydate] on [dbo].[Profile]
AFTER Insert
as
begin
declare @UserId int;
Declare @cur Cursor
set @cur=cursor for select i.UserId from inserted i
open @cur
FETCH NEXT FROM @cur into @UserId
WHILE @@FETCH_STATUS = 0
BEGIN
update Profile
set LastDeliveryDate=GETUTCDATE() where UserId=@UserId
FETCH NEXT FROM @cur INTO @UserId  --fetch next record
END
END

Thus, I could free my website from the “delay” spirit! The SQLBulkCopy helped make my query super-fast and it worked fabulous thereafter.

SQL Bulk copy
5 comments
0
FacebookTwitterLinkedinTumblr
previous post
An Introduction to Ektron Framework API
next post
WordPress get_posts method and 5 different usages

You may also like

Most Viewed Blogs of 2020

January 13, 2021

How to Make the Most Out of Salesforce...

December 23, 2020

How Salesforce-Slack Acquisition can benefit your Salesforce CRM?

December 22, 2020

5 Questions Why Your Business Needs Salesforce CPQ

December 16, 2020

Top 10 Forecasted CRM Trends In 2021

December 16, 2020

Scaling to the ‘New Normal’

November 30, 2020

Microsoft Dynamics Unified Client Interface (UCI): Why and...

November 27, 2020

Top Features of Sitecore 10 to Benefit Developers...

November 19, 2020

6 CX Tips For a Different Holiday Season...

November 12, 2020

5 Ways Lead Scoring Can Close the Gap...

November 5, 2020

5 comments

Avatar
Karthi August 1, 2013 - 6:55 pm

Good One.. Any ORM based solution available to handle the bulk copy?

Reply
Avatar
Arun V B August 2, 2013 - 1:31 pm

Good read.. well written.

Reply
Avatar
Hamid Narikkoden August 3, 2013 - 12:01 am

@Karthi:
@Arun:
Thanks for your feedback
Dapper , A simple micro ORM supports bulk data insertion. It just expand system.Data.IDbConnection interface and provides typed output.
A sample query can be as follows:

using (IDbConnection connection = new SqlConnection(constring))
            {
                connection.Execute(@"Insert into details values(@Name,@Salary,@Basic_Pay)", detaillist());
            }

We can specify a collection of model in the insert query and on execution ,the data get inserted within a fraction of second(Approx for 10000 entries, it consumed 5 seconds)

Reply
Avatar
Ujjwala Datta October 1, 2013 - 5:21 pm

Yes, this code is helpful, but i have a requirement and it is,
i wanted to truncate Destination Table itself (Content) before Uploading data. for that i had used instead of trigger which is not working. can you specify better solution for this,
http://forums.asp.net/t/1940035.aspx?Trigger+not+firing+On+SqlBulkCopy

Reply
Avatar
Chirag Patel June 19, 2015 - 4:58 pm

Great! HI Its work for me, Thanks

Reply

Leave a Comment Cancel Reply

Save my name, email, and website in this browser for the next time I comment.

Keep in touch

Twitter Linkedin Facebook Pinterest

Recent Posts

  • Most Viewed Blogs of 2020

    January 13, 2021
  • 6 RPA Trends for 2021

    January 8, 2021
  • How to Make the Most Out of Salesforce Post Implementation

    December 23, 2020

Categories

  • Twitter
  • Linkedin
  • Facebook
  • Instagram
  • Fluid Solutions
    • Buyer Rhythms Engine
    • Lead Prioritization
    • Customer LifeTime Value
    • Chatbot
    • Account Based Marketing
  • Platforms
    • CRM
      • Salesforce
      • Dynamics
    • CMS
      • Sitecore
      • Drupal
      • Episerver
      • Sitefinity
    • Ecom
      • Magento
      • Sitecore commerce
    • RPA
      • UiPath
    • Analytics
    • Martech
  • Services
    • CX Consulting
    • DSaaS
    • Product Engineering
  • Intel
    • Blog
    • eBooks
    • Webinars
    • Case Studies
  • About Us
    • Management Team
    • Advisory Board
    • Our Story
    • Testimonials
  • Careers

© 2020 Suyati Technologies


Back To Top
Suyati Technologies

Popular Posts

  • 1

    What are the Top 3 risks for implementing a CX Program?

    August 30, 2019
  • 2

    What is Salesforce CRM and What Does it Do?

    February 19, 2014
  • 3

    Do you need a separate CX Team at your company?

    September 2, 2019
  • 4

    How to build Employee Advocacy for your Business?

    September 3, 2019
  • 5

    Tips to Reduce Salesforce Pricing

    February 17, 2015
© 2020 Suyati Technologies

Read alsox

5 things you should be careful of while migrating your...

January 12, 2015

Robotic Process Automation & Artificial Intelligence: Built for Each Other

October 30, 2020

Micro-Apps: The Minions of App-Development

October 31, 2016
Suyati Logo

Technology Insights. Delivered.

Know more about business technology platforms for your enterprise.

Thank you!

We're glad to have you as part of our community. Please feel free to contact us anytime with feedback or suggestions.

Suyati Logo

Leaving So Soon?

Know more about business technology platforms for your enterprise.

Thank you!

We're glad to have you as part of our community. You'll start receiving updates shortly. Please feel free to contact us anytime with feedback or suggestions.

By continuing to use this website you agree with our use of cookies. Read More Agree