Create Assembly in SQL Sevrer 2008 without knowing exact path

Question

Hi.

I need help with how to create an assembly in a sql server 2008 database witout being able to issue the command "CREATE ASSEMBLY FROM" with a local or networked path.

I have a Database Setup project that references the .dll, I could also have it as a resource if that helps.  The Setup project is wpf and will run as an exe and will if needed run from a directory where the .dll in question resides.
 
What I need is a way to create the assembly in the database without SQL Server knowing where the assembly is (at least from a local or UNC point of view), as install paths and computernames differ...obviously. It needs to be generic and work from all paths therefore I cannot hard code the path into the CREATE ASSEMBLY command. Of course I don't have to issue that command if I can use others, but thats what I know of now.

I will of course be able to establish a SqlConnection between my client and Sql server

Is this even possible?

I could elaborate a whole lot more to get you to understand my problem, but it would most likely be too much so I'll wait and see if anyone gets this.


Best Regards/t

Answer 1

You can create an assembly from a stream of bits, you don't need a physical
file. Reference the CREATE ASSEMBLY DDL statement. For an example, locate
any installed assembly in SSMS, right-click and choose "script assembly...
as create". This is the mechanism Visual Studio's SQL Server projects use
for their autodeploy functionality.

Cheers,
Bob Beauchemin
SQLskills

Answer 2

Hi, thanks!  Yes I have seen thoose bitstreams when generating sql scripts from our db's and I use them when building new database setup scripts. Also the current solution uses the stream but as a part of a T-SQL script. But I never thought of the solution to get the stream of bits and using it instead of the "physical" assembly or the prefab T-SQL script, so many thanks for that :) !

I trying to avoid to having to create the T-SQL script, or stream of bits, ahead of compiling/building our solution because we increment the assembly version the last thing we do before making a complete build. The way I'm going for makes it sustainable, whenever we use our DB setup program it always have the correct assembly to "deploy" to the new or updated database instead of having to increnment versions, build,  deploy to db, create script, edit db setup software, build again but without incrementing... you get the idea...


So that helps me with the physical file, next problem. How do I get the stream of bits from a referenced project/assembly without having it as a resource (and from that one as well I guess...)? I'm not a star C# or .Net programmer (as you might have guessed), but, uhumm, I'm trying!

Many thanks, cheers!
/Torkel.

Answer 3

You'll need to create a T-SQL script somewhere in the process, as CREATE ASSEMBLY is the only way to get an assembly into SQL Server. You can create a script by extracting the bitstream from the DLL file the compiler creates using the System.IO classes and outputing a hexidecimal representation of the bits. You can do that as part of your project build process, similar to the way Visual Studio "SQL Server Projects" do. I'd start by studying the way these projects work or look at the build project at http://www.codeplex.com/sqlclrproject

Hope this helps,
Bob Beauchemin
SQLskills

Answer 4

Hi Bob, thanks for your patience :)

Yep, I'm clear on all those steps. My issue is "extracting the bitstream from the DLL file the compiler creates using the System.IO classes and outputing a hexidecimal representation of the bits"

I now have the assembly loaded in a Memorystream and uploaded in a byte array.

formatter.Serialize(assemblyStream, assembly);

assemblyStream.Seek(0, SeekOrigin.Begin);

dllAsArray = assemblyStream.ToArray();

How do I get the hexadecimal representation?, that's the problem!

Now I have a solution, with some inspiration from others:

using (MemoryStream assemblyStream = new MemoryStream())
{
formatter.Serialize(assemblyStream, assembly);
assemblyStream.Seek(0, SeekOrigin.Begin);

byte[] dllAsArray;

dllAsArray = new byte[assemblyStream.Length];

assemblyStream.Read(dllAsArray, 0, (int)assemblyStream.Length);

assemblyStream.Close();

StringBuilder sb = new StringBuilder();

//we're expecting a preceding 0x
sb.Append("0x");

//then read through the byte array and convert it to binary, format it wide by "X2"
foreach (byte b in dllAsArray)
{
sb.Append(b.ToString("X2"));
}

string assemblyString = sb.ToString();

}

But I was hoping for a "cleaner" way, without having to loop through and building the string.

Regards/Torkel.



EDIT: this does however not work as it seems that formatter.Serialize(assemblyStream, assembly); only reads the assembly name. Is there any way to get a byte[] from an assembly without having to read it from disk???!