Insert Vs Update in a DB

I have come across this problem a lot with my database coding. We have some data to insert into the database. Depending on whether the data is new or existing will determine whether we do an insert or an update statement on the database.

I find that this solves the problem very nicely,

if exists(select * from Table where key = 'rowId1')

begin

update Table set datavalue = 'newValue', datavalue1 = 'newValue' where key = 'rowId1'

end

else

begin

insert into Table values ('newValue','newValue', etc, etc)

end

Simple and sweet.

ntext Database Type

The ntext database type is a unicode variable length data type in SQL server. SQL server profiler creates these fields if you save a profile to a trace table.

If we try to filter one of these trace tables like this,

select * from TraceTable where TextData like '%value%'

That is fine, but if we try to do this,

select * from TraceTable where TextData = 'value'

We get an error like this,

The data types ntext and varchar are incompatible in the equal to operator.

The solution is this,

select * from TraceTable where cast(textdata as varchar(100)) = 'value'

Dealing with UAC

What happens if your application wants to update itself, or change files which UAC will not allow you to update without elevation. The solution is to not elevate your whole application. This would defeat the purpose of UAC in the first place.

One solution is to create a new project in VS, which might be a console app that you run invisibly for instance. The console app will do some file copying or whatever else needs UAC elevation and that is all. Right click the new UAC project you have created and add an 'application manifest file'.

Make sure this is in the manifest file,

<trustInfo xmlns="urn:schemas-microsoft-com:asm.v2">
  <security>
    <requestedPrivileges xmlns="urn:schemas-microsoft-com:asm.v3">
      <requestedExecutionLevel level="requireAdministrator" uiAccess="false" />
    </requestedPrivileges>
  </security>
</trustInfo>

And you will be away. So back to your unelevated program, calling this console app from your unelevated code can be done like this. What this will do when you call process.Start() is prompt for UAC elevation,

Process process = new Process();
process.StartInfo.CreateNoWindow = true;
process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
process.Exited += new EventHandler(process_Exited);

I have left most of it out, but the important thing there is that I have said it should have no window, and the style should be hidden. I have also defined an exit event so that I can pick up when the UAC app has finished.

WPF and MVVM

Just a note really, here is MVVM explained,

http://www.orbifold.net/default/?p=550

I have implemented something very similar to MVVM, kind of a half MVVM. What mine was missing was commands. The VM should have commands so that these commands can be used from XAML. This will reduce your code behind.

The Acid Test Part #2

Over a year ago I tested my browsers with the acid tests. I wonder whether there has been any improvement? Since then we have the addition of the chrome browser as well.

http://acid3.acidtests.org/

100/100 - Chrome (I got a message saying the link test failed)

72/100 - Firefox 3.5

20/100 - IE8 (I also got the message saying that the link test failed)

So does that mean IE8 is still not up to scratch? Or is my IE8 running in some funny mode that has stuffed up the results.

IE8 gets acid 2 100% correct,

http://acid2.acidtests.org/

On mine the nose was out by probably only 1 pixel on chrome and firefox 3.5 - extremely close

Bulk Inserting Into Your Database

So you have a list of domain objects you want to bang into the database really quickly. You don't want to change anything about the items, and the properties on the domain object map one to one to database field names.

There is a way to do it really fast using the SqlBulkCopy class - especially if you are inserting hundreds or thousands of rows at one time. Here is some code that does it for you,

public class BulkInserter
{
  private object lockObject = new object();

  public void BulkInsertRows<T>(List<T> insertionList, string connectionString,
    string databaseTableName)
  {
    lock (lockObject)
    {
      DataTable dt = ConvertTo<T>(insertionList);

      using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, 
          SqlBulkCopyOptions.Default))
      {
        bulkCopy.BulkCopyTimeout = 60;
        bulkCopy.DestinationTableName = databaseTableName;
        bulkCopy.WriteToServer(dt);
      }
    }
  }

  private DataTable ConvertTo<T>(IList<T> list)
  {
    DataTable table = CreateTable<T>();
    Type entityType = typeof(T);
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

    foreach (T item in list)
    {
      DataRow row = table.NewRow();

      foreach (PropertyDescriptor prop in properties)
      {
        row[prop.Name] = prop.GetValue(item);
      }

      table.Rows.Add(row);
    }

    return table;
  }

  private DataTable CreateTable<T>()
  {
    Type entityType = typeof(T);
    DataTable table = new DataTable(entityType.Name);
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

    foreach (PropertyDescriptor prop in properties)
    {
      table.Columns.Add(prop.Name, prop.PropertyType);
    }

    return table;
  }
}

So what is the code doing? You pass in a list of domain objects, and the CreateTable and ConvertTo methods convert this list into a DataTable. This DataTable is what the SqlBulkCopy class uses to do the bulk insert.

The first method has a parameter databaseTableName. In hind site there is probably a better way to do that, perhaps typeof(T).Name or something similar.

Unit Tests That Involve Dates

How do you unit test some code that uses DateTime.Now? What if you want to move the day one week into the future and test that your logic works as expected.

There are a couple of options. Instead of using DateTime.Now straight in your code you can pass in a parameter to your method which is the date time that you want to use in your logic. This would mean when you call this method from a unit test you could pass in any date you want. The normal code will still just use DateTime.Now though. This is a bit messy but in some instances this is not too bad.

The other option is to create a static class like this,

public static class SystemTime
{
    public static Func Now = () => DateTime.Now;
}

This means you can replace the DateTime.Now calls in your code to SystemTime.Now(). This will default to being the equivalent of DateTime.Now,

SystemTime.Now()

In your unit test you can move the time on by five minutes like this,

SystemTime.Now = () => DateTime.Now.AddMinutes(5);

And then continue to unit test the code as though we are five minutes in the future.