Remove From My Forums

Is there sql transaction inside Enterprise Library

Question
0

Sign in to vote

Hi all,
recently i using enterprise library data access application block to execute some query.

but for some cases i need to run multiple insert statement in 1 dbcommand, do i need to declare a sqltransaction for error this case? i had google on web but all the results return are about single insert sample. is there any sample for multiple insert statement in 1 command.

Thanks

Monday, October 26, 2009 1:03 PM

Answers

Yes you should be able to use multiple insert statement using a single dbcommand

string insertCustomerSql = "INSERT INTO Customers   (Name, EmailAddress) VALUES (@Name,@EmailAddress)   SELECT @CustomerId = SCOPE_IDENTITY()";
string insertOrderSql = "INSERT INTO Orders (CustomerId,   Number, OrderDate) VALUES (@CustomerId, @Number,   @OrderDate)";

using (TransactionScope scope =    new TransactionScope(TransactionScopeOption.RequiresNew))
{
    DbCommand insertCustomerCommand =         database.GetSqlStringCommand(insertCustomerSql);
    database.AddInParameter(insertCustomerCommand,                      "Name", DbType.String, "Bill Doe");
    database.AddInParameter(insertCustomerCommand,          "EmailAddress", DbType.String, "bill@doe.com");
    database.AddOutParameter(insertCustomerCommand,          "CustomerId", DbType.Int32, 4);

    database.ExecuteNonQuery(insertCustomerCommand);

    int customerId = (int)database.GetParameterValue                    (insertCustomerCommand, "CustomerId");

    DbCommand insertOrderCommand =             database.GetSqlStringCommand(insertOrderSql);
    database.AddInParameter(insertOrderCommand,             "CustomerId", DbType.Int32, customerId);
    database.AddInParameter(insertOrderCommand,             "Number", DbType.String, "12345");
    database.AddInParameter(insertOrderCommand, "OrderDate",              DbType.DateTime, System.DateTime.Now);
    database.ExecuteNonQuery(insertOrderCommand);

    DbCommand insertOrderCommandAgain =             database.GetSqlStringCommand(insertOrderSql);
    database.AddInParameter(insertOrderCommandAgain,             "CustomerId", DbType.Int32, customerId);
    database.AddInParameter(insertOrderCommandAgain,             "Number", DbType.String, "23456");
    database.AddInParameter(insertOrderCommandAgain,          "OrderDate", DbType.DateTime, System.DateTime.Now);
    database.ExecuteNonQuery(insertOrderCommandAgain);

    scope.Complete();
}

for more info

Proposed as answer by Alex Feng (SQL) Wednesday, October 28, 2009 7:06 AM
Marked as answer by kklow.27 Saturday, October 31, 2009 8:14 AM

Monday, October 26, 2009 1:50 PM

Answered by:

Is there sql transaction inside Enterprise Library

Question

Answers