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.