Tom Goff's .Net Musings

Tidbits of information regarding .Net, C#, and SQL Server.

Archive for April 2007

Decimal Troubles

leave a comment »

I ran into an annoyance relating to the Decimal data today. While testing my .Net application, I was getting an OverflowException when I was trying to run a query on SQL Server. The text of the exception was (simply) “Conversion overflows” and was coming from SqlBuffer (a private ADO.Net class) via SqlDataReader.

Specifically, the exception was coming from the get accessor of SqlBuffer’s Decimal property. Using Lutz Roeder’s .Net Reflector, I could see that this property was hard coded to throw an OverflowException if the scale of a decimal value was greater than 28. The SQL query I was running did include a column whose datatype was decimal and had a scale larger than 28.

Looking at the SQL Server documentation, the decimal data type can have a precision from 1 to 38 and a scale from 0 to the precision. So “decimal(38, 38)” is a valid data type in SQL Server. Looking at the .Net documentation, the Decimal data type can have a scale between 0 and 28. So I was receiving an OverflowException, because I was trying to select data that was supported by SQL Server, but not by .Net.

If you try to get the results of the following query using ADO.Net, then you will get the same exception:

    DECLARE @testVar DECIMAL(38, 29) 
    SET @testVar = 1.1 
    SELECT @testVar AS TestCol

As usual, I’ve put together a sample application if you would like to see this in action.


Written by Tom

April 26, 2007 at 3:29 pm

Posted in .Net, Ado.Net, C#, Gotcha

SqlBulkCopy Bug

with one comment

While working on a new feature for my company’s flagship product that uses SqlBulkCopy, I encountered a strange InvalidOperationException exception. I was only receiving this exception when copying data to certain tables. The exact message of the exception was:

Failed to obtain column collation information for the destination table. If the table is not in the current database the name must be qualified using the database name (e.g. [mydb]..[mytable](e.g. [mydb]..[mytable]); this also applies to temporary-tables (e.g. #mytable would be specified as tempdb..#mytable).

After doing some searching, I found this blog by Marco Russo. I found that my tables that were failing indeed had dots in their name. Mr. Russo’s blog led me to his bug report to Microsoft, which was created and confirmed as fixed in their “latest internal build” in April of 2006. I was confused because I always keep up with updates released by Microsoft and I thought for sure they had released the fix by now. To my dismay, they have not released the fix yet, and apparently lots of people (myself now included) are upset with Microsoft because they are so slow to release these types of fixes.

My next step was to try to find a work around that did not require me to change the table name. Unfortunately, the SqlBulkCopy class is sealed up and internally it uses other sealed classes. Since we can’t wait for the next service pack, my only option was to replace dots with underscores. Even if Microsoft were to release the service pack tomorrow, I would still have to implement the workaround since not all our users would apply the service pack immediately after being released.

One user reported on the Microsoft bug report that wrapping the table name with square brackets (e.g. [My.TableName]) fixed the issue for him. I’m not sure that he is correct though, because it did not work for me. Using SQL Profiler, I was able to narrow down the problem to a call to “sp_tablecollations”. SqlBulkCopy passes the table name as “.My.TableName” to this stored procedure. The stored procedure would then have to assume that “My” is the owner and “TableName” is the table name. Keep in mind that “[My.TableName]” was passed as the destination table and SqlBulkCopy stripped the square brackets that surrounded the table name, when building the call to this stored procedure.

Setting aside the fact that these types of bugs take a long time to get fixed, I really wish there was a way for me to “fix” it. Since the class is sealed (and there are no virtual methods), I cannot derive from it and customize it’s behavior. If the class did not use internal classes in it’s implementation, then I could build my own version of SqlBulkCopy. Alas, that is not possible either. As I understand it, Microsoft marks classes as internal or sealed because they don’t have time to test every class (and thus can’t guarantee their functionality) or because they may want to change the signatures of the methods or classes.

I think that .Net bugs are bit more frustrating for me because it is easier to see the underlying code and the actual bug, but I just can’t reach into the code a fix it. Although, I’m sure I’ll survive somehow. If you would like to see this bug in action, then check out my sample project here.

Written by Tom

April 9, 2007 at 1:01 pm

Posted in .Net, Ado.Net

Coding and Accessing Properties

leave a comment »

I was recently re-reading parts of “CLR via C#” by Jeffery Richter. In the book, Mr. Richter wished that Properties were not supported in .Net framework for various reasons. The one reason that caught my eye was that a “property can take a long time to execute”.

I use properties through-out my code, for various reasons (XmlSerializer and PropertyGrid are the first two that jump to mind). I’m probably guilty of creating properties that may be a bit to expensive to be properties. Although, I’ve never run into any performance issues relating to them. Regardless, I’m always looking for ways to squeeze a bit more speed/memory from my applications.

So I created a simple class with a single property that does more work than it should:

    public class Class1 { 
        public Int32 Sum { 
            get { 
                Int32 retVal = 0; 
                for (Int32 i = 1; i <= 100; i++) 
                    retVal += i; 
                return retVal; 

I then accessed this property as follows:

    Class1 c1 = new Class1(); 
    for (Int32 i = 0; i < c1.Sum; i++) { 

This resulted in the following compiled IL code:

    .maxstack 2 
    .locals init ( 
        [0] class TestApp.Class1 c1, 
        [1] int32 i) 
    L_0000: newobj instance void TestApp.Class1::.ctor() 
    L_0005: stloc.0 
    L_0006: ldc.i4.0 
    L_0007: stloc.1 
    L_0008: br.s L_001a 
    L_000a: ldloca.s i 
    L_000c: call instance string [mscorlib]System.Int32::ToString() 
    L_0011: call void [mscorlib]System.Console::WriteLine(string) 
    L_0016: ldloc.1 
    L_0017: ldc.i4.1 
    L_0018: add 
    L_0019: stloc.1 
    L_001a: ldloc.1 
    L_001b: ldloc.0 
*-> L_001c: callvirt instance int32 TestApp.Class1::get_Sum() 
    L_0021: blt.s L_000a 
    L_0023: ret 

As you can see by the marked line, the property is access for each iteration of the loop. I then updated the property access to save the value in a local variable as follows:

    Class1 c1 = new Class1(); 
    Int32 sum = c1.Sum; 
    for (Int32 i = 0; i < sum; i++) { 

This resulted in the following compiled IL code:

    .maxstack 2 
    .locals init ( 
        [0] class TestApp.Class1 c1, 
*->     [1] int32 sum, 
        [2] int32 i) 
    L_0000: newobj instance void TestApp.Class1::.ctor() 
    L_0005: stloc.0 
    L_0006: ldloc.0 
*-> L_0007: callvirt instance int32 TestApp.Class1::get_Sum() 
*-> L_000c: stloc.1 
    L_000d: ldc.i4.0 
    L_000e: stloc.2 
    L_000f: br.s L_0021 
    L_0011: ldloca.s i 
    L_0013: call instance string [mscorlib]System.Int32::ToString() 
    L_0018: call void [mscorlib]System.Console::WriteLine(string) 
    L_001d: ldloc.2 
    L_001e: ldc.i4.1 
    L_001f: add 
    L_0020: stloc.2 
    L_0021: ldloc.2 
*-> L_0022: ldloc.1 
    L_0023: blt.s L_0011 
    L_0025: ret 

As expected, the new code only access the property once outside the loop. The new local variable “sum” is then used inside the loop.

Obviously, this is a simple example and would not result in a noticable speed improvement, but it illustrates the point. I don’t think a general rule can be defined (e.g. always save the property value in a local variable), because every case varies. There may be cases where  the code expecting the value to change. Such as:

    while (false == c1.IsCompleted) { 

This is just something to keep in mind when coding and using properties. Personally, I like properties, but I just think of them as pretty methods 😉

Written by Tom

April 6, 2007 at 1:55 pm

Posted in .Net, C#, Optimization

Who’s Locking My Folder?

with one comment

Recently, I ran across a problem deleting a folder used by my application because the folder “was being used by another process”. Using Process Explorer, I was able to determine that it was my application that was holding the lock on the directory.

Because of the steps to reproduce, I was able to narrow down the method that was locking the folder fairly quickly. Once I found the problem method, I set a break point at the start of the method and fired up my application. When the break point was hit, I stepped over the first statement then checked Process Explorer to see if the folder in question was locked. I repeated these steps for each statement in the method, until Process Explorer reported the folder as open by my application.

The offending statement was a call to show an open file dialog (via the OpenFileDialog class). I proceeded by tweaking/removing the various properties of the OpenFileDialog being set by my code. When I changed the RestoreDirectory property from false to true, then the folder was no longer being locked.

MSDN says that if the RestoreDirectory property is true, then the “dialog box restores the current directory to its original value if the user changed the directory while searching for files”. If RestoreDirectory is false, then it will not restore the current directory to its original value and will maintain the directory previously selected by the user. It is apparent that the file dialog(s) open a handle to the directory previously selected by the user in order to use it as the initial directory the next time a file dialog is shown.

Using Lutz Roeder’s .Net Reflector, I was able to determine that the directory handle is opened by the Windows API (and is not specific to the .Net runtime). Specifically, the OFN_NOCHANGEDIR flag of the OPENFILENAME structure corresponds to the RestoreDirectory property.

To be clear, I don’t consider this a bug. It is just one of those gotchas that can have you tearing your hair out if you don’t know about it. A sample project can be downloaded from here.

Written by Tom

April 2, 2007 at 1:25 pm

Posted in .Net, C#, Gotcha

The Obligatory First Post

leave a comment »

I have finally decided to get on the “blog bandwagon” and start my own blog. My blogs will focus on things relating to .Net, C#, and SQL Server. Mainly because those are the areas that get the majority of my attention.

If you have any questions, comments or complaints then please feel free to shoot me an email. As usual, I’m not interested in herbal supplements so no spam please.

Written by Tom

April 2, 2007 at 11:57 am

Posted in Uncategorized