Looking for a generic function to read columns from a DataRow and set them to variables

Question

I have a DataRow loaded with data from an SQL view. I then read the columns of the DataRow and assign them to an Object which properties are actually the column names of the sql view. The goal is to have a generic function that returns the value of the column dynamically. However, if the passing value is numeric insteaad of AlphaNumeric, then it seems that the value is interpreted as Int32 and not as String. The exception is the following: Unable to cast object of type 'System.Int32' to type 'System.String'.

This is the calling function:

foreach (DataRow dr in dt)
{
    MyObject myObject = new MyObject(dr);
    //Continue with myObject
}

In the constructor of MyObject, I am trying to read the values of DataRow and set them to object's properties. I have tried the following

public MyObject(DataRow dr)
 {
     _comments = Utils.GetDbValue<string>(dr, "comments"); //fails if "comments" == "1"
     _comments = Utils.GetDbString(dr, "comments"); //fails if "comments" == "1"
     _comments = dr.Field<string>("comments"); //fails if "comments" == "1"
 }

_comments is a private field of MyObject whereas "comments" is the column name. If "comments" is filled in correctly (like an actual string), then I have no problems whatsoever. If the value is an integer (for instance '1'), then it is interpreted as Int32 and throws a cast exception.

public static class Utils
{
    public static T GetDbValue<T>(DataRow dataRow, string columnName)
    {
        int index = dataRow.Table.Columns.IndexOf(columnName);
    if (index < 0 || index > dataRow.ItemArray.Count())
    {
        return default(T);
    }
    else
    {
        return (T)dataRow[index];
    }
    }

    //Cannot be used for other data types (i.e Int, DateTime)
    public static string GetDBString(DataRow dataRow, string columnName)
    {
        if (dataRow.IsNull(columnName))
    {
        return string.Empty;
    }
    else
    {
        return dataRow.Field<string>(columnName);
    }
    }
}

EDIT:

It seems that this function seems to be working neatly so far, although it is not tested but for Integer, String, DateTime and Decimal types.

        public static T GetDbValue<T>(DataRow dataRow, string columnName)
        {
            int index = dataRow.Table.Columns.IndexOf(columnName);
            if (dataRow.IsNull(columnName) || index < 0 || index > dataRow.ItemArray.Count())
            {
                return default(T);
            }
            else
            {
                return (T)dataRow[index];
            }
        }

Answer 1

Please check below its might usefull for you

private static DataSet SetDefaultForNull(DataSet dataSet)
{

    var retDataSet = new DataSet();

    for (int intTableCount = 0, loopTo = dataSet.Tables.Count - 1; intTableCount <= loopTo; intTableCount++)
    {

        DataTable dt = dataSet.Tables(intTableCount).Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field => object.ReferenceEquals(field, DBNull.Value) | field.Equals(""))).CopyToDataTable();




        retDataSet.Tables.Add(dt);

        foreach (DataRow row in retDataSet.Tables(intTableCount).Rows)
        {


            foreach (DataColumn col in retDataSet.Tables(intTableCount).Columns)
            {

                if (row(col) is DBNull)
                {
                    string dataType = col.DataType.ToString;

                    switch (col.DataType.ToString)
                    {
                        case "System.DateTime":
                            {
                                row(col) = new DateTime();
                                break;
                            }
                        case "System.String":
                            {
                                row(col) = string.Empty;
                                break;
                            }
                        case "System.Boolean":
                            {
                                row(col) = false;
                                break;
                            }
                        // row(col) = Nothing
                        case "System.Double":
                            {
                                break;
                            }

                        default:
                            {
                                row(col) = 0;
                                break;
                            }
                    }

                }
                // retDataSet.Tables(intTableCount).Columns.Add(col)
            }

            // retDataSet.Tables(intTableCount).Rows.Add(row.ItemArray)


        }

    }


    return retDataSet; // dataSet
}

Answer 2

If you want to retrieve string no metter what type is stored in a column, you can do this next way

Utils.GetDbValue<object>(dr, "comments")?.ToString();

But your function GetDbValue does not support null values and potentially some other edge cases, so i recommend to use built-in Field<T>() method under the hood

public static T GetDbValue<T>(DataRow dataRow, string columnName)
{
    int index = dataRow.Table.Columns.IndexOf(columnName);
    if (index < 0 || index > dataRow.ItemArray.Count())
    {
        return default(T);
    }
    else
    {
        return dataRow.Field<T>(columnName);
    }
}

But this code needed only if you want to return default value in the case when a column does not exists in a DataTable. If you does not need this check, then you can simply use dr["comments"].ToString() to retrieve value as string and dr.Field<T>(columnName) for other cases.