CLR Assemblies - Lifecycle and memory options. Is Singleton possible ?

[steve]

Hi All,
I need a CLR custom type to address a few requirements we have.
The class is something like this (extremely simplified to the point)

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class MyProc
{
XMLDoc config;

public MyProc()
{
config = GetConfigFromFileSystem();
}

[Microsoft.SqlServer.Server.SqlProcedure]
public static void MyProc(varchar(20))
{

// Do Stuff with varchar(20) that relies on config info
}
}

The pont being is I dont understand the life cycle of an object like
this in a SQL context.
What I really want is something like this ;

1. SQL Engine starts
2. Assembly loads and constructor is called.
3. All methods are now available to all SQL calls (like static)

If I go the 'static' route I am concerned that on each call to the
method I am going to have to get config from file each time.
This is going to hammer performance...

Can anyone offer advice ? I really want a singleton

Rgds

s

[Erland Sommarskog]

> The pont being is I dont understand the life cycle of an object like
> this in a SQL context.
> What I really want is something like this ;
>
> 1. SQL Engine starts
> 2. Assembly loads and constructor is called.
> 3. All methods are now available to all SQL calls (like static)
>
> If I go the 'static' route I am concerned that on each call to the
> method I am going to have to get config from file each time.
> This is going to hammer performance...

I think you need to save the config_file information as a static
member in the class. The method to retrieve data would have to check if
the object is there, and read the file if necessary. Note that this can
happen on the 47th acccess as well as the first, as SQL Server can have
evicted the AppDomain.

Note that if you have a static member, the assembly needs UNSAFE permission.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx