Friday, November 21, 2008

Brute Force Data Find Method for SQL Server

On several occasions, I have been tasked with finding some miscellaneous piece of information in a database. Usually, it is some Third party database that I am not that familiar with. For example, someone says, “I need to know where all the occurrences of a particular person’s email appears in this database.” My first approach was to try and find all the fields that might have EMAIL or MAIL or something like that in the title. I quickly found out that not all database designers are created equal. Standards are lacking and consistency is foreign word. To that end, I decided to create a brute force method for finding data. I may not be the quickest but it let’s someone else do the work; namely the database engine.

I am going to be using the Information_Schema views for this exercise. Since they are ANSI standard views, they should be compatible with future versions of SQL server as well. I try to stay away from system tables as there is no guarantee that they will remain consistent between versions. The way I decided to approach this was to query the Information_Schema.Columns view in order to retrieve a complete listing of tables and columns in the database. I use the results of this view to build another script which will actually do the work. This script I will call the “build” script. We will use the build script to build the “query” script.

The build script will consist of 3 parts; 1) create the table variable to hold the results, 2) populate that table with the results and 3) retrieve the values from the variable. Part 1 looks like this.

Build Script Part One
Set NoCount ON
Print 'Set NoCount On'
Print ''
Print 'Declare @Results Table '
Print '('
Print ' TableName varchar(50),'
Print ' FieldName varChar(50),'
Print ' FieldLength int,'
Print ' DataValue varChar(2000)'
Print ')'
Print ''

If we set the results screen in query analyzer to TEXT, then when we run this, it will generate the declaration much like you see it here. This will build the first part of the query script to declare the table value we will then populate. Part 2 will generate the statements to populate this. It looks like this.

Build Script Part Two
Select 'Insert Into @Results Select ''' + Table_Name + ''' as TableName, ''' +
Column_Name + ''' AS FieldName, ' +
Convert(varchar,Character_maximum_length) + ' as FieldLength, [' +
Column_Name + '] as DataValue ' +
'From [' + Table_Schema + '].[' + Table_Name + '] ' +
'Where Upper([' + Column_Name + ']) Like Upper(''%Prestwick%'')'
From Information_Schema.Columns

Where Data_Type in ('char', 'varchar','nvarchar','nchar') And
Character_maximum_length >= 9
and Table_Schema = 'dbo'

Let’s examine this code bit by bit. We are going to generate Insert statements. According to our @Results table from above, we are going to need 4 pieces of information. We will need the Table Name, the Field Name, the Field Length, and the actual data value where we found what we are looking for. As you can see from the code above, that is exactly what we are extracting from the Information_Schema.Columns view. We are using the meta data in the database to build insert statements that will return to query the data. On line 6, we specify the selection criteria for the “query” script. To eliminate and CASE issues, we will convert the value of the column as well as the value of the TextToFind to Upper case. You can massage this part to better fit your needs. If you know the value is exact, you can change the ‘LIKE’ to an ‘=’. In this case, I am looking for an address that contains the word Prestwick. I need to know the table and column. In the last part of the build query , I use the meta data in the columns view to eliminate some of the field. If I know that I am looking for text, I can immediately eliminate all the numeric, bit and date fields. If I know the text is at least 12 characters, there is no need to query any Char(6) fields. When this portion is run agains AdventureWorksDW database, here are some of the results.

Insert Into @Results Select 'DimCustomer' as TableName, 'CustomerAlternateKey' AS FieldName, 15 as FieldLength, [CustomerAlternateKey] as DataValue From [dbo].[DimCustomer] Where Upper([CustomerAlternateKey]) Like Upper('%Prestwick%')

Insert Into @Results Select 'DimCustomer' as TableName, 'FirstName' AS FieldName, 50 as FieldLength, [FirstName] as DataValue From [dbo].[DimCustomer] Where Upper([FirstName]) Like Upper('%Prestwick%')

Insert Into @Results Select 'DimCustomer' as TableName, 'MiddleName' AS FieldName, 50 as FieldLength, [MiddleName] as DataValue From [dbo].[DimCustomer] Where Upper([MiddleName]) Like Upper('%Prestwick%')

Insert Into @Results Select 'DimCustomer' as TableName, 'LastName' AS FieldName, 50 as FieldLength, [LastName] as DataValue From [dbo].[DimCustomer] Where Upper([LastName]) Like Upper('%Prestwick%')

Insert Into @Results Select 'DimCustomer' as TableName, 'Suffix' AS FieldName, 10 as FieldLength, [Suffix] as DataValue From [dbo].[DimCustomer] Where Upper([Suffix]) Like Upper('%Prestwick%')

Of course there are more records that fit this criteria but for now, I am only showing a few for example. Now that we have the declaration and the insert statements generated, the last part of the script is the retrieval of those records. That looks like this.

Build Script Part Three
Print ''
Print 'Select Distinct * From @Results'

If we take all three sections of the Build script and combine them together and run it, we can generate a script that will query each potential field in the database for the value you are looking for. Remember, to generate the “query” script, you will need to set the query analyzer to return text results, not grid results. The next step is to copy the contents of the results of the Build script and paste them into a new query window. If I run the entire script on the AdventureWorksDW database, I can now find out which tables and fields contain this value. The actual Query script that this generates will look at over 140 fields and let you know what it finds. The results of my script are as follows.

TableName FieldName FieldLength DataValue
----------------- ------------- ----------- ---------------------
DimCustomer AddressLine1 120 1588 Prestwick Drive
DimCustomer AddressLine1 120 1841 Prestwick Drive
DimCustomer AddressLine1 120 4152 Prestwick Drive
DimCustomer AddressLine1 120 4998 Prestwick Ave.
DimCustomer AddressLine1 120 5259 Prestwick Dr.
DimCustomer AddressLine1 120 6208 Prestwick Dr.
DimCustomer AddressLine1 120 6709 Prestwick Ave
DimCustomer AddressLine1 120 7568 Prestwick Court
DimCustomer AddressLine1 120 8079 Prestwick Drive
DimCustomer AddressLine1 120 9271 Prestwick Ave.
DimCustomer AddressLine1 120 9875 Prestwick Court
ProspectiveBuyer AddressLine1 120 1841 Prestwick Drive
ProspectiveBuyer AddressLine1 120 8079 Prestwick Drive
ProspectiveBuyer AddressLine1 120 9875 Prestwick Court
vTargetMail AddressLine1 120 1588 Prestwick Drive
vTargetMail AddressLine1 120 1841 Prestwick Drive
vTargetMail AddressLine1 120 4152 Prestwick Drive
vTargetMail AddressLine1 120 4998 Prestwick Ave.
vTargetMail AddressLine1 120 5259 Prestwick Dr.
vTargetMail AddressLine1 120 6208 Prestwick Dr.
vTargetMail AddressLine1 120 6709 Prestwick Ave
vTargetMail AddressLine1 120 7568 Prestwick Court
vTargetMail AddressLine1 120 8079 Prestwick Drive
vTargetMail AddressLine1 120 9271 Prestwick Ave.
vTargetMail AddressLine1 120 9875 Prestwick Court

From here I can see that the value ‘Prestwick’ appears in 3 different tables. This gives me a headstart on doing the rest of my research.

Wednesday, September 24, 2008

Book Review: Best Kept Secrets of Peer Code Review by Jason Cohen

Recently, SmartBear Software ( offered a free book for the taking on the subject of peer code reviews. Now you may be saying, “free huh?” We have all gotten information that appears to be free but is really advertising for some product. Before making any assumptions, let me assure you this too is advertising for Code Collaborator, SmartBear’s code review software application. Having said that, I read the book anyway and was very surprised. Out of the ten chapters, only one is dedicated to the software. The other nine chapters have some really good information concerning code reviews. I recommend the read to anyone who is considering implementing code reviews as a part of their development process. Finally, let me say this is not a software review. I have never used or even seen the application in question. This review is about the book and the book alone.

The book is organized as a series of essays dealing with various topics of code review such as facts and figures, statistics, case studies and even the human factors (ego, pride and the overwhelming desire to not have your work criticized). The book starts with making a case for reviews and contrasting the what the detractors have to say about the reviews. It then goes into facts figures and a case study with Cisco Systems. The book also addresses the social effects of code reviews and analyses how to cope with that as management. Finally, once the code reviews are done, there is a chapter on capturing metrics so that you can concretely tell if the code reviews are working for your organization. There is only one chapter at the very end that pushes the product itself. All in all, the organization was very good, logically put together, and useful.

I have worked for various companies in the past, some of which engaged in code reviews. Early on, a code review would be 6 or 7 people in the room with one person saying “Anyone have a problem with line 1….. Anyone have a problem with line 2… “. This was a brutal way to spend an afternoon and usually didn’t produce results. The content of this book focuses more on reviews as a way to improve the code, not eat up valuable development time. Several different types of reviews are studied in here, not only the traditional walk-thru meeting. The book covers traditional meetings from a different point of view but also covered other techniques like email reviews and over the shoulder reviews. Of course, there is talk of tools to facilitate these kinds of reviews but nothing specific (until chapter 10 of course).

When it comes to the big review meetings, not only was time wasted but so was goodwill. If reviews aren’t handled properly, then people get angry, upset or offended. This is not a good situation to have within a team that has to cooperate work in harmony to get the job done. The book deals with techniques for helping developers to have a good feeling about the reviews. Concepts such as “don’t tie performance evaluations to quantity of defects.” When you start trying to use the results in this way, people fudge the results, hide the actual defects, try to defend an indefensible position and in the end, the big loser is the deliverable; the product itself. Code reviews should be limited to a review of the code, not a review of the people.

I also liked the section on metrics which deals with ways of collection information to support how successful your code reviews are at improving the quality of code. You can’t determine success unless you can measure it.

All in all, it was a great little book. It could have used one more pass through the editor as there were some needless typos in the book but nothing that will stand in the way of the information getting through. It is short, to the point, and inexpensive ($4.19 on Amazon at the time of this writing). I don’t know how long it will last but at the moment, SmartBear software is offering the book for free, if you will fill out the appropriate forms. The location for this is…

.NET 3.5 Master Pages

This article will look at Master Pages, a built in feature of ASP.Net that was introduced in .NET 2.0 and has been enhanced in the current 3.5 version. This article will not cover every aspect of Master Pages but instead is intended to be an introduction. We will examine how to create a Master Page, content pages and hook them together. We will also look at how to setup several mechanisms for communication between the pages and finally go over a few pitfalls to avoid. The writer assumes the read has a basic understanding of how to create ASP.Net web applications using Visual Studio 2005 or later.

The Call for Master Pages:
Although Master Pages is a relatively new technology, the need for the concept is as old as web development itself. "How do I easily give my pages a consistent look and feel, not only with colors and fonts but with layout and even some functionality?" In the past, we would accomplish this with server side includes, user controls, and other creative solutions. With the introduction of Master Pages, .NET web developers now have a method for developing web pages with consistent layouts and common functionality that is both easy to code and a snap to maintain.

How Master Pages Work:
The job of a master page is to provide a layout or "shell" for the content of your website. By the master page handling the details of the layout (various screen areas, menus, advertisements, footers), the content page can focus on one thing: content. At the server level, when the content page is invoked, the master page and the content page are merged to form one cohesive page. This page is then delivered to the client without the client ever knowing the difference. As far as the browser is concerned, it is one page.

Getting Started:
To get started with Master Pages, create a web application using Visual Studio. To that web application you will need to add at least one "Master Page" and one "Web Content Form". In your solution explorer, click on "Add > New Item" and you should find each of these file types. Create the Master Page first. Once that is done, when you create the Web Content Form, a dialog will pop up allowing you to select the master page to associate with the new content page. The IDE will automatically do the initial hookup between the two. So what exactly got created?

At the top of the master page, you will notice a new directive:

<%@ Master Language="C#" AutoEventWireup="true"... %>

Instead of a Page directive you now have a Master directive. This will identify the page as a master page. The rest of the page is fairly innocuous. It should look just like any other ASPX file markup - the only difference is that you will have a default ContentPlaceHolder that looks like this:

<asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server">

This is the spot where the content page will put its information. When you look at the content page, however, it looks quite different. At the top, you will find the normal page directive but now, the only tag allowed in a content page is the <asp:Content> tag. It looks something like this:

<asp:Content ID="SampleContent" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">

Whatever is placed inside of the content tag will become the content on the Master Page. This content tag is where you will place HTML markup, ASPX controls and other text you want to appear on the page. One of the nice abilities of Visual Studio IDE is that you can edit the content in the designer and see the master page at the same time. When editing the content page, you are not allowed to edit the master page but you can still see it in the background, which is a helpful tool during development.

Auto Hookup in the Web.Config:
One way to associate master pages with content pages is through the Page directive as mentioned above, which links a particular content page with a particular master page. If your site uses several different master pages, this is a good way to specify which master page each content page uses. However, if your site has only one primary master page, you can declare a global master page that all undeclared content pages use. You can easily set this up in the Web.Config file by setting the masterPageFile attribute of the pages element as shown below. (Note: There may be much more code in the Web.Config file but only the relevant sections and attributes are shown.)

<pages masterPageFile="~\Global.master">

By setting the masterPageFile attribute of the pages element in web.config, any content page that is not specifically tied to a master page will use the master page specified here.

Communication between Master Page and Content Page:
Now that you have the basic look and feel of the master page and content page defined, you will need to add functionality. Since the master page is part of every content page, you may want to localize some functionality in the master page that it can be accessed by all the content pages. Also, you may want to change the appearance or context of the master page, depending on the particular content page that is being loaded. Let’s take a look at a couple of examples.

Suppose you have a menu control on the master page. This is a common control to place on a master page since you typically want the menu to be available to all content pages. However, one of your requirements is to change one of the menu items depending on which page is being loaded. You can easily access the menu control from the content page by using the FindControl method of the content page’s master page. Within the content page, you can add the following code to the Page_Load method:

//Alter the Menu
Menu mnu = (Menu)(Master.FindControl("Menu1"));
if (mnu != null)
mnu.Items[0].Text = "Custom Menu";

In this example, Menu1 is the name of the control on the master page. The FindControl method will locate the control and return a menu object. We then cast the object to a Menu and check to see that we have received a valid Menu reference (e.g. mnu is not null). With a valid reference in hand, we can then access the methods of the menu object and modify it just as if we were in the master page. There are some drawbacks to this method. Primarily, if the menu name ever gets changed, this will not be identified by the compiler which can cause a maintenance problem.

There is a second method for accessing information in the master page. We can early bind attributes so that any anomalies will be caught during compile time. Simply expose whatever information you want exposed to content pages in the master page class. For example, you may have a drop down list and you want it to be stored on the master page but you want the current selection to be made available for consumption in the content page. The best way to handle this is to expose the current selection as a property of the master page. In the master page, create the following property.

public string Selection
return cboTest.SelectedValue.ToString();

Note that the return type of the property is not a DropDownList object, but a string object. This is because we are not interested in exposing the control as much as we are interested in the control’s selected item. This allows us to abstract the call and if we decide later to use a third party control or a different method for selecting the data, you will not have to change all of the content pages that consume this data. The next step is to access this data from the content page. Before you can do this, there is one change that you will need to make to the content page. In order for the content page to be able to recognize the properties added to the master page, we need to tell the content page which class to use for this. Use the @MasterType directive to set the strong type for the content page’s master page, as accessed through the Master property. In the markup for the content page, add the following tag:

<%@ MasterType VirtualPath="~/Example.master" %>

Substitute the actual master page file name where it says Example.master. With that in place, we can access the above defined property with the following code in the content page.

Label1.Text = Master.Selection;

This will access the Selection property of the Example master class and assign the results to a label on the content page. Above, you saw the example of how to declare a global master page. In order to strongly type the master page to the content page, you must include the @MasterType directive in each content page. Some have used inheritance to get around this which Microsoft has confirmed and is considering a change for future versions of .NET.

Relative Paths:
One of the confusing parts of master pages is how it handles relative paths. If a content page and its associated master page are in the same directory, then there aren’t going to be any issues related to relative paths. However, if the two are in different directories, the resultant page is going to default to the content page’s location to resolve any relative path links. For example, let’s say you have a master page in the root directory and a content page in a subdirectory called \Content. Also, both of these pages reference a graphic called HorizontalLine.gif. This graphic is in a directory called \Images. The content page might refer to this resource as...

<img src="../Images/HorizontalLine.gif"/>

The Master page, might refer to this as ...

<img src="Images/HorizontalLine.gif"/>

However, once the master page and content page are merged, the references in the master page will fail due to the page loading in the context of the content page’s current location. There are several ways to fix this.

1. Reference as many resources as you can from a CSS style sheet. All references in the style sheet are based on the style sheet’s current location.

2. Create references with server controls rather than plain HTML. You can optionally use the tilde (~) character as the start of the path. If the resource is processed on the server side, the references are automatically resolved. See note below.

3. Design your directory structure where the relative paths are not an issue.

Note: If using .NET 3.5, you do not need the tilde character. In this version of the .NET framework, relative paths will resolve themselves as long as the resource is from a server control. To take a quick look at this, suppose we have the following directory structure:

In the MasterPages directory, we decide to store all of our master pages. Within those master pages, we use images that are stored in the subdirectory MasterPageImages. Now suppose we have two content pages, one stored in the MasterPages directory itself and one stored in the Main directory. The Master Page has a reference to an image that looks like this:

<asp:Image runat="server" ImageUrl="MasterPageImages/LoginBackground.jpg" />

Note that the control does not include the tilde. Also note that it is relative to the master page itself. If we invoke a content page that is in the same directory as the Master page itself, the resultant image tag will look like this:

<img src="MasterPageImages/LoginBackground.jpg" style="border-width:0px;" />

The location is relative to both the master page and the content page. Now, if you invoke the content page that is in the Main directory, this is what the image tag looks like:

<img src="MasterPages/MasterPageImages/LoginBackground.jpg" style="border-width:0px;" />

Note that even though the Image control is in the master, the path automatically adjusts relative to the content page. This is handled by the server. By not having to rely on the tilde character to identify the root, you can contain the master pages and their resources anywhere and even move them to another project without having to adjust paths.

Multiple Content Sections:
A master page is not limited to a single content section. You can have as many content sections as you wish. The content pages have the option of specifying a content section for each entry in the master page. However, if a content page specifies a content section, that section must exist in the master page or an error will occur.

One common use for this would be to place Meta tags on your page. Another use would be to include scripts on the master page that the content page might need. Remember, if you declare a contentPlaceHolder on the master, you can opt to fill it in from the content page or not. If you remember, the master page contains the actual <HEAD> tag. This section is where the Meta tags would be placed. You can have a section for normal content, and you can have an additional section for the Meta Tags. In your master page, declare the following <HEAD> section.

<head id="Head1" runat="server">
<title>Example Master</title>
<link href="~/application_styles.css" rel="stylesheet" type="text/css" />
<asp:ContentPlaceHolder ID="MasterMeta" runat="server">
<meta http-equiv="expires" content="0">
<asp:ContentPlaceHolder ID="MasterScript" runat="server">

Note the two content place holders. This could be accomplished with one but for clarity sake, I have made it into two. Now in the content page, include the following markup.

<asp:Content ID="MetaContent"
ContentPlaceHolderID="MasterMeta" runat="server">
<meta name="keywords" content="example, master, pages" />
<meta name="description" content="Something out of this world" />
<asp:Content ID="Script"
ContentPlaceHolderID="MasterScript" runat="server">
<script type="text/javascript" language="javascript">
function doSomething()
alert('We are Doing something');

Note that the <HEAD> tag in the master page has the runat="server" attribute defined. This is necessary because the merging of the master and content pages happens on the server.

While working with Master Pages, I found that the Page_Load firing sequence is not very intuitive. The content Page_Load event fires before the master’s Page_Load event fires. This is important if the master does initialization that the content page relies on. There are other ways around this by using other events and exposing specific initialization methods. Please see links below to reference the appropriate sequence of events.

Master Pages can be a very valuable tool for consolidating formatting and layout information for web applications written in ASP.Net. As a built in feature of .NET, creating content pages and associating them with Master Pages is very easy. Since it is a built in feature of .NET, Visual Studio integrates with the technology by presenting a seamless development environment for Master Pages. Consolidation, Code Reuse, and standardization of layout are just some of the benefits of using Master Pages technology.

Additional Links:
ASP.NET Master Pages Overview

@ MasterType Directive

Events in ASP.NET Master and Content Pages

Tuesday, September 23, 2008

Welcome to the first posting for Talking Frog Software. This post will be very short. The purpose is to get the blog up and running and tell you a little about what the blog is about. This blog will be a technical blog on matters concerning software and programming. The thoughts and articles presented here will be mostly of a technical nature but may also contain commentary. The theme is the world of programming and software development. If you have found this blog, stay tuned for more information.