Geek Answers Handbook

Data Access in Static Classes SQLCLR Constructors

In the SQL-CLR assembly.
Is there a way that allows me to load certain data and possibly read from a database with only downloads ?
Suppose I have a class of user-defined functions, these functions use some regular expressions that need to be built and compiled based on the data in some tables that I have in the database, is there any way I can read from the tables, Create my Regex objects and save these objects throughout the entire AppDomian life cycle. Therefore, when any user-defined function using these objects is called, does it need to be rebuilt?

EDIT: Static constructors are not a valid context for reading data.

Hope I made it clear enough, thanks in advance.

+4
source share
2 answers

Is there a way that allows me to load certain data and possibly read from a database with only downloads ?

It depends on how flexible you are with the location of this "defined data" and / or PERMISSION_SET assembly.

If the assembly should remain as SAFE (which is preferable, if at all possible), then only two sources that can be read from this are external to the assembly:

  • Environment Variables
  • The app.config file (see my answer to this SO question for details: Does SQL Server CLR Integration support configuration files? - and yes, it also works with the appSettings section). UPDATE: So far, it seems that reading from the configuration file does not work on Linux (new platform with SQL Server 2017).

Although none of them is "dynamic" like reading from this table, you can configure a trigger in this settings table to output the corresponding data to sqlservr.exe.config file. I would not recommend this for a table with a high transaction, but I I suppose that the values ​​for these purposes do not change so often, especially since the plan should cache them anyway in static readonly variables.

If the assembly is already installed on EXTERNAL_ACCESS , you can do regular / external SqlConnection to get data from the database. However, if this is the only reason you set the assembly to EXTERNAL_ACCESS , then I would choose the configuration file option noted above, because performance is better by deterministic methods if the assembly is set to SAFE .

+1
source

I think the behavior has changed slightly between different versions of SQL Server, but static constructors could help: http://msdn.microsoft.com/en-us/library/k9x6w0hc(v=vs.80).aspx

0
source

More articles:


All Articles