How to create assembly using command?

Question

Hi,

after creating the database "Data Source=localhost\SQLEXPRESS;Initial Catalog=My_DB;Integrated Security=SSPI;"

I created some functions into example.dll and tried to call hello like this:

command.CommandText = "CREATE ASSEMBLY demo FROM '..\Debug\example.dll'";
command.ExecuteNonQuery();

command.CommandText = "CREATE FUNCTION dbo.hello(@args NVARCHAR(256)) RETURNS NVARCHAR(256) as  EXTERNAL NAME demo.test.hello;";                             
command.ExecuteNonQuery();

The code runs without problems.

However when checking on ssms, the assembly did not appear.

Is this unusual?

I tried creating a table and querying with commandtext

"SELECT hello('something') from table"

but got an error: build-in function not found.

How I can fix this?

Thank you.

Answer 1

command.CommandText = "CREATE ASSEMBLY demo FROM '..\Debug\example.dll'";

The path for the CREATE ASSEMBLY statement looks funny. The path you give is resolved by SQL Server, so .. is relative to SQL Server's current folder, whichever that may be. It is not relative to the current folder of the running the program. You should always have an absolute path here. But that is only in the case that you know for sure that the assembly is on the machine where SQL Server is running - which may be different from the machine where your C# program runs.

If the DLL is intended to be located on the same machine as the C# program, you should read the DLL into a byte[]pass CREATE ASSEMBLY a binary:

CREATE ASSEMBLY demo FROM 0x08000.....

However when checking on ssms, the assembly did not appear.

Given the file path, I am not surprised if the assembly never was created. You said that the code ran fine. May the issue is that your exception handler swallows the error?

I tried creating a table and querying with commandtext "SELECT hello('something') from table" but got an error: build-in function not found.

You must always call user-defined functions with two-part notation, that is, you need to include the schema:

SELECT dbo.hello('something')

A scalar function without schema is always interpreted as a built-in function.