When to use the DataReader Get___ vs GetSql___ Methods in C#?

Question

When pulling data from a Microsoft SQL Server using the DataReader from System.Data.SqlClient, it appears that I can use either Get or GetSql commands (for example GetString or GetSqlString.)

When would I use GetString over GetSqlString? Or the other way around?

I've searched the Microsoft documentation and Stack Overflow, and while I can find examples of how to use either method, I have yet to find a why.

It seems there are some hints it might be something to do with handling null values?

I'm working on creating a Web Api project, and have been reading in strings using the method below, and thought possibly my method had some danger I wasn't aware of, but it seems to handle nulls fine, and am not explicitly using either GetString or GetSqlString, but should I be?

SqlDataReader dr = cmd.ExecuteReader();
while (dr.read())
{
    loggedMessages.Add(new LoggedMessage
    {
        MesssageId = dr["ID"].ToString()
    });
}

user_1265167 · Answer

The reader["ColumnName"] approach returns an object and generally strongly typed alternatives would be preferred.

The straight Get commands, e.g. reader.GetString() will error if the value of a particular column is NULL and therefore require separate checking for DBNull. See the "Remarks" section of the API docs at: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.getstring?view=dotnet-plat-ext-6.0#remarks.

The equivalent GetSql commands, e.g. reader.GetSqlString() return "Sql" types which can cope with NULL values, and in the case of a string when cast to the c# System.String simply result in a null value if the data was NULL.

I can't find any particular Microsoft documentation outside of the terse API docs, but here is a reference source code of the SqlDataReader class (albeit .NET 4.7) to help dig into the implementation specifics: https://github.com/microsoft/referencesource/blob/master/System.Data/System/Data/SqlClient/SqlDataReader.cs

When to use the DataReader Get_ vs GetSql_ Methods in C#?

1 Answers