First off, thanks for the great work in this new beta release.

I have a SQL Server 2005 instance, and have a CLR Stored Procedure running within. Right now, all of the data queries in the procedure are being done via standard, painful SQLCommand objects.

I would like to try leveraging LLBLGen classes within the procedure. SQL Server will automatically load all referenced assemblies, except it has problems with EnterpriseServices running inside a SQL Server instance.

Without this reference, my LLBLGen assembly will load into the server just fine. Is there any way around the Enterprise Services reference?

Thanks and good work, Josh

In the runtime library you mean? I can't, because classes in the runtime libs require enterprise services

What you can do is build a custom build of the runtime lib sourcecode. It's provided in the Sourcecode folder. There are a few classes (LIke the DataAccessAdapterBase) which refer to enterprise services namespace, but it's relatively minor. If you need help with certain areas, let me know.

I have made the minor changes to the source, and the ORMSupportClasses assembly compiles fine (had to make a new key pair). However, when I alter the reference in the generated project it gives me reference errors.

For example: 'The type SD.LLBLGen.Pro.ORMSupportClasses .DynamicQueryEngineBase is defined in an assembly that is not referenced. you must add a reference to assembly SD.LLBLGen.Pro.ORMSupportClasses .NET20, Version=2.0.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27'

Do I need the original strong name key?

Thanks for the help.

This appears that it may be a known issue for the beta.

VS.NET 2005 selects v1.0.2005.1 runtime dlls when a newly generated project is loaded. Workaround: reset the references manually to the proper new runtime libraries.

If that doesn't fix it then let us know and we can provide more help.

I was aware of the broken references, the problem was due to the fact that I didn't re-compile the SqlServer.DQE against my new ORMSupportClasses.dll. Oops.

I compiled a kind of 'lite' version of the ORMSupportClasses.dll, that avoids references to the following assemblies: System.Design System.Drawing System.Drawing.Design System.EnterpriseServices System.Web System.Windows.Forms

This was done to avoid having to load these external references into a SQLServer 2005 instance. Though they are part of the Framework, they are not present in the SQLServer 2005 CLR Framework apparently.

I did this by removing a few design-time support files, and commenting out some designer attributes / design time checks.

Here are the list of files I removed. I you foresee any problems removing these files / functionality please let me know.

ComPlusAdapterContextBase.cs DataSourceDesigner2ConfigurationForm.cs DataSourceDesignerConfigurationForm.cs DesignTimeDatabindingClasses.cs (keeping ths TypeConverter classes) EntityFactorySelector.cs LLBLGenProDataSourceClasses.cs TransactionComPlusBase.cs

The only slight obstacle I've run into now is the fact that you have to install the assemblies into SQL Server as "UNSAFE". I'm not sure exactly why this is, if you try as SAFE, SQLServer throws an error about a Synchonization attribute on a dispose method in one of the classes.

With this lite version of the ORMSupportClasses, I am hoping to leverage LLBLGen generated classes in CLR stored procedure code. (partly by taking advantage of the new mechanism by which LLBLGen will return a raw DataReader from the DQE, which can be returned to the client) I am interested to see what the performace will be in this scenario.

I'm new to having an assembly installed in sqlserver 2005. Could you specify the command to use for installing so I can try it here as well, and build a lite version?

Sure, it's T-SQL syntax, with the following command:

CREATE ASSEMBLY [Name (can be any name)] FROM [path of assembly (C:\myassembly.dll)] WITH PERMISSION_SET={SAFE | EXTERNAL_ACCESS | UNSAFE}

Without the WITH PERMISSION_SET tag, it defaults to SAFE, but it didn't allow the 'lite' assmebly to catalog that way. (Try it and see the error message)

you can find the full syntax here: http://msdn2.microsoft.com/en-US/library/ms189524.aspx

To update an assembly (after a recompile for example) use ALTER ASSEMBLY with the same syntax as above. http://msdn2.microsoft.com/en-US/library/ms186711(SQL.90).aspx

SQLServer2005 will automatically reflect all references from the assembly you load in, and load those in as well into the server. So I just loaded my LLBLGen DAL class library, and it automatically pulled in the ormsupportclasses and sqlserver.dqe.

Of course, to use any of this code, you need a CLR stored procedure. Make any class, and a public static method that does something. Be sure to set the connection string to "context connection= true" before doing data access with the classes. Then load the assembly into SQL Server, and create the stored procedure reference by doing the following:

CREATE PROCEDURE [proc_name] EXTERNAL NAME [Name (from the Create statement)].ClassName.MethodName

If you have a namespace, ClassName must be [namespace.classname] (in the brackets)

Good Luck, Josh

Thanks!

What I wondered though was: wouldn't this be inefficient due to the out-of-process SqlServer provider used by the DQE? If I'm not mistaken, CLR code executing queries can be a lot more efficient if a different sqlserver provider is used (perhaps I'm talking nonsense, this is what I remember from looking at it months ago)

SQL Server 2005 actually hosts the CLR inside it's own process. Everything, including your assemblies, run within sqlserver.exe. The only assemblies even accessed from outside the database are the framework assemblies, from the GAC.

Using the SQLClient classes are the suggested way to code CLR procs / functions / etc. It all runs within the context of the current connection, and right within the SQL Server process. My understanding is that as long as you use the connection string "connection context=true", all SQLCommand objects associated with that connection are run within the local context. i.e. You get the same SQLCommand object by doing:

SQLCommand cmd = SQLContext.GetCommand();

or by

using(SQLConnection conn = new SQLConnection("connection context=true"))
{
       SQLCommand cmd = conn.CreateCommand();
}

Other than the additional memory overhead from the CLR having to be loaded / operating and the class instances, it seems like the performance shouldn't be too bad. I am going to be testing this afternoon, and I'll see what I can find out. My intention is using this not for standard insert/select action, but for a rules engine sp that needs to be able to crunch through large amounts of data. Microsoft claims that for CPU-intensive tasks, .NET outperforms T-SQL. We shall see. =)

I just ran some basic tests against the Profiler and gathered some interesting results.

1. Inserting a record into a table via standard T-SQL: (via Management Studio)

INSERT INTO Contribution VALUES(450,'4/4/2005',1)

• Duration: 2-8 ms.

2. Insert a record via a CLR Stored Procedure that uses LLBLGen classes. The following class was compiled into a class library and loaded into SQL Server 2005. It uses a modified version of ORMSupportClasses.dll, with many framework dependencies removed. (SQL Server only has access to a limited subset of the framework)

    public class StoredProcedures
    {
        [SqlProcedure]
        public static int InsertContribution(SqlInt32 contactID, SqlDecimal amount, SqlDateTime datetime)
        {
            // Do null check
            if (contactID.IsNull || amount.IsNull || datetime.IsNull)
                return -1;

            // Do insert
            RulesEngine.HelperClasses.DbUtils.ActualConnectionString = "context connection=true";
            RulesEngine.EntityClasses.ContributionEntity ce = new RulesEngine.EntityClasses.ContributionEntity();
            ce.ContactId = contactID.Value;
            ce.Amount = amount.Value;
            ce.Date = datetime.Value;
            bool success = ce.Save();
            return success ? 0 : -1;
            
        }
    }

This stored procedure was executed via T-SQL as

EXEC dbo.InsertContribution 1,123,'6/6/2005'

• Duration: 3-10 ms

3. Insert a contribution via a test client running the same LLBLGen generated DAL assembly to call the CLR stored procedure. Code to run this simple command line app:

namespace TestClient
{
    class Program
    {
        static void Main(string[] args)
        {
            RulesEngine.StoredProcedureCallerClasses.ActionProcedures.InsertContribution(1, 650, DateTime.Now);
        }
    }
}

• Duration: 1-4 ms

The difference in duration between these three tests is negligible. Granted, this was done in a controlled, local instance, with no load, but it looks very promising for .NET coders who want to put logic into SQL Server. Especially considering they can use LLBLGen classes with no performance penalty! (at first glance).

Yesterday I made the final changes to the ORMSupportclasses to get it compiled against the compact framework and I think it's exactly the same code that'll work inside SQLServer, when build against the normal framework library dlls instead of the CF dlls.

I'm not sure if I should include the lite versions of sqlserver DQE and ormsupport classes though, although it looks like just another makefile . did you need to make template changes?

I could use LLBLGen Pro in SQL Server 2005 CLR stored procedures, triggers, and functions too. Will it be supported?

Casper

Not at the moment, as in: officially with a ready-to-run preset. I don't see a problem with the generated code being used inside sqlserver 2005, though it might require tweaking on your part, we don't support that at the moment, nor are there plans for supporting this in the near future.

In 2006 you guys had the follow exchange:

chorns wrote:

I could use LLBLGen Pro in SQL Server 2005 CLR stored procedures, triggers, and functions too. Will it be supported?

Casper

Then

Otis wrote:

Not at the moment, as in: officially with a ready-to-run preset. I don't see a problem with the generated code being used inside sqlserver 2005, though it might require tweaking on your part, we don't support that at the moment, nor are there plans for supporting this in the near future.

So its 2009 now, is LLBLGen SQLCLR friendly (supported) yet?

There was no plan to support this, and nothing has changed since then.

To elaborate a bit: the enterpriseservices assembly is required for our COM+ transaction support. This is in the runtime lib and in a couple of classes. If you don't want that, you can remove these classes from the sourcecode and compile the ormsupportclasses yourself. Ditto with the generated code. This then will give you a code base which is usable inside sqlserver, however as stated we don't have plans to officially support this, so it might be in edge cases there might be pieces of code which also dont work as we don't test for these scenarios.