Auto Generate History Table and Corresponding Trigger (SQL)

Extending the excellent work of this post by Bryan Massey, I’ve updated the script to replace the deprecated SysProperties table in lieu of the sys.extended_properties so that this procedure will operate in SQL 2008.  Enjoy!

/************************************************************************************************************
Created By:  Bryan Massey
Created On:  3/11/2007
Modified By: Peter Samwel
Modified On: 2/11/2011 — Updated SysProperties table refs (deprecated) to use the new (2008) extended_properties view.
Comments:  Stored proc performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table (“History_” + @TableName) to mimic the original table, plus include
additional history columns.
3) If @CreateTrigger = ‘Y’ then it creates an Update/Delete trigger on the @TableName table,
which is used to populate the History table.
******************************************* MODIFICATIONS **************************************************
MM/DD/YYYY – Modified By – Description of Changes
************************************************************************************************************/
CREATE PROCEDURE [dbo].[History_Bat_AutoGenerateHistoryTableAndTrigger]
@TableName VARCHAR(200),
@CreateTrigger CHAR(1) = ‘Y’ — optional parameter; defaults to “Y”
AS

DECLARE @SQLTable VARCHAR(8000), @SQLTrigger VARCHAR(8000), @FieldList VARCHAR(6000), @FirstField VARCHAR(200)
DECLARE @TAB CHAR(1), @CRLF CHAR(1), @SQL VARCHAR(1000), @Date VARCHAR(12)

SET @TAB = CHAR(9)
SET @CRLF = CHAR(13) + CHAR(10)
SET @Date = CONVERT(VARCHAR(12), GETDATE(), 101)
SET @FieldList = ”
SET @SQLTable = ”

DECLARE @TableDescr VARCHAR(500), @FieldName VARCHAR(100), @DataType VARCHAR(50)
DECLARE @FieldLength VARCHAR(10), @Precision VARCHAR(10), @Scale VARCHAR(10),  @FieldDescr VARCHAR(500), @AllowNulls VARCHAR(1)

DECLARE CurHistoryTable CURSOR FOR

— query system tables to get table schema
SELECT CONVERT(VARCHAR(500), SP2.value) AS TableDescription,
CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType,
CONVERT(VARCHAR(10),SC.length) AS FieldLength, CONVERT(VARCHAR(10), SC.XPrec) AS FieldPrecision,
CONVERT(VARCHAR(10), SC.XScale) AS FieldScale,
CASE SC.IsNullable WHEN 1 THEN ‘Y’ ELSE ‘N’ END AS AllowNulls
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN SysTypes ST ON SC.xtype = ST.xtype
LEFT OUTER JOIN sys.extended_properties SP ON SC.ID = SP.major_id AND SC.ColID = SP.minor_id
LEFT OUTER JOIN sys.extended_properties SP2 ON SC.ID = SP2.major_id AND SP2.minor_id = 0
WHERE SO.xtype = ‘u’ AND SO.Name = @TableName
ORDER BY SO.[name], SC.ColOrder

OPEN CurHistoryTable

FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls

WHILE @@FETCH_STATUS = 0
BEGIN

— create list of table columns
IF LEN(@FieldList) = 0
BEGIN
SET @FieldList = @FieldName
SET @FirstField = @FieldName
END
ELSE
BEGIN
SET @FieldList = @FieldList + ‘, ‘ + @FieldName
END

IF LEN(@SQLTable) = 0
BEGIN
SET @SQLTable = ‘CREATE TABLE [DBO].[History_’ + @TableName + ‘] (‘ + @CRLF
SET @SQLTable = @SQLTable + @TAB + ‘[History’ + @FieldName + ‘] [INT] IDENTITY(1,1) NOT NULL,’ + @CRLF
END

SET @SQLTable = @SQLTable + @TAB + ‘[‘ + @FieldName + ‘] ‘ + ‘[‘ + @DataType + ‘]’
IF UPPER(@DataType) IN (‘CHAR’, ‘VARCHAR’, ‘NCHAR’, ‘NVARCHAR’, ‘BINARY’)
BEGIN
SET @SQLTable = @SQLTable + ‘(‘ + @FieldLength + ‘)’
END
ELSE IF UPPER(@DataType) IN (‘DECIMAL’, ‘NUMERIC’)
BEGIN
SET @SQLTable = @SQLTable + ‘(‘ + @Precision + ‘, ‘ + @Scale + ‘)’
END

IF @AllowNulls = ‘Y’
BEGIN
SET @SQLTable = @SQLTable + ‘ NULL’
END
ELSE
BEGIN
SET @SQLTable = @SQLTable + ‘ NOT NULL’
END

SET @SQLTable = @SQLTable + ‘,’ + @CRLF

FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType,
@FieldLength, @Precision, @Scale, @AllowNulls
END

CLOSE CurHistoryTable
DEALLOCATE CurHistoryTable

— finish history table script with standard history columns
SET @SQLTable = @SQLTable + @TAB + ‘[HistoryCreatedOn] [DATETIME] NULL,’ + @CRLF
SET @SQLTable = @SQLTable + @TAB + ‘[HistoryCreatedByUserID] [SMALLINT] NULL,’ + @CRLF

SET @SQLTable = @SQLTable + @TAB + ‘[HistoryCreatedByUserName] [VARCHAR](30) NULL,’ + @CRLF
SET @SQLTable = @SQLTable + @TAB + ‘[HistoryAction] [CHAR](1) NOT NULL’ + @CRLF
SET @SQLTable = @SQLTable + ‘ )’

PRINT @SQLTable

— execute sql script to create history table
EXEC(@SQLTable)

IF @@ERROR <> 0
BEGIN
PRINT ‘******************** ERROR CREATING HISTORY TABLE FOR TABLE: ‘ + @TableName + ‘ **************************************’
RETURN -1
END

IF @CreateTrigger = ‘Y’
BEGIN
— create history trigger
SET @SQLTrigger = ‘/************************************************************************************************************’ + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘Created By: ‘ + SUSER_SNAME() + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘Created On: ‘ + @Date + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘Comments: Auto generated trigger’ + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘***********************************************************************************************/’ + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘CREATE TRIGGER [Trigger_’ + @TableName + ‘_UpdateDelete] ON DBO.’ + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘FOR UPDATE, DELETE’ + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘AS’ + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘DECLARE @Action CHAR(1)’ + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘IF EXISTS (SELECT ‘ + @FirstField + ‘ FROM Inserted)’ + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘BEGIN’ + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ‘SET @Action = ”U”’ + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘END’ + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘ELSE’ + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘BEGIN’ + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ‘SET @Action = ”D”’ + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘END’ + @CRLF + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘INSERT INTO History_’ + @TableName + @CRLF
SET @SQLTrigger = @SQLTrigger + @TAB + ‘(‘ + @FieldList + ‘, HistoryCreatedOn, HistoryCreatedByUserName, HistoryAction)’ + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘SELECT ‘ + @FieldList + ‘, GETDATE(), SUSER_SNAME(), @Action’ + @CRLF
SET @SQLTrigger = @SQLTrigger + ‘FROM DELETED’
–PRINT @SQLTrigger
— execute sql script to create update/delete trigger
EXEC(@SQLTrigger)

IF @@ERROR <> 0
BEGIN
PRINT ‘******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ‘ + @TableName + ‘ **************************************’
RETURN -1
END

END

Client Specific Metadata in your MVC Application

Recently I was working on a new requirement for one of our systems in which each different client may refer to the same data types differently.  For example, ClientA might refer to roadways as a “Parkways” whereas ClientB and ClientC might refer to them as “Highways”.   Changes like these obviously aren’t big enough to warrant separate Views for each screen in which data types like these are referenced or mentioned, however sometimes small things like that make a big difference to the client and makes the application feel more in line with their existing business processes.

So, in order to make it so that my application is still a single code-base that will work for all the various clients that might come along and their varying terminologies, here is what I came up with that I think integrates nicely into an existing MVC application and requires only slight modifications.

First off, here is my interface and concrete implementation of a dictionary wrapper that I will use to not only store the key/value pairs which represent the data types but which will be what my Views utilize as an appendage to their ViewModel instances.

    public interface IMeta

    {

        MetaDictionary Meta { get; set; }

    }

 

    public class MetaDictionary

    {

        private Dictionary<string, KeyValuePair<string,string>> dict;

        public MetaDictionary()

        {

            dict = new Dictionary<string, KeyValuePair<string, string>>();

        }

 

        public void Add(string key, KeyValuePair<string,string> value)

        {

            if (!dict.ContainsKey(key))

                dict.Add(key, value);

            else

                dict[key] = value;

        }

        public void Remove(string key)

        {

            if (dict.ContainsKey(key))

                dict.Remove(key);

        }

 

        public string GetValue(string key)

        {

            if(dict.ContainsKey(key))

                return dict[key].Key;

            else

                return "";

        }

        public string GetValueAbbrev(string key)

        {

            if(dict.ContainsKey(key))

                return dict[key].Value;

            else

                return "";

        }

    }

Next, I need to write an action filter that will fetch the appropriate key/value pairs relevant to the currently logged in user and which client they belong to.  Please note that this is where your logic goes to determine A) where to retrieve your key/value pairs to begin with (DB, XML, config file, etc) and B) what differentiating factor(s) you might have which would determine how your application knows each client.  In my situation, I currently have a variable which would store the ClientID in a pre-existing injected Session wrapper.  Also note, I’m using StructureMap as my DI container.

    public class ClientMetaAttribute : ActionFilterAttribute, IActionFilter

    {

        private readonly IControllerDependency Services;

        public ClientMetaAttribute():this(ObjectFactory.GetInstance<IControllerDependency>())

        {

        }

        public ClientMetaAttribute(IControllerDependency service)

        {

            Services = service;

        }

        public override void OnResultExecuting(ResultExecutingContext filterContext)

        {

            //Lookup all appropriate attributes.

            int? clientid = Services.SessionHandler.ClientID;

            var metadict = new MetaDictionary();

            //FILL YOUR META dictionary here!!

 
           ((IMeta)((ViewResultBase)filterContext.Result).ViewData.Model).Meta = metadict;

            base.OnResultExecuting(filterContext);

        }

 

        public override void OnResultExecuted(ResultExecutedContext filterContext)

        {

            base.OnResultExecuted(filterContext);

        }

Now, once I’ve implemented IMeta in the ViewModel used for my action method:

    public class ClientViewModel: IMeta

    {

        public MetaDictionary Meta { get; set; }

        //Rest of model class definition…

    }

Then I simply need to update any hardcoded references within View(s) which use this ViewModel such as:

<h4><%: Model.Meta.GetValue("roadname") %> (<%: Model.Meta.GetValueAbbrev("roadname") %>)</h4>

Which for ClientA will give me

Parkway (PKWY)

and for for ClientB & ClientC will give

Highway (HWY)

Obviously this could be expanded in terms of the dictionary value storage to include a host of other information that may be specific to your needs, however this example shows the intent.  Additionally, another enhancement would be to extend this for use in a localization scenario where multiple languages may be necessary as well. 

Expression based RenderAction HtmlHelper extension (with Authorization)

        public static void RenderAuthorizedAction<TController>(this HtmlHelper helper, Expression<Action<TController>> action) where TController : Controller

        {

            var routeValuesFromExpression = Microsoft.Web.Mvc.Internal.ExpressionHelper.GetRouteValuesFromExpression<TController>(action);

 

            if(helper.IsAuthorized(action))

                helper.RenderAction(routeValuesFromExpression["Action"].ToString(), routeValuesFromExpression);

        }

 

        public static bool IsAuthorized<TController>(this HtmlHelper helper, Expression<Action<TController>> action)

        {

            var call = action.Body as MethodCallExpression;

 

            if (call == null) return false;

 

            var authorizeAttributes = call.GetAttributes<IAuthorizationFilter>();

            if (authorizeAttributes.Length == 0) return true;

 

            var controllerContext = helper.ViewContext.Controller.ControllerContext;

            var controllerDescriptor = new ReflectedControllerDescriptor(typeof(TController));

            var actionDescriptor = new ReflectedActionDescriptor(call.Method, call.Method.Name, controllerDescriptor);

 

            return authorizeAttributes.All(a => IsAuthorized(a, controllerContext, actionDescriptor));

        }

I’m sure this is coming in MVC 3, however I needed it now and it’s fairly straightforward.  To use it, simply call from your View as:

<% Html.RenderAuthorizedAction<MyController>(a => a.MyActionMethod()); %>

Funniest thing I’ve read in a while….

This isn’t tech related…but I am crying here after reading this.  No idea if it’s true or not, but it gave me a great laugh on a day when I needed it.

First Date Gone Horribly Wrong

Color Transitions in Winform Controls

If you’ve ever used web-sites like the hosted WordPress blogs (like this one!) and others, you’ve noticed the new sweet hotness is to have screens give feedback on success or failure of an operation by showing color gradient transitions.  The most common is simply “Green to White” (success!) or “Red to Pink” (failure!).  This is a quick way to let the user know they can move along and do more work or whether they had an issue.  Granted, more detailed logging and/or messages are needed on the “fail” side of things, however since code slingers like us write perfect code all the time….we are just gonna go with quick feedback for now 😉

So, being as I’ve only started in the last few months writing winform/WPF apps, I thought there must be an easy way to do these gradients on forms and other controls quite easily.  No so.  Sure, there are ForeColor and BackColor properties, however transitioning in a gradient-like fashion from one color to the next isn’t “built in” in any way that I could tell.  NOTE: Yes, I’m well aware of the fact that WPF is going to take over the world.  But for now this is a Winform app, and until such time as WPF becomes the de-facto standard, it will remain as such.  Accordingly, please don’t comment about how WPF can handle this with ease.  I know.

So here’s what I came up with:

        public static void UpdateColor(Control ctl, Color from, Color to)

        {

            int step = 1; //Increase if you want the transition to be faster.

            var fromT = new Tuple<int, int, int>(

                Convert.ToInt32(from.R),

                Convert.ToInt32(from.G),

                Convert.ToInt32(from.B));

            var toT = new Tuple<int, int, int>(

                Convert.ToInt32(to.R),

                Convert.ToInt32(to.G),

                Convert.ToInt32(to.B));

            var curT = new Tuple<int, int, int>(fromT);

            var done = new Tuple<bool, bool, bool>(false, false, false);

 

            while (true)

            {

                //Red

                if (curT.First >= toT.First)

                    done.First = true;

                else

                {

                    var tempval = curT.First < toT.First ? curT.First += step : curT.First -= step;

                    curT.First = tempval > toT.First ? toT.First : tempval;

                }

                //Green

                if (curT.Second >= toT.Second)

                    done.Second = true;

                else

                {

                    var tempval = curT.Second < toT.Second ? curT.Second += step : curT.Second -= step;

                    curT.Second = tempval > toT.Second ? toT.Second : tempval;

                }

                //Blue

                if (curT.Third >= toT.Third)

                    done.Third = true;

                else

                {

                    var tempval = curT.Third < toT.Third ? curT.Third += step : curT.Third -= step;

                    curT.Third = tempval > toT.Third ? toT.Third : tempval;

                }

 

                if (done.First && done.Second && done.Third)

                {

                    ctl.BackColor = to;

                    ctl.Refresh();

                    break;

                }

                ctl.BackColor = Color.FromArgb(curT.First, curT.Second, curT.Third);

                ctl.Refresh();

            }

        }

So, basically I’m just getting the integer values of the RGB color scheme for the from and to colors passed in, looping through adding/subtracting from the start values (from) until I get to the end values (to), refreshing the control along the way.  Not too terribly difficult, but interesting none-the-less and quite a nice little UI feature that doesn’t look like most winform applications.

Also, note I’ve added a step variable that you can tweak to adjust how fast or slow the transition occurs.  It’s a pretty significant jump from say 1 to 3, so if you want more granular timing, you could adjust the int storage to decimal and account for parts of steps (such as += 0.3).  Or you could implement a true timing system that would force the transition to take place within a specific time construct (perhaps the next article on this topic??).  But for my needs currently, this is more than sufficient.

And of course, I’m using my handy little Tuple<T1,T2,T3> class, since tuples (or at least the concept of them) rock.  Hint hint…please make this concept a part of C# 4.0 Anders…puuuleeeeeease?

    public class Tuple<T1,T2,T3>

    {

        public T1 First { get; set;}

        public T2 Second { get; set; }

        public T3 Third { get; set; }

 

        public Tuple(T1 t, T2 k, T3 l)

        {

            First = t;

            Second = k;

            Third = l;

        }

        public Tuple(Tuple<T1,T2,T3> t)

        {

            First = t.First;

            Second = t.Second;

            Third = t.Third;

        }

    }

 

Til next time….keep on slangin!