Using static properties in SQLCLR UDTs

re: Using static properties in SQLCLR UDTs

Oleg — Mon, 24 Nov 2008 12:54:51 GMT

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;

}

};

re: Using static properties in SQLCLR UDTs

Firefight — Mon, 12 Jan 2009 21:41:16 GMT

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.

re: Using static properties in SQLCLR UDTs

Adam Machanic — Tue, 13 Jan 2009 15:03:09 GMT

I don't really see the point of having a static member scoped to the session, and this implementation certainly does not prevent you from being able to accomplish that without the use of UNSAFE. All you need to do, in essence, is set up a keyed collection and use a GUID or something similar to uniquely identify the session. Some kind of expiration logic would also be necessary, in case the session doesn't call back as planned...