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\$
  • \$\begingroup\$ Where is Conn coming from? \$\endgroup\$ – BCdotWEB Oct 14 '15 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 '15 at 22:11
  • \$\begingroup\$ Why not just use Linq-To-SQL or Entity Framework? \$\endgroup\$ – RubberDuck Oct 14 '15 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 '15 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 '15 at 7:47
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\$
  • 1
    \$\begingroup\$ I'm not sure about this. You are not disposing the SqlCommand. \$\endgroup\$ – t3chb0t Oct 15 '15 at 15:42
  • \$\begingroup\$ @t3chb0t Very good point, I was focused on the extension method issue and missed that one. \$\endgroup\$ – unholysampler Oct 15 '15 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\$
  • \$\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 '15 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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