How to use Generic with SqlDataReader

Question

I'm trying to come up with a way just to load a table from SQL Server into a class, without having to tell it anything. Basically, just create the class and have it know what to load, based on that. Here's what I have so far.

My question is, is there some way to keep from having to hard code the types, to call reader.readString, reader. readInt32, etc.. based on the FieldType?

 private Int32? readInt32(SqlDataReader reader, string columnName)
    {
        Int32? result = null;


        if (!reader.IsDBNull(reader.GetOrdinal(columnName)))
        {
            result = reader.GetInt32(reader.GetOrdinal(columnName));
        };

        return result;
    }

  public List<T> readTable(string table, string wherecls, string connStr)
    {
        List<T> result = new List<T>();
        using (SqlConnection connection = new SqlConnection(connStr))
        {
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = "select * from " + table;
                if (wherecls.Length > 0) command.CommandText += " where " + wherecls;
                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Object i = Activator.CreateInstance(typeof(T));

                        System.Reflection.FieldInfo[] fieldInfoList = typeof(T).GetFields();
                        foreach (System.Reflection.FieldInfo f in fieldInfoList)
                        {
                            if (f.FieldType == typeof(string)) f.SetValue(i, readString(reader, f.Name));
                            if (f.FieldType == typeof(Int32)) f.SetValue(i, readInt32(reader, f.Name));
                            if (f.FieldType == typeof(Int16)) f.SetValue(i, readInt16(reader, f.Name));
                            if (f.FieldType == typeof(byte)) f.SetValue(i, readByte(reader, f.Name));
                            if (f.FieldType == typeof(short)) f.SetValue(i, readShort(reader, f.Name));
                        }
                        result.Add((T)i);
                    }
                }
            }
        }
        return result;
    }

Thank you, Dan Chase

Answer 1

What you describe is a lot of work... and is exactly what tools like "dapper" already do. So my suggestion here: use dapper:

// Dapper adds a Query<T>(this DbConnection, ...) extension method
var data = connection.Query<T>(sql, args).AsList();

I would, however, say that string wherecls sends shivers down my spine - that sounds like a SQL injection nightmare. But... that's up to you.

Answer 2

Try this.

Make sure the type has a public default constructor--one that takes no arguments--and that the column names in the SQL string exactly match the name of the type's public properties.

namespace MyNamespace {
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Reflection;

    public static class MyExtensions {

        public static IEnumerable<T> Query<T>(this SqlConnection cn, string sql) {
            Type TypeT = typeof(T);
            ConstructorInfo ctor = TypeT.GetConstructor(Type.EmptyTypes);
            if (ctor == null) {
                throw new InvalidOperationException($"Type {TypeT.Name} does not have a default constructor.");
            }
            using (SqlCommand cmd = new SqlCommand(sql, cn)) {
                using (SqlDataReader reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        T newInst = (T)ctor.Invoke(null);
                        for (int i = 0; i < reader.FieldCount; i++) {
                            string propName = reader.GetName(i);
                            PropertyInfo propInfo = TypeT.GetProperty(propName);
                            if (propInfo != null) {
                                object value = reader.GetValue(i);
                                if (value == DBNull.Value) {
                                    propInfo.SetValue(newInst, null);
                                } else {
                                    propInfo.SetValue(newInst, value);
                                }
                            }
                        }
                        yield return newInst;
                    }
                }
            }
        }
    }
}

Answer 3

Maybe my solution is a bit better. I populate type T using extension with handling null values and populating properties in order I like.

Example:

public async Task<ObservableCollection<T>> Search_data<T>()
    {
        var data = new ObservableCollection<T>();

        try
        {
            using (OracleConnection con = new OracleConnection(connn_string))
            {
                con.Open();

                OracleCommand cmd = new OracleCommand("MySchema.SomeTable", con)
                {
                    CommandType = CommandType.StoredProcedure
                };
              
                cmd.Parameters.Add("result", OracleDbType.RefCursor, ParameterDirection.Output);
                
                using (OracleDataReader rdr = cmd.ExecuteReader())
                {
                    while (await rdr.ReadAsync())
                    {
                        var item = Activator.CreateInstance<T>();

                        item.SetValue("NAME", rdr.IsDBNull(0) ? null : rdr.GetString(0));
                        item.SetValue("SURNAME", rdr.IsDBNull(1) ? null : rdr.GetString(1));
                        item.SetValue("ADDRESS", rdr.IsDBNull(2) ? null : rdr.GetString(2));
                      
                        data.Add(item);

                    };
                }
            }
            return data;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
             
            return null;
        }
    }

Extension:

public static void SetValue<T>(this T _source, string _property_name, object _value)
    {
        _source.GetType().GetProperty(_property_name).SetValue(_source, _value);
    }