McBean Information Technology

SQL: Case, Switch and Data Models

2010-06-24T13:25:00.000-07:00

I was trying to remember how to do a case statement in an MS-Access Query. Having worked mostly in SQL Server the last while I forgot that MS-Access uses the switch keyword instead of case. The syntax is very similar.

A case or switch statement in SQL allows you to translate a specific value of a field into another value. It is often used to translate something meaningless like an enum or numeric identifier into something readable. It is often used in views or reports.

In the following example the MSAccess switch statement is used to translate an enumeration field into readable text:

 SELECT switch([T_Member.FamilyTypeId]=1,"parent",[T_Member.FamilyTypeId]=2,"dependent") AS SomeFieldName FROM SomeTable

SQL Server Books Online (2005) shows a simple example of the case statement:

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Now that I've shown you how to do these statements properly, I'm going to argue why you should never use them.

Almost always a case statement means the data model is incomplete. A case statement basically inserts ad hoc lookup tables randomly though-out your SQL statements. As good designers will say, hard-coded text values or numeric literals in any type of code are a maintenance nightmare. How hard is it to extend the data model to provide text definitions of the things you are translating with a case statement?

I feel the same way about the use of case statements in programming languages. The logic is static and cannot be changed except through rebuilding and redeploying. Adding an additional case label to a statement is out of reach for the system administrator, whether SQL or code.

Case or switch fields are not editable by the user. In some cases views that use them can make the entire row not editable. But almost all GUIs support editing of foreign keys through drop downs boxes.

I would argue that case and switch statements should never be used in SQL statements, and focus instead on complete data models, with proper relationships.

Testing C# Class Properties With NUnit - Part 2

2010-01-12T17:37:00.000-08:00

In a previous post I showed how to auto-test object properties in a NUnit test. I forgot to include a function called GetProperties() which was referenced in the code example. Here it is:

       /// <summary>
        /// An test that iterates through an object's properties and does
        /// a very minimal test on get/set integrity.
        /// </summary>
        /// <param name="patient"></param>
        /// <returns></returns>
        public ArrayList GetProperties(Patient patient)
        {
            ArrayList Properties = new ArrayList();
            PropertyInfo[] propertyInfo = patient.GetType().GetProperties();

            foreach(PropertyInfo item in propertyInfo)
            {
                Properties.Add(item.Name);
            }

            return Properties;
        }

Of course this is pretty simple minded and should be re-factored in any number of ways. Most importantly to get rid of the specific object reference and make it generic. Cut and paste programming makes us angry, very very angry.

The Weird Syntax of SQL DELETE From a JOIN

2009-05-22T01:02:00.000-07:00

It is perfectly legal in most SQL dialects to DELETE records that come from a JOIN clause. However, I never remember the syntax because its kind of strange and I don't do it often.

I wrote the following example in SQL Server 2005:

delete from arledger 
from arledger
inner join arinvoice on arinvoice.arinvoiceid = arledger.invoicenumber
where arinvoice.brokersaleid = 2259

Nothing complicated. Just note line 2 where the FROM clause is repeated.

Current Reading

2009-03-05T23:11:00.000-08:00

I'm currently reading Pro WPF in C# 2008 by Matthew MacDonald. So far it seems well written and I'm getting a good feel for WPF. I'm anxious to do my first WPF project but didn't want to just jump in without some background.

However, ultimately you can't learn a technology without jumping in and making a whole bunch of stupid mistakes.

Article on Setting Properties Dynamically

2009-03-04T22:51:00.000-08:00

For the previous post on NUnit and testing properties using reflection I was indebted to this article. The code examples in this article are in VB.Net.

Testing C# Class Properties With NUnit

2009-03-04T21:57:00.000-08:00

In their book Pragmatic Programing Andrew Hunt and David Thomas argue that all programmers should adhere to the DRY principle. DRY stands for do not repeat yourself. Its a daily battle to keep my code library from descending into the usual cut and paste mess of massively duplicated code.

I had a few C# classes that had an high number of properties and I wanted to make sure each property had a unit test to make sure they were assigning correctly. I envisioned this as being massively tedious. I thought that a better approach might be to iterate through the properties using reflection and test them by setting and retrieving test values. This would also mean I wouldn't have to change the unit test if I added, deleted or renamed properties.

The code I came up with is very sketchy and preliminary but it does work. It can be refactored any number of ways to suit invididual tastes. In this unit test the class Patient is being tested.

I'm only testing properties of type String and Integer in this example. You can add any type you want. I am thinking about a way to test these properties that is type independent.

        [Test]
        public void Properties()
        {
            Patient patient = new Patient();
            int intTestData = 0;

            foreach (string propertyName in GetProperties(new Patient()))
            {
                Type patientType = patient.GetType();
                PropertyInfo property = patientType.GetProperty(propertyName);

                
                string stringTestData;

                if (property.PropertyType == typeof(System.String))
                {
                    intTestData++;
                    stringTestData = String.Format("Testdata{0}", intTestData);
                    
                    Console.WriteLine(String.Format("Testing property {0} of type {1} with value {2}", propertyName, property.PropertyType.ToString(),stringTestData));
                    
                    if (property.CanWrite == true && property.CanRead == true)
                    {
                        property.SetValue(patient, stringTestData, null);
                        Assert.AreEqual(stringTestData,property.GetValue(patient,null));
                    }
                }

                if (property.PropertyType == typeof(System.Int32))
                {
                    property.SetValue(patient, intTestData, null);
                    Assert.AreEqual(intTestData, property.GetValue(patient, null));
                }
            }
        }

Delphi: Iterating through components

2009-01-01T18:46:00.000-08:00

The code example below shows how you can iterate through components in a Delphi form or data module, check the type of a hosted component and examine its properties. I wrote this as a unit test because the programmers were forgetting to remove their local connection strings from data objects. In this test we checked that the object has a reference to a DataConnection object and not a data connection string.

This is a fairly trivial example. It is the preferred solution if you want to iterate over a set of specific objects and set properties in a consistent way without having to write repetitive code referencing specific objects.

var
  Temp: TComponent;
  
begin

  for i := ComponentCount - 1 downto 0 do
  begin
    Temp := Components[i];
    if (Temp is TADOQuery) then
    begin

      if Length(TADOQuery(Temp).ConnectionString)>0 then
        raise Exception.Create('UnitTest(1): The use of a data connection string is not supported in this model.' + Temp.Name);

      if TADOQuery(Temp).Connection = nil then
        raise Exception.Create('UnitTest(2): A default connection object should be specified. ' + Temp.Name);

    end;
  end;

Some notes:

The code TADOQuery(Temp).Connection is shorthand for casting the TComponent object to an TADOQuery object and accessing its property Connection at the same time. A temporary unnamed TADOQuery object is created on the stack and disappears after the code is executed.

ComponentCount is a property that every object has that is derived from TComponent.

SQL Update with JOIN

2008-12-30T16:18:00.000-08:00

I was trying to remember how to do an SQL UPDATE statement using fields from a JOIN. Its one of those things I don't do very often, and the syntax is not exactly intuitive. Its a good example of the power of SQL.

The general template for such a statement is:

UPDATE [target table]
SET clause
FROM [target table]
JOIN clause
WHERE clause

In my case I had two tables. The listing table contained items for sale and the pertinent fields were Price and Commission, both money fields. The CommissionRate table has the same fields. The goal was to update the Listing.Commission field from the CommissionRate table, by joining on the Price fields.

The UPDATE statement ended up as:

UPDATE Listing
SET Listing.Commission = CommissionRate.Commission
FROM Listing
INNER JOIN CommissionRate ON Listing.Price = CommissionRate.SaleAmount
WHERE Listing.Commission <> CommissionRate.Commission

Refactoring:

The Commission.SaleAmount field needs to be renamed to Commission.Price to be consistent within the model

The WHERE clause should include a condition to NOT apply the update to certain records that should not be bulk updated.