locked
Decimal Scale in asp:parameter RRS feed

  • Question

  • User252054804 posted

    Background:

    I set up a SQL 2016 always encrypted database with various columns that are encrypted. I am able to successfully insert, update and read values using c# ADO.NET through the use of SqlParameters that specify the exact type, precision <g class="gr_ gr_393 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="393" data-gr-id="393">and</g> length for each parameter. e.g.:

                    SqlParameter paramAnnualSalaryLocal = cmd.CreateParameter();
                    paramAnnualSalaryLocal.ParameterName = @"@AnnualSalaryLocal";
                    paramAnnualSalaryLocal.DbType = DbType.Decimal;
                    paramAnnualSalaryLocal.Direction = ParameterDirection.Input;
                    paramAnnualSalaryLocal.Value = HREmployeeRow["AnnualSalaryLocal"];
                    paramAnnualSalaryLocal.Precision = 18;
                    paramAnnualSalaryLocal.Scale = 2;
                    cmd.Parameters.Add(paramAnnualSalaryLocal);

    Not explicitly specifying the precision and scale of the parameter would cause the <g class="gr_ gr_1470 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="1470" data-gr-id="1470">sql</g> update to fail, due to the column level encryption requiring this level of specificity.

    Problem:

    While I am able to read the encrypted data and display it on a data grid, using a SqlDataSource I am unable to update values back to the database.

    I can see that this is because of a data-type miss-match between the parameter value and the database type:

    Uncaught Error: Sys.WebForms.PageRequestManagerServerErrorException: Operand type clash: decimal(1,0) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'HRDBCEK1', column_encryption_key_database_name = 'HRDB') is incompatible with decimal(18,2) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'HRDBCEK1', column_encryption_key_database_name = 'HRDB')
    Statement(s) could not be prepared.

    I believe this to be the case <g class="gr_ gr_831 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="831" data-gr-id="831">because</g> SqlDataSource.UpdateParameters implements a Parameter collection (not SqlParameter).

    System.Web.UI.WebControls.<g class="gr_ gr_915 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="915" data-gr-id="915">Parameter</g> does not have a property for "scale". Only "DBType" and "Size".

    When I switch the DBType from "Decimal" to "String", I can indeed see the "decimal(1,0)" portion of the above exception change to "<g class="gr_ gr_1087 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="1087" data-gr-id="1087">nvarchar</g>(1)".

    Specifying the Size as "18" does not help.

    Is there any way to get these updates to work via the use of a SqlDataSource?

    My only option seems to be to intercept the OOTB <g class="gr_ gr_1210 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="1210" data-gr-id="1210">batchEdit</g> event of the grid, cancel the action and to write a complete manual implementation of the update using a SqlCommand object, which does support the SqlParameter object where I am allowed to define the "scale".

    Thank you all.

    Thursday, April 19, 2018 6:20 PM

All replies

  • User-1716253493 posted

    Using sqldatasource wizard maybe you can generate insert/update/delete commands

    You can use code behind to set the parameter values like this

            SqlDataSource1.UpdateParameters["id"].DefaultValue = "1";
            SqlDataSource1.UpdateParameters["name"].DefaultValue = "someone";
            SqlDataSource1.Update();

    Friday, April 20, 2018 3:09 AM
  • User252054804 posted

    All that I see in your example is the defining and setting of a parameter value. It doesn't appear you have specified the parameter type or scale/precision, which is the issue at hand.

    -Thank you for the response though!

    Due to time constraints, I ended up removing the UpdateParameters from the SqlDataSource, intercepted the updating of the DataGrid and replaced it with my own implementation that utilizes a SqlCommand instead.

    For sanity reasons it would still be nice to find out if there is a <g class="gr_ gr_474 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="474" data-gr-id="474">way</g> this could have been achieved with a SqlDataSource. I was under the impression that it should fully support all aspects of SQL Always Encrypted.

    Friday, April 20, 2018 3:39 PM
  • User-1716253493 posted

    Try this in sql

    set AnnualSalaryLocal=cast(@AnnualSalaryLocal as decimal(18.2)) 

    Saturday, April 21, 2018 9:31 AM