CLR Assembly C# inside SQL Server

Question

Is it possible to make a big project in C# (lots of functions), then,
Create CLR Assembly for it, then,
In SQL Server IN A STORED PROC, call a function that is in the assembly,
The table (which I would pass to ASSEMBLY) is computed in other stored proc...
- If so, What would be the steps?

I am thinking something like this.

-- I have a stored proc that gets a table, let it be myStoredProcTable

--FIST ENABLE DATA ACCESS
EXEC sp_serveroption 'TheServerName', 'DATA ACCESS', TRUE

--main.sql calls yStoredProcTable.sql and the calls functionAssembly
SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(SERVERNAME, 'EXEC test.dbo.myStoredProcTable 1')


-- pass the table to assembly
-- how would i pass the table to assembly code?, Is this POSSIBLE?
    EXEC functionAssembly #tmpTable

------------------------------------------edit

Following @faester answer: - How could I use XML in the code, I suggested to use the numberTOstring thing, but I guess XML option is the best here...

Again, I really do this, even if is not the best choice...

Well, I'd like to, but those steps are already done. I thought this so `I can make a bridge for all this code` — edgarmtze, Jun 15 '11 at 20:52

score 6 · Accepted Answer · answered Jun 15 '11 at 21:23

6

Yes you can register assemblies, but it is rarely a good idea due to performance issues.

But if you make complex numeric calculations or similar operations on scalar values it can give you a lot of flexibility. But the problem remains that SQL is natively set oriented which C# isn't, so you will easily run into mismatches.

You should also be aware that you can only import static members on static classes.

But an example This class - which intentionally doesn't have a namespace since it seems to be impossible to import classes in a namespace.

    public static class Math
    {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static int Add(int a, int b)
        {
            return a + b;
        }


        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void Void(int a, int b)
        {
        }
    }

It takes some SQL to get the server ready and you probably need to be admin.

    EXEC SP_CONFIGURE 'clr enabled', 1
    GO
    RECONFIGURE
    GO
    -- CONSIDER: DROP ASSEMBLY SqlClr
    GO
    CREATE ASSEMBLY SqlClr 
    FROM 'pathtoassembly'
    WITH PERMISSION_SET = SAFE;
    GO
    SELECT * FROM sys.assemblies
    GO
    CREATE FUNCTION [MathAdd] (@a int, @b int)
    RETURNS INT 
    AS EXTERNAL NAME [SqlClr].Math.[Add]
    GO 
    CREATE PROCEDURE [Void] @a INT, @b INT
    AS EXTERNAL NAME [SqlClr].Math.[Void]
    GO 
    SELECT dbo.MathAdd (1, 2)
    EXEC void 1, 2

AGAIN: You really should be confident that you need this, it is rarely a good idea! (I have used it once for email validation making dns lookups etc, but that was on a system where all business logics was written in SQL. And that is bad!)

Some useful references:

http://msdn.microsoft.com/en-us/library/ms189524.aspx

http://www.codeproject.com/KB/database/CLR_in_Sql_Server_2005.aspx

answered Jun 15 '11 at 21:23

faester

14,886
5
45
56

Yes exactly what I need!!!!, only one thing , as you pass `2 ints` as parameters I was thinking to pass column by column and assignt it to a var, so for example lets say if I have 10 columns, make 10 variables, and then assign to each var each column `How would you pass or save in a variable a table column, I mean, as you said (@a int,@b int)... How to make a vector?` – edgarmtze Jun 15 '11 at 21:27
I am not sure, but in general there are mappings between CLR and SQL types, so perhaps you can pass in a DataTable that would translate into a construct like `DECLARE @t TABLE (id int not null)`. This is pure guessing though. Your procedure could also read from some table. – faester Jun 15 '11 at 21:54
Type conversion here: http://msdn.microsoft.com/en-us/library/ms131092.aspx - It looks as if you cannot input collections, but as I mentioned earlier anything but scalar values is expensive in this context. Your best option for inputting collections are probably to create SqlXml elements first. – faester Jun 15 '11 at 21:57
I was thinking in doing a select table of a column and make the numbers a string `declare @List varchar(max) select @List = isnull(@List + ',', '') + cast(ColumnName as varchar) from MyTable` is this factible?, then in C# code I would restore the string to a vector..., could you please post some code following your XML idea.... – edgarmtze Jun 16 '11 at 06:26
About XML I was just thinking that is was simpler to pass multiple values in an XML doc than in some csv-style string. I have not used it so I cannot produce any code. – faester Jun 16 '11 at 08:29
So, An XML file would have to be generated, or is it another way?, returning to the big string idea, What do you think? – edgarmtze Jun 16 '11 at 15:57
Splitting a string in C# is surely peace of cake, so big string probably isn't a bad idea. Knowing a bit more of your context might be usefull though. – faester Jun 16 '11 at 20:57
the context would be a table full of numbers as in the example in the question, I am planning to read the table and assign all numbers in a column... – edgarmtze Jun 16 '11 at 23:16

score 1 · Answer 2 · answered Jun 15 '11 at 20:55

1

Use the SQL CLR to perform the exact function you described.

answered Jun 15 '11 at 20:55

Jonathan

1,866
12
10

1

Is it possible to make a dll with that c# code and call it in SQL? – edgarmtze Jun 15 '11 at 20:58

score 0 · Answer 3 · answered Jun 15 '11 at 20:54

0

It is possible. (But read the comments).

answered Jun 15 '11 at 20:54

driis

161,458
45
265
341

CLR Assembly C# inside SQL Server

3 Answers3

Linked