Dates and numbers when using locales other than en-US on the example of Test.SqlServer

[Andrei-Errapart]

Hi Joel,

I cannot change the regional settings the SQL Server and the computers have to use here. The SQL Server variable @@language returns 'Deutsch' and the regional settings are also German ("de-DE").

The following problems were encountered when running Test.SqlServer:

1. Wrong format for the datetime strings. This happened during insert. The cure was to add "Current Language=us_english; " to the connection string.
2. Now the format of the number 123.456 was "123,456" in the German locale and it was of course wrong for the SQL Server. This was fixed by adding the following to the beginning of Main:
   System.Globalization.CultureInfo.CurrentCulture = System.Globalization.CultureInfo.InvariantCulture;

I can think of the following options:

1. Use the abovementioned fixes. I am not sure about alphabetical ordering; the initial tests show that it might work.
2. Check all the uses of ToString() and put System.Globalization.CultureInfo.InvariantCulture everywhere.
3. Create a function to format datetimes and strings according to the column type (as fetched from the database) and the CultureInfo.InvariantCulture.
4. Skip conversion to string by stashing everything to query parameters. This makes debugging queries harder than it needs to be. On the plus side, all conversion woes should disappear and the library will tolerate any locale settings on both user side and SQL Server side.

There is a related problem, namely, I cannot insert into tables with columns of type "datetime", because there are too many decimal points and the SQL Server cannot convert it. Although the decimal points are zeroes, but still. Maybe just drop the extra zeroes?

Which options of the above are "no-no"? Is there any "right" solution? I would give implementing it a try.

I would be happy for all the input.

[jchristn]

Hi @Andrei-Errapart thanks for your note and for all of the details you've supplied. This gives me a lot of context on the issue you're encountering.

For item 2 (fixed by adding following to the beginning of Main) - are you referring your program's Main method?

In terms of the options:

Solution 1
a) It may be worthwhile to have constructors that allow the developer to specify the connection string, e.g. public DatabaseClient(DbTypeEnum dbType, string connectionString). This would address at least the first of the two issues.
b) In general I'm not a fan of imposing statements in user code (e.g. the change to main you propose)

Solution 2
This is probably the right approach

Solution 3
Also probably the right thing to do, in conjunction with solution 2

Solution 4
This one would be really, really difficult to do. Right now there is zero parameterization in the library (though there is extensive sanitization for safety). Realistically, this is the best solution, but far and away the hardest.

As far as the DateTime issue, can this be addressed by adjusting the Helper.TimestampFormat and Helper.TimestampOffsetFormat properties?

If you're open to a PR with solution 2 and 3, I would absolutely love it!

Cheers,
Joel

[Andrei-Errapart]

@jchristn , long time no see. I always wanted to get it done ASAP, but somehow the time for that was never there.

Option 4 has been implemented for DatabaseWrapper.SqlServer and will open a PR. When the solution for SqlServer has been hammered to satisfactory state, I will implement it for other databases, too.

Regarding other options:
2. Using InvariantCulture for type conversions was OK for one of our database, but not for another one - they were in different locales.
3. This solution seemed to me the same amount of work as 4, but more fragile.