Computer Predictions from 1981

One of my favorite pastimes is reading old magazines, especially old computer magazines. They serve as a history book that was written as the history was being created. Thanks to Archive.org there are plenty available to read. One of the thing I especially enjoy in old computer magazines are the predictions of the future of the industry. In the January 1981 issue of Personal Computing I ran across an article by William R. Parks called "What will Computing Be in Fifty Years". He tries to predict what computing will be like in 2030 and 2080. It is still 13 years from the first date at the time I am writing this but a lot of what is predicted here is already possible or even happened. Here are the predictions in italics and my commentary on them.

 

"In today's America nearly every household has at least one television set. In fifty years, almost every home will have a computer system which will be even more powerful than the most advanced computer we have today."

According to a 2013 US Census report 83.3 percent of households reported owning a computer (1). If you include video game consoles, tablets and smartphones that people may do not automatically refer to as a "computer" then the number is likely higher. Most, if not all of these devices are more powerful than what was available in 1981, so I would say this prediction is accurate. It's interesting to note that with the current rise in the use of phones, tablets and computers to consume video content I wouldn't be surprised to see a decline in the number of TV's in US households.

 

"Nearly every appliance will have an intelligent component processor that can hear and speak. Some families will own mobile robots that can speak as well as listen to commands."

This is two predictions in one, so let's take them one at a time.

Most appliances these days have some sort of embedded processor in them, and we are definitely starting to see appliance with more advanced processing capabilities, especially with internet connected appliances. Voice synthesis is something that could  be added to any appliance today at very little additional cost, so I think this prediction is technologically possible, but it's not something that there is a lot of demand for. Voice recognition is a little harder since it requires more processing power, and I think we haven't seen a lot of this in appliance for both technological and practical reasons. If we do start seeing more voice activated appliances we probably won't see the technology built directly into the appliance, but instead will see devices like the Amazon Echo which will process the voice commands and then send them to the appliance. Most appliances have a very simple user interface so if you have to walk over to the appliance to talk to it, you might has well just use the manual interface. On the other hand, being able to preheat my oven without getting off the sofa has a lot more value. Of course voice recognition and response become much more valuable when we start talking about handicapped users.

As for mobile robots, this is something that lots of predictions of the future think we will have and it's something that I am sure a lot of people would want, but we clearly aren't there yet. We are starting to see robots in the home, but in very limited use cases like vacuuming and mowing the lawn. We really don't seem that much closer today to a general purpose home robot then we were in 1981.

 

"The huge television industry we have today, serving the general public in producing programs viewed by hundreds of millions of people, will be an even larger industry employing countless computer programmers in the production of mass media interactive programmed events in the year 2030."

There is no doubt that the TV industry has grown considerably since 1981, but they probably didn't imagine how much the industry would change. Back then people in the US were primarily getting their TV programming from the big three (four counting PBS) broadcast networks. Those networks are still around but have been joined by smaller broadcast networks, hundreds of cable networks, premium channels like HBO and Showtime, streaming services like Netflix and Amazon, not to mention user created content on YouTube.

As for the interactive part, this idea has been around for a long time in a lot of different forms. On-demand services have added some level of interactivity by allowing us to watch programs whenever we want. People also imagined being able to access things like weather, or stock quote, or even electronic mail through their TV, but the internet fulfilled that need. There was also the idea of the audience being able to directly interact with a program which I think is what this author was referring to. We have seen this happen in small ways, like being able to vote for contestants on a reality show, or participate in some sort of enhanced experience on your tablet while you are watching the show, but in general the idea of true interactive programming hasn't caught on. The technology is in place to do this, but I just don't think anyone has come up with a truly compelling way to make TV interactive.

Computer programmers have definitely become more a part of the TV industry just like they have in most industries.

 

"Fifty years from now, all banking and mail will be done directly from a computer terminal in the home. A kind of paperless society will result and 20 percent of the population will work at home, communicating to their places of business"

Let's take this one a piece at a time starting with banking. A Pew Research article from 2013 said that 51% of adults bank online (2), and a 2015 article from CBS Money Watch says that 39% of adults with mobile phone use them for online banking.(3) We could also consider ATM use as doing banking from a "computer terminal".

As for mail it's clear that a lot of personal correspondence has moved from mailed letters to online communications. Mail also ties in with the online banking. Total first class mail volume going through the USPS has dropped from 97.7 B pieces in 2006 to 62.4 B pieces in 2015 (5). According to a 2015 article in Digital Transactions, US consumers paid 37% percent of bills online, but 90% still want to receive the bills on paper. (4)

Banking and mail have definitely made large moves online, but we are still a long way from "all banking and mail", and I doubt we will reach that point by 2030.

According to the United State Bureau of Labor Statistics, in 2015, 24% of employees did some or all of their work from home up from 19% in 2003 (6), so we appear to at least be close to the prediction already.  In the 80's people definitely thought telecommuting would be a big thing in the future and the internet has helped make that possible, but people have also discovered that even though there are some good things about telecommuting, there are also significant down sides which probably explains the slow growth over the past 12 years, a trend which I would expect to continue.

 

"The same chips that manipulate data will also be able to store many volumes of books. Some of the programs and data will be so complicated that it will take many years of training to fully utilize such a system. For example, there will be one chip especially for doctors (reference programs and data for medical professions) or a machinist's chip (all necessary programs and graphics displays to operate as a machinist). Computers will also be a very important subject in the schools."

I am not exactly sure what the author was trying to get at with this prediction. Chips that had both processing and persistent storage did exist even at that time, but even today the only place you really see this is in embedded controllers. The whole idea of data and processing both being on the same chip isn't even that important since you can have them on separate chips in the same package and get the same result. As to the prediction about how complicated it will be, there are certainly programs that have a large learning curve, things like CAD and design software, but even these don't require years of training. I think in general software has become easier to use and not harder.

One way we could could think about the realization of this prediction is in the form of smartphones and tablets where they could be loaded with all the apps and data (or a connection to the data on the internet) for a specific job.

That last part has definitely come true, classes in computers are common in both grade schools and even more so in colleges where we have entire curriculums about computers.

 

"The problem of unemployment will be solved by distributing the workload. Every citizen' will work a fewer number of hours per week because of automation. Programmers, however, will work at home as many hours per week as they like and computer programming will be the most open field of employment."

Clearly the problem of unemployment is nowhere near being solved and the issues brought up by this prediction are quite complex. We have definitely seen an increase in automation which has eliminated jobs, but this has not necessarily lead to a shorter work week. The Internet has also lead to the elimination of jobs by allowing people to do things online where they used to have to call customer service, but on the flip side the Internet has also opened up other areas of employment and made it much easier for people to set up their own companies online. As discussed in one of the earlier prediction there has definitely been an increase in work at home jobs, and programming is one of those jobs that is well suited to working from home. Were this will all go over the next 13, and even more so 63, years is hard to predict but it will definitely have a large impact on our society.

 

"A project to make computers virtually human-like will succeed. The obvious dangers to society will be overcome by a liberally educated society that will see the differences between man, the human, and simulated man, the computer-robot. Man will be in control and children will be taught to distinguish between artificial intelligence and human intuition. The power of the computer will nevertheless make some think that certain robots are "alive" and some computer personalities will be as famous as Hollywood stars are Today."

Making a computer that can behave like a human has been a dream of computer scientists since the beginning of the computing. Some progress was made in niche areas, for example programs that can play chess, and more recently we have seen things like IBM's Watson project which was able to beat a human in a game of Jeopardy. Despite this progress we still haven't reached a point where that average person will run into a general purpose AI. Educating people about this will be important, but I think we will have a period, maybe a long one, where people are easily fooled by AI even when they have been educated about it. We will likely see AI in computer form first, because making a human like robot holds challenges beyond the AI. The idea of computer personalities still seems very far off. Special effects technology has come a long way and photo realistic digital characters, both human and otherwise are becoming common in movies, but there are still talented actors behind the scenes bringing them to life.

 

"The 1970's and 1980's will be looked upon as the important beginnings of miniaturized computer technology at low cost. Perhaps, in the future complex society, computer-robots will sell for the same price automobiles sell for today. The robot industry will become as important as the auto and steel industries and just as nearly every household has a car today, it will also have a computer-robot."

The first statement is obviously true. That was the era where the transition from main frames to personal computers started and it definitely was the start of computers becoming smaller, more powerful and cheaper. I have talked about robots earlier in the article, as for cost that is really hard to say at this point. The few robots that are available today are significantly less expensive than cars, but it's hard to say what a fully functional personal robot might cost.

1. http://www.census.gov/library/publications/2014/acs/acs-28.html

2. http://www.pewinternet.org/2013/08/07/51-of-u-s-adults-bank-online/

3. http://www.cbsnews.com/news/smart-phones-are-revolutionizing-consumer-banking-habits/

4. http://www.digitaltransactions.net/news/story/Americans-Overwhelmingly-Prefer-Paper-Bills_-but-Increasingly-Pay-Them-Electronically

5. https://about.usps.com/who-we-are/postal-facts/decade-of-facts-and-figures.htm

6. https://www.bls.gov/opub/ted/2016/24-percent-of-employed-people-did-some-or-all-of-their-work-at-home-in-2015.htm

C# Substrings

Anyone who has worked with strings in C# is probably familiar with the Substring function. For example, if we want to get the first three characters of a string we could do this:

string s = text.Substring(0,3);

or to get characters 3 and 4:

string s = text.Substring(2,2);

Only slightly less known is that if you want to get the end of a string you can omit the length and just use the starting character. So to get everything from the 3rd character to the end of the string you can do this:

string s = text.Substring(2);

We can also use Substring to get a single character but there are actually some lesser known ways of doing this.
The first way is to use the fact that a string in C# can be treated as a character array. To get the third character you could do this:

char c = text[2];

This method could also be used to iterate through all the characters in a string:

for(int i=0; i<text.Length; i++) {
   char c = text[i];
   // Do something with c 
}

but there is an even simpler way to handle this:

foreach(char c in text)
{
     // Do something with c 
}

This last method is a much cleaner way to iterate through all the characters in a string.

Handling Whitespace

In this post I want to offer some tips for a very simply issue, but one that can cause you a lot of headaches if you don’t deal with it.

Let’s say we are validating user input and we need to be sure the user has entered something in a specific field. This data may be coming from a form, or XML file, etc., so for demonstration purposes I will just use string.

  string s1 = "";
  string s2 = null;
  string s3 = " ";
  string s4 = "\t";

  if (s1 == "") Console.WriteLine("s1 not entered");
  if (s2 == "") Console.WriteLine("s2 not entered");
  if (s3 == "") Console.WriteLine("s3 not entered");
  if (s4 == "") Console.WriteLine("s4 not entered");

If I am checking for user input in a field I would want all four of these to be considered invalid, but if I run the program, only the first one will be found, the other three will look ok. This happens because we are comparing against a literal empty string which is not the same as a null, a space, or a tab. Here is another way to do this check...

 
  if (string.IsNullOrEmpty(s1)) Console.WriteLine("s1 not entered");
  if (string.IsNullOrEmpty(s2)) Console.WriteLine("s2 not entered");
  if (string.IsNullOrEmpty(s3)) Console.WriteLine("s3 not entered");
  if (string.IsNullOrEmpty(s4)) Console.WriteLine("s4 not entered");

The string class provides a function call IsNullOrEmpty that can help with the second case. This function will return true if the the string is empty or it’s a null. This is very useful when you are about to call a function on a string, for example SubString(), because this would throw an exception if the string is null. This is a step in the right direction, but it still doesn’t help with the last two cases since a string with a space or tab aren’t considered empty.  Let’s look at another function on the string class:

 
if (string.IsNullOrWhiteSpace(s1)) Console.WriteLine("s1 not entered");
if (string.IsNullOrWhiteSpace(s2)) Console.WriteLine("s2 not entered");
if (string.IsNullOrWhiteSpace(s3)) Console.WriteLine("s3 not entered");
if (string.IsNullOrWhiteSpace(s4)) Console.WriteLine("s4 not entered");

If you run this code it will recognize all four strings as invalid. IsNullOrWhiteSpace works similar to IsNullOrEmpty but it also looks for any combination of white space, spaces, tabs, carriage returns, etc.
It’s a good idea to avoid comparing a string to and empty string, and better to use one of these functions instead.
Even when you are not just looking for whitespace or not it can give you trouble. Let’s look at this example:

  
string s1 = "OK";
string s2 = " OK ";
string s3 = "\tOK\t";

if (s1 == "OK") Console.WriteLine("s1 is OK");
if (s2 == "OK") Console.WriteLine("s2 is OK");
if (s3 == "OK") Console.WriteLine("s3 is OK");

If you run this, only the first case if found to be OK because the others two have whitespace before and after the string. We can solve this by using the Trim function:

  
if (!string.IsNullOrEmpty(s1) && s1.Trim() == "OK") Console.WriteLine("s1 not entered");
if (!string.IsNullOrEmpty(s2) && s2.Trim() == "OK") Console.WriteLine("s2 not entered");
if (!string.IsNullOrEmpty(s3) && s3.Trim() == "OK") Console.WriteLine("s3 not entered");

Since Trim is an instance method we need to first be sure s1 isn’t null so I use the IsNullOrEmpty function for this. Now that we know the string isn’t null we can call Trim() on it which removes any leading or trailing whitespace. Now all three of these will find OK.

ASP.NET Core 1 - Blank Template Part 2

 

In my last post I walked through the code of the blank template that is created for an ASP.NET Core 1 RC1 application. In this post we will take a closer look at the how this application works.

First let’s run the application by clicking the IIS Express button in Visual Studio.

Your browser should open and display the message “Hello World!”. If you look in your system tray you will find IIS Express serving up WebApplication1 so you application is running on IIS. If you now go into your browser’s debugging tools (for example, press F12 in Internet Explorer), go to network monitor and refresh the page you will be able to view response header:

You can see that even though we are running on IIS, the web server is reported as Kestrel since that is the actual server we are running on, IIS is just serving as a reverse proxy passing the requests to Kestrel.

Next click the down arrow next to IIS Express:

In the list below IIS Express you will see all the commands that have been declared in the project.json file. In the blank template there is only one command “web”. Click on web to start the application using this command. This time instead of a web browser opening you will see a window like this:

In this case we are running directly on Kestrel instead of Kestrel running behind IIS. To view the output of the application use the URL that is being listened on shown in the window, in this case http://localhost:5000. You will not see IIS Express in the system tray and if you look at the network monitor it will still show the server as Kestrel.

Now let’s look at the IISPlatformHandler. This piece of middleware allows Windows Integrated Authentication and TLS Authentication to pass through to Kestrel. In Startup.cs change the line that displays “Hello World” to this:

await context.Response.WriteAsync("Hello " + context.User.Identity.Name);

Just like previous versions of ASP.NET you can use the User property of the HttpContext to get authentication information. To run your app in IISExpress using Windows Authentication go to the project properties, go to the Debug tab, make sure the IISExpress profile is selected and then uncheck Enable Anonymous Authentication, and check Enable Windows Authentication.

If you run this using IISExpress you will see your account name if you are on a Windows domain, or you will be prompted to enter your username and password and again the name will be displayed. In this scenario the IISPlatformHandler is handling the authentication and passing their information along to Kestrel in the context.

Now run the application use the web command. When you browse to the page this time you will just get “Hello”. In this scenario we are connecting directly to Kestrel and not going through IISExpress so the IISPlatformHandler doesn’t have an effect.

With the web page displayed try adding a file to the end of the URL, for example http://localhost:63694/test.html. When your browse to this URL the page will still return “Hello”. How about adding a path, http://localhost:63694/test/test.html. Again you will only get “Hello” as a response. As I mentioned in my introductory article ASP.NET Core 1 applications have no functionality by default, not even the ability to server files. Every URL we use just runs the same middleware to display the hello message. In the a future post I will show how to enable various pieces of stock middleware to enable these features.

I hope the demonstrations in this article give you a better idea of some of the differences between the new ASP.NET and previous versions.

ASP.NET Core 1–Blank Template

 

In my last post I talked about some of the basics of ASP.NET Core 1, in this posted we will get setup to do some development and take a look at some code. I am going to be working on Windows using Visual Studio but the concepts still apply to doing ASP.NET on Mac or Linux. As of this writing the current version of ASP.NET Core 1 (which was formally know as ASP.NET 5) is RC1 and that is what this article is based on.

The instructions for setting up ASP.NET Core 1 on Windows can be found here:

http://docs.asp.net/en/latest/getting-started/installing-on-windows.html

That site also has instructions for getting setup on Mac and Linux. I did this install on two different systems. It worked fine on one, but on the other, when I created a new project in Visual Studio, I got an error saying “Method not found: 'Newtonsoft.Json.Linq.JValue Newtonsoft.Json.Linq.JValue.CreateNull()'.”. I eventually figured out that I had version 6.0.0 of Json.NET installed in the GAC. I installed version 6.0.6 to the GAC and this resolved the problem.

Once you have completed the installation, start up Visual Studio 2015 and select New Project and then under C#, Web, pick ASP.NET Web Applciation. If you have installed everything properly you should see a section called ASP.NET 5 Templates.

We will start with a very basic application so select the Empty project type and be sure Host in the cloud is unchecked.

Let’s take a look at some of the files that make up the default Empty project. We will start with project.json. This file basically replaces web.config, although there is a web.config in the project which I will talk about later, and is required by the DNX runtime to execute the application.

"version": "1.0.0-*",

The first line is just a piece of metadata that declares the version of you application. You can also specify things like “authors” or “description”.

"compilationOptions": {
"emitEntryPoint": true
},

Next we have the compiler options. The only option that is included by default is “emitEntryPoint”. If this is set to true then the project will be an executable, if it’s false the project will be a DLL that since it will have no execution entry point.

"dependencies": {
  "Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
  "Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final"
},

The next section, dependencies, declares the assemblies that this project needs to run. These will normally come from NuGet but also could come from other projects. The two dependencies declared by default are used to handle the web hosting of the application. As I mentioned in my previous post, ASP.NET Core 1 is not tied to any specific web server. Here we see a reference to Microsoft’s open source and cross platform Kestrel web server. This compact web server will run as part of your application and can handle requests directly or can run behind and existing web server. Note that Kestrel is not a full featured web server like IIS, other packages will need to be included in your project to do even simple things like serving static files.

The other dependency is the IISPlatformHandler, which is needed when you are running Kestrel behind IIS. In this configuration IIS simply serves as a reverse proxy passing requests back and forth to Kestrel. The IISPlatformHandler allows things like Windows Authentication to pass through to your application.

"commands": {
  "web": "Microsoft.AspNet.Server.Kestrel"
},

This section defines entry points for your application that can be run from the command line or Visual Studio. When you execute the program using a command it will looks for a Main function in the specified assembly and start execution there. Commands can also have command line options that will get passed to the Main function. You can have multiple commands in a project that allow it to be run in different ways.

"frameworks": { 
  "dnx451": { }, 
  "dnxcore50": { }
},

The next section defines the frameworks that the application can run with. As of this writing you have the options of three version of the full .NET Framework, dnx451, dnx452, and dnx46, and also the .NET Core Framework, dnxcore50. In the default project both core and the full framework are selected, so the application can only use features that exist in both frameworks.

"exclude": [
  "wwwroot",
  "node_modules"
],

In ASP.NET Core 1 you have the option of deploying all your source files to the server and they will be compiled into memory when the application is first started. Every file in the folder with a project.json file, and any subfolders will become part of the program. You can prevent files from being included in the project by setting up exclusions. Here we are excluding the contents of the wwwroot and node_modules sub directories.

"publishExclude": [
  "**.user",
  "**.vspscc"
]

The final section is similar to the exclude section, but this one specifies files that you don’t want published along with your project.

I mentioned earlier that even though project.json takes the place of Web.Config you will still see a Web.Config file in the wwwroot directory of the default template. This file configures the httpPlatformHandler which is an IIS module that allows it to serve as a proxy in front of another web server, in this case acting as a proxy for Kestrel. This module is only used on Windows when running behind IIS.

The final file we will look at is Startup.cs which contains that code that is called to start up your application. Let’s start with the last line:

public static void Main(string[] args) => WebApplication.Run<Startup>(args);

This is the Main function that will be called if you start the application from the DNX command line. It simply starts the web application and passes the command line arguments. If you are running the application using IIS or IIS Express this function will be skipped and WebApplication.Run will be called directly.

When the web application is started up the first function in this class that will be called is ConfigureServices. This class is used to setup any services that your application will need, for example EntityFramework, and makes these services available for dependency injection. In the empty template nothing is done in this function.

The final part of the startup process is a call to the Configure function which looks like this:

public void Configure(IApplicationBuilder app)
{
  app.UseIISPlatformHandler();
  app.Run(async (context) =>
  {
     await context.Response.WriteAsync("Hello World!");
  });
}

The purpose of this function is to build the HTTP request pipeline. As I mentioned in my introductory article, ASP.NET Core 1 applications have almost no functionality out the box. They can receive HTTP requests, but without some setup they can’t do anything with them. The Configure function sets up the processing pipeline be enabling one or more piece of Middleware. Middleware receives an HTTP request, does some sort of processing based on it, and then either returns a response to the requestor, or calls the next piece of Middleware in the chain.

In the blank template you will find two pieces of Middleware. The first is the IIS Platform Handler. This Middleware is used to handle IIS specific authentication like Windows Integrated Authentication. This is only useful when running behind IIS, so it wouldn’t have any function when running on Mac or Linux. When this Middleware completes its task it sends the request on to the next piece of Middleware.

The final piece of code is the one that actually sets up what this application will do, which is to simply return the text “Hello World!” to the calling browser. The app.Run function adds and inline delegate that will be called each time a request to this application is recieved by the server. When called it calls the WriteAsync function to write the text back to the response.

In this post I showed the ground work for a very basic ASP.NET Core 1 application. In my next post we will do a little more with this application to get a better understanding of how things work.

ASP.NET Core 1 Intro

There are a lot of changes currently going on in the Microsoft development work in general and with .NET specifically, so I wanted to write some articles to look at one area in particular and that is ASP.NET. The changes going on with ASP.NET are pretty radical and exactly what is happening can be a little confusing. This is only made worse by a recent name change for the next version of ASP.NET.

Last year Microsoft announced a new vesion of ASP.NET that they called ASP.NET 5.0. Most updates to the .NET components are incremental updates that build on what came before. ASP.NET 5.0 on the other hand is a total re-engineering of ASP.NET from the ground up. To meet some of the design goals of the new version Microsoft has dropped, at least for now, some features from ASP.NET. For this reason Microsoft recently changed the name from ASP.NET 5.0 to ASP.NET Core 1.0. The new version is only at RC1 at the time this was written, but when it is released there will be two release version of ASP.NET, the full featured 4.6, and the new Core 1.0.

So what is different in ASP.NET Core 1.0. Here are some of the major bullet points.

Cross Platform Execution - The word “Core” in the new name is a reference to .NET Core which is a new version of the .NET Framework designed from the ground up to run on Windows, Mac and Linux. The new version of ASP.NET can run on either the existing 4.6 Framework, or on top of the new Core framework which allows it to be hosted on non-Windows servers. Since a lot of the things in the framework need to be updated to work cross platform, Core currently supports a lot fewer namespaces then the 4.6 framework.

Cross Platform Development - Besides running cross platform, .NET Core applications can be developed on Windows, Mac and Linux. Windows development can be done in Visual Studio as before, and all three environments can use command line tools and the new cross platform Visual Studio Code editor.

New Runtime - ASP.NET Core 1 uses a new run time called DNX. DNX is not only a key component of the cross platform support but also provides all the tools needed to build apps on each platform. DNX contains command line tools used to manage the installed frameworks, manage dependencies and execute .NET core applications.

Modular - Core 1.0 has been designed to be highly modular, you only bring in the features you need using NuGet.  A bare bones ASP.NET Core 1.0 application cannot do much beyond handing  HTTP requests and repsonses. If you want static file handling, there is a module for that, logging is another module, MVC yet another, etc.

Performance - The new version of ASP.NET has received a lot of performance optimizations. A lot of this performance comes from the modular nature. Since the HTTP pipeline only needs to contain what you absolutely need for your app you can have a much leaner and faster pipeline. Even with full MVC enabled Core 1.0 provides better request performance than the previous version.

Web Server - The previous versions of ASP.NET were closely tied to IIS, your application actually ran as a processes within IIS. To allow for cross platform support ASP.NET Core 1 breaks the dependancy and runs your application is a seperate process. On Windows IIS can still used, but is can serve as just a reverse proxy that passes requests back and forth to ASP.NET via the open source Kestrel web server of the more lightweight WebListener. Kestrel can also be used to run cross platform.

Open Source - ASP.NET Core 1 is open source, you can download the current source on https://github.com/aspnet. Besides being open source, the whole development process has been very open. You can watch the weekly update videos from the development team here, https://live.asp.net/.

In my next post we will take a look at some actual ASP.NET Core 1 code.

SQL Server Indexes

When it comes to improving the performance of database queries one of the most important areas to look at is Indexes. Proper indexes can actually lead to queries running hundreds if not thousands of times faster. Despite this, indexes are often neglected by software developers. In this article I will talk about the basics of using indexes with Microsoft SQL Server.

First, let’s talk about what an index is. An index on a database table is actually a lot like the index in the back of a book. If a book didn’t have an index and I needed to find every occurrence of a certain word, I would have to read every page in the book to find them. When the book does have an index, I can quickly scan through the index and find the word I am looking for which will point me to where to find it in the book. Since the index is in alphabetical order, I don’t even need to read the whole thing, I can quickly jump to the page that has the word I am looking for.

When you create an index on a table, the server will store the indexed field or fields in alphabetical/numeric order so that any value in those fields can be rapidly found in the index. Let’s take a look at some queries and how they can be improved with indexes.

I have included a script along with this article that will create a very simple example of a sales order header table and will populate it with some semi-random data. Simply run this script on a test database and it will create a table called SalesOrder and fill it with data.

To analyze the performance of the queries we will use two tools within SQL Server Management Studio. First, before executing each query we will run the command “set statistics io on” which will show how many reads the server does from each table in the query. After the query we will run “set statistics io off” to turn this off. The second tool is the Execution Plan. You turn this on by selecting Include Actual Execution Plan from the Query menu before you run the query. We will talk about how to use these two tools in more detail when we execute our first query.

Let’s start out with this query…

set statistics io on   

select OrderNumber from SalesOrder where orderNumber = '10017'   

set statistics io off   

Run this query, then click on the Messages tab to look at the statistics, you will see something like this:

Table 'SalesOrder'. Scan count 1, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The two values we want to focus on are logical reads and physical reads. All data in SQL Server is stored in 8K pages, so these values are the number of pages that were read during the execution of the query. A physical read means that the page was read from disk. Once a page is read from disk it can be cached in memory in case it is needed again. Reading a cached page would be a logical read. This query did 59 logical reads, but if the data had not been accessed recently these could have been physical reads. Either way we want to try to minimize this number. Our data records are pretty small so there are going to be a lot of records in each page in this case, so you can see that 59 reads means it had to read a lot of records to get the one we were looking for.

To understand why this happened let’s look at the Execution Plan tab. If you don’t see this tab be sure “Include Actual Execution Plan” is selected on the Query menu.

This shows that to execute the query the server did a Table Scan which means I had to read every row in the table to find the one we are looking for. It doesn’t know that OrderNumber is unique so it doesn’t stop even when it finds the record because it assumes that there might be more than one record with the same order number.

Now let’s create an index that will make this query more efficient. This query will create an index on the SalesOrder table for the OrderNumber column. Don’t worry about the NONCLUSTERED keyword, we will talk about this later.

CREATE NONCLUSTERED INDEX IX_SalesOrder ON dbo.SalesOrder ( OrderNumber )

With the index created let’s re-run the original query and look at the results. Here is the statistics output:

Table 'SalesOrder'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You can see that the number of reads has dropped from 59 down to only 2 which is a considerable improvement. Let’s look at the execution plan:

The Table Scan has changed to an Index Seek. Index Seeks are the best case for a query. This means it’s using the index to rapidly find the record(s) we are looking for.
So far we have only been returning the OrderNumber, so now let’s return both the OrderNumber and the Customer:

set statistics io on   

select OrderNumber,Customer from SalesOrder   
where orderNumber = '10017'   

set statistics io off   

If you look at the statistics you will see it has gone up from two reads to three. Let’s look at the execution plan to see why.

The query still uses the Index Seek but we now have a second step which is a Lookup. To understand this you need to understand how SQL Server stores indexes. When a NONCLUSTERED index is created the values in the field being indexed are copied to another part of the database in sorted order. Each of these index records also has a pointer back to the original data which is where all the fields are stored. Since the index and the data are stored in different pages in the database, once the value we are looking for is found in the index, the server must read the corresponding data page to get the customer field. This lookup isn’t a big issue for this query since we only have one record to lookup, but it would become a bigger problem if we were returning multiple records. One way we could fix this is to add the Customer field to the index.

drop INDEX IX_SalesOrder ON dbo.SalesOrder    
CREATE NONCLUSTERED INDEX IX_SalesOrder_1 ON dbo.SalesOrder     
(     
OrderNumber,     
Customer     
)

This query drops and re-creates the index so it includes both the OrderNumber and Customer. If you re-run the previous query you will see that the lookup goes away. The server uses the index to find the order you are looking for, and since the customer is part of the index it no longer needs to lookup the data page to get the customer field value.

Now that we have Customer as part of an index, let’s try to run a query on it.

select OrderNumber from SalesOrder    
where Customer = '30075'

Here is the execution plan:

Notice that we are now getting an Index Scan, instead of a Seek. When you create an index with multiple fields the index record is created by appending the two fields together in the order you specified them when you created the index. So if a record has a customer of 30075 and an order number 10048, the index essentially stores “1004830075”. Since the index is sorted by the order number first, it cannot use an Index Seek to find the customer number, but it can scan through all the index records to find the customer. The index Scan is preferable to a Table Scan because an index record will usually be much smaller than the table record, so there are a lot more records per page which results in fewer page reads. This query required 40 page reads instead of the 59 that was required for the whole table. This difference would get even larger if there were more fields in the table.

Now let’s create an index on the customer:

CREATE NONCLUSTERED INDEX IX_SalesOrder_Cust ON dbo.SalesOrder   
(   
Customer   
)   

and then run a query that returns all the order numbers for a specific customer:

select OrderNumber from SalesOrder    
where Customer = '30075'

If you look at the execution plan you will notice something odd, it’s ignoring the index and still doing an index scan:

The reason this happens is that when SQL Server generates an execution plan it not only considers the structure of the table and indexes, but also looks at statistics about the data in the index. The details of Statistics are outside the scope of this article, but for the purpose of this query I will say that the statistics for the Customer index will tell SQL that customer numbers often appear multiple times in the table. Since the server needs to do a lookup to get the order number for each record, it assumes that it will just be faster to scan the order index, then it would be to use the customer index and do the lookups. I can re-run the query and force SQL Server to use the customer index like this:

set statistics io on    
select OrderNumber from SalesOrder with (index(IX_SalesOrder_Cust))     
where Customer = '30075'     
set statistics io off

The version of the query that did an index scan had to do 40 reads, but when I force this query to use the customer index it required 92 reads, so in this case the server guessed correctly that the table scan is more efficient. The customer number in the test data is randomly generated so using the index will not always have been less efficient. If you happen to use a customer number that only has one or two orders, then the index version would be faster. SQL Server is making a best guess at what is most efficient, and even though certain executions of the query may end up being less efficient, in the long run SQL’s guess will likely result it better query performance.

To get SQL to use the index in this case we could do what we did with the order number index, and add the customer to it, but since we already have an index on the order number it’s probably not worthwhile to have the order number as part of the customer index, so there is a second option for handling this:

drop index [IX_SalesOrder_Cust] ON [dbo].[SalesOrder]

CREATE NONCLUSTERED INDEX [IX_SalesOrder_Cust] ON [dbo].[SalesOrder]  
(  
[Customer] ASC  
)  
INCLUDE ([OrderNumber])    

In this index I have added the INCLUDE clause which adds the OrderNumber to the index, but doesn’t actually make it part of the indexed field. Now SQL can use this index without having to do a lookup to get the OrderNumber.

You might wonder why you would use INCLUDE instead of just making the field part of the index? If you are not going to use the field in a WHERE clause, then having it as an INCLUDE is more efficient since the server doesn’t need to consider the value when updating the index which helps improve the performance of inserts, updates and deletes. Another advantage is that you can INCLUDE type of fields which cannot be indexed, Text fields for example.

So far we have been limiting the fields we return in the select, so let’s trying retrieving all the fields:

select * from SalesOrder   
where Customer = '30075'

This will once again result in a table scan. Even though we have an index on Customer, and SalesOrder is included in the index it would still need to do a lookup to get the rest of the fields, so it decides that a table scan would be more efficient. We can solve this issue by using a Clustered Index.

So far all the indexes we have built have been non-clustered indexes. As I explained earlier a non-clustered index stores the indexed fields in another part of the database with a pointer back to the actual data. By contrast, a clustered index stores the actual data records in the index order so no matter what fields we are selecting the lookup is not needed since the index and data are all in the same place. Since this index controls the order of the data, there can only be one clustered index per table because the table can only be sorted one way.

Let’s drop the original customer index and re-create it as clustered. The include is no longer needed since a clustered index effectively has all the fields included.

drop index [IX_SalesOrder_Cust] ON [dbo].[SalesOrder]   
CREATE CLUSTERED INDEX IX_SalesOrder_Cust ON dbo.SalesOrder    
(    
Customer    
)

Now if you re-run the query it will do an index seek.
Now let’s try this query.

set statistics io on   
select * from SalesOrder    
where orderDate = '1/1/2015'    
set statistics io off

Because every field is part of the clustered index, the execution plan will show that it did a clustered index scan. The statistics on this query show 66 reads which is 2 more reads then a table scan. I have read that due to the way the data is organized that the scan is actually a little more efficient then a table scan but index scans on a non-clustered index will normally be much more efficient then on a clustered index.

Everything I have shown here so far is for conditions in the WHERE clause, but this all applies to joins also, just think of the ON part of the join as being a WHERE. These indexes will also come into play when you do updates or deletes with a WHERE clause.

Salesforce Case/Lead Assignment Rules

One of the nice features of Salesforce Cases and Leads is the ability to setup Assignment Rules that will assign a record to a specific queue and send a notification when certain criteria are met. In this article I will show the basics of setting up Case Assignment Rules and how to they work with the REST API. Lead Assignment Rules work basically the same way so I will only cover Cases.

To setup the rules, go to Setup, App Setup, Customize, Cases and select Case Assignment Rules. You will see a list of any rules that are currently setup.

Click New to create a new rule and enter a unique name. At this point you can check Active to enable this rule immediately, or you can wait until you have completed the setup of the rule and activate it later.

 

Once you enter the name you will be taken back to the rule list where you can click on the rule to edit it.

Next to Rule Entries, click New to create a new rule entry. Here I have setup a rule that will assign the case to the US – Platinum Gold queue when the priority is High and the Reason is Breakdown. The sort order at the top is used if you have more then one rule and determines the order they are applied. Once the criteria of a rule is met none of the other rules will be evaluated. If you want the user or queue notified then be sure to select an e-mail template otherwise the e-mails will not be sent but the cases still will be re-assigned.

Once you have completed the rule entry you will be returned to the initial screen for the rule where you will want to edit the Rule Detail and select active to activate the rule.

When you use the REST API to create or update a Case all the active rules will be applied. The API also provides an HTTP header called Sforce-Auto-Assign that gives you some more control over this. If you are using the System.Net.HttpWebRequest class you would add the header like this:

request.Headers.Add(“Sforce-Auto-Assign: TRUE”);

There are three possible values for this header. Setting it to TRUE will cause all active assignment rules to be applied, which is the default behavior if you leave off the header. If you set it to FALSE, none of the rules will be applied. The final option is to use the id of an assignment rule like this:

request.Headers.Add(“Sforce-Auto-Assign: 01QE0000000bJiI”);

In this case, only that one rule will be applied, and it will be applied even if it is not active. You can get the Rule ID from the URL while you are editing the rule:

setup/own/entityruledetail.jsp?id=01QE0000000bJiI&setupid=CaseRules

C# Null-Conditional Operator

With the recent release of Visual Studio 2015 we also get the new version of C# with some nice new language features. In this post I will talk about one of my favorites, the Null-Condition Operator.
Let’s say I have these two classes (obviously over simplified for the purposes of this article)…

class SalesOrder
{
    public Address SoldTo;
}

class Address
{
    public string PostalCode;
}

If  we then declared an instance of SalesOrder and wanted to access the PostalCode property and trim off the spaces we would do this…

string zip = order.SoldTo.PostalCode.Trim();

This line would work ok only if order, SoldTo and PostalCode were not null, if any of them were you would get a Null Reference Exception. To handle this in the past we would have to create an if statement to check each part to see if it’s null before we try to trim the postal code.

In C# 6.0 we can solve this problem using the Null Reference Operator which makes for much cleaner code.

string zip = order?.SoldTo?.PostalCode?.Trim();

The question mark after each part is the null reference operator. When the code is being executed, if the part before the question mark is null execution of the line will end and null will be returned. So if order, SoldTo or PostalCode is null, zip will be set to null without an exception being thrown. If order and SoldTo both had values and PostalCode was null the null reference operator after PostalCode will prevent Trim from throwing and exception. As soon as the program sees that PostalCode is null it will skip the attempt to execute Trim().

You can also use this on arrays and collections like this…

List<SalesOrder> orders = null; 
order = orders?[1];

If the null reference operator was not used in the second line, this would throw a null reference exception because orders is null. With the operator the order variable will be set to null.

When new features like this are added to the language I always like to take a look at a disassembly of the code to see how the features are implemented. Nothing too surprising here, it’s done pretty much how we would have done it before this feature was added. Note that each part of the assignment is assigned to a variable before the null check is done. This is done in case there is a function call so it doesn’t get called more then once.

    if (salesOrder != null)
    {
        Address soldTo = salesOrder.SoldTo;
        if (soldTo != null)
        {
            string postalCode = soldTo.PostalCode;
            if (postalCode != null)
            {
                str = postalCode.Trim();
            }
            else
            {
                str = null;
            }
        }
        else
        {
            str = null;
        }
    }
    else
    {
        str = null;
    }

Windows Live Writer Problem

Ever since I have been doing my blogs on Blogger.com I have been using Windows Live Writer as my editor. It integrated nicely with Blogger and the PreCode plugin made it really easy to add code snippets to the posts. Every thing was great until last week when I started getting an "Invalid Username or Password" error every time I tried to connect to Blogger from Live Writer.

A quick search of the Blogger and Microsoft support forums revealed that I wasn't the only one having this problem. It turns out the Google recently changed how external application authenticate when they connect to Blogger and even though they announced they would be making this change a while back Microsoft never updated Blogger. Initially the responses from both side blamed the other, but according to recent Twitter post by Scott Hanselman both companies are now working together on a fix that will be available "soon".

 

Update (6/2/2015): This problem has now been resolved. Thanks to Microsoft and Google for getting this issue resolved quickly.

String Concatenation

One of the things beginner .NET developers should have learned is that it’s not a good idea to add strings (e.g. s = s + “text”)  in .NET languages to concatenate them. The reason for this is that .NET string are immutable, meaning they cannot be changed once created. Because of that, each time you add strings it’s creates a new string object which replaces the existing string, thus leaving an object that needs to be disposed of. For the occasional concatenation this isn’t a big problem, but if you are doing this in a loop you could end up creating a lot of unnecessary objects. The solution to this problem is to use the System.Text.StringBuilder object which let’s you concatenate strings without creating extra objects.
In this article I want to take a closer look at string concatenation to understand what is actually happening behind the scenes. Let’s start with this block of code

string s1 = "one";
string s2 = "two";
string s3 = s1 + s2;

If we compile this code and then de-compile it  (in this case I am using Telerik’s JustDecompile) here is the resulting code:

string s1 = "one";
string s2 = "two";
string s3 = string.Concat(s1, s2);

You will notice that the addition of strings was replaced by the compiler with a call to the string.Concat function. Let’s take a look at the code for Concat. I have stripped out some of the code from the function just to show the important part.

int length = str0.Length;
string str = string.FastAllocateString(length + str1.Length);
string.FillStringChecked(str, 0, str0);
string.FillStringChecked(str, length, str1);
return str;

This code uses FastAllocateString to create a new string object that is big enough to hold both of the strings being concatenated. It then uses FillStringChecked to place the two strings in the correct spots in the new string. Finally it returns the new string. Here is where you can see the new object being created which replaces the old object that was in s3 in our original code.

There are versions of the String.Concat function that take up to four strings. So if you were to do s5 = s1+s2+s3+s4, this would call String.Concat(s1,s2,s3,s4). What happens after four strings? For example this code:

string s1 = "one";
string s2 = "two";
string s3 = "three";
string s4 = "four";
string s5 = "five";
string s6 = s1 + s2 +s3 +s4 +s5;

compiles to:

string s1 = "one";
string s2 = "two";
string s3 = "three";
string s4 = "four";
string s5 = "five";
string[] strArrays = new string[5];
strArrays[0] = s1;
strArrays[1] = s2;
strArrays[2] = s3;
strArrays[3] = s4;
strArrays[4] = s5;
string s6 = string.Concat(strArrays);

This version of Concat will scan through the array adding up the total length of all the strings, create a new string this length and then load each string into the correct location. This does create one extra object, the array, but it doesn’t create a new object for each concatenation.

One other thing to note about string concatenations. If for some reason you did something like this, maybe for purposes of code clarity:

string s1 = "one" + "two" + "three" + "four";
Console.WriteLine(s1);

the resulting code will look like this:

string s1 = "onetwothreefour";
Console.WriteLine(s1);

The compiler is smart enough to know that you are adding together 4 string literals so it automatically creates a single literal for you.
In conclusion, concatenation isn’t always bad in .NET programs. As long as all the concatenation happens in one line you don’t end up with a lot of extra objects. But if you are concatenating to a single string over multiple lines or within a loop, it’s a good idea to use a StringBuilder instead.

Excel Graphs with EPPlus

In my last post I showed how to write data to an Excel file using the EPPlus library. I this post we will look at a more advanced topic, how to create a graph on an Excel worksheet.
We will start by using the same code as in my last post to create a new workbook, worksheet, and then add some sample data to it.

using (var package = new ExcelPackage())
{
var workbook = package.Workbook;
var worksheet = workbook.Worksheets.Add("Sheet1");

worksheet.Cells["C1"].Value = "Widgets";
worksheet.Cells["C2"].Value = 20;
worksheet.Cells["C3"].Value = 5;
worksheet.Cells["C4"].Value = 30;
worksheet.Cells["C5"].Value = 32;
worksheet.Cells["C6"].Value = 17;
           
worksheet.Cells["B2"].Value = "Jan";
worksheet.Cells["B3"].Value = "Feb";
worksheet.Cells["B4"].Value = "Mar";
worksheet.Cells["B5"].Value = "Apr";
worksheet.Cells["B6"].Value = "May";

In this sample data we have labels in column B and values in column C. The first step in creating the chart is to create a new chart object in our worksheet, we do this with the AddChart function:

var chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnStacked);

AddChart takes two parameters, the first is a name for the chart and the second is the type of chart. EPPlus supports a large number of different chart types, in this case we will use a stacked column (bar) chart. Next we need to add a data series to our chart:

var series = chart.Series.Add(“C2:C6", "B2:B6");           

The Series.Add function takes two parameters. The first specifies a range of cells that will be used for the values in the series, so we set this to C2:C6 which contains our numeric data. We skip C1 since it contains the column label, we will come back to this in a minute. The second parameter specifies a range of cells that contain the labels for our X-Axis, so we point this to B2-B6 which contains our month names.

If we were to run this now the series would end up with a default name in the legend of the chart. We can fix this using this line:

series.HeaderAddress = new ExcelAddress("'Sheet1'!C1");

With the HeaderAddress property of the series we can set which cell we want the name of the series to come from. Note that for this to work you must specify the full address of the cell including the sheet name. If you leave out the sheet name the name of the series will remain blank.

Since this is a stacked column chart we can add more then one series. If we had additional numeric data in column D we could create a second series like this:

var series2 = chart.Series.Add("D2:D6", "B2:B6");               
series2.HeaderAddress = new ExcelAddress("'Sheet1'!D1");

One thing that is missing from EPPlus is the ability to change the style (color, etc.) of a series, you have to stay with the default styles.

The final step is to save the workbook:

package.SaveAs(new System.IO.FileInfo(@"c:\temp\demoOut.xlsx"));

If you run this you will get the following results:

Writing Excel Files using EPPLus

In my last post I introduced the EPPLus library and showed how to use it to read Microsoft Excel XLSX files. In this post I will show how we can use it to write these files. Writing is pretty similar to reading but there are a lot more options you are likely to use when writing. Here is the basic code you need to create an XLSX file:

using (var package = new ExcelPackage())
{
var workbook = package.Workbook;
var worksheet = workbook.Worksheets.Add("Sheet1");
var cell = worksheet.Cells["A1"];

cell.Value = 100;
package.SaveAs(new System.IO.FileInfo(@"c:\temp\demoOut.xlsx"));
}

We start out by creating an ExcelPackage object. Since we are going to be creating a new file, we don’t pass a FileInfo object to the constructor like we did when reading an existing file. Next we add a new worksheet to the Worksheets collection of the package. Now that we have a worksheet we can access the cells just like we did when reading, in this case we have a variable called ‘cell’ which points to cell A1. Next we put a value in the cell, in this case the number 100. Finally we need to save the file, we do this by calling the SaveAs function on the package. Note, just like when we open a file to read, SaveAs does NOT take a file name as a parameter, but a FileInfo object instead.

Beyond setting cell values, EPPLus also allows you to change the appearance of the sheet. To change the appearance of a single cell we use the Style property on the cell. For example here is how we would change the font properties on a cell using the cell variable we created above.

cell.Style.Font.Name = "Arial";
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 12;

We can change the background color of a cell like this:

cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);

First we have to set the PatternType for the background, in this case we will make it solid. Setting the PatternType is required before you can change the color, the second line will throw an exception if the PatternType is not set. In the second line we set the color. If you are doing this from a console application you will need to add a reference to System.Drawing to be able to use System.Drawing.Color.

Another common Excel formatting task is to create borders around a cell. Here is how we would set a thick border around an entire cell:

cell.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick);

You can also set each part of the border individually by using the Bottom, Top, Left, and Right properties of the Border. Each of these has a Color and Style property to set the appearance of the border.

So far we have been setting the style on a single cell, but we can also use these properties on a group of cells. For example this code will set the color and border on the block of cells between B1 and C10:

var cells = worksheet.Cells["B1:C10"];
cells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Blue);                cells.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.MediumDashed);

You will notice that each cell is set to blue, but the border is put around the whole block, not around each cell.

Besides setting properties on cells you can also work with the properties on columns and rows. For example here is how to set the width of a column:

worksheet.Column(1).Width = 100;

It’s important to remember that columns and rows start numbering at one no zero. EPPLus will allow you to use column and row index 0, but you will get an error when you try to open the workbook in Excel. To hide a column you can do this:

worksheet.Column(1).Hidden = true;

There are also similar properties for rows:

worksheet.Row(1).Height = 20;
worksheet.Row(1).Hidden = true;

These are some of the basic tasks you will do when creating an Excel file, but EPPLus lets you do almost anything with a workbook that you can do directly in Excel.

Working with XLSX files

It’s not unusual when writing applications, especially business applications, then you will have a need to work with Microsoft Excel files. If you are working with .XLSX format files there is an excellent open source library called EPPlus that makes it’s easy to both read and write these files. In my next few posts I want to show how to use this library to handle various tasks. I will start with a discussion of how to use EPPlus to read from existing XLSX files.
First you will need to download the library. The easiest way to do this is to open your Visual Studio Project and in the Package Manager Console type:
Install-Package EPPlus
For the purposes of these posts I will be using version 3.1.3. At the time of this writing Version 4.0 is in the works but it is currently Beta, so I am going to stick with the older stable version for now.
I am going to be doing this demo in a Console application but you could also use the library in a Winforms or even a web application. First you need to import the following namespace:

using OfficeOpenXml;

Now let’s say that I know I have a number in cell B2 of the spreadsheet, here is the code to read that value:

static void Main(string[] args)
{
  using (var package = new ExcelPackage(new System.IO.FileInfo(@"c:\temp\demo.xlsx"))) {
       var workbook = package.Workbook;
       var worksheet = workbook.Worksheets[1];

       double data = (double)worksheet.Cells["B2"].Value;
       Console.WriteLine(data);
  }

  Console.ReadLine();
}

First we need to open the Excel file we want to read. We do this by creating a new ExcelPackage object. ExcelPackage won’t take a filename as a parameter so you have to either pass in an existing FileStream or, as I did in this case, a FileInfo object. I have put everything in a using block to be sure the ExcelPackage gets disposed properly.

Once we have the package open, we get the Workbook object. Workbook contains a collection of all the Worksheets in the file. In this case we will assume there is only one worksheet so we will get the first one into worksheet variable. Note that the Worksheet starts at 1 not 0. Now that we have the worksheet we want to work with we can access any cell by using the Cells property. Here we are getting the Value in cell B2 which will return an object type that in this case we case to a double. In a real application you will want to do some error checking to be sure that cell really contains the type you are expecting.

Another way you can access cells is to use their column and row coordinate like this:

double data = (double)worksheet.Cells[2,2].Value;

Again, note that the rows and columns starting numbering at 1 not 0. This method is useful when you need to iterate over a series of cells. You can also iterate over a specific range of cells like this:

var range = worksheet.Cells["B2:B5"];
foreach (var cell in range)
{
    Console.WriteLine(c.Value);
}

Up until this point we have been using the Value property of the cell which get the raw value. There is also a property called Text which returns what would be displayed in the cell as a string. For example if a cell contained the value .5 and it was formatted as a percentage, Value would return the double value .5, but Text would return the string “50%”. You can also access the formula in a cell by using the Formula property which will return a string containing the formula, or an empty string if the cell does not have a formula.

One final way of querying cells is to use a LINQ query:

var query = (from c in worksheet.Cells["B2:B13"]
                  where c.Value is double
                  && (double)c.Value > 7
                  select c);
foreach (var c in query) Console.WriteLine(c.Address + " " + c.Value.ToString());

This piece of code searches for all cells in the range of B2 to B13 that have a value greater than seven and then prints that address (e.g. B2) and the value. Note that in the where clause we first check if the value is a double in case the cell contains something that can’t be cast to a double.

Enum Tricks

In this article I want to talk about a few tricks for using enums in .NET programs. The examples I will provide are in C# but the concepts apply to VB.NET programs also.
Enums are useful when you have a variable that needs to hold only a specific set of values. For example what if we had a class that represented a sales order and it had a status property that could be set to Pending, Open, Shipped or Closed. We could simply use a string for this but this is very error prone, for example at one point in our program we might set the status to Complete instead of the correct value Closed. This problem can be solved by using an enum like this:

public enum StatusEnum
{
    Pending,
    Open,
    Shipped,
    Closed
}
public StatusEnum OrderStatus {get; set;}  

Now we can only set OrderStatus to one of the values in the enum. What if we have a situation where we still need to deal with the string value for Status, for example if we want to store it in a database. We can handle this by adding a public string Status property to our class then changing the OrderStatus property to a StatusEnum type and having it get and set the string value. Here is what this code would look like:

public string Status { get; set; }

public StatusEnum OrderStatus
{
    get
    {
        return (StatusEnum)Enum.Parse(typeof(StatusEnum), Status);
    }
    set
    {
        Status = value.ToString();
    }
}

The setter part is pretty simple, you just call ToString() on the enum value to convert it to a string. The getter is a little more complicated but still one line. To convert from a string back to an enum we use the Enum.Parse function passing it the type of the enum we want to convert to and the string we want to convert. Any time we want to work with the status in code we can use the OrderStatus property so we can work with the value safely, and then we can use the string Status property when we want to read/write to a database or display the status value.

One important thing to note about this getter function. If the string contains a value that isn’t in the enum, the get will throw an exception. Whenever you want to the user to input a value for status it is best to provide a drop-down list of options so that they cannot enter an invalid value. The Enum class provides a function that makes this easy:

UIOrderStatus.DataSource = Enum.GetNames(typeof(SalesOrder.StatusEnum)); 

This will fill the ComboBox control UIOrderStatus with the values in the enum, this way if you add a value to the enum you don’t have to remember to add it to your drop downs. If you still have situations where the user could provide an incorrect status value, for example if you are importing data from a file, or in the case of a web application were you can’t trust the values coming back from the page you can validate the value like this:

Enum.IsDefined(typeof(StatusEnum), statusName)) 

This will check if the string statusName matches one of the values in the enum. This will return true if there is a match, and false if there isn’t.

Even with all these safeguards you could still have a bug in your program that allows an invalid status value to get through. The exception thrown by the get will be a good thing in this case but it still could be a little tricky to find the bug since we won’t necessarily know where the invalid value was set. To help with this we can make the following change to the string Status property:

public string Status
{
    get { return status; }
    set
    {
        if (!Enum.IsDefined(typeof(StatusEnum), value)) throw new ArgumentException("Status value " + value + " is invalid");
        status = value;
    }
} 

With this code the value is checked at the time the status is set, so we will get the exception immediately which will make it easier to track down the bug.

Now, what if we wanted to provide longer descriptions for each enum value? We could easily write a function that uses a switch statement to provide a description for each enum value, but a better way is put an attribute on each enum item that contains the description, this will allow us to keep the enum and descriptions all in one place. To do this we will use the Description attribute from the System.ComponentModel namespace:

public enum StatusEnum
{
    [Description("Order is pending Sales review")]
    Pending,
    [Description("Order is open and ready for fulfillment")]
    Open,
    [Description("Order has shipped")]
    Shipped,
    [Description("Order has been invoiced and closed")]
    Closed
}

To easily access the description we will write an extension method that can be used on any enum to get an item desription:

public static string ToDescription(this Enum en) 
{
    Type type = en.GetType(); 

    MemberInfo[] memInfo = type.GetMember(en.ToString()); 

    if (memInfo != null && memInfo.Length > 0)
    {
        object[] attrs = memInfo[0].GetCustomAttributes(typeof(DescriptionAttribute), false);
        if (attrs != null && attrs.Length > 0) return ((DescriptionAttribute)attrs[0]).Description;
    } 

    return en.ToString();
}

This function is used like this:

Console.WriteLine(order.OrderStatus.ToDescription());

This will display the description of the the OrderStatus. If the enum value doesn’t have a description attribute that name of the value will be displayed instead.

T-SQL Data type Precedence

I recently ran across a piece of T-SQL code that looked something like this:

DECLARE @test Int = 1

SELECT 
    CASE 
        WHEN (@test = 1) THEN 5.75
        WHEN (@test = 2) THEN CAST(3.25 as DECIMAL(38,0)) 
     END as Result

At first glance you would expect this to return 5.75 as the result, but in fact it will return 6 instead. Why does this happen? A CASE statement in T-SQL is only allowed to return one data type, so each of the WHEN clauses has to return the same type. In the example the two WHENs are returning different types, so SQL uses type precedence to determine which type to use. Since 5.75 cannot fit in a DECIMAL(38,0) because decimals have a maximum precision of 38, so there is no room for the decimal portion of 5.75. To resolve this, SQL casts 5.75 to DECIMAL(38,0) which causes it to round up to 6. If we were to change the DECIMAL(38,0) to DECIMAL(10,0) in this example we would get the expected result, 5.75, because there is now room for the decimal portion.

If the data types in the WHEN clauses are totally different, SQL will use the type with the highest precedence. For example this code will return “1900-01-06 18:00:00.000”.

DECLARE @test Int = 1

SELECT 
    CASE 
        WHEN (@test = 1) THEN 5.75
        WHEN (@test = 2) THEN GETDATE() 
     END as Result

DateTime has a higher precedence then decimal, so 5.75 gets cast to a DateTime. You can the precedence order for all the T-SQL datatypes here.

To avoid any confusion it is always best to be sure you CASE expressions return the same data type in each WHEN clause.

Salesforce Get Deleted/Get Updated

Salesforce has just started releasing the Winter ’14 update which contain a couple new features in the REST API. You can view the full release notes here:

https://help.salesforce.com/help/doc/en/salesforce_winter14_release_notes.pdf

In this post I want to talk about the new Get Deleted and Get Update resources. As the names suggest these resources are used to retrieve lists of recently deleted and update records. These are specifically designed to facilitate replication of data. For example, if you are developing a mobile app you may want to store a local copy of some of the contact fields. These resources will help you keep that local copy in sync.

Get Deleted

First, let’s look at Get Deleted. The URI for this resource looks like this:

/services/data/v29.0/sobjects/Contacts/deleted/
?start=2013-05-05T00:00:00+00:00&end=2013-05-10T00:00:00+00:00

This will return a list of all Contacts that were deleted between the specified start and end dates.
There are a couple things you need to be careful of when specifying the dates. First, the dates are UTC not local time, second they must be in ISO 8601 format and third the start date has to been within 30 days of the current date.

Let’s talk a little more about how to format the dates. If you want to send the date in the “2013-05-05T00:00:00+00:00” format you must UrlEncode it because of the plus sign. This can be done in .NET like this:

System.Web.HttpUtility.UrlEncode(“2013-05-05T00:00:00+00:00”)

Note that you may need to add a reference to the System.Web assembly if you are developing a desktop application. It turns out that there is no simple way to generate this format date in .NET short of using a custom format string. As an alternative you can use a slight variation of this format, "2013-10-08T04:00:00Z". There is a standard format that come pretty close to this but puts a space between the time and date instead of the ‘T’, but this can easily be fixed. Here is an example of how to convert a DateTime variable in local time to the properly formatted UTC time:

dtStart.ToUniversalTime().ToString("u").Replace(" ","T");

When you make the Get Deleted call you will get a JSON result that looks like this:

{
  "deletedRecords" : [ {
    "id" : "003E000000kkhvtIAA",
    "deletedDate" : "2013-10-09T12:17:12.000+0000"
  }, {
    "id" : "003E000000kle9zIAA",
    "deletedDate" : "2013-10-09T19:03:28.000+0000"
  }, {
    "id" : "003E000000kmVqiIAE",
    "deletedDate" : "2013-10-11T11:46:00.000+0000"
  } ],
  "earliestDateAvailable" : "2012-10-28T16:00:00.000+0000",
  "latestDateCovered" : "2013-10-11T12:05:00.000+0000"
}

you will see an array called deletedRecords which contains the id of the deleted record and the date it was delete, again in UTC. In a synchronization scenario you would use these id’s to delete the records from the local copy. The lastDateCovered value is the last date that the query looked at for deleted records. You would save this value and use it as the start date for you next Get Delete query. The API documentation says that earliestDateAvailable is “timestamp (Coordinated Universal Time (UTC)—not local— timezone) of the last physically deleted object.”, although I haven’t been able to figure out what that really means.


Get Updated

Get updated works like Get Deleted, but returns a list of the ID’s of objects that were either added or modified between the specified dates. The URI for this resource looks like this:

/services/data/v29.0/sobjects/Contacts/updated/?start=2013-05-05T00:00:00+00:00&end=2013-05-10T00:00:00+00:00

All the rules for dates are the same as they are for Get Deleted. When you make a Get Updated call you will get back a JSON result that looks like this:

{
  "ids" : [ "003E000000QCZ1kIAH", "003E0000001ZufeIAC", "003E000000kmVoiIAE" ],
  "latestDateCovered" : "2013-10-21T10:59:00.000+0000"
}

The ids array contains the id of each object that was either added of modified. Just like with Get Deleted, lastestDateCovered is the last date that the query looked at for updated records. You would save this value and use it as the start date for you next Get Updated query

Salesforce REST Exceptions

I get a lot of emails from people working with the Salesforce REST who say that they are getting a 400 error (Bad Request) when trying to perform one of the API functions. In this article I will talk about this error and how to troubleshoot it as well as other API errors.
For the purpose of this article I will assume you are using an HttpWebRequest object to make your requests to Salesforce. The function on HttpWebRequest that does the actual work is  GetResponse. This function makes the call to the remote server and retrieves the response. If anything goes wrong with a REST API request on the Salesforce server it will generate an 400 HTTP error response code and then GetResponse will throw a WebException containing that status code. The 400 response code isn’t too useful since there are a lot of things that can cause it, but if you catch the exception you can still access the response stream which will contain a JSON object with more details.
As an example, if we make a call to get an Access Token and pass an incorrect client_secret you will get this web exception:
"The remote server returned an error: (400) Bad Request."
If we then get the response stream we find this:
{\"error\":\"invalid_client\",\"error_description\":\"invalid client credentials\"}
Here is another example. If we issue a query command with a syntax error in the SOQL query (SELEC id,accountNumber,name from account), you will get this:
{ \"message\" : \"unexpected token: SELEC\",\n  \"errorCode\" : \"MALFORMED_QUERY\"}
You can see that it didn’t recognize “SELEC” since it is missing the ‘T’. Note that the format of the JSON in this message is different then the first one.
So what is the best way to handle this? There are a number of options depending on you needs, but here is one suggestion. Here is the piece of code that makes the web request that would be in a library used by all Salesforce REST calls. ‘req’ is  a HttpWebRequest object.

req.Method = "GET";
System.Net.WebResponse resp = null;
try
{
    resp = req.GetResponse();
}
catch (WebException ex)
{
    string msg = "";

    if (ex.Status == WebExceptionStatus.ProtocolError)
    {
        resp = ex.Response;
        msg = new System.IO.StreamReader(resp.GetResponseStream()).ReadToEnd().Trim();
    }

    throw new SalesforceException(msg, ex);
}

In the try block we make the call to Salesforce and get the response. Next we catch only WebExceptions, other exceptions are allow to percolate up to the next level which is a good practice for library functions. Next we check that status of the WebException. There are a lot of things that can cause WebExceptions but most of them will result in a response not being returned, so we just look for a ProtocolError. If we do have a protocol error we retrieve the JSON error message from the response. Finally we create and throw a custom exception I call SalesforceException that will hold both the JSON message and the original exception in the InnerException. The code for the SalesforceExpcetion is here:

public class SalesforceException : Exception 
{
    public SalesforceException(string message, Exception innerException)
        : base(message, innerException) { }
}

One other thing to note about Salesforce REST errors. Since REST commands are based on URIs, if you have an error in the URI you will normally get back a HTTP 404 error instead of a 400 error. For example if we send a read command with an invalid object ID in the URI, we will get a 404 error instead of a 400 error.