Data Access in SQLCLR static class Constructors

In a SQL-CLR assembly..

Is there a way that enables me to load certain data and possibly read from database on assembly load only ?

Lets say I have a class of user-defined functions , these functions use some regular expressions that have to be built and compiled based on the data in some tables I have in the DB , Is there a way that i can Read from the tables, Create my Regex objects and keep those objects alive for the life time of the AppDomian , So that whenever any user-defined function that uses those objects is called ,it doesn't have to rebuild them ?

EDIT: Static Constructors are Not a valid conte开发者_JAVA百科xt for reading data.

I hope I made my self clear enough, Thanks In Advance.

---

Is there a way that enables me to load certain data and possibly read from database on assembly load only ?

It depends on how flexible you are with either the location of that "certain data" and/or the PERMISSION_SET of the assembly.

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

• environment variables
• app.config file (please see my answer to this S.O. 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 as though reading from the config file does not work on Linux (new platform as of SQL Server 2017).

While neither of those are "dynamic" like reading from that table, it is possible to set up a trigger on that settings table to write out the appropriate data to the sqlservr.exe.config file. I wouldn't exactly recommend this for a highly transactional table, but I assume here that the values for this purposes aren't changing that frequently, especially since the plan is to cache them anyway in static readonly class variables.

If the assembly is already set to EXTERNAL_ACCESS, then you can make a regular / external SqlConnection to get the data from the DB. However, if this is the only reason you would be setting the assembly to EXTERNAL_ACCESS, then I would opt for the config file option noted above since performance is better on deterministic methods if the assembly is set to SAFE.

---

I think behavior changed a bit between different SQL server versions, but static constructors might help: http://msdn.microsoft.com/en-us/library/k9x6w0hc(v=vs.80).aspx