CODEWITHSUNDEEP
c#sql
Coyolero
Coyolero

Reputation: 2433

Loop inside a transaction in C#

I have a method in C# that executes some queries to insert in the data base inside a transaction.

What I want to do is a loop to insert several "Phone Numbers"

// Creates the transaction
dbTransaction = dbConnection.DbConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

// Creates the sql command
dbCommand = factory.CreateCommand();
dbCommand.Connection = dbConnection.DbConnection;
dbCommand.Transaction = dbTransaction;

... logic stuff

// Inserts the phone
foreach (PlacePhoneDTO placePhoneDTO in placePhoneList)
{
    dbCommand.CommandText = sqlStatementPhone.ToString();
    // Adds the parameters
    AddParameter<int>("@PlaceID", placeID, ref dbCommand);
    AddParameter<string>("@PhoneNumber", placePhoneDTO.phoneNumber, ref dbCommand);
    dbCommand.ExecuteNonQuery();
}

dbTransaction.Commit();

The second time that executes the loop fails

Thanks a lot

Upvotes: 2

Views: 3889

Answers (1)

Patrik Melander
Patrik Melander

Reputation: 585

You either have to clear the command parameters like slugster suggested in his comment. You could also move the command text outside the loop since it's the same all the time

dbCommand.CommandText = sqlStatementPhone.ToString();

foreach (PlacePhoneDTO placePhoneDTO in placePhoneList)
{
    dbCommand.Parameters.Clear();

    // Adds the parameters
    AddParameter<int>("@PlaceID", placeID, ref dbCommand);
    AddParameter<string>("@PhoneNumber", placePhoneDTO.phoneNumber, ref dbCommand);

    dbCommand.ExecuteNonQuery();
}

or recreate the command inside the loop like so:

foreach (PlacePhoneDTO placePhoneDTO in placePhoneList)
{
    dbCommand = factory.CreateCommand();
    dbCommand.Connection = dbConnection.DbConnection;
    dbCommand.Transaction = dbTransaction;  
    dbCommand.CommandText = sqlStatementPhone.ToString();

    // Adds the parameters
    AddParameter<int>("@PlaceID", placeID, ref dbCommand);
    AddParameter<string>("@PhoneNumber", placePhoneDTO.phoneNumber, ref dbCommand);
    dbCommand.ExecuteNonQuery();
}

Upvotes: 2

Related Questions