Is there sql transaction inside Enterprise Library RRS feed

  • Question

  • 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.


    Monday, October 26, 2009 1:03 PM


  • 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);
        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);
        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);
    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