Uncategorized

Wow. I learned something very important about SQL today.

Consider this SQL statement, containing a inline table-valued function (TVF)

SELECT *
FROM dbo.Games_StandardLotto_Summaries(
dbo.Date(DATEADD(D, -1, GETDATE())), 
dbo.EndOfDay(DATEADD(D, 1, GETDATE())), 
NULL, NULL, NULL);

It’s taking 17 seconds to run, and I cannot figure out WHY. I copied the guts of the TVF from a sql script I was tweaking for the good part of an hour. It’s a single line with CTEs that runs a fairly complex query in about a second or two – vast improvement from getting the same data from temp tables in 15-20 times the time. But now, it’s back to square one, it seems.

Unless…

DECLARE @sd DATETIME, @ed DATETIME;
SELECT @sd = dbo.Date(DATEADD(D, -1, GETDATE())), 
    @ed = dbo.EndOfDay(DATEADD(D, 1, GETDATE()));
SELECT * FROM dbo.Games_StandardLotto_Summaries(@sd, @ed, NULL, NULL, NULL);

…runs in under 1 second for me.

So what SQL is telling me here is that for TVFs, and I’m sure more than that, it uses the parameters by reference – meaning it executes the representation of the expression, rather than the value of the expression at the time the TVF is called.

The lesson learned here is that you should always declare and set your variables and use those to execute the TVF, rather than calling it with complex statements. As you can see above, I’m calling GETDATE(), then adding or subtracting a day from that value, and then running a scalar function to make it midnight or 11:59:59:999, twice per row minimum. Storing it into variables knocked the time down to next to nothing. Changing the SQL code above that calls the TVF resulted in a drop from 16.766 seconds to 0.275 seconds. That’s almost a 61x improvement!

Advertisement
Uncategorized

Shared Version Number Across All Projects In A Solution

Backstory: We have a terminal app that uses WCF services and an MVC web back-end. We want to show the version # of the terminal on the MVC page.

To do this, first create a SharedAssemblyInfo.cs in a solution folder, or in your main project file. It will contain no more than the following text:

// Solution version

using System.Reflection;
[assembly: AssemblyVersion(“0.1.1.6”)]
[assembly: AssemblyFileVersion(“0.1.1.6”)]

For each project that will be sharing this file,
(a) go to the /Properties/AssemblyInfo.cs file and comment out the version # stuff.
(b) Add an existing file to the project. Find the SharedAssemblyInfo.cs and click the dropdown next to “Add” and select “Add as link”.

Done!

If you are displaying a version number in your web project and System.Reflection.Assembly.GetExecutingAssembly() returns null, you need to use GetEntryAssembly().

If you find that the version # always returns 0.0.0.0 when using (System.Reflection.Assembly.GetexecutingAssembly() ?? System.Reflection.Assembly.GetEntryAssembly()).GetName().Version.ToString(), then get a type that is outside your Assembly (I have a Common project consumed by all assemblies) and get its version # from that, like this:

typeof(Some.Type.Outside.of.Web.Assembly).Assembly.GetName().Version.ToString()

Hope this helps!

Uncategorized

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;
        db.SaveChanges();
}
C#, EF Entity Framework, Reflection, UTC

Converting all date/time properties of an object graph to local time from UTC

Backstory: In a project I am working on, I am storing all times in the database as UTC. When I get the values from the database, I want to convert the times to local time to be displayed in my client. There’s many articles on this, that involve changing to local time on the server before shipping to client, which I didn’t care for (for example, hooking into the ObjectMaterialized event on the ObjectContext and using reflection). This had to be done on the client itself to take advantage of the client’s time zone.

I didn’t want to convert them all by hand, either. What if I added D/T properties later? If it’s nullable, then it’s an extra line of code to check that it’s not null first. What about an object graph (nested objects)? It sounded like a lot of work, until I cobbled together this code from a couple sources and reworked it a bit to do what I want:

	/// <summary>
	/// Since all dates in the DB are stored as UTC, this converts dates to the local time using the Windows time zone settings.
	/// </summary>
	public static class AllDateTimesAsUTC
	{

		/// <summary>
		/// Specifies that an object's dates are coming in as UTC.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="obj"></param>
		/// <returns></returns>
		public static T AllDatesAreUTC<T>(this T obj)
		{
			if (obj == null)
			{
				return default(T);
			}
			IterateDateTimeProperties(obj);
			return obj;
		}

		private static void IterateDateTimeProperties(object obj)
		{
			if (obj == null)
			{
				return;
			}
			var properties = obj.GetType().GetProperties();
			//Set all DaetTimeKinds to Utc
			foreach (var prop in properties)
			{
				var t = prop.PropertyType;
				if (t == typeof(DateTime) || t == typeof(DateTime?))
				{
					SpecifyUtcKind(prop, obj);
				}
				else if (t.IsEnumerable())
				{
					var vals = prop.GetValue(obj, null);
					if (vals != null)
					{
						foreach (object o in (IEnumerable)vals)
						{
							IterateDateTimeProperties(o);
						}
					}
				}
				else
				{
					var val = prop.GetValue(obj, null);
					if (val != null)
					{
						IterateDateTimeProperties(val);
					}
				}
			}
			//properties.ForEach(property => SpecifyUtcKind(property, obj));
			return; // obj;
		}

		private static void SpecifyUtcKind(PropertyInfo property, object value)
		{
			//Get the datetime value
			var datetime = property.GetValue(value, null);
			DateTime output;

			//set DateTimeKind to Utc
			if (property.PropertyType == typeof(DateTime))
			{
				output = DateTime.SpecifyKind((DateTime)datetime, DateTimeKind.Utc);
			}
			else if (property.PropertyType == typeof(DateTime?))
			{
				var nullable = (DateTime?)datetime;
				if (!nullable.HasValue) return;
				output = (DateTime)DateTime.SpecifyKind(nullable.Value, DateTimeKind.Utc);
			}
			else
			{
				return;
			}

			Debug.WriteLine("     ***** Converted date from {0} to {1}.", datetime, output);
			datetime = output.ToLocalTime();

			//And set the Utc DateTime value
			property.SetValue(value, datetime, null);
		}
		internal static Type[] ConvertibleTypes = {typeof(bool), typeof(byte), typeof(char),
   typeof(DateTime), typeof(decimal), typeof(double), typeof(float), typeof(int), 
   typeof(long), typeof(sbyte), typeof(short), typeof(string), typeof(uint), 
   typeof(ulong), typeof(ushort)};

		/// <summary>
		/// Returns true if this Type matches any of a set of Types.
		/// </summary>
		/// <param name="types">The Types to compare this Type to.</param>
		public static bool In(this Type type, params Type[] types)
		{
			foreach (Type t in types) if (t.IsAssignableFrom(type)) return true; return false;
		}

		/// <summary>
		/// Returns true if this Type is one of the types accepted by Convert.ToString() 
		/// (other than object).
		/// </summary>
		public static bool IsConvertible(this Type t) { return t.In(ConvertibleTypes); }

		/// <summary>
		/// Gets whether this type is enumerable.
		/// </summary>
		public static bool IsEnumerable(this Type t)
		{
			return typeof(IEnumerable).IsAssignableFrom(t);
		}

		/// <summary>
		/// Returns true if this property's getter is public, has no arguments, and has no 
		/// generic type parameters.
		/// </summary>
		public static bool SimpleGetter(this PropertyInfo info)
		{
			MethodInfo method = info.GetGetMethod(false);
			return method != null && method.GetParameters().Length == 0 &&
				 method.GetGenericArguments().Length == 0;
		}

	}

How do you use it? To change aall dates on an object sent to the client via WCF, this is how it’s done:

		void svc_ZOut_GetZOutSummaryCompleted(object sender, ZOut_GetZOutSummaryCompletedEventArgs e)
		{
			svc.ZOut_GetZOutSummaryCompleted -= new EventHandler<ZOut_GetZOutSummaryCompletedEventArgs>(svc_ZOut_GetZOutSummaryCompleted);
			svc = null;
			var whenDone = (Action<bool, ZOutResult>)e.UserState;
			if (e.Error != null)
			{
				FireOnExceptionRaised(e.Error);
				whenDone(false, null);
			}
			else
			{
				var res = e.Result.AllDatesAreUTC();
				FireOnSessionReceived(res.IsError, res.Session);
				if (res.IsError == true)
				{
					whenDone(false, null);
				}
				else
				{
					whenDone(true, res.Result);
				}
			}
		}

That’s it! It handles a whole object graph, DateTime and nullable DateTime obejcts.

Uncategorized

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;

EFDataStore.GetActiveGamesByDateTime(dt)

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.

Uncategorized

Conditionally show the expand/collapse button (+/-) dependent on an expression

I’m working on a report where some of the rows have drilldowns and some do not. The boss wanted only the rows with drilldowns to have the expander next to them. I didn’t think it was possible but he was able to guide me in the right direction, so here is how it is done.

Normally, you would have your group or details’ visibility toggled by another report item (a textbox in almost all cases). The textbox is whatever item you want the expander to appear next to. There is no built-in expression to toggle the visibility of the expander itself, so to conditionally hide or show the expander:

  1. Add an empty column to the left of where the expander would normally appear. Size it down so it’s not too big (too small and the expander won’t appear at all). This column is not bound to any data so it will be blank all the time.
  2. Right click your detail group you want hidden or shown and click Properties. Click the Visibility tab. Change the textbox so it’s the new blank column instead of the one to the right of it. Click OK to close the dialog.
  3. Run the report so you can be sure the expander appears. If the field is sized too thin, the expander won’t appear, so be sure you do this before the next step.
  4. Right click the empty cell where the expander is shown and click Properties. Click the Visibility tab. Under Initial Visibility, select Expression and enter the expression to use to hide or show that expander (+/-) control.

Now, the expander control itself is controlled by that expression you entered in step 4. Super easy to do and nice outside-the-box thinking to make it work. Thanks boss!

Uncategorized

ClickOnce deployments to a networked drive

Learned something new. I could not deploy a WPF application using ClickOnce using a mapped network drive. It kept coming back at me saying that “The path does not exist.”, even though I copied the path from Windows Explorer directly into the ClickOnce deployment dialog.

The problem was that I had VS2010 starting using administrator credentials – and there was no way to allow access to that shared drive that initially requires a username and password to access. Starting VS not using admin credentials fixed the problem.

Hope this helps someone else out there, as I was stumped for a bit on this one.

Uncategorized

Monotouch Deserialization FormatException “Input string was not in the correct format” – Solved

Ran into an interesting issue with some POCO entities being passed from my VS2010 WCF service to my Monotouch client. Here’s an example of how you cannot always trust the error message that is given to you.

I’m passing down an object graph, with a container object that contains an array of events and an array of People objects, each containing a Dictionary of event IDs and times. (Why nullable int as the key? Monotouch has issues with value types as dictionary keys, see Xamarin’s list of limitations.) The event IDs contained in there map to the events sent in the container object. Some of the people passed down may not have attended the event at all, meaning that aforementioned dictionary was empty. When I was getting the “Input string was not in the correct format” error, I thought that Monotouch WCF was bombing on a nullable int on the person being null, which didn’t make sense since I was pulling down the same person object in a different call that had nullable ints and everything worked peachy. Come to find out, it was the empty Dictionary causing the problem. In my service code, before passing the object back to the client, I loop through and null out any empty dictionaries in my object graph.

Hope this helps someone out there.

Uncategorized

Monotouch.Dialog – Nullable Date and Nullable Date/Time Elements

http://stackoverflow.com/questions/10199084/monotouch-dialog-dateelement-that-accepts-a-null-value-as-input/10212471#10212471 .

I rolled my own nullable date/time and date-only pickers for Monotouch.Dialog out of necessity.

I’m curious to see if someone can make the picker pop up like an actionsheet on the current view rather than entering into another view controller… it would look more professional and be in line with the rest of Apple’s UI. Anyone care to take a stab at doing this?

You can see my source code at the StackOverflow link.