CODEWITHSUNDEEP
c#sql-server-2008tableadapter
Shagglez
Shagglez

Reputation: 1522

Decimal passed incorrectly from C# to SQL Server with TableAdapters

Yesterday I noticed an odd behaviour when using TableAdapters, for some reason when passing a decimal < 0.1 it makes it into an integer. For example if I pass 1.0123, I can see 1.0123 in SQL Profiler, but if I pass 0.0123 I will get 123. Is there a known issue? You can do the following steps to reproduce the problem:

  1. Create a new database TestDatabase, and create the following stored procedure

    create proc DecimalParametersSelect
(
    @Foo decimal(10,5)
)

as

select @Foo

  2. Create a new project and add a new DataSet file SampleDataset. Add a new TableAdapter and add DecimalParametersSelect as Select procedure (it should be the only one in your db).

  3. Run your project and try to select some data, e.g. 

    using (SampleDatasetTableAdapters.DecimalParametersSelectTableAdapter dta = new SampleDatasetTableAdapters.DecimalParametersSelectTableAdapter())
{
    var table = dta.GetData(0.01588M);
}

In profiler you should see that the value passed in is 1588 (interestingly the value returned is recognized correctly in C# as 0.01588)

Upvotes: 2

Views: 780

Answers (1)

Ed Harper
Ed Harper

Reputation: 21505

This appears to be a display bug in SQL Profiler when the TextData column is not included in the trace and the text of the RPC command is reconstructed from another source (presumably BinaryData).

I followed your steps and was able to repo on SQL 2008 R2 using a default trace in SQL profiler.

However, when the trace properties are changed to include the TextData column for RPC:Completed, the correct command is displayed.

enter image description here

Upvotes: 2

Related Questions