ADO.Net rounding the DateTime parameter

I have been trying to debug a bug that was causing the records being returned out of the date window. It took me some time to realize that ADO.Net was rounding the C# DateTime and that was causing the invalid records to be returned.

My table looks somewhat like this:

and the code was:

using (SqlConnection conn = new SqlConnection("server=localhost\\SQLExpress;Database=TestDB;Trusted_Connection=True"))
using (SqlCommand cmd = new SqlCommand("SELECT * FROM MyTestTable WHERE CreatedAt <= @dateTimeParameter", conn))
{
if(conn.State != ConnectionState.Open) conn.Open();
DateTime dtParameter = new DateTime(2017,03,20);
dtParameter = dtParameter.AddDays(1).AddTicks(-1); //The last moment on particular date
cmd.Parameters.Add(new SqlParameter("@dateTimeParameter", SqlDbType.DateTime) {Value = dtParameter});
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
}

With this code in place, I was trying to get the records for the last moment on March 20th. I ended up using the following code to get the last moment on the particular date.

DateTime dtParameter = new DateTime(2017,03,20);
dtParameter = dtParameter.AddDays(1).AddTicks(-1);

But when this code was executed, I got the records for the next day as well, which was clearly not intended. I tried SqlDateTime type as parameter, but it still had the same effect.

 var sqlDateTime = new SqlDateTime(dtParameter);
 cmd.Parameters.Add(new SqlParameter("@dateTimeParameter", SqlDbType.DateTime) { Value = sqlDateTime });

One thing I noticed using SqlDateTime is that the Ticks rounded to 636256512000000000 from 636256511999999999, during the conversion.

This forced me to look at the reference code for SqlDateTime, the constructor called method to get SqlDateTime from DateTime, at the end of method chain, I found the method with the following comment

// Convert from TimeSpan, rounded to one three-hundredth second, due to loss of precision
        private static SqlDateTime FromTimeSpan(TimeSpan value) {

This was the reason the DateTime was getting rounded to the next day. The fix was to use Milliseconds to get the last moment of the day. Even passing a single millisecond wasn’t enough , I had to use 2.

   dtParameter = dtParameter.AddDays(1).AddMilliseconds(-2);

This fixed the issue and I was able to get the records for the date window.

How not to write SQL queries in C#

Everyday , I see questions on Stack Overflow with string concatenation to form SQL queries. Not only this approach is difficult to debug, it is prone to SQL Injection .

Code like below is usually seen multiple times with respect to executing SQL queries in .Net (C#).

SqlConnection connection = new SqlConnection("ConnectionString");
string sqlQuery = "SELECT COUNT(*) FROM Users where Username = '" + txtUserName.Text + "';";
SqlCommand cmd = new SqlCommand(sqlQuery,connection);

 

The code above would simply break if the input (username) would contain character ('). This would cause invalid SQL to be created as a result of string concatenation and that statement would eventually cause exception during execution.

The other major issue is SQL injection, any attacker could pass in  malicious SQL statements in the input. An example would be (from wiki article):

a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't

With the above input the sql statement would look like as below that could potentially delete the users table :

SELECT COUNT(*) FROM Users where Username = 'a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't';

Solution

Very simple, use parameters.  One could implement methods to sanitize the input, but it is difficult to code against all the possible approaches and characters. Using parameters saves us from explicitly sanitizing inputs.

 string sqlQuery = "SELECT COUNT(*) FROM Users where Username = @username";
 using (SqlConnection connection = new SqlConnection("ConnectionString"))
 using (SqlCommand cmd = new SqlCommand(sqlQuery, connection))
 {
 cmd.Parameters.AddWithValue("@username", txtUserName.Text);
 int count = (int) cmd.ExecuteScalar();
 //......rest of the code
 }

I used AddWithValue  method above while adding parameters. It is useful as long as the data types are correctly matched, but if there is ambiguity in data types on SQL Server end and C# then use Add method and explicitly specify data type like:

cmd.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar) {Value = txtUserName.Text});

One more thing is it to enclose SqlCommand and SqlConnection objects in using statement. This will ensure proper disposal of resources (closing connection etc.)

Popular Bobby Tables 😉

Image from: https://xkcd.com/327/

 

String comparison mistakes in C#

I have seen lots of questions on Stack Overflow where String.Compare  is used to compare strings if one needs case insensitive comparison. I have even seen production code using the same method.  I guess the usage is due to most of the developers having c/c++ background and used to methods like strcmpi. 

Code like:

string str1 = "TEST";
string str2 = "test";
bool caseInsensitiveEqual = String.Compare(str1, str2, StringComparison.InvariantCultureIgnoreCase) == 0;

is often found and considered a usual practice when it comes to case insensitive string comparison.

Another way is converting both of the parameters to either upper or lower case and then doing the comparison like:

string str1 = "TEST";
string str2 = "test";
bool caseInsensitiveEqual = str1.ToUpper() == str2.ToUpper();

Both of these approaches would work for most of the time, but there are issues:

• String.Compare  is used for determining position in sort order (which string is bigger than other)
• Using ToUpper or ToLower could case incorrect results for strings in different cultures. (The Turkish İ Problem and Why You Should Care)

Solution:

Use the framework provided String.Equals group of methods.

string str1 = "TEST";
string str2 = "test";
bool caseInsensitiveEqual = String.Equals(str1, str2, StringComparison.InvariantCultureIgnoreCase);

It has overloads to specify string comparison approaches using StringComparison Enumeration

Offline mapping in Windows applications using C#

Imagine a windows desktop computer without internet access that should have the ability to show maps and perform spatial operations(routing, navigation, searching etc), this would require offline mapping application.

Creating an offline mapping solution for windows application is rare and should be avoided. I say, should be, because there are alternatives to developing an offline mapping solution from the scratch. Having said that, there are some possible ways for doing it. Few things to consider are:

Data

Data in GIS applications is of type Vector or Raster. We will be dealing with Vector data in this article. (See Vector vs Raster data in GIS)

One of the starting point is to collect offline data.

1. You can ask TeleAtlas, NAVTEQ to provide offline data. This will involve licencing fee and NDA. This option could cost a lot
2. The other options is to use http://www.openstreetmap.org, Download its data and then work with it.

The other thing to consider is the data format. There are number of formats available to work in GIS applications. You can either use a proprietary one, or Shapefile format which is ESRI standard.

Application Development

After selecting data, the next step is to plan for application development. There should be three major steps involved in development of a mapping application.

• Reading Data
• Rendering data (geometries)
• Optimization

First step starts with writing code to read/access data from offline source. This could be a ShapeFile or data stored in Spatial database like PostGIS, Spatialite etc. The approach should be to read data and store in appropriate objects for point, line and polygons.

Once the data is read into objects, the next step is rendering that data on screen, This involves converting geographical coordinates to 2D or 3D map projections on screen. Here is an excellent code project article about it: Writing GIS and Mapping Software for .NET. After transformation of data, the transformed objects are converted to geometry objects and placed on WinForm or WPF window. This might involve a bit of reading about GDI, DirectX, OpenGL or a simpler version of System.Drawing in WinForm or geometry drawing in WPF.

Later comes the optimization part. Map data is usually huge and consists of millions of geometries for a city/metropolis. The optimization in reading and more importantly rendering usually requires lots of work.

Layering

There has to be a layering technique involved and then how to show a limited number of geometries. Take an example of google maps, When you are viewing google maps at country level, you can only see city names, city/state/province boundaries, major roads etc, but it doesn’t show street level details. But as soon you start zooming in, one can see the details getting higher and higher. A similar approach could be used in displaying offline maps.

• Layers for City names (major Point of interest) (Points)
• Layer for Country/City polygon (Polygon)
• Layer for major road networks (Line strings)
• Layer for intercity highways (Line strings)
• Layer for street level roads (Line strings)
• Point of interest at street level (Points)

Once the data is setup in these layers, the application can use defined rules to show layer at a specific zoom level. Remember showing geometries is resource intensive, so only show that would be useful at a particular zoom level. For example if zoom levels are defined 1 to 10, where 10 is the highest zoom level, where the whole country would be visible in the application, then at zoom level 10 there is no need to show street level roads or even inter city highways.

Geometry Normalization

After determining the layers and their respective zoom level, there is another optimization technique that I have used in a similar application. That is to normalize geometrical data. For example a major highway line string contains 1 million points connected with each other forming a line string. The reason that particular geometry has so many points is to provide accurate information for navigation, but, these many points would be irrelevant if the major highway is show at the highest zoom level. A normalized geometry object could be used.

The diagram above shows original line string in blue colour, which would have a lot of points, and the red geometry shows normalized geometry with lot fewer points. This could be used at the highest zoom level for showing highways.

Open Source alternatives

There are few open source projects which does the exact same thing and much more. They are:

• SharpMap
• DotSpatial

There are other controls which are available for some licencing fee:

• cartotype.com
• TatukGIS
• osmand.net

C# – There is no “Pass by Reference” without ref keyword

One common question that I see on Stack Overflow is about parameter passing in C#. A common statement for parameter passing sounds like:

Reference types are passed by reference and value types are passed by value

This is incorrect.

Before jumping on the details for parameter passing, I would ask you to visit this excellent article by Jon Skeet on parameter passing in C#. 

I will concentrate on parameter passing with reference types, as this is the main reason for confusion.

Consider the following example

using System;
namespace TestApplication
{
	class Program
	{
		static void Main(string[] args)
		{
			Person personMain = new Person { Name = "X", Id = 1 };
			ModifyPersonObject(personMain);
			Console.WriteLine(personMain.Name);	// prints "New Person Name"

			ModifyPersonObjectFail(personMain);
			Console.WriteLine(personMain.Name);	// still prints "New Person Name"

			Console.ReadLine();
		}

		static void ModifyPersonObject(Person person)
		{
			person.Name = "New Person Name";
		}

		static void ModifyPersonObjectFail(Person person)
		{
			person = new Person { Name = "Some Name", Id = 2 };
		}
	}

	class Person
	{
		public int Id { get; set; }
		public string Name { get; set; }
	}

}

In the above code a person object is created with initial values as “Name: X and Id: 1”. Then a method is called which is suppose to modify that person object. That method receives a paramter of type “Person” and modifies its Name property. The effect is visible after the call to method is completed.

Next, that object is send to another method which is suppose to assign a completely new “Person” object to it. But that fails and after the call to “ModifyPersonObjectFail” method is completed, the object “personMain” is still holding hold values.

Why is this happening.

A reference is kind of a pointer to some memory location holding the actual data. For example in our above code “personMain” is a reference which is pointing to a person object in memory with values Name: X and Id: 1. So in the first method call: “ModifyPersonObject(personMain);”, the parameter in the method is pointing to same memory location.

So, both “personMain” and “person” (parameter) are pointing to same location, thus changing “Name” property in the method actually modifies the value in object storage memory. .

Next is the method call to “ModifyPersonObjectFail(personMain);”, At the entry point in the method, both “personMain” and paramter “person” is pointing to same object, just like the diagram above, but this method has a line:

person = new Person { Name = "Some Name", Id = 2 };

which is assigning a new object to the parameter “person”. Now here is the important part, this line is actually creating a new object in object storage memory and assigning its address/reference  to the parameter. The original object in memory remains unaffected.

Therefore, when the call to method “ModifyPersonObjectFail” is completed the original object in the caller remains unaffected.

So to conclude here is the main statement

There is no pass by reference in C# without “ref” keyword. In parameter passing address of the object (or reference) is only passed.

If you have method like:

static void ModifyPersonObjectByReference(ref Person person)
{
	person = new Person { Name = "Some Name", Id = 2 };
}

and then call that method like:

static void ModifyPersonObjectByReference(ref Person person)
{
	ModifyPersonObjectByReference(ref personMain);
}

then this would be somewhat pure pass by reference, as now even assigning a new value to parameter would be visible in caller.

C# 6.0 – Changes in New Features

(The Roslyn project and documentation has been moved to GitHub)

C# 6.0 is the new upcoming version of C#, it is part of open source compiler platform (Roslyn) by Microsoft. The new compiler is written in C# as well, (How is that possible see this and this)

There are many new features which are planned to be released with the new version, One can find the detailed documentation about them here.

Here are few which have changed from their initial planned version.

Static Import

This feature allows a user to access (allowable) static members of a class/type, without a qualifying class name.

Consider the example of

Console.WriteLine("Some test message");

With the static import, we would be able to to call method “WriteLine” directly without using the class name “Console”.

The current version of C# 6.0 supports Static Import but with the explicit keyword “static” in “using” statement.

using static System.Console;
namespace TestApplication
{
	class Program
	{
		static void Main(string[] args)
		{
			WriteLine("My test message");
		}
	}
}

 The previous planned version for C# 6.0 had static import feature without “static” keyword in using directive.  

“static import” feature might case a breaking change in very rare situation. Consider the following example with respect to “Extension Methods”

Consider the following code example in prior version of C#.

List<int> myList = new List<int> { 1, 2, 3, 4, 5, 6, 7 };
var query = Enumerable.Where(myList, r => r % 2 == 0);

In the code above extension method “Where” is called like a static method, instead of like an instance method on “Enumerable<T>”

But if we use “static” import feature, one would thought that we should be able to do something like:

using static System.Linq.Enumerable;
//.... class definition 
List<int> myList = new List<int> { 1, 2, 3, 4, 5, 6, 7 };
var query = Where(myList, r => r % 2 == 0); //ERROR! 

But it will error out with

The name ‘Where’ does not exist in the current context

Since “Where” is defined as an extension method. But using “Enumerable.Range” like below will not cause any error as “Range” is an ordinary static method and not an extension method. :

var items = Enumerable.Range(0, 10);
var items2 = Range(0, 10);

So now changing an ordinary static method to extension method could cause a breaking change, which wasn’t the case with prior versions of C#.

Null-Conditional Operator

Only the name/term has been changed, earlier version had Null Propagation operator. The feature is useful in a way that it provides a safe navigation operator on an object properties in case of the object being null.

int[] myArray = null;
//...some code
int? length = myArray?.Length;