Batch update in C#

Posted on 2009-12-26
Last Modified: 2012-05-08

I am working on using C#

I wanted to do Batch insert from my stored proc

I wrote below code

But to execute batch command I need DataTable, DataSet etc

Cannot I do this using my object as using my below code

Please help me doing batch update

Many Thanks
public void BatchUpdate(List<TripDetailValuesBO> tripDetailValuesList)


    int batchSize = 20;

    DbConnection dbConn = new DbConnection();

    SqlConnection connection = dbConn.Connection();

    SqlDataAdapter adapter = new SqlDataAdapter();

    foreach (TripDetailValuesBO tripDetailValuesBO in tripDetailValuesList)


	adapter.UpdateCommand = new SqlCommand(

	    "EXEC insertTripData @tripID, @questionID, @answer", connection);


	   SqlDbType.Int, tripDetailValuesBO.tripID);


	   SqlDbType.Int, tripDetailValuesBO.questionID);


	   SqlDbType.VarChar, 8000, tripDetailValuesBO.tripdata_question_answer);

	adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;


    adapter.UpdateBatchSize = batchSize;



Open in new window

Question by:tia_kamakshi
    LVL 11

    Expert Comment

    unfortunately you won't be able to do batch update on your SqlAdapter without using a dataset. If you do need to use a SqlAdapter (and not LinqToSql) and if you have to use a List, then you can try converting your List into a dataset.
    here is a thread about this subject:

    Author Comment

    Thanks for your reply.

    If I donot wanted to convert my list List<TripDetailValuesBO> to datatable

    Then what is the best way to do batch update, If I cannot use SqlAdapter

    Also, If I have to do insert query into the database table (using MSSQL 2005), what is the good max batch size I can give

    Please suggest
    LVL 11

    Expert Comment

    typically, how many records are we talking about for your updates and inserts?

    Author Comment

    Its around 300 to 400 records

    Thanks for reply and support
    LVL 11

    Accepted Solution


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now