StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » executescalar in middle (be part) of transaction

executescalar in middle (be part) of transaction

Fabian R Silva, - — Tue, 23 Oct 2012 15:55:50 GMT

I need to execute a "executescalar" on a transaction to get the last invoice number again (if someone get the number that the actual invoice have... change it before save on this transaction)

I have:

...(try)...

BusinessLayer.TransactionBegin("invoiceTr", IsolationLevel.ReadCommitted);

var nextNumber = invBO.GetNextNumber(invBO.IdTalonario, DataSourceKey); // <--- THIS METHOD call a EXECUTESCALAR VALUE.... freezes and timeout after some seconds...

invBO.Numero = invBO.HastaNumero = nextNumber;

saveUndoResult = invBO.Save(true);

...

...(rollback/commit)...

RE: executescalar in middle (be part) of transaction

Fabian R Silva, - — Tue, 23 Oct 2012 15:55:50 GMT

I see that I was wrong on what I tried to accomplish, I will explain better.

I need to save invoices, and was searching better way of update the invoice number (each type have his own number)

I was planning to select max actual record and put that max+1, but if I do this the problem is that there is a posible repeat of number if it is not part of transaction.

¿can be a good idea to have a table like "InvoiceNumbers" that have a column "type" and a "number"?

Then on the current transaction I can fill the table InvoiceNumbers" adding +1 to actual number by the actual invoice type (¿this lock that table?)

then I read the current table InvoiceNumbers on transaction to set that number to the Invoice.

if another transaction try to get same invoice number and type on invoicenumbers, it will wait ?

thanks and sorry my lack of understanding about transactions, first time that have multiuser app that really need to be transactional and update some tables.

RE: executescalar in middle (be part) of transaction

Fabian R Silva, - — Tue, 23 Oct 2012 12:10:39 GMT

RE: executescalar in middle (be part) of transaction

Edhy Rijo — Mon, 13 Aug 2012 12:14:04 GMT

The GetHastaNumeroProximo method should be in the ComprobantesBO or if you have a BO Base class, then create it in that class and pass the parametes of the fields to use in the SELECT MAX() statement.
I am not a C# developer, but here is the version I came up with:
[codesnippet]

        public static int GetHastaNumeroProximo(int idTalonario)
         {
             int result;
             using (var sqlCmd = new SqlCommand())
             {
                 // select (max)numero from comprobantes where IdTalonario= Comprobantes.IdTalonario and anulado=0
                 var sql = string.Format("select max({0}) from {1} where {2} = @valor", ComprobantesBOFieldNames.HastaNumero.ToString, this.TableNameAndSchema, ComprobantesBOFieldNames.IdTalonario.ToString);
                 sqlCmd.CommandTimeout = 0; // Added this to avoid taking to much time, also make sure your tables are properly indexed for the SQL condition
                 sqlCmd.CommandText = sql;
                 sqlCmd.Parameters.Add("@valor", SqlDbType.Int).Value = idTalonario; //Also noticed that this parameter should be Integer and not String as you had it originaly.
                 var res = this.ExecuteScalar(sqlCmd);
                 result = (res == null) || res == DBNull.Value ? 1 : ((int)res) + 1;
             } // end using
             return result;
         }
[/codesnippet]

RE: executescalar in middle (be part) of transaction

Fabian R Silva, - — Mon, 13 Aug 2012 11:12:56 GMT

basically it receive a data from the bo to be saved (idTalonario) and with that execute a max(number) from the invoice table to get the max value for that idTalonario

I have to call this method twice, one for invoice BO and another time for the payments BO, the first time it executes (I suppose that it are out of transaction, but return value) and the 2nd time (for payments) it lock and give me timeout

I see in ms help that the "executescalar" (for ado.net?) have a IDbTransaction that can be passed... I doen't know how I can pass something that tell the executescalar the transation here...

thanks Edhy!

update:I see that I'm calling the scalar on a new object...

if I pass the BO as a parameter to that method and call the executescalar method over it, that can take care of the transaction? (or can I do something like that that call the execute scalar on the transaction...)

thanks!

-----------------------------------

code:

public static int GetHastaNumeroProximo(int idTalonario, string dataSourceKey){

            int result;

            var campoHastaNumero = ComprobantesBOFieldNames.HastaNumero.ToString();

            var campoNumero = ComprobantesBOFieldNames.Numero.ToString();

            var campoIdTalonario = ComprobantesBOFieldNames.IdTalonario.ToString();

            using (var bo = new ComprobantesBO() { DataSourceKey = dataSourceKey })

                // select (max)numero from comprobantes where IdTalonario= Comprobantes.IdTalonario and anulado=0

                var sql = string.Format("select max({0}) from {1} where {2} = @valor", campoHastaNumero, bo.TableName, campoIdTalonario);

                var sqlCmd = new SqlCommand(sql);

                sqlCmd.Parameters.Add("@valor", SqlDbType.VarChar).Value = idTalonario;

                var res = bo.ExecuteScalar(sqlCmd);

                result = (res == null) || res == DBNull.Value ? 1 : ((int)res) + 1;

            } // end using

            return result;

[quote][b]Edhy Rijo (8/13/2012)[/b][hr]Hi Fabian,

Please post the code for the GetNextNumber method as well as any stored procedure being called or TSQL code.
[/quote]

RE: executescalar in middle (be part) of transaction

Edhy Rijo — Mon, 13 Aug 2012 11:01:08 GMT

Hi Fabian,

Please post the code for the GetNextNumber method as well as any stored procedure being called or TSQL code.