Using static properties in SQLCLR UDTs

Hi,

I have function and I must have static member in it.

I do not see another way. Also, i detected interesting effect:

1. I have function in  one db

2. I call it, then I change connectin to another db

3. I call this function  again and I have result! but function exists only in the 1st db :-)

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

using nvg_net2;

public partial class UserDefinedFunctions

{

   static readonly SynonymFinder sp;

   static UserDefinedFunctions()

   {

       sp = new SynonymFinder();

   }

   [SqlFunction(DataAccess = DataAccessKind.Read)]

   public static SqlString SynonymFinderor(SqlString strInput)

   {

       using (SqlConnection m_connection = new SqlConnection("context connection=true"))

       {

           if (sp != null && !sp.IsInitialized())

           {

               if (m_connection != null && m_connection.State != ConnectionState.Open) m_connection.Open();

               sp.m_connection = m_connection;

               sp.InitObject();

           }

       }

       Regex rx = new Regex(@"\w+");

       string result = rx.Replace(strInput.ToString(), new MatchEvaluator(Synonym));

       return result;

   }

   private static string Synonym(Match m)

   {

       string x = m.ToString();

       string str = "";

       sp.correct(x, ref str);

       if (str == "") str = x;

       return str;

   }

};

I can't believe they implemented static members in SQLCLR this way. They should have made static variables unique to each session/connection. That's how Oracle implemented their package level variables in PL/SQL and static method in their embedded Java implementation, and it's what you want 99.9% of the time.

Now almost every 3rd party library is going to have to be marked as UNSAFE because of this. You almost never want pragmatic information passing accross connections like this, I don't know what they were thinking.