Which sql-server date-time type to use?

Sql Server offers several datetime types. Here I will offer quick&simple guidelines on how to pick the right one for your specific need.
The basic idea is that we want maximum precision for minimal storage space:

• If all you need is accuracy by date, irregardless of time, use the date type. It uses 3 bytes.
• If you need date + rough time value (accuracy level - minutes), use can the smalldatetime type. It uses 4 bytes. However there is another point to take into consideration here - Microsoft doesn't recommend using smalldatetime, quote: "Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable." So you should use smalldatetime only if the storage benefit (2 bytes less than the next best option) really justifies using a less portable type.
• The next type of choice is datetime2. This type completely replaces the datetime type - it's more accurate, more sql-compliant, and can be configured to use less storage space. While datetime always uses 8 bytes, datetime2 can use 6, 7 or 8 bytes, depending on the requested precision (1-7, which indicates how many digits are kept for fractions of a second)
• The best precision you can get for 6 bytes is 2 digits for seconds fractions (i.e. hundreds of a second - such as 2017-02-05 08:55:39.83). If this precision is enough for you - specify datetime2(2) as your type.
• The best precision you can get for 7 bytes is 4 digits for seconds fractions (for example: 2017-02-05 08:59:24.1733). So if this precision is enough for you, specify datetime2(4) as your type.
• If the latter still doesn't meet your need, than you must use 8 bytes, The best precision you can get here is 7 digits for seconds fractions (example: 2017-02-05 09:01:57.9833333). In this case, specify datetime2(7) as your type. 

Note that datetime2(7) is the most precise date-time type Sql Server offers. As comparison, the datetime type offers only 3 digits for second fractions, while datetime2(7) offers 7, So there is no more need to use the datetime. In fact, even datetime2(4) is more precise than datetime, while it only uses 7 bytes instead of 8.

There is another Sql Server date-time type - datetimeoffset. It's beyond the scope of this post. In essence, it's composed of datetime2 + an additional indication of local time (offset from UTC). It may be useful in multi time-zone scenarios.

Adam Porat is a senior c# developer at Travolutionary.

How to use a dictionary wisely

A dictionary is a touchy object - if you try to access a non-existant key, you get an exception. If you try to add an item using the Add() method which already exists in the dictionary - you get an exception. So, particularly in a multi-threaded environment, it is worth knowing some dictionary tricks

1. If you want to retrieve an item which may not be in the dictionary, use TryGetValue() method. This way you access the dictionary only once, but accomplish 2 things - you get the item if it exists, or safely know that it doesn't. The alternative - using ContainsKey() method and then accesing the item directly using its key - is not as good, because it envolves accessing the dictionary twice - once to check if the key exists, and second to get the item. And, if the item has been removed between these two actions - you get an exception on the second action of retrieving the item.

2. When you want to add an item to the dictionary, don't use the Add() method, use the indexer. Add() throws an exception if the item already exists, and usually (not always) this is not desirable. So, do this: dict[key] = value; This way, if the item exists, it is silently overriden.

Also note, in multi-threading environment which includes any add/remove operations, any access to the dictionary, including read operations, must be protected with a lock, as Dictionary is not thread-safe and a read operation which happens at the same time as an add/remove operation may encounter an exception. In any multi-threading context, consider using ConcurrentDictionary class.

Adam Porat is a senior c# developer at Travolutionary.

Migrating from WCF MessageSecurity with custom username/password to TransportSecurity with custom username/password

In performing a migration from WCF MessageSecurity with custom username/password to TransportSecurity with custom username/password, I learned some things the hard way. So they are therefore documented here.

First, TransportSecurity does not support custom username/password. If you try to use TransportSecurity with clientCredentialType="Basic", you get an exception "The HTTP request is unauthorized with client authentication scheme 'Basic'. The authentication header received from the server was..."So you need to use "mixed mode", which is configured like this:

<security mode="TransportWithMessageCredential">

      <message clientCredentialType="UserName"/>

 </security>

Good links to study more on this are here and here.

When doing this migration, if you have been using a test certificate, it might not suit its new use. The test certificate for TransportSecurity should be created with a locally-created CA (Certificate Authority). Also, the name of the certificate (CN/SubjectName) must be the same as the service domain (for example service1.hunterhrms.com), otherwise you get an exception "The remote certificate is invalid according to the validation procedure".
So create your test certificate according to this link. Note that the test certificate does not have to be installed on the client, only the CA.
To complement the data in the above link:
To create the certificates, open a cmd window as administrator and navigate to Microsoft SDK's bin folder.
From there, to create the CA run something like:
> makecert -n "CN=NiloosoftCA" -r -sv NiloosoftCA.pvk NiloosoftCA.cer
To create the test certificate, run something like:
> makecert -sk service1.hunterhrms.com -iv NiloosoftCA.pvk -n "CN=service1.hunterhrms.com" -ic NiloosoftCA.cer -sr localmachine -ss my -sky exchange -pe 

You can write "CN=*.hunterhrms.com" instead of the "CN=..." above, to allow the certificate to be used on all services with the given domain (wildcard certificate). Otherwise, you would need to create a separate test-certificate for each service. 

Service Configuration:

In the service behaviour, no need for the <serviceCertificate> element under <serviceCredentials> - the certificate is set on the IIS site's https binding (just like any https site).

Client-side client endpoint configuration:

I haven't found the <identity> element to be of any meaning.

Also, in the endpoint behaviour, no need for the <serviceCertificate> element under <clientCredentials>. Looks like the client simply checks the server's certificate against an existing Certificate Authority.

The writer is R&D team leader at Niloosoft Hunter HRMS

how to organize a multi-type elasticsearch query

Suppose your search involves quering a parent type as well as one or more of its child types.

Well, it's pretty obvious that you should use a Bool Query to combine the queries of the different document types.

However, suppose each type involves both a query and a filter. How would you then combine all the queries and filters together?

My first attempt at this was to use a single Filtered Query for the entire query, and each type would contribute to the overall FilteredQuery/query and FilteredQuery/filter.
I later found out this model sometimes produced incorrect search results, was complicated and perhaps not so efficient.

The more logical way to do this, is that each type produces its own Filtered Query, and the top Bool Query mearly joins those filtered queries together. This way, each type has it own independant clause, which results in a simple and clean overall query structure. And the search results are always correct, too.

The writer is R&D team leader at Niloosoft Hunter HRMS

Clarifying elasticsearch TopChildren, "factor" & "estimated hits size"

I found the TopChildren documentation to not be totally clear. So here is my clarification.

The "estimated hits size" (also reffered to in the documentation as "hits expected") referes to the number of child documents hits. That is to say - how many child documents will be looked for in the query on the child docs.

The set of child documents thus found, are then aggregated into parents.

If you asked for 10 parents (query size=10), elasticsearch will use the default factor value of 5, and search for 50 child documents (the "hits expected" as mentioned above). The found documents will then be aggregated into parent documents. 

In case several child docs belong to the same parent, the aggregation may result in less parents than asked for. In this case, if there are additional child documents to query, elasticsearch will expand the query to include more child doc, using the incremental_factor parameter.

The total_hits in the response would not be accurate if the "estimated hits size" is less than the number of child documents which actually match the query. The larger the "estimated hits size" is (controlled by the factor parameter), the larger the potentiall total_hits. But this of course hurts performance.

An additional factor to be aware of, is that the x amount of parent documents is the number of docs returned by the TopChildren query itself. This amount may be further reduced by adjacent or higher -level queries/filters.

If this short explanation clarifyed things for you, please leave a comment and let me know :)

The writer is R&D team leader at Niloosoft Hunter HRMS

Java static class Vs. C# static class

While static fields and methods in Java and C# have the same function and meaning, static classes are different.

In C#, a static class may contain only static members, and cannot be instantiated into an object. It is also sealed - another class cannot inherit from it.

In Java, a static class is called a static member class. It must be declared inside another class. It is actually a regular class - it has none of the restrictions mentioned above for C# static classes. It is similar to a regular nested class in C#. So what's "static" about it? It can only reference static members of its containing class. This is in contradiction to a Java nonstatic member class, which automatically holds a reference to its containing class via the "this" keyword.

In Java, to make a static class behave more like a C# static class (namely to prevent its instantiation into an object), make its constructor private.

The writer is R&D team leader at Niloosoft Hunter HRMS

How to create a certificate for Google Domain Registration

As a developer in Niloosoft HunterHRMS, I needed to register our domain with Google in order to start integrating with Google Docs. As part of the registration process, I had to submit a certificate file. Creating the file has been an unpleasant process, as even Google's instructions are very insuffcient. Therefore, for myself and others, I hereby summarize the exact step-by-step instructions on how to create this precious file.
Google currently requires a pem cert file.

1. Download open ssl to the computer
2. Create 2 environment variables on the computer:
   A. Name: RANDFILE            Value: .rnd
   B. Name: OPENSSL_CONF  Value: The full file name of the openssl.cnf file. For example: D:\Program Files\openssl-0.9.8k_X64\openssl.cnf  (To reach Envrironment Variables editing window: computer -> properties -> Advanced system settings -> Advanced tab -> Environment Variables button.)
3. Run openssl.exe (only after the environment variables have been created!)
4. Create private key file + certificate file:
(Note: you will be prompted to enter the certificate information. When you are asked for the Common Name, enter the domain - such as www.hrms.me)
OpenSSL> req -x509 -nodes -days 365 -newkey rsa:1024 -sha1 -keyout myrsakey.pem -out myrsacert.pem
5. Create a pfx certificate (used by .Net) based on the pem private key + certificate:
(Note: you will be prompted for a password. It may be left empty)
OpenSSL> pkcs12 -export -in myrsacert.pem -inkey myrsakey.pem -out CertForGoogle.pfx -name "Cert for Google".