DOS command-line tricks

Remember DOS? It's still with us, in the form of the Windows command prompt, and it still has some useful tricks up its sleeve. I recently needed to automate the daily copying of some database backup files from one server to another. I combined several DOS, Windows and Windows task schedule techniques to make this work.

The goal: At 7:30 AM each day, copy all files named ynot_backup_*.bak from the folder G:\sqlbackup on the database server to the folder G:\ynot_backup on one of the web servers. Both servers are running Windows Server 2003.

First, I came up with an xcopy command to copy the appropriate files: xcopy /Y g:\SQLBackup\ynot_backup_*.bak \\10.2.66.30\g$\ynot_backup.A few points of interest:

• This command uses a UNC path (a path starting with two backslashes) to access the web server across the LAN. Specifically, \\10.2.66.30\g$ refers to the default share of the G: drive on the web server, and I've configured the permissions, both in the share and in the file system -- to give write access to authenticated users. (This is safe, because the share is accessible only to authenticated users using a non-routable private IP address on a local area network.)
• The /Y flag tells xcopy to overwrite any existing files without prompting the user for confirmation.

The above xcopy command works fine when executed in a DOS window on the database server. But to be able to schedule it, two changes are needed.

• First, we need to give the full path to xcopy.
• Second, we need to execute the command shell, cmd.exe, passing it the /c flag and the command to execute.

It ends up looking like this: C:\WINDOWS\system32\cmd.exe /c "xcopy /Y g:\SQLBackup\ynot_backup_*.bak \\10.2.66.30\g$\ynot_backup".

The final step is to go to Control Panel | Scheduled Tasks and create a new scheduled task to execute the above command at 7:30 AM each day. The only trick here is choosing an appropriate account under which to run the task. I chose an administrator account that has sufficient permissions and whose password doesn't change too frequently -- because each time the password changes, we must inform the scheduled task of the new password.

Now all I need to do is keep an eye on the web server and make sure the whole G: drive doesn't fill up with old database backups!

How to simulate any browser by editing the user agent string in Safari

I recently modified one of my websites to display different content depending on which device is used to access it. For conventional web browsers, a traditional homepage is displayed, while for mobile devices the user can choose between downloading a mobile app or proceeding to the website. The question was how to test this without borrowing several different types of mobile devices and installing several web browsers. Safari provides a convenient way. Here's the procedure, using Safari 5.1.1 on Windows...

If the "Develop" menu isn't already enabled, click the "Tools" icon at the top right and select "Preferences..." from the resulting menu. On the "Advanced" tab, ensure that "Show Develop menu in menu bar" is selected.

Now use the "Develop" menu -- either by selecting the icon just to the left of the tools icon, or by pressing the Alt key to display the menu bar across the top of the window. On the "Develop" menu, click "User Agent".

You'll be presented with a list of several user agent strings to choose from, simulating popular browsers. Better still, you can click "Other..." and type in any user agent string you wish.

Safari uses the specified user agent string for the current page. The setting appears to persist for any page viewed in the current tab, but doesn't apply in new tabs, new windows, or after your close the browser.

Thanks to my colleague Nidhi Bhargava for researching this tip.

Notes from a WordPress newbie

So my day job involves a lot of .NET and SQL, and I've worked with dozens of programming languages and software packages during my career, but I've never done anything with WordPress. Thanks to a volunteer project for the Youth Orchestra of Essex County, that's about to change. I've been asked to maintain some content on their website, which uses WordPress.

One problem I noticed was with the menu at the top of the site. It worked fine in Chrome, but in Internet Explorer 9, when I hovered the mouse over a menu tab, and the submenu was displayed, a small vertical gap appeared between menu and submenu. When passing the mouse over this gap to try to select a submenu item, the submenu disappeared. Very annoying!

I posted this problem to the WordPress forum, and got a response within hours. (Thanks, vtxyzzy!) The response said:
Try adding this to the end of style.css:
ul.children { top: 24px !important }

So I had to figure out how do to that, having never used WordPress before. Here's what I did...

• Log in to the admin page for yoec.org, yoec.org/wp-admin.
• There's a menu of links on the left. One of them is Appearance. I clicked that. and it expanded.
• This displayed a menu of links related to Themes. One link was Editor. I clicked that.
• This displayed a text editor pane in the middle of the screen, and a list of files on the right.
• I scrolled through the list of files, found a category labeled Styles, and under that, a file labeled Stylesheet (style.css).
• I clicked that filename, and the file was displayed in the text editor pane.
• I scrolled through the (rather long) text of style.css until I reached the bottom.
• I appended the recommended line, ul.children { top: 24px !important }.
• I saved my changes by clicking the Update File button at the bottom of the page.
• I viewed the website in IE9, and the problem was gone!

I will probably have more to say about WordPress, from a total noob perspective, in a future post.

TFS command-line interface - unlocking files locked by other developers

One of the developers who works for me went to work on a project in Visual Studio, which is under source control in Team Foundation Server. He discovered that some files were locked for editing by another user, so he couldn't complete his task. The other user was an intern who no longer worked for us, and couldn't be reached to correct the problem. I used TFS's command-line utility, tf.exe, to unlock the files.

First I determined which files were locked, and by whom. I did this using Visual Studio 2010 on the laptop I use for software development. (My installation of Visual Studio includes Visual Studio 2010 Team Explorer.) From the Team tab, I opened the Source Control window. Looking in the Pending Change column and the User column, I could see which files were locked for edit, and by which users.

Then I used Remote Desktop to connect to the server on which TFS is hosted.

I opened a Visual Studio command prompt. On my server, this is accomplished by selecting Start | All Programs | Microsoft Visual Studio 2010 | Visual Studio Tools | Visual Studio Command Prompt (2010).

At the command prompt, I used a command similar to the one below to unlock each file.

tf undo "$/LocalUp/LocalupMenus/CMSBackEnd/bin/CMS.BackEnd.ClassLibrary.dll" /WORKSPACE:INTERN2-PC;mbakiev /server:http://10.2.66.30:8080/tfs/defaultcollection

There are three pieces of information you need to construct such a command.

First, in red, is the name -- as TFS understands it -- of the file to unlock. You can get this by right-clicking the file in Source Control Explorer and selecting Properties. On the General tab, look for the Server Name.

Second, in blue, is the name of the workspace of the user who has locked the file. Get this by right-clicking the file and selecting the Status tab. There you can see the name of the user's workspaceFor example, one of my developers' workspaces is named INTERN2-PC;mbakiev.

Third, in green, is the name -- as TFS understands it -- of the server. To determine this, I clicked the top node of the source tree in the Team Explorer window. Note this has to be in the small Team Explorer pane, not the big Source Control Explorer pane. Then the Properties window will display various information, including the "Url". That's the value -- http://10.2.66.30:8080/tfs/defaultcollection in my case -- you need to supply as the server name.

Not simple, but it worked!

A tip of the hat to this helpful article.

Working with a dynamic RadioButtonList

ASP.NET provides a RadioButtonList control, which has the useful ability to bind text and values (perhaps, for example, retrieved from a database) to a set of radio buttons at runtime. You might find the need to examine and manipulate attributes of the radio buttons in the list, even though you don't know in advance what those radio buttons may be. Here's an example of how to do that.

In my example, there's a list of radio buttons. The list may (or may not) include a radio button labeled "Countdown". If the list includes a "Countdown" radio button, then I want to disable it. Furthermore, if "Countdown" was selected, I want to instead select the first radio button in the list. Here's the code:

foreach (ListItem listItem in rblSelectionType.Items)

{

if (listItem.Text == "Countdown")

{

if (listItem.Selected)

rblSelectionType.SelectedIndex = 0;

listItem.Enabled = false;

break;

}

}

Thanks to this tutorial for setting me on the right track.

How a small typo caused an infinite loop and wasted half a day

I recently made a few rather minor changes to my .NET website. One of these changes introduced a bug, a "request has timed out" error that could be reliably replicated. Finding the cause took a whole afternoon.

One of the changes consisted of adding a column to one table in a SQL Server database and a few lines of C# code to read and display the value of that column. Here's the class where I added the new field, displayName:

public class MenuSize
{
private int menuSizeID;
private string displayName;
private int displayOrder;
private string name;
private int sizeID;
private bool showName;
private int menuCategoryID;

public string DisplayName { get { return displayName; } set { displayName = value; } }
public int MenuSizeID { get { return menuSizeID; } set { menuSizeID = value; } }
public int DisplayOrder { get { return displayOrder; } set { displayOrder = value; } }
public int SizeID { get { return sizeID; } set { sizeID = value; } }
public string Name { get { return name; } set { name = value; } }
public bool ShowName { get { return showName; } set { showName = value; } }
public int MenuCategoryID { get { return menuCategoryID; } set { menuCategoryID = value; } }
}

Note the line private string displayName;. I typed it wrong, putting diplayName instead of displayName.

Then when I added the line public string DisplayName { get { return displayName; } set { displayName = value; } }, I relied on Visual Studio's IntelliSense to fill in the variable name displayName. Because of my typo, displayName with a lowercase "d" didn't exist, and IntelliSense put in DisplayName with a capital "D". As a result (which I didn't notice), the line read public string DisplayName { get { return DisplayName; } set { DisplayName = value; } }.

Since this property sets DisplayName to itself, an infinite loop results! Just to make it really hard to find this bug, the method that instantiates MenuSize is inside a WSDL web service, and my web site calls the web service. So all I could tell was the call to the web service method was timing out. It took me about 6 hours of troubleshooting to figure out why. All because of a stupid missing letter "s" in displayName!

Good old-fashioned SQL optimzation using an index

Sometimes simple, basic stuff really works.

This query was taking too long to execute:
SELECT ISNULL(SUM(Quantity),0) as 'count'
FROM dbo.AllOrderItems WITH (NOLOCK)
WHERE CustomerID=@customerID AND ItemID=@itemID

AllOrderItems has nearly 5 million rows. I executed this query with a customerID and itemID that corresponding to 3 records. It took 23 seconds to run.

I created an index on the CustomerID and ItemID columns like this:
CREATE NONCLUSTERED INDEX [Customer_Item] ON [dbo].[ArcOrderItems]
(
[CustomerID] ASC,
[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

With this index, the execution time was reduced from 23 seconds to less than one second.

Moral of the story: sometimes the obvious fix is the right one when it comes to database optimization.

Bonus tip: When testing the speed of a query, it will often run faster the second and subsequent times, because SQL caches the results. These two commands clear the cache, ensuring a realistic measurement of the execution time:
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

Interesting book about engineering

When I attended June's Google NYC Tech Talk on performance bugs, speaker Jon Bentley recommended the book To Engineer Is Human by Henry Petroski. I enjoyed Jon's presentation so much, I read the book.

This book is not about software. It's primarily about civil and aeronautical engineering. But the lessons it imparts about how to learn from failure and create more reliable products apply nicely to software engineering.

The writing style was a bit formal, and some of the examples -- this book is from the 1980s -- are dated. But overall I enjoyed it, and it made me think about how to be a better software developer.

Using SQL to concatenate values from multiple rows into a single string

Lots of SQL programmers probably know this trick already, but it was a new one for me and seems worth sharing. In just a few lines of code, without using a cursor, you can concatenate values from multiple rows into a single string. For example, if you have a table with a FirstName column, and a SELECT statement returns the FirstName values 'Aaron', 'Betty' and 'Carol', it's easy to form a string like this: 'Aaron, Betty, Carol'. The comments in the code snippet below explain how.

-- create a test table and insert some rows of test data
CREATE TABLE Test (
FirstName VARCHAR(10),
LastName VARCHAR(10)
)
INSERT INTO Test VALUES ('Aaron', 'Aardvark')
INSERT INTO Test VALUES ('Betty', 'Baboon')
INSERT INTO Test VALUES ('Carol', 'Condor')

-- form a comma-delimited list of the FirstName values from all records
DECLARE @myList VARCHAR(100) -- this works with a VARCHAR or NVARCHAR variable, but _not_ with a CHAR variable
SET @myList = '' -- initialize the variable; if you don't, the output string will be blank

-- The next SELECT statement is the key. It appends the FirstName from each record to whatever is in the string so far.
-- The CASE statement is just for putting commas between the names, but not in front of the first one.
SELECT @myList = @myList + CASE @myList WHEN '' THEN '' ELSE ', ' END + FirstName FROM Test
SELECT @myList AS MyList -- output the string, which should say 'Aaron, Betty, Carol'

-- delete the test table
DROP TABLE Test

Fun book about AI - "Final Jeopardy"

An interesting computer science read: "Final Jeopardy" by Stephen Baker. It's an account of IBM's massive project to build Watson, the computer system that defeated two human champions on the game show "Jeopardy." My key takeaway is that there are two approaches to artificial intelligence, and they're both hard.

You can simulate human-like reasoning with something like a neural network -- hard because it requires vast processing power

Or you can "teach" a computer system myriad rules and bits of information -- hard because it requires lots of people to spend lots of time.

Console app to send a SOAP request

For troubleshooting purposes, it can be useful to invoke a web service method by sending an XML-formatted SOAP request and receiving the response. Here's a C# console application to do that. Just replace the URL and the XML request string with your own values.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.IO;

namespace post
{
class Program
{
static void Main(string[] args)
{
// Replace with the URL of your web service.
string strUrl = "http://oosapi/OosApiService.asmx";

// Replace with the XML-formatted SOAP request for the web service method you wish to call.
// Don't forget to escape any double quotes.
string strRequest = @"b5a3e821-6f7d-4ad0-b5d0-b723222bc319";

// Create the Request object.
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(strUrl);
req.Method = "POST";
req.ContentType = "text/xml";
req.ContentLength = strRequest.Length;

// Send the request.
StreamWriter swRequest = new StreamWriter(req.GetRequestStream(), System.Text.Encoding.ASCII);
swRequest.Write(strRequest);
swRequest.Close();

// Receive the response.
StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
string strResponse = srResponse.ReadToEnd();
srResponse.Close();

// Output the response.
Console.WriteLine(strResponse);
Console.ReadKey();
}
}
}

.NET cache stores only a reference -- and what to do about it

.NET provides the System.Web.Caching.Cache object as a way to persistently store data. This is useful, for example, if you retrieve some data from a database and want to refer to it repeatedly, perhaps from several different pages, without the overhead of accessing the database again each time.

When I attempted to store an instance of a class I had created with several members -- I discovered a quirk of the Cache object: adding such an object to the cache seems to store a reference to the object, not a copy of the object. An example will make this clear.

// define a class with some members
class Animal
{
public string species;
public string name;

public Animal(string s, string n)
{
species = s;
name = n;
}
}

// create a dog named Spot
Animal animal = new Animal("dog", "Spot");

// store Spot in the cache
HttpContext.Current.Cache.Insert("MyAnimal", animal);

// change Spot's name to Rover
animal.name = "Rover";

// get Spot from the cache
Animal animal2 = (Animal)HttpContext.Current.Cache.Get("MyAnimal");

// this is the cached object; the name should be Spot, but instead it's Rover ??!!
Label1.Text = animal2.name;

I expected the above code to set the label to Spot, but it actually sets it to Rover. Cache. Insert appears to store a reference to the object. I have yet to find anyplace this is mentioned in Microsoft's documentation. Kudos to Martin Bakiev of Penn State University for figuring this out.

Here's one way to get around the problem. Add another constructor to the Animal class. Use to create a copy of the object, and then store that copy in the cache. This has the desired effect, setting the label to Spot.


// define a class with some members
class Animal
{
public string species;
public string name;

public Animal(string s, string n)
{
species = s;
name = n;
}

public Animal(Animal a)
{
species = a.species;
name = a.name;
}
}

// create a dog named Spot
Animal animal = new Animal("dog", "Spot");

// store Spot in the cache
//HttpContext.Current.Cache.Insert("MyAnimal", animal);
HttpContext.Current.Cache.Insert("MyAnimal", new Animal(animal));

// change Spot's name to Rover
animal.name = "Rover";

// get Spot from the cache
Animal animal2 = (Animal)HttpContext.Current.Cache.Get("MyAnimal");

// this is the cached object; the name should be Spot, but instead it's Rover ??!!
Label1.Text = animal2.name;

That avoids the problem, but it's not very convenient if you want to store instances of many different classes in the cache; you'd need to create a new constructor for each class. I found a better solution: I wrote methods that use the .NET BinaryFormatter class to serialize an object on its way into the cache, and deserialize it on the way back out. I may be able to share that code in a future post.

Determining whether a numeric value has at most two decimal places

I was working on a .NET website and wanted to write some C# code to validate that a value input by the user was numeric and had at most two decimal places. This is useful, for example, when validating that the input represents a dollar amount.

I first tried this, which didn't work:
try
{
// must be numeric value
double d = double.Parse(s);
// max of two decimal places
if (100 * d != (int)(100 * d)) // max of two decimal places
return false;
return true;
}
catch
{
return false;
}

The above is unreliable because, since d is a floating-point number, 100 * d isn't always exactly equal to (int)(100 * d), even when d has two or fewer decimal places. For example, 100 * 1.23 might evaluate to, say, 122.9999999.

This post on StackOverflow offers several solutions, but none of them looked right for my purpose. Instead, I came up with this:
try
{
// must be numeric value
double d = double.Parse(s);
// max of two decimal places
if (s.IndexOf(".") >= 0)
{
if (s.Length > s.IndexOf(".") + 3)
return false;
}
return true;
catch
{
return false;
}

The same thing could be accomplished using a regular expression, if you prefer.

Reading a stored procedure's output variable in .NET

I recently wrote some C# code to call a stored procedure, pass some input and output parameters, read the resulting recordset, and then read the value of an output parameter. When I tried to read the output parameter's value, the result was null. It took a long time to figure out why.

It turns out that, when using SqlDataReader.ExecuteReader -- as opposed to ExecuteScalar or ExecuteNonQuery, you must close the reader before you can obtain the value of an output variable. (A tip of the hat to The Code Project for that information.)

Here's an example:

The SQL code:
CREATE PROCEDURE test
@foo INT,
@goo INT OUTPUT
AS
BEGIN
SET @goo = @foo * @foo
SELECT @goo AS hoo
END

First version of the C# -- wrong, output value is null:
string connectionString = "...";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("test", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@foo", 9);
SqlParameter outParam = cmd.Parameters.AddWithValue("@goo", 0);
outParam.Direction = ParameterDirection.Output;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();

// obtain a value from the recordset
int result = Int32.Parse(rdr["hoo"].ToString());

// obtain the value of the output parameter
// reader is still open
// System.NullReferenceException occurs because the value is null
Label1.Text = cmd.Parameters["@goo"].Value.ToString();
} // reader is closed here
}

Second version of the C# -- right, output value is 81:
string connectionString = "...";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("test", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@foo", 9);
SqlParameter outParam = cmd.Parameters.AddWithValue("@goo", 0);
outParam.Direction = ParameterDirection.Output;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();

// obtain a value from the recordset
int result = Int32.Parse(rdr["hoo"].ToString());

// obtain the value of the output parameter
// reader is still open
// System.NullReferenceException occurs because the value is null
} // reader is closed here
Label1.Text = cmd.Parameters["@goo"].Value.ToString();
}

Configuring access to a web service in production, test and development environments

I'm developing an API in the form of a .NET web service. Third parties will consume the API, and so will my own website. The servers and developer workstations hosting the API must support four different ways of accessing the API:

1. Third parties must be able to access the API on my production web server using a secure (HTTPS) URL visible outside my private IP space.
2. The production version of my website must be able to access the API on my production web server using a secure (HTTPS) URL visible inside my private IP space.
3. The test version of my website must be able to access the API on my test web server using a secure (HTTPS) URL visible inside my private IP space.
4. A developer's local copy of my website must be able to access the API on the developer's computer using a non-secure (HTTP) URL visible on the local computer, avoiding the need to configure a local IIS site to support SSL.

Here's how I fulfilled all four requirements:

IIS:

• On the live web server, I created a website with IP address x.y.z.134*, ports 80 and 443, no host header.
• On the test web server, I created a website with IP address x.y.z.130, ports 80 and 443, no host header.
• On my local computer, I created a website with IP address "All Unassigned", port 80, and host header "oosapi".

Hosts file:

• On the live web server, the hosts file includes the entry "x.y.z.134 oosapi.localupmenus.com".
• On the test web server, the hosts file includes the entry "x.y.z.130 oosapi.localupmenustest.com".
• On my local computer, the hosts file includes the entry "127.0.0.1 oosapi".

*IP addresses are masked to avoid divulging my private IP address space.

You might wonder why I didn't just use the name "oosapi" in the hosts file for all three environments. The answer is that I wish to use SSL on the live and test web servers. My SSL certificates are for the domains localupmenus.com and localupmenustest.com, not oosapi. If I used oosapi, an "invalid certificate" warning would occur, and .NET would refuse to connect to the web service.

In the web.config for the my website, one of three keys identifying the URL for the web service is uncommented:

• On the live web server:
• On the test web server:
• On my local computer:

(An interesting side note: A Google search for "dynamic web reference" turns up many articles that suggest setting the web reference's "URL Behavior" property to "Dynamic" in Visual Studio. There's just one catch: A Visual Studio web site, as opposed to other project types, doesn't support the "URL Behavior" property. Fortunately, the answer is simple: for a web site, the web reference URL is automatically stored in web.config, and it's just a matter of editing that web.config key to suit each deployment environment.)

The above handles requirements #2 - #4. That just leaves requirement #1. For third party access, I created a DNS entry in LocalUp's GoDaddy account, mapping oosapi.localupmenus.com to 74.116.125.134. That's the public IP that corresponds to the private IP x.y.z.134.

Using session variables to reduce the number of database connections

My .NET website, which accesses a SQL Server 2005 database, has been performing poorly when usage is heavy. When processing more than about 5 - 10 orders per minute, CPU utilization on the database server approaches 100%, the website's response time becomes slow, and database timeout errors become frequent. I would have expected the website to handle hundreds of orders per minute.

The main problem seems to be that the website makes many database calls order, so many that a large number of database connections are open at any one time, straining CPU and RAM resources on the database server. This is a consequence of how the previous programmers built the web site. For example, a restaurant record can be looked up in the database by calling a stored procedure that accepts the restaurant ID as a parameter. The website was calling this stored procedure multiple times per page, even though the restaurant information never changes. (Okay, it does change sometimes, but typically only once every few months, and in ways that don't need to be taken into account during an in-progress order.)

Even caching the restaurant record in a local variable just for the duration of each page would help, but I went one step further and cached it in a .NET session variable, so that other pages can access the record without going back to the database.

In simplest terms, the pseudocode is like this:
if (session var exists) {
read restaurant record from session var
}
else {
read restaurant record from database
store restaurant record in session var
}

In practice, it was a little trickier, for two reasons. First, it's possible for a customer to place an order from one restaurant, and then without closing the browser, place an order from another restaurant. So the pseudocode really needs to be:
doLookup = true
if (session var exists) {
read restaurant record from session var
if (requested restaurant ID = restaurant ID from session var) {
doLookup = false
}
}
if (doLookup) {
read restaurant record from database
store restaurant record, including restaurant ID, in session var
}

Second, in .NET, session variables aren't accessible during very early parts of the page lifecycle. For example, in my website, a restaurant lookup can occur in the Application_PreRequestHandlerExecute handler. When writing code that uses session, and which may be called at any point during the page lifecycle, it's necessary to check whether the .NET session object is available before attempting to access it; otherwise an exception occurs. This is as simple as: if (HttpContext.Current.Session != null).

Now, the above isn't the whole story of improving my website's performance, for two reasons. First, it's not just restaurants that were being looked up in the database repetitively. The same problem applied to lots of other entities. I applied the same solution to as many of those entities as I could in the time available.

Second, by using SQL Server's SQL Profiler and Database Engine Tuning Adviser, I determined that the creation of some indexes and statistics could likely improve performance -- according to the Database Enginer Tuning Adviser, by as much as 85%. (Thanks to my friend Max Fomitchev for his excellent assistance with these SQL Server performance tools.)

So I've implemented the session variable stuff, plus the indexes and statistics. It will all go live later this week. I'll post a comment once I know the results.

Banker's rounding in C# and SQL

Today I discovered an arithmetical quirk called banker's rounding.

My .NET 3.5 website, written in C#, performs some calculations which, because they deal with currency, round to two decimal places. Here's an example:
double saleAmount = 205.75;
double commissionPercentage = 6.0;
double commission = Math.Round(saleAmount * commissionPercentage / 100.0, 2);

The result of the calculation is stored in a database. I had reason to believe there was a logic error in my application, so I wrote a database script to verify some of the calculations by directly examining values in the database. Sometimes the value calculated by the T-SQL script in SQL Server Management Studio 2005 was off by one cent when compared to the value calculated in C#.

For example, in C#, Math.Round(12.345, 2) is 12.34, while in T-SQL, Round(12.345, 2) is 12.35 -- one cent higher.

I learned in science class that, when the value to be rounded ends in 5, you round up. It turns out that .NET takes a different approach, called banker's rounding. It's based on IEEE Standard 754, section 4, and here's Microsoft's explanation of it. In short, when the last digit is a 5, it rounds up if the preceding digit is odd, and down if even. This is intended to minimize rounding errors that would accumulate from consistently rounding a midpoint value in the same direction.

SQL Server does not perform banker's rounding by default. In fact, I don't think there's any built-in function in SQL Server to perform banker's rounding. A number of people have posted their own SQL functions for banker's rounding. I'm not sure all of them are correct.

Rather than find or write a banker's rounding function in T-SQL, I worked around the problem. All I needed was to identify values that were way off from the expected value. So I used a SQL comparison like this to find values that differed by more than one cent: IF ABS(@expectedSalesCommission - @amount) > 0.01...

That turned out to not quite work. Because floating-point calculations are imprecise, sometimes values the computed value of @expectedSalesCommission was a trifle more than one cent away from the computed value of @amount. I changed it to IF ABS(@expectedSalesCommission - @amount) > 0.011..., and that did the trick.