3
\$\begingroup\$

The following ingests a preexisting SqlCommand and returns the values.

A couple caveats right now.

1 ) Type properties must be named identical to the SQL column

2) properties must be given a custom [SQLColumn] attribute.

The attribute contains a string which I plan to use later for variation in property names while keeping column designation.

    public static IEnumerable<T> FromCommand<T>(this IEnumerable<T> b, SqlCommand command)
    {

        using (command.Connection)
        {

            command.Connection.Open();

            using (var reader = command.ExecuteReader())
            {

                var readerColumns = new HashSet<string>(Enumerable.Range(0, reader.FieldCount).Select(reader.GetName));
                var properties = typeof(T).GetProperties().Where(o => o.IsDefined(typeof(SQLColumn)) && readerColumns.Contains(o.Name)).ToList();

                while (reader.Read())
                {

                    object OUTPUT = Activator.CreateInstance(typeof(T));

                    foreach (var item in properties)
                    {
                        item.SetValue(OUTPUT, reader[item.Name] is DBNull ? null : reader[item.Name]);
                    }

                    yield return (T)Convert.ChangeType(OUTPUT, typeof(T));
                }
            }
        }
    }

As requested, here is an example of the class utilizing the extension method.

public class MyType
{

    #region Properties Block

    [SQLColumn]
    public Guid GuidColumn { get; set; }

    [SQLColumn]
    public int IntColumn { get; set; }

    [SQLColumn]
    public bool BitColumn { get; set; }

    #endregion Properties Block

    #region Select Block

    [Description("Selects and returns all rows.")]
    public static IEnumerable<MyType> SelectAll()
    {

        SqlCommand command = new SqlCommand(QueryRepository.SELECT_MyType_ALL, Conn.ConnectionString) { CommandTimeout = 0 };

        return new List<MyType>().FromCommand(command);

    }

    [Description("Selects and returns rows filtered by GuidColumn.")]
    public static IEnumerable<MyType> SelectFiltered(Guid GuidColumn)
    {

        SqlCommand command = new SqlCommand(QueryRepository.SELECT_MyType_GuidColumn, Conn.ConnectionString) { CommandTimeout = 0 };

        command.Parameters.Add("@GuidColumn", SqlDbType.UniqueIdentifier).Value = GuidColumn;

        return new List<MyType>().FromCommand(command);

    }

    #endregion Select Block

}
\$\endgroup\$
6
  • \$\begingroup\$ Where is Conn coming from? \$\endgroup\$
    – BCdotWEB
    Oct 14, 2015 at 21:06
  • \$\begingroup\$ Conn is a class file containing methods that extract and return a SqlConnection object. ConnectionString is an alias for one of the many methods, normally it is named after the data base. \$\endgroup\$
    – E-French
    Oct 14, 2015 at 22:11
  • \$\begingroup\$ Why not just use Linq-To-SQL or Entity Framework? \$\endgroup\$
    – RubberDuck
    Oct 14, 2015 at 23:17
  • \$\begingroup\$ The method I have been asked to use follows this pattern, though the way it was shown had me explicitly setting each SQL column to a property. The reasoning is that it affords us more low level control over our queries and data layer. Though I would rather not have to explicitly state and cast everything, so that's where this comes in. \$\endgroup\$
    – E-French
    Oct 15, 2015 at 0:00
  • \$\begingroup\$ @E-French But are you properly disposing of that SqlConnection? Because you don't seem to be doing that for the SqlCommand, and both of these would be red flags in a code review. \$\endgroup\$
    – BCdotWEB
    Oct 15, 2015 at 7:47

2 Answers 2

2
\$\begingroup\$

You are never using b in the method. Why make it an extension method if you are going to completely ignore the first argument? In order to use this code, you need to create a new instance that is immediately thrown away. That is unneeded waste.

Instead, you can just specify the generic type in the function call.

public static IEnumerable<T> FromCommand<T>(SqlCommand command)
{
    // ...
}

[Description("Selects and returns all rows.")]
public static IEnumerable<MyType> SelectAll()
{
    SqlCommand command = new SqlCommand(QueryRepository.SELECT_MyType_ALL, Conn.ConnectionString) { CommandTimeout = 0 };

    return FromCommand<MyType>(command);
}

The expression that assigns to properties is over 100 characters. That doesn't count the indentation or the variable declaration. That is going to be hard to follow. I suggest that you extract the lambda being passed to Where() into a static function with a descriptive name. That way I don't need to think about what is being filtered out.


You are adding extra blank lines after opening curly brackets and before some closing curly brackets. This is just inflating the file size and not helping readability.

\$\endgroup\$
2
  • 1
    \$\begingroup\$ I'm not sure about this. You are not disposing the SqlCommand. \$\endgroup\$
    – t3chb0t
    Oct 15, 2015 at 15:42
  • \$\begingroup\$ @t3chb0t Very good point, I was focused on the extension method issue and missed that one. \$\endgroup\$ Oct 15, 2015 at 15:58
1
\$\begingroup\$

I'm a big fan of caching reflection bits to aid in speed of subsequent access:

private static readonly IDictionary<Type, IList<PropertyInfo>> propertyMap =
    new Dictionary<Type, IList<PropertyInfo>>();

public static IEnumerable<T> FromCommand<T>(this IEnumerable<T> b, SqlCommand command)
{

    using (command.Connection)
    {

        command.Connection.Open();

        using (var reader = command.ExecuteReader())
        {
            var type = typeof(T);
            var readerColumns = new HashSet<string>(Enumerable.Range(0, reader.FieldCount).Select(reader.GetName));
            IList<PropertyInfo> properties;

            lock (propertyMap)
            {
                if (!propertyMap.TryGetValue(type, out properties))
                {
                    properties = type.GetProperties().Where(o => o.IsDefined(typeof(SQLColumn)) && readerColumns.Contains(o.Name)).ToList();
                    propertyMap[type] = properties;
                }
            }

            while (reader.Read())
            {

                var OUTPUT = (T)Activator.CreateInstance(type);

                foreach (var item in properties)
                {
                    item.SetValue(OUTPUT, reader[item.Name] is DBNull ? null : reader[item.Name]);
                }

                yield return OUTPUT;
            }
        }
    }
}
\$\endgroup\$
1
  • \$\begingroup\$ This is great, I honestly never even thought about locking those down because the types never really get too large. ms are ms though, and every one counts! \$\endgroup\$
    – E-French
    Oct 14, 2015 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.