Run CLR in SQL query from Embedded Resource

[JOSheaIV]

So hopefully the title makes a little sense, but I'll explain a little more.

Currently I am developing a program at work that is designed to query a SQL database for results. The code is meant to be stand alone, a single .exe that can be run on any machine. Now, I want to improve the logic of these queries a little, which after talking with a co-worker, have determined a CLR function would be perfect. The thing is, I have never done CLR before.

I have been searching the net for it, and the idea behind it seems pretty straight forward. However, here's the kicker.

The program I am working on, I want to write the CLR function within the code, and be able to SQL queries in the same program using that CLR function. The servers I am going to be querying against will have CLR Integration enabled, so I don't have to worry about that.

The problem is, I have no clue how to go about doing this. The program is meant to be lightweight, aka a single exe. I don't want to start extracting files everywhere with this. Also, it needs to be able to run on any machine, and communicate with the SQL Server.

So how would I go about achieving the goal listed above. If I could build a project and somehow load the assembly into memory that the SQL could use it that would be great. But remember, the SQL queries I am running are within the same solution as the where I want to develop the CLR function.

Any help would be greatly appreciated. I am not exactly sure what the proper terming is or logic behind what I am trying to do, which is making it hard to search for.

Thanks

[deceptikon]

Hmm, either my understanding of CLR integration in SQL Server is woefully out of date, or there's a disconnect somewhere. CLR functions are server side entities. You still need vanilla connection strings and T-SQL in your client application.

So your problem boils down to two things as I see it:

1. Creating a standalone .NET application that doesn't need an installer or any secondary files.

2. Defining communication with the SQL Server in a way that doesn't need configuration files yet can still be used universally.

The first is simple enough, provided you can safely assume the target .NET framework to be installed. Just make sure your application doesn't need anything that won't be provided by the framework. The second is harder for obvious reasons, and not friendly to changes, but a good start is enabling remote access to the SQL Server and ensuring it's widely available.

[JOSheaIV]

Hmm, I feel I might have lacked explaining the full scenario (knowing me I probably just confused people, ha).

Well first I should point out, this program does run fine, and has been run on other machines without any problems. This includes running SQL queries and getting data back properly (remember it's a stand alone application). To better explain it, it's used to view XML data that is stored within the database and search it (thank heavens for XQuery).

Now what I am working on is upgrading the application, making the queries even smarter. Originally I was looking to build a SQL User-Defined function, but a co-worker suggested I write a function in C# and use CLR to my advantage (since the servers we are running against must have CLR enable).

My thing is, I want to be able to hand someone, who isn't super tech savvy and executable that they can run, that's it. No special setup or anything (and I want to keep it to one .exe file).

The ways I have seen to use CLR is you either have to create a project, and build it. Once there, you copy the assembly from the bin folder over to a folder SQL Server recognizes. The other is to set up a SQL Server Project, which to me looks to be limited to a single server/database, and if I were to run this elsewhere it wouldn't work. ALSO, I can't guarantee that the system this program is run on will be the same one that houses the SQL Server Database (meaning that first part would be useless), only that it can talk to the database.

I want to be able to write the CLR function in this current solution I already have, and use it in the existing SQL queries I already have. One executable that does all my work, that already does all the queries, but now has this nice CLR object at its disposal to use as well.

Hmmm, should have wrote it this way the first time. Seems to make more sense now. Guess that discussion I had about other topic with one of the devs stimulated my brain, haha

[JOSheaIV]

Quick update.

So I have found how to load an assembly from a file, into SQL Server with the CREATE ASSEMBLY and CREATE FUNCTION SQL commands.

Now my next question is, how can I do this from an Embedded Resource loaded into memory?

The Create Assembly command I have now requires a file path. The only way at the moment I can think to approach this is to extract the Embedded Resource to a designated path, and then, use that path for the SQL query. But I would really love if I could just load it from memory

[JOSheaIV]

Okay last update.

So I ended up finding out that you can load in the Assembly from an Embedded Resource (and if anyone finds this I thought they'd like to know).

When it comes to the CREATE ASSEMBLY function, you can pass in the path of a dll like I mentioned before. However, there is another way. You can load in the DLL from a byte array.

This is an example of what that snippet of code might look like

//Note: That before this I did a query to see if the Assembly already was loaded 
//      in use COUNT. Using 
//      "SELECT COUNT(*) FROM sys.assemblies WHERE name = 'AssemblyName'

//Also, I pass in the name of the resource in the string "EmbeddedResource"
using (Stream stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(EmbeddedResource))
{
    if (stream != null)
    {
        byte [] ByteArray = new byte [(int) stream.Length];
        stream.Read(ByteArray, 0, (int) stream.Length);

        StringBuilder HexString = new StringBuilder(ByteArray.Length * 2);
        foreach (byte b in ByteArray)
        {
            HexString.AppendFormat("{0:x2}", b);
        }

        command.ExecuteNonQuery(String.Format(@"CREATE ASSEMBLY {0} FROM 0x{1} WITH PERMISSION_SET = SAFE", AssemblyName, HexString));
    }
    else
    {
        throw new Exception(String.Format("Embedded Resource: {0}, Could Not Be Found", EmbeddedResource));
    }
}

Hope this helps someone else who may be trying to do the same task I was