Devart Forums

Posted: **Fri 19 Mar 2010 10:50**

Hello,

I've also submitted this via the "contact us" page, but unfortunately I don't seem to getting any emails from devart through to my work address, so apologies for the multiple submissions!

We are currently evaluating dotConnect Universal for use in our application. We have found what appears to be a
bit of a show-stopper bug for us in the implementation of UniParameter.

Looking at the code for UniParameter in reflector, it appears that the underlying provider parameter (e.g. SqlParameter) is
lazily instantiated. However, when this underlying parameter is created, the value of the SourceColumnNullMapping property
is not copied. This means that the value of SourceColumnNullMapping is 'forgotten', and becomes false even if it was
initially set to true.

Code to demonstrate the bug is:

Code: Select all

            
            using (UniConnection conn = new UniConnection(connectionString)) {

                conn.Open();

                using (UniCommand command = conn.CreateCommand()) {

                    command.CommandText = "SELECT @test";

                    UniParameter param = new UniParameter() {
                        ParameterName = "@test",
                        DbType = DbType.Int32,
                        UniDbType = UniDbType.Int,
                        SourceColumnNullMapping = true,
                        Value = 0,
                    };

                    command.Parameters.Add(param);

                    Console.WriteLine(param.SourceColumnNullMapping); // prints true

                    // this causes the underlying SqlParameter to be created.
                    command.ExecuteScalar();

                    Console.WriteLine(param.SourceColumnNullMapping); // prints false
                }
            }

The effect of this bug is that queries generated using the strongly typed dataset designer (i.e. via DbCommandBuilder) which use parameters of the form @IsNull_* end up throwing an exception.

Thanks in advance for your help!

Posted: **Fri 19 Mar 2010 12:23**

We've sent you a letter, please check that it was not blocked by your mail filter.

We will fix the issue. Please look forward to the nearest build.

Also, could you please describe the scenario in which the exceptions are thrown in more details?

Posted: **Fri 19 Mar 2010 13:34**

Thank you for your prompt reply.

Unfortunately I haven't received any emails, please could you forward anything to devart AT antjones.fastmail.fm? Thanks. Also, could you let me know when the next build be available to us, either via the forum or that email address?

The scenario in which we encountered the problem is using a strongly typed dataset generated using the visual studio (2008) strongly typed dataset designer.

When generating update/delete statements using ConflictOption.CompareAllSearchableValues and when there are columns that allow NULL values, System.Data.Common.DbCommandBuilder generates update/delete statements of the form:

UPDATE TableName SET ... WHERE ... ((@IsNull_Column = 1 AND Column IS NULL) OR (Column = @Original_Column))

The IsNull_Column parameters are mapped to the underlying Column via the SourceColumn property, but with SourceColumnNullMapping set to true.

Logic in System.Data.Common.DbDataAdapter.ParameterInput() checks the value of SourceColumnNullMapping, and if it is set to true it sets the value of the parameter to 1 or 0 rather than the original column value as would happen otherwise.

However, since the SourceColumnNullMapping has had it's value reset, the @IsNull_Column paramter has it's Value property set to the original column value (a string in our case). This leads to an System.FormatException:

Code: Select all

Devart.Data.Universal.UniException: Failed to convert parameter value from a String to a Int32. ---> System.FormatException: Failed to convert parameter value from a String to a Int32. ---> System.FormatException: Input string was not in a correct format.
   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
   at System.Int32.Parse(String s, NumberStyles style, IFormatProvider provider)
   at System.Convert.ToInt32(String value, IFormatProvider provider)
   at System.String.System.IConvertible.ToInt32(IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
   at System.Data.SqlClient.SqlParameter.GetCoercedValue()
   at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
   at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Devart.Data.Universal.UniCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3, Boolean A_4)
   --- End of inner exception stack trace ---
   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
   at 
...

To demonstrate this, here are the rough steps I followed:

- Create a SQL server database with a table without any RowVersion / TimeStamp columns (so that the command builder uses CompareAllSearchableValues for concurrency checking), and that contains a NVARCHAR(n) NULL column.
- Connect the the DB in VS Server Explorer using the UniConnection provider
- Using the VS dataset designer, drag and drop the table to create the typed dataset and DataAdapter.
- This should lead to a generated update query that uses a parameter of the form IsNull_XXX and that has SourceColumnNullMapping set to true in the designer code-behind.
- Using the generated dataset and adapter:
- insert a row with the nvarchar column set to a non-null value and call adapter.Update() to commit the inserted row
- make a change to the row, call adapter.Update() again. This will suceed since at the point the SourceColumnNullMapping is read the underlying SqlParameter has not yet being generated.
- Update the row once more and call adapter.Update() for a third time. This time, SourceColumnNullMapping will return false, and @IsNull_XXX will get set to a string value (the original value of the column) rather than 0 or 1. The exception should then be thrown.

Posted: **Fri 19 Mar 2010 17:22**

The problem should be solved after we fix the UniParameter behaviour.

We plan to release the next build in about a week. We will inform you in this topic when the build is released.

Posted: **Tue 25 May 2010 06:48**

We have released the new 3.20.16 build of dotConnect Universal. It can be downloaded from
http://www.devart.com/dotconnect/univer ... nload.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

The new build contains the fix for problem with UniParameter.SourceColumnNullMapping changing its value. For more information on fixes and improvements available in version 3.20.16, please see
http://www.devart.com/forums/viewtopic.php?t=18035

Devart Forums

issue with UniParameter.SourceColumnNullMapping

issue with UniParameter.SourceColumnNullMapping