CODEWITHSUNDEEP
datareader
AJ.
AJ.

Reputation: 16719

DataReader best-practices

Similar to this question, but the answers never really got around to what I want to know. Is there any standards around getting values from a DataReader? I.e., is this

dataReader.GetString(dataReader.GetOrdinal("ColumnName"));

considered better/worse/the same as this?

(string) dataReader["ColumnName"];

Upvotes: 9

Views: 14142

Answers (3)

Bruno Marquez
Bruno Marquez

Reputation: 49

This an extension class; it works pretty ok for me. It handles nulls for int and boolean:

   public static class Utility
        {
            public static string ValidatorDataReaderString(this SqlDataReader pSqlDataReader, string pCampo)
            {
                var wValorReader = pSqlDataReader[pCampo];
                if (wValorReader != null)
                {
                    string wValorRetorno = Convert.ToString(pSqlDataReader[pCampo]);
                    return _ = wValorRetorno == string.Empty ? null : wValorRetorno;
                }
                else
                {
                    return null;
                }
            }
    
            public static int ValidatorDataReaderInt(this SqlDataReader pSqlDataReader, string pCampo)
            {
                var wValorReader = pSqlDataReader[pCampo];
                if (wValorReader != null)
                {
                    bool wValorParse = int.TryParse(Convert.ToString(pSqlDataReader[pCampo]), out int oValorRetorno);
                    return wValorParse ? oValorRetorno : 0;
                }
                else
                {
                    return 0;
                }
            }
    
            public static bool ValidatorDataReaderBool(this SqlDataReader pSqlDataReader, string pCampo)
            {
                if (pSqlDataReader[pCampo] != null)
                {
                    var wValor = Convert.ToString(pSqlDataReader[pCampo]);
    
                    if (wValor == "1" || wValor == "True")
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
        }
    }

Upvotes: 0

Joel Mueller
Joel Mueller

Reputation: 28765

I made some extension methods to let me treat an IDataReader as an enumerable, and to deal with DbNull by returning nullable ints, etc. This lets me check for null and apply a default value with the C# ?? operator.

/// <summary>
/// Returns an IEnumerable view of the data reader.
/// WARNING: Does not support readers with multiple result sets.
/// The reader will be closed after the first result set is read.
/// </summary>
public static IEnumerable<IDataRecord> AsEnumerable(this IDataReader reader)
{
    if (reader == null)
        throw new ArgumentNullException("reader");

    using (reader)
    {
        while (reader.Read())
        {
            yield return reader;
        }
    }
}

public static int? GetNullableInt32(this IDataRecord dr, string fieldName)
{
    return GetNullableInt32(dr, dr.GetOrdinal(fieldName));
}

public static int? GetNullableInt32(this IDataRecord dr, int ordinal)
{
    return dr.IsDBNull(ordinal) ? null : (int?)dr.GetInt32(ordinal);
}

...and so on for the other GetDataType() methods on IDataReader.

Upvotes: 9

Andrew Hare
Andrew Hare

Reputation: 351526

Here is the way that I do it:

Int32 ordinal = dataReader.GetOrdinal("ColumnName");

if (!dataReader.IsDBNull(ordinal))
    yourString = dataReader.GetString(ordinal);

It is important to check for DBNull like I have shown above because if the field is null in the DataReader it will throw an exception when you try to retrieve it.

Upvotes: 11

Related Questions