LLB v2.6 Adapter template Sql Server 2008

I have seen a couple of old threads where using LLB entities in SQL CLR is mentioned. This approach would save me a lot of time if it works.

I am thinking about creating views each with an instead of update trigger which would have some logic to decide whether to simply continue with the update or add a history row with temporal dates and some row versioning numbers.

I can generate T-SQL for this, but it occurs to me that my LLB project has already got everything I need to do it very efficiently.

I have experimented with getting my LLB dlls into the sql server using CREATE ASSEMBLY ... but I get a dependence on system.windows.forms which prevent this.

Adding system.windows.forms with CREATE ASSEMBLY works, but then I get more problems.

Here is where I have reached:

CREATE ASSEMBLY [System.Windows.Forms] from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'
WITH permission_set = UNSAFE
CREATE ASSEMBLY [Temporal.Adapter] 
FROM 'D:\Work\Temporal\Adapter\DatabaseGeneric\bin\Debug\Temporal.Adapter.dll'
WITH permission_set = UNSAFE

Which outputs:

Warning: The Microsoft .NET Framework assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
Warning: The Microsoft .NET Framework assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
Warning: The Microsoft .NET Framework assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
Warning: The Microsoft .NET Framework assembly 'system.runtime.serialization.formatters.soap, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
Msg 10301, Level 16, State 1, Line 5
Assembly 'Temporal.Adapter' references assembly 'system.design, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(failed to retrieve text for this error. Reason: 15105)). Please load the referenced assembly into the current database and retry your request.

I guess it would be really useful if someone with more knowledge of this than me could tell me whether I am wasting my time or not.

I wonder why you get a dependence on system.windows.forms. Anyway I don't know if this ever worked. http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=2605

daelmo wrote:

I wonder why you get a dependence on system.windows.forms. Anyway I don't know if this ever worked. http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=2605

Yes I saw that thread from 5 years ago and wondered if its lack of a conclusion had been superceded.

I too was baffled by the Forms dependency. I searched all the generated code and couldn't find it. Maybe it is needed by some other dll.

There is a windows forms dependency in the ormsupportclasses, namely for the dialogs which are shown when you design a collection for windows forms design time databinding. Likewise there's also a design time related dependency on web for the datasourcecontrols.

We did look into removing these dependencies, i.e. to move them to other assemblies. The problem is that this will break any application which currently uses our runtime and design time functionality. So to make this work you, you have to manually compile the ormsupportclasses from sourcecode and remove the dialogs and likewise the dependency.

I have managed to remove the dependency on System.Windows.Forms and compile the two dlls successfully.

Unfortunately, I still can't add them to SQL Server as they have a dependency on System.Web, which has a dependency on System.Drawing...

I think it is called "Shaving a Yak".

Unless anyone has actually done this succesfully and can advise me, I am going to give up.

Thanks for the comments anyway!

You should try to remove the System.Web dependancy the same way you removed the System.Windows.Forms dependancy. You won't be needing the LLBLGenProDataSource inside the SQL CLR

Walaa wrote:

You should try to remove the System.Web dependancy the same way you removed the System.Windows.Forms dependancy. You won't be needing the LLBLGenProDataSource inside the SQL CLR

I have removed the System.Web dependency, but now I have a dependency on System.EnterpriseServices. I don't know whether to try and remove this dependency too or try and get the assembly into SQL Server somehow.

This dependency seems to be exclusive to the Adapter template.

Putting System.EnterpriseServices into SQL Server requires a deeper understanding of how to use safe, external use, and unsafe assemblies than I currently possess, but i guess I can learn.

Please check this out: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6052

It's also a bit of a problem, the dependencies could be refactored out but there's another problem: sql server treats the assemblies really as hostile things, IMHO. Versioning is very important, it becomes a general pain to update the assemblies after you've installed them: you have to drop all referring objects in the db before removing the assemblies, then update the assemblies, then re-create the objects referring to them.

The dependencies on web and winforms could be refactored out, but we didn't do this as it would break a lot of code with design time data. If you can live with a specific version of the runtime, you can remove the classes referring to these 'external' classes and recompile the assembly. For enterprise services, you indeed have to comment out some code in DataAccessAdapterBase but that shouldn't be too hard.

Just an update. I have managed to eliminate forms, design and enterpriseservices dlls. Some of this was a little bit messy and I think next time round I could be cleaner over it.

Mainly, I created stubs for classes that were expected which used those dlls.

I have created a Self Service project and added the 3 new dlls plus my new one as unsafe assemblies in Sql Server.

Next I need to see if I can actually use them in a database project to create objects such as triggers.

If anyone has any advice or pointers on my next steps I would be truly grateful. Otherwise, I will plough on using trial and error.

Thanks for the help so far.

The only advice I have is to keep things stateless at all costs. So your code can't store state anywhere.