Can a .NET 4.5.2 Assembly be programmed to use CLR version 2.0 for SQL Server 2008 R2

Question

I am writing an assembly in Microsoft .NET Framework 4.5.2 that defines a type to be using on both sides of a database connection (inside the database as well as the DataTable on the .NET side).

The server is running SQL Server 2008 R2.

The SQL Server CLR version is v2.0.50727, and the .NET version on the windows server (version 2008 R2 also) is 4.5.2.

The SQL Server instance will only support .NET 3.5 and below due to the CLR version. I can't change that without upgrading to SQL Server 2012 immediately because this is a production server, but I have built an application around ASP.Net 4.5.2 and I don't believe I can change that now to use 3.5. My experience with the types is that they must be identical on both sides of the data connection.

I either have to force the 4.5.2 type .dll to be limited to CLR v2, force the compiler to allow mixed-version .NET assemblies, or find another way around the CLR v2 limitation on SQL Server 2008 R2.

Upgrading the SQL Server instance is not possible within the time frame I am working with.

Does anyone have a solution to this problem?

UPDATE:

As part of the solution, I implement the IBinarySerialize and Serialize interfaces in the type class definition. I use XDocument in the ToString() and Parse methods.

On the .NET side, I use stored procedure calls with the Dapper ORM to perform the CRUD operations. The custom types must be sent in to the SQL Server instance using the ToString() method and the type is parsed with the Parse method by SQL Server before being stored using the Write implementation by SQL Server.

Fetching of the data is done from the record using the Read method and sent to the .NET side using the ToString() method. On the .NET side, the return is then stored in the type using the Parse method by the .NET interface using Reflection.

It is the Reflection interface that expects the .NET type to match the SQL Server Assembly type. The information sent to the .NET Reflection interface includes the information about the type assembly that created the information being sent to the .NET side.

I guess the bottom line is that I need a way of tricking the Reflection interface into believing a type created with a .NET 3.5 is the same as a .NET 4.5.2 type declared the same way.

Answer 1

I have determined that the issue is caused by the Dapper micro-ORM. Dapper handles the serialization and deserialization on communications with the database.

SQL Server sends back information indicating the type for each column of a table it returns. This is where the fact that a custom type uses a specific assembly comes from.

This information is used by Dapper to load the assembly for custom types and parse the type using Reflection to determine which methods to use. If you use Dapper or perhaps another ORM, then the types must be defined the same on both sides of the communication. I can't say if this problem would be the same using the Entity Framework because I haven't used it. Our programming has been using Dapper/Dapper Extensions for all of our .NET programs.

So, the real solution to the problem is to go with the general lower-level DataTable methodology and not an ORM in this situation.

Thanks to Solomon Rutzky for pointing me in the right direction on this.

UPDATE:

Since posting this solution, I tried something I didn't think would work before.

I simple recompiled the assemblies using .NET 3.5 instead of .NET 4.5.2, and reinstalled them on the SQL Server 2012 instance I am using for development and testing. There were no changes to the coding details. I wasn't using async/await, the primary difference between 3.5 and 4.x, so this wasn't a problem.

This actually did the trick. I didn't have to rewrite the Web Api 2.2 web service I was using to communicate with the database to accommodate circumventing Dapper. Dapper only looks at the assembly number, and I didn't change that.

This probably won't work with every assembly, but it worked for the particular circumstances of my application set.

Answer 2

You likely won't be able to use the exact same type on both sides given that there is no way around the CLR 2.0 limitation of SQL Server 2005, 2008, and 2008 R2. I've never tried a mixed-version DLL, but not sure where that will get you unless you have different code for the CLR 2.0 usage since there won't be any "new in CLR 4.0 or above" functionality available to the Assembly running in SQL Server 2008 R2, in which case you might as well just write the Assembly to work in CLR 2.0 as that will run just as well in CLR 4.0 due to backwards compatibility.

You might consider saving a serialized representation of the type that can be fed into either version, and save it as either XML or VARBINARY on the SQL Server side for now, with the intention of upgrading the column to the UDT once SQL Server is upgraded in the future. But then you can always have the T-SQL code read in that serialized value in the constructor (or some other method). Basically it will just take one extra step on the database side, for now. Does that make sense?