using SqlHelperwith SqlTransactions

[Guest]

I use SqlHelper.Execute Scalar a lot in my apps. I need now to use it with
SqlTransactions.

ie: I am currently using code from your:
// Microsoft Data Access Application Block for .NET
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//
// SQLHelper.cs
//
// This file contains the implementations of the SqlHelper and
SqlHelperParameterCache
// classes.


I use the ExecuteScalar lots with stored procedures which I rely on the
Derive Parameters feature (SqlCommandBuilder.DeriveParameters(cmd) in
DiscoverSpParameterSet method in SqlHelperParameterCache.

The SqlHelper.ExecuteScalar and other methods which take SqlTransactions
parameters require the SqlParameters[] to be specifically passed in. The
SqlCommandBuilder.DeriveParameters(cmd) doesn't seem to function within an
SqlTransaction. Maintaining the sql parameters in C# code in addition to the
stored procedures is a lot of ongoing work.

I need to use transactions but I would really like to have my Data Layer
derive the parameters.

Is there a workaround or perhaps another version of the SqlHelper which
allows deriving parameters from stored procedures which inside an
SqlTransaction?

- Marcel

Peak Performance Software Corp.

 

[W.G. Ryan eMVP]

Marcel - we've overloaded a bunch of the stuff in the DAAB to support Async
operations as well as Transactions. For each Executexxx we have a
Transactional version as well. As you can see, Derive parameters is used in
it and it's just a simple case of overloading to get you what you want.
Hopefully this will give you enough to implement your own, but if not, let
me know and I'lls ee what I can do.

public static int ExecuteNonQueryCommit(SqlTransaction transaction,
CommandType commandType, string commandText, params SqlParameter[]
commandParameters) {
if( transaction == null ) throw new ArgumentNullException(
"transaction" );
if( transaction != null && transaction.Connection == null ) throw new
ArgumentException( "The transaction was rollbacked or commited, please
provide an open transaction.", "transaction" );

// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType,
commandText, commandParameters, out mustCloseConnection );
int retval;
try {
// Finally, execute the command
retval = cmd.ExecuteNonQuery();
} catch (Exception e) {
if (transaction != null) {
transaction.Rollback();
transaction.Dispose();
}
throw(e);
} finally {
// Detach the SqlParameters from the command object, so they can be used
again
cmd.Parameters.Clear();
}
transaction.Commit();
transaction.Dispose();
return retval;
}

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

I use SqlHelper.Execute Scalar a lot in my apps. I need now to use it with
SqlTransactions.

ie: I am currently using code from your:
// Microsoft Data Access Application Block for .NET
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//
// SQLHelper.cs
//
// This file contains the implementations of the SqlHelper and
SqlHelperParameterCache
// classes.


I use the ExecuteScalar lots with stored procedures which I rely on the
Derive Parameters feature (SqlCommandBuilder.DeriveParameters(cmd) in
DiscoverSpParameterSet method in SqlHelperParameterCache.

The SqlHelper.ExecuteScalar and other methods which take SqlTransactions
parameters require the SqlParameters[] to be specifically passed in. The
SqlCommandBuilder.DeriveParameters(cmd) doesn't seem to function within an
SqlTransaction. Maintaining the sql parameters in C# code in addition to the
stored procedures is a lot of ongoing work.

I need to use transactions but I would really like to have my Data Layer
derive the parameters.

Is there a workaround or perhaps another version of the SqlHelper which
allows deriving parameters from stored procedures which inside an
SqlTransaction?

- Marcel

Peak Performance Software Corp.