Detecting changes in a table

Suppose I want to sync with my clients a bunch of information when the program has started and at specific intervals throughout the day. However, I don’t want the server to send over a huge list of all the information every single time it is requested, as it’s a waste of bandwidth and time. I should mention that a lot of clients I work with have spotty internet connections, so it’s important that the apps function well offline as well as transfer only the data that is needed to be transferred.)

How do I know that the client has the latest updated copy of data from the database? We do this by generating a checksum of the data that we would transfer. The client would send over its last known checksum value, or NULL if it does not have one stored, and the server only pushes data over if the checksum value passed back to the server is NULL or differs from the checksum the server generates. It’s ridiculously fast, at least for the data sets I send over – a few hundred to a few thousand records per set.

Here are some functions I created to get the data that needs to be synced:

CREATE FUNCTION FranchiseInfo_Franchises (@franchiseID INT) AS ...<br />CREATE FUNCTION FranchiseInfo_Items (@franchiseID INT) AS ...<br />CREATE FUNCTION FranchiseInfo_Staff (@franchiseID INT) AS ...<br />CREATE FUNCTION FranchiseInfo_Locations (@franchiseID INT) AS ...<br />CREATE FUNCTION FranchiseInfo_PrepaidCards (@franchiseID INT) AS ...

Et cetera.

Each function returns columns of data that I push to the client. You don’t specifically NEED to use table-valued functions, but in this scenario it definitely makes it easier to do the following steps. (Specifically, if I had used just stored procs, I would need to copy and paste the same SQL used for the procs in the checksum procedure, which reduces maintainability.)

What we want is some sort of a checksum that would change when the data in the table has changed.

CREATE PROCEDURE FranchiseInfo_GetChecksums
@franchiseID INT
SELECT 'Franchise' AS DataType, (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.FranchiseInfo_Franchises(@franchiseID)) AS [Checksum]
UNION SELECT 'Items', (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.FranchiseInfo_Items(@franchiseID))
UNION SELECT 'Locations', (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.FranchiseInfo_Locations(@franchiseID))<br /> UNION SELECT 'PrepaidCards', (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.FranchiseInfo_PrepaidCards(@franchiseID))<br /> UNION SELECT 'Staff', (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.FranchiseInfo_Staff(@franchiseID))<br /> ;

(Later I changed it so each one returned the checksum value in a column, so the procedure would return only one row with the checksum values. It was much easier to do that, rather than parse the row values. I’ll leave this as an exercise to the reader.)

BINARY_CHECKSUM() takes in a list of columns, in this case, the columns returned from the stored procedure. Yes, using * for columns is frowned upon but I don’t care, as it makes it easier to update the functions and know that it’s including that in the aggregation. CHECKSUM_AGG aggregates the other checksums into one checksum.

In my client code, I provide a list of the checksums I already have – as Nullable<long> / long? datatype, and if I have the checksum stored, I pass it in, otherwise, I leave the value as NULL. The server calls the function above and compares the checksums returned. If the checksum is different than what was passed in, then I call the appropriate procedure to return updated data to the client. This way, I’m only passing down the data set only if it has changed.


Updating an object with MVC

When editing an object (like an entity) in MVC, it returns a new one populated with your objects. If you try to save it using dataContext.Attach(), you may find that when saving it bombs on you. A great example of this would be something like a DateTime called .CreatedOn, since you wouldn’t let users edit it, would be the same value as new DateTime() which is year 0 or 1. When the object attempts to be persisted it won’t allow a date that low to be stored in a normal datetime field in SQL (datetime2, yes, but datetime should be sufficient in most cases) and bombs with a YSOD.

Maybe there’s a better way, but until I discover it or someone points it out to me, here’s what I did:

/// <summary>
/// Maps only the submitted changes in model state from one object to another.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="mapToObject"></param>
/// <param name="mapFromObject"></param>
public static void MapChangedProperties<T>(this ModelStateDictionary modelState, ref T mapToObject, T mapFromObject)
	var props = typeof(T).GetProperties();
	foreach (var item in modelState)
		var prop = props.FirstOrDefault(p => p.Name == item.Key);
		if (prop != null)
			prop.SetValue(mapToObject, prop.GetValue(mapFromObject, null), null);

To use, get the object out of the database and run this on it:

// "obj" is our returned model name, and we have a database connection open
if (ModelState.IsValid)
        dbObj = db.Objs.Single(o => o.ID = obj.ID);
        ModelState.MapChangedProperties(ref dbObj, obj);
        dbObj.ModifiedDT = DateTime.UtcNow;

Linq to SQL, Linq to EF, DateTime.Now and GETDATE() Woes

A client of ours requires that each one of their cruise ships have different timezones they exist in. When they create gaming records for their ships, the records created have to fall in their timezone in order for the game to close at the right time. They called us up today and mentioned that the games were closing an hour early for a particular set of ships.

That’s the backstory, what was found was that a Linq to EF query with DateTime.Now in it gets converted to GETDATE() in the expression tree, while L2S does not.

The example here EFDataStore.GetActiveGamesByDateTime(DateTime.Now) would be converted to SELECT * FROM Games WHERE CloseTime >= GETDATE(), and on the DB server, daylight savings time cannot be disabled, and on the app server, DST is turned off.

Changing the C# code to this solved the problem:

DateTime dt = DateTime.Now;


When EF walks the expression tree using reflection, it won’t see DateTime.Now in there and won’t convert it to GETDATE(), but it will use the actual date and time stored in the variable.

We never had problems with other ships because the servers that run these particular ships have a separate database server than the application server, where the other ships have application and DB servers on a single box.


Get enums by index, get index of an enum value in an enum

I like using enums for simple selections. I feel it makes my code clean. Over time you may feel that parts of your program have “outgrown” enums and you may need to switch to classes to handle extra logic. You can, but with attributes, you do not have to. If a parameter must be one of a few choices, even if it’s nullable, then use an enum.

I’m writing an iPhone app using Monotouch where I am displaying enum options to be picked from a list. The functions I am listing below help greatly in dealing with indices on enums.


public static TEnum GetEnumByIndex(int index)
var etype = typeof(TEnum);
if (!etype.IsEnum)
throw new ArgumentException(“‘TEnum’ must be of an enum type.”);
var enums = Enum.GetValues(typeof(TEnum));
if (index > enums.Length)
throw new ArgumentOutOfRangeException(“index”);
TEnum option = (TEnum)enums.GetValue(index);
return option;

public static int? GetIndexOfEnumValue(TEnum value)
var etype = typeof(TEnum);
if (!etype.IsEnum)
throw new ArgumentException(“‘TEnum’ must be of an enum type.”);
var enums = Enum.GetValues(typeof(TEnum));
int i = 0;
foreach (object e in enums)
if (((TEnum)e).Equals(value))
return i;
return null;

In other news, my daughter is doing really well with her chemo treatments. Hopefully she will be cancer free in a few years! See my wife’s page detailing my daughter’s battle with Acute Lymphoblastic Leukemia.


Silverlight, popup windows, and browser/out of browser differences

Opening popup windows in Silverlight is a pain in the rear. However, a way has been found to work around this. Subclass the HyperlinkButton and expose a new method that calls the protected Click() method.

internal class OOBPopup : HyperlinkButton
public void Navigate(Uri url)
this.NavigateUri = url;
this.TargetName = "_blank";

To use:

new OOBPopup().Navigate(url);

Let’s compare this with the two other options, HtmlPage.Navigate() and HtmlPage.PopupWindow(). Everything below is on Windows 7 64-bit. (IB = in browser, OOB = out of browser)

IB IE 9: All three methods work.
IB Safari 5.1.1: Only the OOB popup works.
IB Firefox 7: Only the OOB popup works.
IB Chrome: Only the OOB popup works.
OOB: Only the OOB popup works.

Where a method does not work, either you will get a message saying that the HTML/DOM scripting bridge is disabled, or the browser (especially Safari) will just eat the popup and act like nothing happened.

With the OOBPopup class setting enableHtmlPopups in the OBJECT tag hosting the silverlight app is not required, so it’s a great way to get popup windows to work. The app I am working on makes heavy use of SSRS so popup windows are necessary.


RIA Services and Timestamp/Concurrency Field Gotcha

I’m working with a table that has a timestamp column in EF. I’m using this to detect when multiple people are editing the same data so that one person’s changes don’t get overwritten by another user. In the EF designer, you would mark this property as StoreGeneratedPattern=Computed and ConcurrencyMode=Fixed, so that the code gen knows to generate a read-only field that gets roundtripped to the server on updates. If, during an update, the timestamp value that gets roundtripped to the server differs from the one in the database, then an OptimisticConcurrencyException is thrown so you have a chance to get the stored version of the data and possibly pop up a dialog that allows you to merge the changes together.

However, I have come across a gotcha when working with RIA services. Since the timestamp field is not NULLable, RIA services marks it as [Required] and [Editable(false)] when generating the code. When creating a new class to add to the data store, this ends up messing with validation, so in my datagrid it pops up with “Timestamp field is required”. You can’t set the field yourself, as it will throw an exception saying that the field is read-only.

A solution is found in the comments here for Linq-to-SQL, so here’s how to do it for EF.

I didn’t bother using the EF designer, I right-clicked the file in VS and clicked “Open With…”, then clicked XML editor. This brings up the XML schema that’s used to generate all of the code. Scroll down until you can find the class, mine was <EntityType Name="Class">. Find the property for your timestamp, mine was <PropertyType Name="Timestamp" ConcurrencyMode="Fixed" Nullable="false" ...>. Change that Nullable="false" to true, and save. The RIA services generated code will no longer require you to fill in the timestamp, and the database will handle creating the timestamp for you when the object is persisted to the database.

However, there is one more gotcha – it’s still marked as a required field. There may be a better way to accomplish this, but what I did was this: I have a field called CreationTimestamp, so I used the partial OnCreationTimestamp() method:

partial void OnCreationTimestampChanged()
if (Timestamp == null)
Timestamp = new byte[] { };

This fixes the issue, for now.

Anyone know of a better way?