SQLCLR 静态类构造函数中的数据访问
在 SQL-CLR 程序集中..
有没有一种方法可以让我加载某些数据，并可能在仅加载程序集时从数据库中读取数据？
假设我有一类用户定义的函数，这些函数使用一些正则表达式，这些正则表达式必须根据数据库中某些表中的数据构建和编译，有没有一种方法可以从表中读取，创建我的 Regex 对象并在 AppDomian 的生命周期内保持这些对象处于活动状态，以便每当调用使用这些对象的任何用户定义函数时，都不必重建它们？
编辑：静态构造函数不是读取数据的有效上下文。
我希望我已经表达得足够清楚了，提前致谢。
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 context for reading data.
I hope I made my self clear enough, Thanks In Advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这取决于您对“某些数据”的位置和/或程序集的
PERMISSION_SET的灵活性。如果程序集必须保持安全（如果可能的话，这是首选），那么唯一可以从程序集外部读取的两个源是：
appSettings部分）。 更新：到目前为止，从配置文件中读取似乎无法在 Linux（SQL Server 2017 的新平台）上运行。虽然这些都不像从该表中读取那样“动态”，但可以在该设置表上设置触发器以将适当的数据写入 sqlservr.exe.config 文件。对于高度事务性的表，我并不完全推荐这样做，但我在这里假设用于此目的的值不会频繁更改，特别是因为计划无论如何都将它们缓存在
static readonly类中变量。如果程序集已设置为EXTERNAL_ACCESS，那么您可以创建常规/外部SqlConnection来从数据库获取数据。但是，如果这是您将程序集设置为 EXTERNAL_ACCESS 的唯一原因，那么我会选择上面提到的配置文件选项，因为确定性方法的性能会更好，如果程序集设置为
SAFE。It depends on how flexible you are with either the location of that "certain data" and/or the
PERMISSION_SETof 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:appSettingssection). 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 readonlyclass variables.If the assembly is already set to
EXTERNAL_ACCESS, then you can make a regular / externalSqlConnectionto get the data from the DB. However, if this is the only reason you would be setting the assembly toEXTERNAL_ACCESS, then I would opt for the config file option noted above since performance is better on deterministic methods if the assembly is set toSAFE.我认为不同 SQL Server 版本之间的行为发生了一些变化，但静态构造函数可能会有所帮助： http://msdn.microsoft.com/en-us/library/k9x6w0hc(v=vs.80).aspx
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