SQL Server 2005 create assembly from stream with C#

Can i create an assembly from a stream much like MSSMS generated assembly script? I need to create an assembly from a DLL that is placed in a directory the SQL Server doesn't have ac开发者_StackOverflowcess to.

The assembly installer does have access to the assembly though.

So my question is: can i read the assembly and generate a create from the stream read?

Thanks.

---

I think i figured it out..
I'm using Tomalak's Byte Array to Hexadecimal method from this post.

     private string getAssemblyAsHex(string asmPath)
     {
        using (BinaryReader br = new BinaryReader(File.OpenRead(asmPath)))
        {
            byte[] buff = new byte[br.BaseStream.Length];
            br.Read(buff, 0, buff.Length - 1);
            return ByteArrayToString(buff);
        }
     }


    private static string ByteArrayToString(byte[] ba)
    {
        StringBuilder hex = new StringBuilder(ba.Length * 2);
        foreach (byte b in ba)
            hex.AppendFormat("{0:x2}", b);
        return hex.ToString();
    }

Then i just concat this string to the CREATE ASSEMBLY statement.
The code is written with a small DLL assembly in mind.

---

I was just pleasantly surprised to discover that Visual Studio 2013 automatically generates a SQL script with the bit string used in a CREATE ASSEMBLY call when you build a SQL CLR project.

Build the project, then in the bin folder unpack the "*.dacpac" file using DacUnpack.exe. In the folder that gets unpacked look for "model.sql". It contains a CREATE ASSEMBLY call that looks like so:

CREATE ASSEMBLY [MyFunctions]
AUTHORIZATION [dbo]
FROM 0x4D5A90000[very long string here...];

---

The initial versions of Visual Studio / SSDT (starting with Visual Studio 2005) that supported SQLCLR (which was introduced in SQL Server 2005) did rely upon the DLL and a file system reference to it in the CREATE ASSEMBLY command of the publish / deploy script. However, by Visual Studio 2012, that approach was replaced by scripting out the VARBINARY literal (i.e. hex bytes string that the documentation refers to). So anyone using Visual Studio / SSDT since that time would not have necessarily needed to do this step manually, if one was using the generated deployment scripts. If you are not using SSDT generated deployment scripts, then the best approach to converting the DLL into this VARBINARY literal is an open-source utility that I created that:

• handles very long lines of hex bytes by employing line-continuation. This makes scripts both more readable and more manageable in SSMS and other editors that don't work so well on single lines of 100k+ characters.
• outputs either to a file (used for automating / including the output in a deployment scripts), or directly to the clipboard (used along with a short-cut added to your Windows Login's SendTo folder, allows for right-clicking on a DLL and then simply pasting into a script that you are editing).

The code (and pre-compiled EXE) can be found on GitHub here: BinaryFormatter

For information on working with SQLCLR in SQL Server 2017 and newer, please see my blog posts:

• SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
  Asymmetric Key-based solution: a few more steps than "Solution 2", but no changes to the main SQLCLR project in Visual Studio. This is a way to get an otherwise empty DLL containing the asymmetric key into SQL Server without disabling any security.
• SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2
  Certificate-based solution: fewer steps than "Solution 1", but requires a minor change to how the main SQLCLR project is built in Visual Studio.

For more information on working with SQLCLR in general, please visit: SQLCLR Info