CLR Trigger for Automatic Registration of UDXs on CREATE ASSEMBLY
This code is provided "AS IS" with no warranties, and confers no rights.
I've made some small modifications to comply with standard naming conventions on 11/30/2005.
Save the following into AutoRegisterTrigger.cs
--------------------------------------------------------
using System;
using System;
using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Xml;
using System.Reflection;
using System.Text.RegularExpressions;
namespace Microsoft.SqlServer.Sample
{
public class AutoRegister
{
[SqlTrigger(Name = "AutoRegister", Event = "FOR CREATE_ASSEMBLY", Target = "DATABASE")]
public static void AutoRegisterTrigger()
{
// Checks that we are executing this trigger after a create assembly
SqlTriggerContext triggContext = SqlContext.TriggerContext;
if (triggContext.TriggerAction != TriggerAction.CreateAssembly)
{
throw new Exception("This trigger is only meaningful for CREATE ASSEMBLY.");
}
// Retrieves the name of the assembly being registered
String asmName = null;
XmlReader eventreader = triggContext.EventData.CreateReader();
while (eventreader.Read())
{
if (eventreader.Name.Equals("ObjectName"))
{
asmName = eventreader.ReadElementContentAsString();
break;
}
}
if (asmName == null)
{
throw new Exception("Could not find the assembly name.");
}
// Retrieve the clr name of the assembly
String ClrName = null;
using (SqlConnection myConnection = new SqlConnection("Context connection = true"))
{
myConnection.Open();
using (SqlCommand myCommand = new SqlCommand())
{
myCommand.Connection = myConnection;
myCommand.CommandText = "SELECT clr_name FROM sys.assemblies WHERE name = @AsmName";
myCommand.Parameters.Add("@AsmName", SqlDbType.NVarChar);
myCommand.Parameters[0].Value = asmName;
ClrName = (String)myCommand.ExecuteScalar();
}
}
// Load the assembly from SQL database to memory
Assembly a = Assembly.Load(ClrName);
// Iterating over types in assembly
Type[] types = a.GetTypes();
foreach (Type type in types)
{
try
{
// Iterating over custom attributes of type
Attribute[] Type_attributes = Attribute.GetCustomAttributes(type);
foreach (Attribute att in Type_attributes)
{
if (att.ToString().Equals("Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute"))
{
RegisterUDT(asmName, type, (SqlUserDefinedTypeAttribute)att);
}
else if (att.ToString().Equals("Microsoft.SqlServer.Server.SqlUserDefinedAggregateAttribute"))
{
RegisterUDA(asmName, type, (SqlUserDefinedAggregateAttribute)att);
}
}
}
catch (AutoRegisterException e)
{
SqlContext.Pipe.Send("[" + asmName + "].[" + type.Name + "] : " + e.Message);
}
// Iterate over public static methods
MethodInfo[] methodInfos = type.GetMethods();
foreach (MethodInfo methodInfo in methodInfos)
{
if (methodInfo.IsPublic && methodInfo.IsStatic)
{
try
{
Object[] Method_attributes = methodInfo.GetCustomAttributes(false);
foreach (Attribute att in Method_attributes)
{
if (att.ToString().Equals("Microsoft.SqlServer.Server.SqlProcedureAttribute"))
RegisterProcedure(asmName, type, (SqlProcedureAttribute)att, methodInfo);
if (att.ToString().Equals("Microsoft.SqlServer.Server.SqlTriggerAttribute"))
RegisterTrigger(asmName, type, (SqlTriggerAttribute)att, methodInfo);
if (att.ToString().Equals("Microsoft.SqlServer.Server.SqlFunctionAttribute"))
RegisterUDF(asmName, type, (SqlFunctionAttribute)att, methodInfo);
}
}
catch (AutoRegisterException e)
{
SqlContext.Pipe.Send("[" + asmName + "].[" + type.Name + "].[" + methodInfo.Name + "] : " + e.Message);
}
}
}
}
}
private static void RegisterUDT(String asmName, Type type, SqlUserDefinedTypeAttribute sqlUserDefinedTypeAttribute)
{
String Name = sqlUserDefinedTypeAttribute.Name;
if (Name == null) Name = type.Name;
// Check that the name is valid
if (!IsValid(Name))
throw new InvalidNameException(Name);
// Check if the object is already registered
if (IsRegistered(Name))
throw new ObjectRegisteredException(Name);
String CommandString = "CREATE TYPE [" + Name + "] EXTERNAL NAME [" + asmName + "].[";
if (type.Namespace != null) CommandString += type.Namespace + ".";
CommandString += type.Name + "]";
Register(CommandString);
}
private static void RegisterUDA(String asmName, Type type, SqlUserDefinedAggregateAttribute sqlUserDefinedAggregateAttribute)
{
String Name = sqlUserDefinedAggregateAttribute.Name;
if (Name == null) Name = type.Name;
// Check that the name is valid
if (!IsValid(Name))
throw new InvalidNameException(Name);
// Check if the object is already registered
if (IsRegistered(Name))
throw new ObjectRegisteredException(Name);
String CommandString = "CREATE AGGREGATE [" + Name + "]";
String CommandString_return = "";
bool hasAccumulateMethod = false;
// Iterate over public methods to find parameter and return type
MethodInfo[] methodInfos = type.GetMethods();
foreach (MethodInfo methodInfo in methodInfos)
{
if (methodInfo.Name.Equals("Accumulate"))
{
// Add parameter
ParameterInfo[] pis = methodInfo.GetParameters();
if (pis.Length != 1)
throw new InvalidNumberOfArgumentsException();
ParameterInfo pi = pis[0];
char[] refchar = { '&' }; // double check that this is a unicode array
String param_type;
// if parameter is passed by reference as in (@i INT OUTPUT) display an error
if (pi.ParameterType.ToString().LastIndexOfAny(refchar) != -1)
throw new InvalidArgumentByReferenceException(pi.ParameterType.ToString());
param_type = NETtoSQLmap(pi.ParameterType.ToString());
if (param_type == null)
throw new InvalidTypeException(pi.ParameterType.ToString());
// Check the validity of the parameter strings (we construct param_type no need to validate it)
if (!IsValid(pi.Name))
throw new InvalidNameException(pi.Name);
CommandString += " (@" + pi.Name + " " + param_type + ")";
hasAccumulateMethod = true;
}
else if (methodInfo.Name.Equals("Terminate"))
{
String return_type = NETtoSQLmap(methodInfo.ReturnType.ToString());
if (return_type == null)
throw new InvalidReturnTypeException(methodInfo.ReturnType.ToString());
CommandString_return = " RETURNS " + return_type + " "; // we construct return_type no need to validate it
}
}
if (CommandString_return.Equals(""))
throw new MissingTerminateMethodException();
if (!hasAccumulateMethod)
throw new MissingAccumulateMethodException();
CommandString += CommandString_return;
CommandString += "EXTERNAL NAME [" + asmName + "].[";
if (type.Namespace != null) CommandString += type.Namespace + ".";
CommandString += type.Name + "]";
Register(CommandString);
}
private static void RegisterProcedure(String asmName, Type type, SqlProcedureAttribute sqlProcedureAttribute, MethodInfo methodInfo)
{
String Name = sqlProcedureAttribute.Name;
if (Name == null) Name = methodInfo.Name;
// Check that the name is valid
if (!IsValid(Name))
throw new InvalidNameException(Name);
// Check if the object is already registered
if (IsRegistered(Name))
throw new ObjectRegisteredException(Name);
String CommandString = "CREATE PROCEDURE [" + Name + "]";
CommandString += GetParameterString(methodInfo);
CommandString += " AS EXTERNAL NAME [" + asmName + "].[";
if (type.Namespace != null) CommandString += type.Namespace + ".";
CommandString += type.Name + "].[" + methodInfo.Name + "]";
Register(CommandString);
}
private static void RegisterTrigger(String asmName, Type type, SqlTriggerAttribute sqlTriggerAttribute, MethodInfo methodInfo)
{
String Name = sqlTriggerAttribute.Name;
if (Name == null) Name = methodInfo.Name;
// Check that the name is valid
if (!IsValid(Name))
throw new InvalidNameException(Name);
// Check if the object is already registered
if (IsRegistered(Name))
throw new ObjectRegisteredException(Name);
// Check that the target is valid
String Target = sqlTriggerAttribute.Target;
if (Target == null)
throw new NoTargetException();
if (!IsValid(sqlTriggerAttribute.Target))
throw new InvalidNameException(sqlTriggerAttribute.Target);
// Check that the event is valid
String Tr_event = sqlTriggerAttribute.Event;
if (Tr_event == null)
throw new NoEventException();
if (!IsValid(sqlTriggerAttribute.Event))
throw new InvalidNameException(sqlTriggerAttribute.Event);
String CommandString = "CREATE TRIGGER [" + Name + "] ";
CommandString += "ON " + Target + " WITH EXECUTE AS CALLER ";
CommandString += Tr_event + " AS EXTERNAL NAME [" + asmName + "].[";
if (type.Namespace != null) CommandString += type.Namespace + ".";
CommandString += type.Name + "].[" + methodInfo.Name + "]";
Register(CommandString);
}
private static void RegisterUDF(String asmName, Type type, SqlFunctionAttribute sqlFunctionAttribute, MethodInfo methodInfo)
{
String Name = sqlFunctionAttribute.Name;
if (Name == null) Name = methodInfo.Name;
// Check that the name is valid
if (!IsValid(Name))
throw new InvalidNameException(Name);
// Check if the object is already registered
if (IsRegistered(Name))
throw new ObjectRegisteredException(Name);
String CommandString = "CREATE FUNCTION [" + Name + "]";
CommandString += GetParameterString(methodInfo);
String return_type = NETtoSQLmap(methodInfo.ReturnType.ToString());
if (return_type == null)
throw new InvalidReturnTypeException(methodInfo.ReturnType.ToString());
CommandString += "RETURNS " + return_type + " ";
CommandString += "AS EXTERNAL NAME [" + asmName + "].[";
if (type.Namespace != null) CommandString += type.Namespace + ".";
CommandString += type.Name + "].[" + methodInfo.Name + "]";
Register(CommandString);
}
// could we use a solution similar to Xiaowei's question to make this more robust
private static String NETtoSQLmap(String typeName)
{
switch (typeName)
{
case "System.Data.SqlTypes.SqlBoolean": return "bit";
case "System.Data.SqlTypes.SqlBinary": return "varbinary"; // other choices: binary, image, timestamp,
case "System.Data.SqlTypes.SqlByte": return "tinyint";
case "System.Data.SqlTypes.SqlChars": return "nvarchar"; //other choices: char, nchar, text, ntext, varchar
case "System.Data.SqlTypes.SqlDateTime": return "smalldatetime"; // other choice: datetime
case "System.Data.SqlTypes.SqlDecimal": return "decimal";
case "System.Data.SqlTypes.SqlDouble": return "float";
case "System.Data.SqlTypes.SqlGuid": return "uniqueidentifier";
case "System.Data.SqlTypes.SqlInt16": return "smallint";
case "System.Data.SqlTypes.SqlInt32": return "int";
case "System.Data.SqlTypes.SqlInt64": return "bigint";
case "System.Data.SqlTypes.SqlMoney": return "money"; // other choice smallmoney
case "System.Data.SqlTypes.SqlSingle": return "real";
case "System.Data.SqlTypes.SqlString": return "nvarchar"; //other choices: char, nchar, text, ntext, varchar
case "System.Data.SqlTypes.SqlXml": return "xml";
default:
{
switch (typeName.ToLower())
{
case "short": return "smallint";
case "int": return "int";
case "long": return "bigint";
case "bool": return "bit";
case "boolean": return "bit";
case "float": return "float";
case "single": return "single";
default: return null;
}
}
}
}
// Check if the object is already registered, return true if it is, false otherwise
private static bool IsRegistered(String name)
{
bool b;
using (SqlConnection myConnection = new SqlConnection("context connection = true"))
{
myConnection.Open();
using (SqlCommand myCommand = new SqlCommand())
{
myCommand.Connection = myConnection;
myCommand.CommandText = "SELECT * FROM sys.objects WHERE name = @Name";
myCommand.Parameters.Add("@Name", SqlDbType.NVarChar);
myCommand.Parameters[0].Value = name;
if (myCommand.ExecuteScalar() != null) b = true;
else b = false;
}
return b;
}
}
private static void Register(String commandString)
{
using (SqlConnection myConnection = new SqlConnection("context connection = true"))
{
myConnection.Open();
using (SqlCommand comm = new SqlCommand(commandString, myConnection))
{
try
{
comm.ExecuteNonQuery();
SqlContext.Pipe.Send("AutoRegister: " + commandString);
}
catch (SqlException e)
{
SqlContext.Pipe.Send(e.Message);
}
}
}
}
/* We assume namespace, class and method names are valid strings for
* TSQL statements (alphanumeric or underscore only) since the
* compiler accepted them and so is the assembly name since
* it was provided by the DBA */
private static bool IsValid(String s)
{
return Regex.IsMatch(s, "[^_.[:alnum:]]");
}
private static String GetParameterString(MethodInfo methodInfo)
{
String s_params = "";
// Add parameters
ParameterInfo[] pis = methodInfo.GetParameters();
bool empty = true;
char[] refchar = { '&' }; // double check that this is a unicode array
String param_type;
foreach (ParameterInfo pi in pis)
{
// add OUTPUT if parameter is passed by reference as in (@i INT OUTPUT)
int ix = pi.ParameterType.ToString().LastIndexOfAny(refchar);
if (ix != -1)
{
param_type = NETtoSQLmap(pi.ParameterType.ToString().Substring(0, ix));
if (param_type == null)
throw new InvalidTypeException(pi.ParameterType.ToString().Substring(0, ix));
param_type += " OUTPUT";
}
else
{
param_type = NETtoSQLmap(pi.ParameterType.ToString());
if (param_type == null)
throw new InvalidTypeException(pi.ParameterType.ToString());
}
// Check the validity of the parameter strings (we construct param_type no need to validate it)
if (!IsValid(pi.Name))
throw new InvalidNameException(pi.Name);
if (empty)
{
s_params += " (@" + pi.Name + " " + param_type;
empty = false;
}
else
s_params += ", @" + pi.Name + " " + param_type;
}
if (!empty)
s_params += ") ";
return s_params;
}
}
internal class AutoRegisterException : ApplicationException
{
internal AutoRegisterException(String Message)
: base(Message + " UDX not registered.")
{
}
}
internal class InvalidTypeException : AutoRegisterException
{
internal InvalidTypeException(String sType)
: base("Argument type " + sType + " cannot be mapped to a SQL Type.")
{
}
}
internal class InvalidReturnTypeException : AutoRegisterException
{
internal InvalidReturnTypeException(String sType)
: base("Return type " + sType + " cannot be mapped to a SQL Type.")
{
}
}
internal class ObjectRegisteredException : AutoRegisterException
{
internal ObjectRegisteredException(String Name)
: base(Name + " is already registered.")
{
}
}
internal class InvalidNameException : AutoRegisterException
{
internal InvalidNameException(String Name)
: base(Name + " is not a valid name.")
{
}
}
internal class NoEventException : AutoRegisterException
{
internal NoEventException()
: base("Missing the 'Event' argument for the SqlTriggerAttribute constructor.")
{
}
}
internal class NoTargetException : AutoRegisterException
{
internal NoTargetException()
: base("Missing the 'Target' argument for the SqlTriggerAttribute constructor.")
{
}
}
internal class InvalidNumberOfArgumentsException : AutoRegisterException
{
internal InvalidNumberOfArgumentsException()
: base("Wrong number of arguments.")
{
}
}
internal class InvalidArgumentByReferenceException : AutoRegisterException
{
internal InvalidArgumentByReferenceException(String Argument)
: base("Expected argument " + Argument + "passed by value, passed by reference instead.")
{
}
}
internal class MissingTerminateMethodException : AutoRegisterException
{
internal MissingTerminateMethodException()
: base("The user defined aggregate is missing a 'Terminate' method.")
{
}
}
internal class MissingAccumulateMethodException : AutoRegisterException
{
internal MissingAccumulateMethodException()
: base("The user defined aggregate is missing an 'Accumulate' method.")
{
}
}
}
-------------------------------------
then run the following TSQL batch
-------------------------------------
CREATE ASSEMBLY AutoRegisterAsm
FROM -- path to your dll goes here
WITH PERMISSION_SET = SAFE
GO
CREATE TRIGGER AutoRegisterTrigger
ON DATABASE
FOR CREATE_ASSEMBLY
AS EXTERNAL NAME AutoRegisterAsm.[Microsoft.SqlServer.Sample.AutoRegister].AutoRegisterTrigger
GO
ENABLE TRIGGER AutoRegisterTrigger ON DATABASE
GO
- Miles Trochesset, Microsoft SQL Server
Comments
- Anonymous
May 25, 2006
Visual Studio already does this, "Auto-Deploy".
I wonder if its possible to call into their libraries.