CODEWITHSUNDEEP
.netsql-serversqlclr
toddmo
toddmo

Reputation: 22406

Getting SQL Server CLR "Create Assembly" location from within the DLL itself

The assembly is stored in the database, and you can get the registered location like this:

SELECT af.name FROM sys.assemblies a
INNER JOIN sys.assembly_files af
        ON a.assembly_id = af.assembly_id
WHERE a.name = 'MyAssemblyName'

However, I'd like to get it without asking SQL Server and I'd like to get it from within the running assembly. Assembly.GetExecutingAssembly().Location returns blank. AppDomain.Current.BaseDirectory returns the binn path of SQL Server.

When I registered the assembly with CREATE ASSEMBLY, I gave it a path. I'd like to get that path.

EDIT (SOLUTION)

I had to consult the database server after all. But it's not so bad because at least the connection string is not hard coded.

[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlString AssemblyLocation()
{
  using (var connection = new SqlConnection("context connection=true"))
  {
    var cmd = new SqlCommand($"SELECT af.name FROM sys.assemblies a INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id WHERE a.name = '{AssemblyName().Value}'", connection);
    connection.Open();
    return cmd.ExecuteScalar().ToString();
  }
}

[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlString AssemblyName()
{
  return typeof(StoredProcedures).Assembly.GetName().Name;
}

[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlString WebApiRoot()
{
  var config = ConfigurationManager.OpenExeConfiguration(AssemblyLocation().Value);
  return config.AppSettings.Settings["WebApiRoot"].Value;
}

Upvotes: 1

Views: 1442

Answers (2)

Ben Thul
Ben Thul

Reputation: 32687

From the comments in the other answer, it sounds like you want a context connection. From the examples in the documentation: 

SqlConnection connection = new SqlConnection("context connection=true")

Upvotes: 0

Solomon Rutzky
Solomon Rutzky

Reputation: 48806

When I registered the assembly with CREATE ASSEMBLY, I gave it a path. I'd like to get that path.

This is not possible as that path was only used to read the Assembly into the database. The content column that you see in sys.assembly_files is the Assembly / DLL. It physically exists in the database now. That is what CREATE ASSEMBLY does. This is a benefit over the old Extended Stored Procedure (XPs) framework as there are no external dependencies; when you back up the database, the Assembly is backed up with it as the data resides in the database, not on the file system.

Upvotes: 1

Related Questions