asked on

Batch update in C#

Hi,

I am working on ASP.net 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);
	adapter.UpdateCommand.Parameters.Add("@tripID",
	   SqlDbType.Int, tripDetailValuesBO.tripID);
	adapter.UpdateCommand.Parameters.Add("@questionID",
	   SqlDbType.Int, tripDetailValuesBO.questionID);
	adapter.UpdateCommand.Parameters.Add("@answer",
	   SqlDbType.VarChar, 8000, tripDetailValuesBO.tripdata_question_answer);
	adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
    }            

    adapter.UpdateBatchSize = batchSize;
    adapter.Update(dataTable);
}

Open in new window

lenordiste

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: http://stackoverflow.com/questions/564366/generic-list-to-datatable

tia_kamakshi

ASKER

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

lenordiste

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

tia_kamakshi

ASKER

Its around 300 to 400 records

Thanks for reply and support

ASKER CERTIFIED SOLUTION

ROMA CHAUHAN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial