10 July 2018

C# - Retrieve objects from a database

Years ago, I wrote this stuff to facilitate working with a database. Recently, I augmented the Retrieve method by returning an IEnumerable of model objects instead of a DataTable. Kind of like LINQ does. Here is the method. Mind that it is built upon the earlier post.
public IEnumerable Retrieve(string query, params object[] parameters) where T : class
{
    using (var connection = new OdbcConnection(ConnectionString))
    using (var command = new OdbcCommand(query, Connection))
    {
        SetParameters(command, parameters);

        using (var reader = command.ExecuteReader())
        {
            var data = new List();
            var columns = new HashSet();

            while (reader.Read())
            {
                if (columns.Count <= 0)
                    for (int i = 0; i < reader.FieldCount; i++)
                        columns.Add(reader.GetName(i));

                var item = Activator.CreateInstance();

                foreach (var prop in typeof(T).GetProperties())
                {
                    if (!columns.Contains(prop.Name))
                        continue;

                    object value = reader[prop.Name];

                    if (value == null || value == DBNull.Value)
                    {
                        if (!prop.IsNullable())
                            prop.SetValue(item, default(T));

                        continue;
                    }

                    prop.SetValue(item, reader[prop.Name]);
                }

                data.Add(item);
            }

            return data;
        }
    }
}

No comments:

Post a Comment