Search code examples
c#sqlsql-serverglobalizationcultureinfo

Some doubts related to the C# CultureInfo and the relation with SQL Server

I am pretty new in C# and I have the following doubt about how work the System.Globalization.CultureInfo class.

I have the following situation.

In a class I read some value (from an XML using XPath) that contains some informations as some data field.

So I have something like it:

System.Globalization.CultureInfo cultureEN = new System.Globalization.CultureInfo("en-GB");

currentDeepSightVuln.LastUpdated = DateTime.Parse(n_alertdocument.SelectSingleNode("./x:LastUpdated", nsmgr).InnerText, cultureEN);

In my XML file this field is:

<x:LastUpdated>2014-05-21T17:00:38</x:LastUpdated>

So it value is 2014-05-21T17:00:38

Running my program when it is performed the previous operation (that initialize the LastUpdate object property) I have that the **LastUpdate property value is: {21/05/2014 17:00:38}.

As you can see it have another format than the XML field value. I think that this is pretty normal because it is converted basing it on the en-GB settings.

My doubt is: if then I save my object on a database table (Microsoft SQL Server) can I have some problem or it is reconverted in the corrected form from SQL Server?

EDIT 1: To insert my object into db I use something like it:

public long insert(DataModel.Vulnerability.Vuln v) {

        _strSQL = "";
        string strSQLParametri = "";
        string query = "";

        long newId;
        long VulnerabilityAlertDocumentPkValue;

        System.Data.Common.DbCommand command;
        command = _connection.CreateCommand();
        try
        {
            _transactionBegin();
            // [VulnerabilityAlertId] insertion on the DB:
            if (v.VulnerabilityAlertId != null)
            {
                _strSQL = "INSERT INTO VulnerabilityAlertDocument ( [VulnerabilityAlertId] ";
                strSQLParametri = " VALUES (@VULNERABILITYALERTID ";
                _addParameter(command, "@VULNERABILITYALERTID ", newId);
            }

            ........................................................................
            ........................................................................
            ........................................................................

            _strSQL += ",[PUBLISHED] ";
            strSQLParametri += ", @PUBLISHED ";
            _addParameter(command, "@PUBLISHED", v.Published);

            ........................................................................
            ........................................................................
            ........................................................................

            query = _strSQL + " ) " + strSQLParametri + " );";
            command.CommandText = query;
            _executeNoQuery(command);

            VulnerabilityAlertDocumentPkValue = _getIdentity();
            Debug.WriteLine("PK della tabella VulnerabilityAlertDocumentPkValue: " + VulnerabilityAlertDocumentPkValue);

Tnx

Solution

  • This isn't actually about culture, isn't about en-GB, and isn't about SQL server; more simply, xml defines dates as specified in ISO 8601 format. That's the end of it.

    You should not use DateTime.Parse specifying en-GB with xml. That is incorrect.

    A correct implementation would be:

    DateTime when = XmlConvert.ToDateTime(...);

    Or more conveniently, if using XElement etc:

    DateTime when = (DateTime)el;

    If you want to talk about dates in SQL server away from xml, then simply: treat them as date or datetime (rather than strings) - either via typed columns or as typed parameters. Then there is never any confusion over format or locale.

    With your edit, again this is unrelated to xml. Assuming that v.Published is a DateTime, and [PUBLISHED] is a datetime, then it should work fine - these values are never strings. A DateTime / datetime is actually a number (some defined interval into a defined epoch). And since they are never strings, again: culture and format does not become involved.