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.
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?