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.") { } } }
-------------------------------------
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