sql server programming: I want to be able to provide an easy way for users to dynamically create
calculations to run on data in my database (SQL 2005). The data that the
calculations will process will be in XML format stored in a field in the
database. I want the user to be able to use my application write an
equation like:

((A+B)*10)-C

The software will know how to dynamically build an assembly containing a
class that can take some XML-format data, extract values for A, B, and C,
plug them into the formula, and provide a result. All of the calculations
will have a common interface/base class, but users could create any number
of these calculations.

In addition to the dynamically compiled calculation classes, I will have a
class designer (UI control) allowing users to safely design new
calculations, and a class builder that will actually create the class and
assembly from the user's input.

The database will have a stored procedure that will take as input the
name/id of a dynamically created calculation class, and some XML data, and
spit out the result of the specified calculation. An example of the SQL
that would use the dynamic calculations is:

SELECT
cdv.ClientName,
sp_Calculator( 'AlphaDynamicCalculationClassName', cdv.XmlDataField) AS
AlphaScore,
sp_Calculator( 'BetaDynamicCalculationClassName', cdv.XmlDataField) AS
BetaScore
FROM
ClientDataView cdv

The stored procedure in the database will need to know about the base
class/interface shared by all of the dynamically-created classes. Also, the
class builder in my application layer will need to know about the base
class/interface so it can construct the descendent/implementing classes. I
understand that SQL can't access stuff in the GAC, so I guess I'll have to
have base assembly/class in the GAC, and also loaded into SQL Server? I
take it as long as I'm using strong names for my base class/interface
assembly, it'll be okay to have 2 copies of the assembly: one in the GAC and
one in SQL Server? The dynamically compiled calculation classes will only
be used in SQL Server so I don't think they need to go in the GAC, unless...

Another wrinkle is that the user may need to update calculations they've
previously created. A dynamically created calculation class will have
methods so that I can interrogate the class to find out the details of the
calculation the user specified. This way, I can display their calculation
in the calculation designer so they can edit it, and I can build a new
version to replace the old one. But if the dynamically-compiled assemblies
are only in SQL Server, is there a way for me to access those classes from
outside SQL Server (i.e. from my business layer)? Or, do I need to be
storing these dynamically compiled assemblies on disk and in the SQL
database, as I do with the base class assembly?



FYI, there are a few reasons I'm doing this. My users need to be able to
create their own calculations. They won't have the ability to write their
own T-SQL or .NET code that will be able to process the XML data to get the
results. Also, I will be providing a number of 'standard' (not dynamically
alterable) calculations that will contain proprietary logic, and storing
this logic in pre-compiled, obfuscated assemblies. This will provide more
protection than if I tried to write this code in T-SQL. I also expect that
the performance of compiled .NET code calculating a value from the XML will
exceed that of T-SQL.

Hello cpnet,

My suggestion is that you could create a SQL CLR Stored Procedure and
create an assembly in the sql server.

Then, when you use the stored procedure, it will load the dll and run the
function you want.

For more detailed information, please follow the articles:

How to: Create and Run a CLR SQL Server Stored Procedure
http://msdn2.microsoft.com/en-us/library/5czye81z(VS.80).aspx

Also, here are some related articles:

http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CLRSProc.aspx

http://www.sqlteam.com/item.asp?ItemID=22074

Hope this will be helpful!

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Wei,

I do plan to have assemblies in SQL Server, and I generally understand how
to do this. I also understand that SQL Server can't access assemblies from
the GAC; assemblies have to be loaded into a SQL Server database.

My problem is that I will have some classes that will be used by my SQL
Server assemblies. These classes will also need to be used in the data
access and/or business layers of my application. I want to confirm that I
can do this, and that the way to do this is to make sure assemblies with
classes being used both inside and outside of SQL Server are strong named.
I will have to have 2 copies of the assemblies (one in SQL and one in the
GAC). This will allow me to get the proper classes from both inside and
outside of SQL Server?

Furthermore, if I have .NET classes in SQL Server, can I access those
classes 'directly'? Or would I have to create a layer of abstraction (using
stored procedures) between the methods/properties of my classes to get at
their functionality from outside of SQL Server?

Thanks,
cpnet

I remember when I went to the first road show for SQL Server 2005 and
listening as the development team was describing the CLR and the method
of storage within the database. I'd guess that 90% of the attendees
were OOP programmers looking to leverage their pre-built assemblies
from within the database; they just didn't get why you couldn't do it.


Finally, the light went on for the room when the presenter mentioned
the issue of database backups. I haven't played with the CLR yet, but
as I understand it, one of the main reasons for hosting the CLR within
SQL Server (rather than storing the assemblies with the GAC) was to
maintain the concept of the database as a portable container. You can
backup a database with CLR procedures and restore it to another SQL
2005 server and the procedures move. No need to worry about moving
additional assemblies or objects; it's just a single container for all
of your data and data access interfaces. That made the few
DBA/Developers in the room VERY happy.

It does mean that there will be some overlap between your business
layer and your CLR-enabled data access layers; however, in many
enterprises, I would think that the database often sits on a server by
itself, and the client layer and the business layer are often on a
single box or a middle tier. In that scenario, even if SQL Server
could access the GAC directly, you'd still have to maintain multiple
copies of the assemblies (one for each physical box).

Not sure if that helps; perhaps I'm just getting old and like to ramble
on for no reason. Get off my lawn!

Stu


[quoted text, click to view]