Retrieve datetime value from SQL Server table using C# Sql Reader

Question

I am querying a SQL Server table to get a value that is of the datatype datetime in the table.

The item in the database is this: 2014-05-17 23:52:09.333

The following code throws this exception:

Unable to cast object of type 'System.DateTime' to type 'System.String'.

Code:

internal List<string> CustomSqlQuery(string dbName, string dbTable, string dbColumn, string query, string connString)
{
        var databaseItems = new List<string>();
        var conn = new SqlConnection(connString);
        var cmd = new SqlCommand(query, conn);

        try
        {
            using (cmd)
            {
                conn.Open();
                var reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        // exception thrown on the following line
                        // Unable to cast object of type 'System.DateTime' to type 'System.String'.
                        var item = reader.GetString(reader.GetOrdinal(dbColumn));
                        databaseItems.Add(item);
                    }
                }
            }
            conn.Close();
        }
        catch (Exception exception)
        {
            Logger.Log(Loglevel.Error, "Boom: {0}", exception.Message);
            return null;
        }
        return databaseItems;
    }

How do you convert the datetime into a string?

This code is broken at a fundamental level. It forces you to build queries in a way that will be vulnerable to sql injection attacks. — Joel Coehoorn
– Joel Coehoorn, Commented May 18, 2014 at 0:30
@Joel How do you make this example less vulnerable to SQL injection? Is it just a few things? I'll read about it and see how to do that. — Jonathan Kittell
– Jonathan Kittell, Commented May 18, 2014 at 0:38
Hard to know where to start with this. It's a bad idea anyway, but at least return a list of object, seeing as this function has no idea how the data is typed. — Tony Hopkinson
– Tony Hopkinson, Commented May 18, 2014 at 0:40
To avoid the security issue, the function also needs to ask for a collection containing parameter information, so that you can build a parameterized query. — Joel Coehoorn
– Joel Coehoorn, Commented May 18, 2014 at 1:06
Table name can't be a parameter. By the time you've finished really securing something like this, you might as well have said stuff it and just used linq. — Tony Hopkinson
– Tony Hopkinson, Commented May 18, 2014 at 1:13

MarcinJuraszek · Accepted Answer · 2014-05-18 00:12:17Z

4

That column is typed to be a datetime, so you have to retrieve it using GetDateTime method call.

If you want all values to be retrieved as string, not matter what is the correct type in database, you can do it with GetValue and ToString method call:

var item = reader.GetValue(reader.GetOrdinal(dbColumn)).ToString();

However, you should be aware that it may throw an exception, when one of the values is null. To avoid that, use following:

var value = reader.GetValue(reader.GetOrdinal(dbColumn));
var item = value == null ? string.Empty : value.ToString();

answered May 18, 2014 at 0:12

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tony Hopkinson Over a year ago

If this is a solution, then you need to change the name of your function to CustomSQLQueryThatOnlyDealsWithADateColumn

MarcinJuraszek Over a year ago

Using GetValue will let you deal with all types, and returns string representation of value.

Tony Hopkinson Over a year ago

Saw GetDateTime at top didn't notice GetValue. Course returning dates as strings gives me the heebies...

Collectives™ on Stack Overflow

Retrieve datetime value from SQL Server table using C# Sql Reader

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related