Category Archives: Development

Entity Framework Tip #1: Loading Grandchildren

When calling .FindById(), it’s a relatively simple task, to have it include child properties explicitly. Simply add a lambda expression.

	myRepository.FindById(1, x=>x.ChildList);

This will ensure that the ChildListis populated and not null.

But what if you need to ensure a GrandChildren entity is populated as well? Writing this won’t work because ChildList is a collection.

	myRepository.FindById(1, x=>x.ChildList.GrandChildren);

What you need to do, is use .Select()

	myRepository.FindById(1, 
		x=>x.ChildList, 
		x.ChildList.Select(y=>y.GrandChildren)
		);

Simple…albeit non-intuitive.

AOP – Logging with Unity

I couple months ago I was tasked with adding logging to an application we are developing. Now, the typical way people handle this, is with a call to “Logger.Log” wherever they want to log that an activity took place. However, this relies on the programmers memory, and while the original team members may know and remember to keep up this practice. The new guy on the team may not. And this is where this technique really starts to break down. Logging is a cross cutting feature. We can’t keep all the code in one place, because it’s called from all over the application.

The solution, is AOP (Aspect Oriented Programming). Since we were using Unity, it’s quite simple to intercept calls based on an interface. There is more it’s capable of, but the issue I had was I couldn’t find a simple, “hello world” example. Everything felt the need to over complicate the example.

First things first, on your project, right click, Manage NuGet packages and add Unity Interception Extension.

Second: Config changes

<?xml version="1.0"?>
<configuration>
    <configSections>
        <section name="unity" type="Microsoft.Practices.Unity.Configuration.UnityConfigurationSection, Microsoft.Practices.Unity.Configuration"/>
    </configSections>
    <unity xmlns="http://schemas.microsoft.com/practices/2010/unity">
      <sectionExtension type="Microsoft.Practices.Unity.InterceptionExtension.Configuration.InterceptionConfigurationExtension, Microsoft.Practices.Unity.Interception.Configuration"/>
 
      <container>
        <extension type="Interception" />
 
        <register type="LoggingTest.Namespace.IInterfaceToLog, LoggingTest.Namespace.Assembly">
          <interceptor type="InterfaceInterceptor" />
          <interceptionBehavior type="LoggingTest.Namespace.Loggers.LoggerBehavior, LoggingTest.Namespace.Assembly"/>
        </register>
      </container>
    </unity>
</configuration>

Third: Create Behavior

   public class LoggerBehavior : IInterceptionBehavior
    {
        public IMethodReturn Invoke(IMethodInvocation input, GetNextInterceptionBehaviorDelegate getNext)
        {
            var stopwatch = new Stopwatch();
 
            var logger = LogManager.GetLogger(input.MethodBase.ReflectedType);
 
            var declaringType = input.MethodBase.DeclaringType;
            var className = declaringType != null ? declaringType.Name : string.Empty;
            var methodName = input.MethodBase.Name;
            var generic = declaringType != null && declaringType.IsGenericType
                              ? string.Format("<{0}>", string.Join<Type>(", ", declaringType.GetGenericArguments()))
                              : string.Empty;
 
            var argumentWriter = new StringWriter();
            for (var i = 0; i < input.Arguments.Count; i++)
            {
                var argument = input.Arguments[i];
                var argumentInfo = input.Arguments.GetParameterInfo(i);
                argument.Dump(argumentInfo.Name, argumentWriter);
            }
            var methodCall = string.Format("{0}{1}.{2}\n{3}", className, generic, methodName, argumentWriter);
 
            logger.InfoFormat(@"Entering {0}", methodCall);
 
            stopwatch.Start();
            var returnMessage = getNext()(input, getNext);
            stopwatch.Stop();
 
            logger.InfoFormat(@"Exited {0} after {1}ms", methodName, stopwatch.ElapsedMilliseconds);
 
            return returnMessage;
        }
 
        public IEnumerable<Type> GetRequiredInterfaces()
        {
            return Type.EmptyTypes;
        }
 
        public bool WillExecute
        {
            get { return true; }
        }
    }

That’s it. One simple class that does the logging. And a config change to mark what interfaces you want logged.

        <register type="LoggingTest.Namespace.IInterfaceToLog, LoggingTest.Namespace.Assembly">
          <interceptor type="InterfaceInterceptor" />
          <interceptionBehavior type="LoggingTest.Namespace.Loggers.LoggerBehavior, LoggingTest.Namespace.Assembly"/>
        </register>

This config change will run the LoggerBehavior on the methods defined in LoggingTest.Namespace.IInterfaceToLog. If you want to log the methods on more interfaces, just add another register node to the config. While you still need to add these manually. You do it at the interface level, rather than the method level. AND you can add/change what is logged after compiling.

There is more you can do, check the Unity codeplex page.

20 GOTO 10

I was hooked.

10 PRINT "HELLO CAFFGEEK! "
20 GOTO 10

I don’t remember how young I was (under five), but the first time I saw that repeated over and over on the screen, with my name, and nobody repeatedly typing it, a seed was planted. I spent countless hours typing in programs from magazines, and computer books. And countless more hounding my parents to type it for me as they were faster. Games like Face Painter only watered that seed. But it was dormant for a while.

Atari 800XL
Atari 800XL

After the Atari 800XL was no longer a viable computer and had been packed away, I made do without a computer for many years. Unimaginable now. Pretty common in the 80’s. Then, when I was in junior high, fertilizer was added. My parents were able to afford a 286 computer. My dad liked to take things apart and see how they work. So learning hardware and how to upgrade them quickly followed. I built my own computer from spare parts. Shortly after a friend introduced me to Turbo Pascal.
Turbo Pascal
Turbo Pascal 5.0

I spent time writing all kinds of things, little paint programs. Terrain tile models for games that I never built. Small little games, reminiscent of those I played on the Atari as a kid. Even writing my own 3d routines to draw, rotate, and shade objects. Some of these I recently uncovered on some 3.5″ diskettes. Maybe I’ll find a drive and see if I can view my first forays into programming (scary they may be). From then on, there was little doubt about what I would eventually do for a living. And here I am today. Still playing. Still learning. Still amazed.

Where were you? What’s your story?

No, I won’t fix your cell phone…

Maybe it’s just me, but after reading Coding Horror‘s latest blog post The PC is Over, it made me realize another trend I’ve been noticing. Typically, a few times a week I get the question “Hey, you’re a computer guy right? My computer is …” and then they outline the issue. I tend to reply that fixing computers isn’t really what I do. I manage to fix my own, but it’s not something I’m good at. And lately, it’s getting more true, I actually have an existing PC “pausing” issue that I can’t seem to fix (it randomly freezes up for a few seconds/minutes then works fine again). Anyhow, back to the point. I’ve been noticing that people aren’t asking me about their computers as often. I still get asked about the same frequency, but now the questions are about their smart phone.

I also started pondering a conundrum we could be in if more people use their phones than their computers. If enough people stop buying computers as frequently as they do, the cost of computers will start to increase. I hope I’m wrong, because to create the software running on the smart phones, we currently need computers. And it will be cost prohibitve to spend the money we need to develop the programs, given most people don’t buy apps for their phones like they will for their computer. Unless we get lucky, and the phones literally become replacements for computers, that I can plug in a screen, keyboard and mouse into (or wireless, whatever) and I can run servers, and development environments upon. That would be interesting…

Reduce your Technical Debt

Now, let me preface all this by saying it’s not a critique on the quality of the person. And I’m not writing this to rip apart anyone. The first goal of development is to write working software that provides business value. However, in providing business value, we can improve the value by improving the quality of code. It’s often cited that 80% of the cost of a system is maintaining it after it’s written. A lot of that maintenance is feature changes. Which is expected. However, the cost of those changes can be greatly reduced by improving the quality of code that is written, and increasing the maintainability of the codebase. Code is written for humans to comprehend, not computers. They do that in the blink of an eye. It takes us developers much longer to understand hundreds of thousands of lines of code.

Technical Debt is what all these bad decisions add up to. Eventually you have to pay it off, and fix it, or you’ll drown, and your application goes bankrupt. Forcing you to rewrite it (more on that later).

So, on with the examples of what not to do.

This is a new take on the normal misuse of booleans

        Select Case isBoolean
            Case True
                myValue = 10
            Case False
                myValue = 99
        End Select

Why not write it clearly in one line?

	myValue = IF(isBoolean, 10, 99)

or in c# (which I prefer)

	myValue = isBoolean ? 10 : 99;

If your code is FULL of hardcoded constants, put them in a config or the database or use enums! There should be NO MAGIC NUMBERS.

Use your database properly. There is no good reason to have a field, that contains delimited values!

Use tools that check the cyclomatic complexity of your functions. Anything over 10 is getting high. Anything over 15 is too high, and should likely be refactored and split up. If you have a complexity of 339! like a 1600 line function I will soon have the luxury of modifying you should be beaten and while in recovery forced to read a book about clean code…like say the book Clean Code: A Handbook of Agile Software Craftsmanship.

If you are copy/pasting in your app, you’re doing something wrong. If the functionality is that similar, extract the commonality and refactor your code properly. You should NEVER need to make the same change in two places. A very common violation of this rules is a switch statement. If you have the same switch statement in two places to control flow, your program is structured wrong.

A recent example of copy/paste programming

        if (fileName.IndexOf(".zg") > 0) {
            partFileName = fileName.SubString(0, fileName.IndexOf(".zg"))
            bolExtentionRecognized = true
        }
 
        if (fileName.IndexOf(".rur") > 0) {
            partFileName = fileName.SubString(0, fileName.IndexOf(".rur"))
            bolExtentionRecognized = true
        }
 
        if (fileName.IndexOf(".dat") > 0) {
            partFileName = fileName.SubString(0, fileName.IndexOf(".dat"))
            bolExtentionRecognized = true
        }

While the intent is clear, it’s not easily maintainable, it’s much simpler if you just had to maintain the list of extensions, like this

        // acceptableExceptions loaded in a config, not inline
        // acceptableExceptions = new [] { ".zg", ".rur", ".dat" }
        foreach (var extension in acceptableExtensions) {
        	var extensionStartsAt = fileName.IndexOf(extension)
		if (extensionStartsAt > 0) {
		    partFileName = fileName.SubString(0, extensionStartsAt)
		    isExtentionRecognized = true
		}
        }

Use the right data types. Don’t store “True” and “False” or “Y” and “N” in a string when you can use a boolean variable. If it’s a number, put it in an int. Don’t store money in floating point numbers. Etc, etc.

Most importantly, know your framework. Stop reinventing the wheel. More than likely the framework has covered all the edge cases you’ll miss, and it’s been veted by thousands of programmers actively using the framework. There are methods to work with directories and files. Path.Combine is a good one to know. There are methods to parse dates, and convert strings of numbers into integers (decimals, etc). These methods can return wether it was successful (TryParse) while returning the parse result to prevent a need for exception handling. Which reminds me, exceptions should be exceptional. If you can check for it, do so first, and ensure the exception can’t happen. If you can’t check, but can’t handle it, there’s no need to catch it.

    Function returnProperTime(ByVal strDate As String) As String
        Dim strHour As String
        Dim strMinute As String
        Dim strSecond As String
 
        strHour = Mid(strDate, 1, 2)
        strMinute = Mid(strDate, 3, 2)
        strSecond = Mid(strDate, 5, 2)
 
        returnProperTime = strHour & ":" & strMinute & ":" & strSecond
    End Function

Should be using ParseExact (and should be using proper types)

    var dateTime = Date.ParseExact(strDate, "HHmmss", CulterInfo.InvariantCulter)
    var formattedTime = dateTime.ToString("HH:mm:ss")

When you see these things, fix them. You don’t have to make the app perfect all at once, but every file you open, leave it in a little better condition than you found it. Follow the boyscout rule. If everyone does that, it won’t take long and the codebase everyone dreaded to work on will become enjoyable to work on. And it didn’t require a grueling rewrite (which you should almost never do).

Waste….

I talked about it in my last post, and it still permeates. The Kanban board I have pushed for at work is still not up. The CIO has even approved it. And you’d think a $100 purchase of a whiteboard and some post-its would be a trivially simple task. However, after a couple weeks, still nothing. We ran a trial on a whiteboard in another workers office while they were away on vacation for a week. And you could quickly see where our bottlenecks were. But the team seeing them, and the people who can actually institute change are two different things. Maybe in another couple weeks we’ll finally get it up. Maybe not.

Another fun part of the past few weeks was taking a week to decide on the font for the title of a corporate report. You’d think we would have a standard format. And you’d be right. We do. I followed it initially. Was asked to change it twice since then. Finally had enough and got a PM, BA, and two other employees, and a member of Senior Management. And yet it took that long for someone to explain what the font should be. Turns out, it was exactly like the corporate standard…but no italics. That was it. A week and probably 10-15 man hours between several people to say “No italics”.

Next topic is going to be about UI…and why you don’t let the users design them. Not that they aren’t critical to the process. After all, they’re the owner of the product!

Wrap existing .NET site in a Portal/Theme

Recently I ran into the problem where we were attempting to have all of our websites run inside of a portal. Where you could easily jump from one app to another, without having to go back and forth, or always back to a home page with links.

What we wanted was a header menu added to ALL of our websites that handled some authentication, and showed what websites you had access to, and allowed you to easily navigate between them, as though they were one large application.

Now, rewriting all these applications would be a nightmare. There was no time. Even if the goal was simply to modify the master pages of each app…some of which were very dated, and didn’t have the luxury of master pages.

So with a little research on the Page Adapter Class and some creative use of the .browsers file, I came up with an unobtrusive method to easily add our new portal to all existing applications, without any recompiling.

First, lets create our Portal Page Adapter project. Add a new class file named PortalPageAdapter.cs

namespace PortalPageAdapter
{
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Web.UI;
	using System.Web.UI.HtmlControls;
	using System.Xml;
	using System.Web.UI.WebControls;
	using System.Web;
	using System.Security.Principal;
	using System.Xml.Linq;
 
	public class PortalPageAdapter : System.Web.UI.Adapters.PageAdapter
	{
		protected override void OnInit(EventArgs e)
		{
			// Do whatever security checks you need to do...
			string user = HttpContext.Current.Request.LogonUserIdentity.Name;
 
			List<string> roles = new List<string>();
 
			//populate your roles
 
			// Setup Context
			HttpContext.Current.User = new GenericPrincipal(HttpContext.Current.User.Identity, roles.Distinct().ToArray());
 
			base.OnInit(e);
		}
 
		protected override void CreateChildControls()
		{
			base.CreateChildControls();
 
			// Inject portal onto page
			HtmlForm html = this.Page.Controls.OfType<HtmlForm>().First();
			html.Controls.AddAt(0, new LiteralControl("<br/>PORTAL GOES HERE"));
 
			// Hide WebControls based on their Role Attribute
			// 	This allows the portal, to handle the rendering of any websites 
			//	you have, it can show/hide items at the control level
 
			List<WebControl> controls = html.Controls.OfType<WebControl>()
				.Where(c => ! string.IsNullOrEmpty(c.Attributes["role"]))
				.ToList();
 
			foreach (WebControl control in controls)
			{
				control.Visible = false;
 
				foreach (string role in control.Attributes["role"].Split(' '))
				{
					if (HttpContext.Current.User.IsInRole(role))
					{
						control.Visible = true;
						break;
					}
				}				
			}
		}
	}
}

Now that we have our portal written, with a trick up it’s sleeve to hide/show content based upon the users roles, let’s look at how we add it to an existing website.

In the App_Browsers folder, add a file called default.browsers

<browsers>
	<browser refID="Default">
		<controlAdapters>
			<adapter controlType="System.Web.UI.Page" adapterType="PortalPageAdapter.PortalPageAdapter" />
		</controlAdapters>
	</browser>
</browsers>

And add the compiled dll of our PortalPageAdapter class to the bin folder.

Navigate to your website and the first thing you see should be the line “PORTAL GOES HERE”, which you can modify in the Portal Page Adapter class to create whatever content you would like. How you generate the html is up to you. Be it includes, databases, xml/xsl, whatever. Get creative.

Now, the last thing, is, the ‘trick’ that has been programmed in, on any aspx page, you can add the “role” attribute to any WebControl (asp:whatever) and it will be rendered (or not) based upon the roles loaded by the PortalPageAdapter class.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="PageAdapter._Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
	<title></title>
</head>
<body>
	<form id="form1" runat="server">
	<div>
		<asp:Panel runat="server">
			Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vestibulum imperdiet justo id sem. Aenean convallis mi sed ipsum. Donec semper dapibus erat. Suspendisse consequat libero quis felis feugiat aliquam. Sed nec elit. Fusce sapien tellus, vestibulum id, pharetra in, scelerisque sit amet, lectus. Vivamus eu turpis at nunc elementum ultricies. Morbi feugiat fringilla est. Fusce urna diam, accumsan vitae, ultrices non, varius vitae, quam. Vivamus vitae enim vel nulla mattis aliquam. Suspendisse lacinia arcu non urna. Praesent convallis ante ut sapien. Nunc sit amet mauris ornare mauris accumsan ultricies. Praesent venenatis tellus id quam. Fusce vel enim a est malesuada venenatis.
			<p />
		</asp:Panel>
		<asp:Panel runat="server" role="ADMIN">
			Sed eget mauris vitae libero imperdiet malesuada. Suspendisse feugiat semper erat. Duis sit amet odio ultricies dui ultrices volutpat. Sed id risus vitae odio bibendum varius. Nam rutrum consectetur felis. Donec vitae velit. Phasellus facilisis ornare mi. Etiam quis dui id leo bibendum vehicula. Proin egestas, neque quis pulvinar malesuada, odio tellus porta orci, nec dictum est nunc id diam. Integer eu dui. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Ut nec ante ut pede ornare ornare. Mauris eget mauris et urna tincidunt tincidunt. Suspendisse sit amet lacus in dui fermentum aliquam. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse odio. Phasellus orci. Aliquam erat volutpat. Nam eget ligula id arcu fermentum lobortis. Sed vel leo.
			<p />
		</asp:Panel>
		<asp:Panel runat="server" Role="USER ADMIN">
			Suspendisse pulvinar. Etiam ipsum. Proin vulputate nibh et purus. Nunc hendrerit. Morbi consequat nibh id tortor. In mollis rhoncus velit. Pellentesque magna ipsum, cursus sit amet, viverra eu, porttitor et, dolor. Cras quis sem. Ut interdum nisi quis magna. Donec mauris erat, consequat id, gravida eu, scelerisque ac, nisi. Nulla ante purus, dignissim nec, mollis quis, euismod quis, arcu. In vel arcu. Duis enim erat, accumsan vel, blandit vel, ultricies id, pede. Nulla luctus, nisi at faucibus tincidunt, orci sapien ornare erat, vitae iaculis justo risus ut magna.
			<p />
		</asp:Panel>
		<asp:Panel runat="server" Role="MANAGER">
			Etiam imperdiet lacus at dui fringilla dictum. Etiam condimentum, diam vitae fringilla faucibus, ante ante facilisis nulla, at porttitor diam ipsum vel odio. Aliquam sollicitudin neque et diam. Aenean feugiat, justo ut imperdiet cursus, sem risus ultrices ligula, eget rutrum orci leo tempor enim. Nunc eget velit. Pellentesque dictum, odio a tempor aliquet, ipsum justo lacinia mauris, sed posuere metus enim vel ipsum. Donec dolor. Aliquam semper, eros vitae vehicula lacinia, dolor massa suscipit libero, a molestie enim justo tincidunt sem. Praesent eget mi ut purus interdum vehicula. Nulla a leo. Morbi lacinia, ligula id pharetra vulputate, sapien arcu dignissim tortor, ac condimentum velit libero at purus.
			<p />
		</asp:Panel>
		<asp:Panel runat="server">
			Donec adipiscing lacus ac sapien. Nulla ac quam. Sed enim. Curabitur magna. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Cras viverra, libero quis molestie cursus, justo lacus semper nisl, in fringilla enim pede ac elit. Pellentesque facilisis. Etiam fringilla adipiscing mauris. Vivamus bibendum nibh nec massa. Maecenas risus. Cras tempus accumsan pede. Cras dictum hendrerit dolor. Curabitur et tortor ullamcorper elit mattis lacinia. Cras tempus euismod velit. Donec feugiat nunc quis dui. Vivamus nisi urna, bibendum et, imperdiet sit amet, facilisis in, libero. Maecenas lobortis velit at ante. Aenean in lacus et massa fringilla molestie. Praesent mollis nibh ut nunc.
			<p />
		</asp:Panel>
		<asp:Button runat="server" role="ADMIN" Text="Admin Button" />
		<asp:Button runat="server" role="USER MANAGER" Text="User Button" />
	</div>
	</form>
</body>
</html>

This would also be a simple way to add an embeded IM client to a series of websites.

Happy coding.

The C# ?? null coalescing operator

A simple tip to save typing and increase your codes readability is the “double question mark operator”, more accurately called the “null coalescing operator”.

Instead of using this to set defaults in a function

function void test(int aVar) {
	int myVar = aVar;
	if (aVar == null) {
		myVar = 42;
	}
}

or

function void test(int aVar) {
	int myVar = aVar == null ? 42 : aVar;
}

You can simply use

function void test(int aVar) {
	int myVar = aVar ?? 42;
}

Granted, you’d likely find much better ways to use this than simply defaults in a function call, but hey, this is just an example, let your imagination do the work.

MSDN reference: http://msdn.microsoft.com/en-us/library/ms173224.aspx

XML Validation with XSDs

I’ve been doing a lot of work with excel uploads lately to allow clients to easily upload data to their systems.  Not the best approach mind you, but they know how to use excel…so what can you do?

What I have worked out is a multi step process to import the excel file to the database and validate the contents to a reasonable degree.  I have these all written into some custom classes and use IOC and DI to handle workflow so that writing subsequent uploads is a trivial task, and the majority of code is simply the validation and transformation files (XSDs, XSLs, and Stored Procs)

First, you have to retrieve the contents of the excel sheet(s)

protected XElement GetWorksheetXML(int sheetNumber, string xsltPath)
{
  // Create working file
  string workingFilePath = Path.GetTempFileName();
  File.WriteAllBytes(workingFilePath, this.filebytes);
 
  // Read the worksheet
  DataSet myDataSet = new DataSet();
  string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + workingFilePath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1;'";
 
  // Fill the Dataset
  OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + GetExcelSheetNames(strConn)[sheetNumber] + "]", strConn);
  myCommand.Fill(myDataSet);
 
  // Clean up
  File.Delete(workingFilePath);
 
  // Return transformed XML
  return XElement.Parse(Utility.Transform(myDataSet.GetXml(), xsltPath, null));
}

Secondly, convert it to a better XML format with an XSLT.  You should also merge any sheets here so you have one XML file from the XSL.  I’m not going to bother showing this, as it’s hardly the point of the article, though, if you are unsure how to do this, feel free to ask

Third, and the whole point of this article, verify data types with XSD.

protected XElement ValidateXSD(XElement item, string xsdPath)
{
  XElement errors = new XElement("Errors");
 
  if (File.Exists(xsdPath))
  {
    // Reference: http://msdn.microsoft.com/en-us/library/bb358456.aspx
    XDocument xsd = XDocument.Load(xsdPath);
    XDocument xml = XDocument.Parse(item.ToString());
    XmlSchemaSet schemas = new XmlSchemaSet();
    schemas.Add("", XmlReader.Create(new StringReader(xsd.ToString())));
    xml.Document.Validate(schemas,
      // Validation Event/Error Handling
      (sender, e) =&gt;
      {
        errors.Add(new XElement("Error", e.Message));
      }
    );
  }
 
  // If there were errors return them, otherwise return null
  return errors.Elements().Count() &gt; 0 ? errors : null;
}

Fourth, at this point, if there are no errors, you should pass the XML file as input to a stored procedure to further validate the data (if there are IDs and Codes that need to be verified with other systems), and assuming all has gone well, pass the XML file to a stored procedure which writes the data into your database…or whatever you need to do.  By this point your XML should be clean, and valid.

Painlessly Audit Your SQL2005 Schema & DDL Information

I recently stumbled across the following page

Painlessly Audit Your SQL2005 Schema & DDL Information.

We all know about SQL DML (Data Manipulation Language) triggers, these are the kind we use  occassionally when data is manipulated.

What you may not know about are DDL (Data Definition Language) triggers.

In short, you can write a trigger to fire whenever tables, stored procedures, views, etc change.

So with the following table and trigger (code stolen from link above) you can audit your stored proc and table changes

CREATE TABLE [dbo].[DBChangeLog](
    [LogId] [INT] IDENTITY(1,1) NOT NULL,
    [DatabaseName] [VARCHAR](256) NOT NULL,
    [EventType] [VARCHAR](50) NOT NULL,
    [ObjectName] [VARCHAR](256) NOT NULL,
    [ObjectType] [VARCHAR](25) NOT NULL,
    [SqlCommandXml] XML NOT NULL,
    [EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate]  DEFAULT (getdate()),
    [LoginName] [VARCHAR](256) NOT NULL
) 
 
CREATE TRIGGER [LOG_DB_OBJECT_CHANGE] ON DATABASE
FOR create_procedure, alter_procedure, drop_procedure
    , create_table, alter_table, drop_table
    , create_function, alter_function, drop_function
AS
 
    SET NOCOUNT ON
 
    DECLARE @DATA XML
    SET @DATA = EVENTDATA()
 
    INSERT INTO DBChangeLog(databasename, eventtype, objectname,
                            objecttype, sqlcommandxml, loginname)
    SELECT @DATA.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
        , @DATA.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)')
        , @DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
        , @DATA.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)')
        , @DATA.query('(/EVENT_INSTANCE/TSQLCommand)[1]')
        , @DATA.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
 
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [LOG_DB_OBJECT_CHANGE] ON DATABASE

And that’s all there is to it.

You could now easily write a webpage to search and filter this table, and perform diffs on different versions.  And never again have a stored procedure get changed on you, clobbering a fix you did the day before…without having a backup of your fix!

And you can log who made the change, so you can exact some revenge.

-c