Generic class for convert list of object to IEnumerable

Question

I've this object:

public class ConsentData
{
    public string ConsentName { get; set; }
    public bool ConsentValue { get; set; }
    public DateTime ConsentDate { get; set; }
}

I should pass a List of ConsentData to a stored procedure in SQL Server via a table-value parameter.

Looking for a way to convert the List of ConsentData in a List of SqlDataRecord i found this generic class on web:

public class SqlTableValueSupport<T> : List<T>, IEnumerable<SqlDataRecord> where T : class, new()
    {
        IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
        {
            //Create Columns (SqlMetaData)
            List<SqlMetaData> records = new List<SqlMetaData>();
            var properties = typeof(T).GetProperties();
            foreach (var prop in properties)
            {
                SqlDbType sdbtyp = GetSqlType(prop.PropertyType);
                records.Add(new SqlMetaData(prop.Name, sdbtyp));
            }

            //Create records/rows (SqlDataRecord)
            SqlDataRecord ret = new SqlDataRecord(records.ToArray());

            foreach (T data in this)
            {
                for (int i = 0; i < properties.Length; i++)
                {
                    ret.SetValue(i, properties[i].GetValue(data, null));
                }

                yield return ret;
            }
        }

        // Map C# Types to SqlDbType
        private SqlDbType GetSqlType(Type type)
        {
            SqlDbType val = SqlDbType.VarChar;
            if (type == typeof(Int64) || type == typeof(Nullable<Int64>))
            {
                val = SqlDbType.BigInt;
            }
            else if (type == typeof(Byte[]))
            {
                val = SqlDbType.Binary;
            }
            else if (type == typeof(Boolean) || type == typeof(Nullable<Boolean>))
            {
                val = SqlDbType.Bit;
            }
            else if (type == typeof(DateTime) || type == typeof(Nullable<DateTime>))
            {
                val = SqlDbType.DateTime;
            }
            else if (type == typeof(Decimal))
            {
                val = SqlDbType.Decimal;
            }
            // Please refer to the following document to add other types
            // http://msdn.microsoft.com/en-us/library/ms131092.aspx
            return val;
        }
    }

I'd like to know how to use the class, how can i pass the List of ConsentData and retrive a List of SqlDataRecord?

You shouldn't retrieve the list of `SqlDataRecord` yourself. The class is correctly [reusing](https://stackoverflow.com/a/10779567/11683) the single instance of `SqlDataRecord`, so it's intended to be enumerated once. The class itself is supposed to store the data, so you should use it as the `List`. Then just pass that list to the `Value` property of the `SqlParameter`. You might need to cast it to `IEnumerable`, because the class is also implicitly `IEnumerable`. — GSerg, Mar 26 '20 at 16:18

score 1 · Accepted Answer · answered Apr 20 '20 at 07:51

In the end, I solved the problem by handling the class with a static method. This is the class:

public class SqlTableValueSupport<T> where T : class
{
    public static DataTable ConvertData(List<T> ValuesList)
    {
        DataTable dtData = new DataTable();
        var objectReference = ValuesList.GetType().GetGenericArguments()[0];
        var properties = objectReference.GetProperties();
        foreach (var prop in properties)
        {
            dtData.Columns.Add(prop.Name, prop.PropertyType);
        }

        foreach (var item in ValuesList)
        {
            var dataArray = new List<object>();
            foreach (var prop in properties)
            {
                dataArray.Add(prop.GetValue(item));
            }

            dtData.Rows.Add(dataArray.ToArray());
        }

        return dtData;
    }
}

The method is called in the following way

var ListOfMyObjectDT = SqlTableValueSupport<MyObject>.ConvertData(ListOfMyObject);

Generic class for convert list of object to IEnumerable

1 Answers1