How do I generically reference a local table in a SQL Server database from an assembly installed in that database?

Question

Let's say I have an assembly with a method does_stuff() that I've installed in an SQL server database. I want this method/storedproc to refer to a specific known table in the database. How do I get does_stuff to access the contents of the table without ever knowing where itself is hosted?

Let's say does_stuff is supposed to access table info_config. If it was hosted in database ALPHA, it would access ALPHA.info_config If it was hosted in database BETA, it would access BETA.info_config

I know how to open DB connections etc with ADO.NET, but those require specific server and database strings. I need flexibility so that the assembly does the same thing no matter where it is hosted.

Google search is giving me nothing.

Thanks in advance

Answer 1

All you have to do is use a context connection.

At the moment it may not seem obvious, but CLR code always executes in some context. There is always something (somebody) that executed the code directly or in some parent scope (stored procedure calls CLR function, CLR trigger fires on insert etc). When program execution reaches context connection, it just takes connection parameters (server, database, SET options etc) of that scope.

I want this method/storedproc to refer to a specific known table in the database.

After context connection is opened, you are querying your database as usual.

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
    conn.Open();
    SqlCommand cmd = new SqlCommand("SELECT * FROM MyTable");
    SqlContext.Pipe.ExecuteAndSend(cmd);
}