ADO.net introduction

Using a datamapper

A datamapper maps data from the datareader to the properties off your dataobject.

This is nice and handy since you don't have to type all this logic yourself. Beneath the datamapper:

// maps a datareader to T
    public class DataReaderMapper<T> where T : new()
    {
        // methods
        public IList<T> MapListAll(IDataReader reader)
        {
            return MapListExcludeColumns(reader);
        }

        public IList<T> MapListExcludeColumns(IDataReader reader, params string[] excludeColumns)
        {
            var listOfObjects = new List<T>();
            while (reader.Read())
            {
                listOfObjects.Add(MapRowExclude(reader, excludeColumns));
            }
            return listOfObjects;
        }

        public T MapRowExclude(IDataReader reader, params string[] columns)
        {
            return MapRow(reader, false, columns);
        }

        public T MapRowInclude(IDataReader reader, params string[] columns)
        {
            return MapRow(reader, true, columns);
        }

        public T MapRowAll(IDataReader reader)
        {
            return MapRow(reader, true, null);
        }

        private T MapRow(IDataReader reader, bool includeColumns, params string[] columns)
        {
            var item = new T(); // 1. 
            var properties = GetPropertiesToMap(includeColumns, columns); // 2. 
            foreach (var property in properties)
            {
                var ordinal=-1;
                try
                {
                    ordinal = reader.GetOrdinal(property.Name); // 3. 
                }
                catch (IndexOutOfRangeException)
                {
                    // not found by propertyName
                    // try to find by attribute
                    var columnAttributeValue = GetColumnAttributeValue(property);
                    if (!string.IsNullOrEmpty(columnAttributeValue))
                    {
                        try
                        {
                            ordinal = reader.GetOrdinal(columnAttributeValue);
                        }
                        catch (Exception)
                        {
                            continue;
                        }
                    }
                    else
                    {
                        continue;
                    }
                }
                if (!reader.IsDBNull(ordinal)) // 4.
                {
                    // if dbnull the property will get default value, 
                    // otherwise try to read the value from reader
                    property.SetValue(item, reader[ordinal], null); // 5.
                }
            }
            return item;
        }

        public IEnumerable<System.Reflection.PropertyInfo> GetPropertiesToMap(bool includeColumns, string[] columns)
        {

            var properties = typeof(T).GetProperties().Where(y =>
                (y.PropertyType.Equals(typeof(string)) ||
                y.PropertyType.Equals(typeof(byte[])) ||
                y.PropertyType.IsValueType) &&
                (columns == null || (columns.Contains(y.Name) == includeColumns)));
            return properties;
        }

        public string GetColumnAttributeValue(PropertyInfo propertyInfo)
        {
            var foundColumnAttributeValue = string.Empty;
                var foundAttribute = propertyInfo.GetCustomAttributes(false).FirstOrDefault(a => a.GetType() == typeof(Model.Attributes.Column));
                if (foundAttribute != null)
                {
                    foundColumnAttributeValue = (((Model.Attributes.Column)foundAttribute).Name);
                }
            return foundColumnAttributeValue;
        }
        
    } 

As you can see the data is mapped on the name of the column in de data-reader result with a fall-back to a column-attribute. If you use a column atribute wich specifies the column name this is used:

[AttributeUsage(AttributeTargets.Property)]
    public class Column:Attribute
    {
        //ctor
        public Column(string name)
        {
            Name = name;
        }

        // props
        public string Name  { get; set; }
    } 

Example usage: a method which accept T as parameter and convert this to the query: "select * from T", the result is mapped to a list of T.

public IList<T> GetByTableName<T>(Expression<Func<T, bool>> queryExpression) where T : new()
        {
            IList<T> result = null;
            string tableName = DetectTableName<T>();

            try
            {

                var query = "select * from " + tableName;
                
                // add the where clause
                if (queryExpression != null)
                {
                    query = query + " where " + new QueryTranslator().Translate(queryExpression);
                }

                using (var sqlConnection = new SqlConnection(ConnectionString))
                {
                    if (sqlConnection.State != ConnectionState.Open)
                    {
                        sqlConnection.Open();
                    }

                    var sqlCommand = new SqlCommand(query, sqlConnection) { CommandType = CommandType.Text };
                    sqlCommand.CommandTimeout = 120;

                    var reader = sqlCommand.ExecuteReader();

                    var mapper = new DataReaderMapper<T>();

                    result = mapper.MapListAll(reader);

                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("Error during the query the table: {0}" + tableName, ex));
                throw;
            }
            return result;
        } 

{{opmerking.Naam}}:

{{opmerking.OpmerkingText}}

            

Saving your comment....

Naam is verplicht!
Email is verplicht!
Opmerking is verplicht!