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

Possible duplicate of How can I easily convert DataReader to List<T>? — Daniel B
– Daniel B, Commented Sep 1, 2018 at 22:57
If you're interested in reflection techniques, you might also give the source for Fastmember a look: github.com/mgravell/fast-member — Parrish Husband
– Parrish Husband, Commented Sep 1, 2018 at 23:04
@DanielB the way this question differs, I really just want to know how to get rid of all of the different if/then/SetValue's in the middle. I made almost the entire method generic, except for this and it's killing me. — Dan Chase
– Dan Chase, Commented Sep 1, 2018 at 23:24

Marc Gravell · Accepted Answer · 2018-09-01 22:52:12Z

7

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.

answered Sep 1, 2018 at 22:52

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

8 Comments

Dan Chase Over a year ago

I appreciate the suggestion, but if I can make readInt32 generic to readValue, then I've replaced even dapper with 2 functions..? I really just want one function.

Parrish Husband Over a year ago

@DanChase let the advice you've just been given really soak in.

Marc Gravell Over a year ago

@DanChase I cant twist your arm... just: there's no magic wand here that makes it not be a lot of work :)

Dan Chase Over a year ago

I'll check out Dapper for sure, was just trying this out, also learned a lot about reflection and generics trying to do it.

Dan Chase Over a year ago

Wow.. just.. wow. Installed Dapper eliminated 90% of my code within 5 minutes and it worked immediately without issue.!!!

|

Nicholas Hunter · Accepted Answer · 2019-11-22 15:25:17Z

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;
                    }
                }
            }
        }
    }
}

Lucy82 · Accepted Answer · 2023-04-13 20:20:31Z

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);
    }

Collectives™ on Stack Overflow

How to use Generic with SqlDataReader

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related