1
\$\begingroup\$

Part of my job is to maintain an old application written in Silverlight talking to a WCF service which in turn fetch/persist data into a SQL Server database and in a BI cube.

Most of the security is specified at database level using database roles, users are authenticated using Windows Authentication (integrated security) and no user can access table data directly, only through stored procedures. For persistence user defined table types are used (as input parameters of procedures).

As most of the code performs data table construction and reads data quite messy, I thought of developing a small framework to work more object oriented.

In order to standardize how data is fetched and persisted, I have created some helper classes and attributes. The vast majority of fetched or persisted data does not go beyond hundreds of rows, so performance is almost never an issue. Also, data can be fetched from both SQL Server and Analysis Server (via ADOMD.NET)

Ok, enough talk. Let's see the code:

1) Base class for all data contracts used to fetch data and sent to the WCF client

[DataContract]
public abstract class BaseDataContract
{
    #region Properties
    public abstract int? PkId { get; set; }

    [DataMember]
    public bool IsReadonly { get; set; }

    public virtual bool IsAdded
    {
        get { return !PkId.HasValue; }
    }
    #endregion

    #region Constructor
    public BaseDataContract()
    {
        IsReadonly = false;
    }
    #endregion

    #region Methods
    /// <summary>
    /// gets property value
    /// </summary>
    /// <param name="property">property name</param>
    /// <returns>property value</returns>
    public object GetPropertyValue(string property)
    {
        PropertyInfo propertyInfo = this.GetType().GetProperty(property);
        return propertyInfo.GetValue(this, null);
    }

    /// <summary>
    /// sets property value
    /// </summary>
    /// <param name="property">property name to set</param>
    /// <param name="value">property value</param>
    public void SetPropertyValue(String property, object value)
    {
        PropertyInfo propertyInfo = this.GetType().GetProperty(property);
        propertyInfo.SetValue(this, value, null);
    }

    #endregion
}

2. Data access base class

public abstract class BaseDataAccess : IDisposable, ICloneable
{
    #region Members
    private string connectionString;
    private string country;
    protected static object connectionLock = new object();
    #endregion

    #region Properties
    public IDbConnection BaseConnection { get; set; }

    public IDbTransaction BaseTransaction { get; set; }

    protected String ConnectionString
    {
        get { return connectionString; }
    }

    public String Country
    {
        get { return country; }
    }

    // this specifies if current user is impersonated or not (no impersonation means that Application Pool user will go to database or cube)
    public bool LocalUseSqlImpersonation { get; set; }
    #endregion

    #region Constructor
    public BaseDataAccess(String connectionString, String country, bool localUseSqlImpersonation = true)
    {
        this.connectionString = connectionString;
        this.country = country;
        this.LocalUseSqlImpersonation = localUseSqlImpersonation;
    }
    #endregion

    #region Methods
    public abstract void EnsureConnection();

    public abstract void RenewConnection();

    public void BeginTransaction()
    {
        EnsureConnection();

        if (BaseTransaction != null)
            throw new InvalidOperationException(Strings.TransactionAlreadyOpened);

        BaseTransaction = BaseConnection.BeginTransaction();
    }

    public void Commit()
    {
        if (BaseTransaction != null)
        {
            try
            {
                BaseTransaction.Commit();
            }
            finally
            {
                BaseTransaction = null;
            }
        }
        else
        {
            // trying to commit a non-existant transaction
            throw new InvalidOperationException(Strings.NoTransactionToCommit);
        }
    }

    public void Rollback()
    {
        // if transaction does not exist, just exiting. This will insure that failed commits do not generate another error
        if (BaseTransaction == null)
            return;

        try
        {
            BaseTransaction.Rollback();
        }
        catch(Exception exc)
        {
            Console.WriteLine("Rollback error - " + exc.Message);
        }
        finally
        {
            BaseTransaction = null;
        }
    }

    public IDbCommand CreateCommand()
    {
        EnsureConnection();
        return BaseConnection.CreateCommand();
    }
    #endregion

    #region Dispose
    public void Dispose()
    {
        if (BaseConnection != null)
        {
            GC.SuppressFinalize(this);
        }

        if (BaseConnection != null && BaseConnection.State != ConnectionState.Closed)
        {
            BaseConnection.Close();
        }

        if (BaseConnection != null)
        {
            BaseConnection.Dispose();
            BaseConnection = null;
        }
    }
    #endregion

    #region ICloneable
    public abstract object Clone();
    #endregion
}

3. Sql Server Data access class

public sealed class SqlDataAccess : BaseDataAccess, IDataAccess
{
    #region Members
    private static Dictionary<String, SortedDictionary<int, string>> userDefinedTableTypesCache = new Dictionary<string,SortedDictionary<int,string>>();

    // user defined table types lock object
    private static object udttLock = new object();
    #endregion Members

    #region Properties
    public String UserName
    {
        get
        {
            string strUser = HttpContext.Current.User.Identity.Name;
            return CommonUtils.ParseUserName(strUser);
        }
    }

    public SqlConnection Connection
    {
        get { return (SqlConnection)base.BaseConnection; }
        private set { base.BaseConnection = value; }
    }

    public SqlTransaction Transaction
    {
        get { return (SqlTransaction)base.BaseTransaction; }
    }
    #endregion

    #region Constructor
    public SqlDataAccess(String connectionString, String country, bool localUseSqlImpersonation = true)
        : base(connectionString, country, localUseSqlImpersonation)
    {
    }
    #endregion Constructor

    #region Overrides
    public override void RenewConnection()
    {
        Connection.Close();
        SqlConnection.ClearPool(Connection);
        Connection.Dispose();
        Connection = null;
    }

    public override void EnsureConnection()
    {
        // this gets windows identity information (not included for brevity)
        WindowsIdentity windowsIdentity = CommonUtils.GetWindowsIdentity();
        WindowsImpersonationContext ctx = null;

        try
        {
            lock (connectionLock)
            {
                if (LocalUseSqlImpersonation && Config.UseSqlImpersonation && windowsIdentity != null)
                    ctx = windowsIdentity.Impersonate();

                if (Connection == null)
                {
                    Connection = new SqlConnection(ConnectionString);
                    Connection.Open();
                }

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
            }
        }
        catch (SqlException ex)
        {
            throw new DataAccessException("Could not open SQL Server Connection.", ex);
        }
        finally
        {
            if (ctx != null)
                ctx.Dispose();
        }
    }
    #endregion

    #region Methods

    #region General
    public SqlCommand CreateSqlCommand(bool useTransaction)
    {
        EnsureConnection();

        Debug.Assert(Connection != null);

        SqlCommand command = Connection.CreateCommand();
        if (useTransaction && Transaction != null)
            command.Transaction = Transaction;

        return command;
    }
    #endregion

    #region Execute
    private void ConstructCommandParameters(SqlCommand command, Params parameters)
    {
        if (parameters != null)
        {
            foreach (var p in parameters)
            {
                if (p.Value is SqlParameter)
                {
                    var sqlParam = (SqlParameter)p.Value;
                    sqlParam.ParameterName = p.Key;

                    command.Parameters.Add(sqlParam);
                }
                else
                {
                    var addedParam = command.Parameters.AddWithValue(p.Key, p.Value ?? DBNull.Value);

                    if (p.Value != null && p.Value.GetType() == typeof(DataTable))
                    {
                        DataTable dt = (DataTable)p.Value;
                        if (!dt.ExtendedProperties.ContainsKey(CommonConstants.DataAccess.DataTableCustomTypeKey))
                            throw new KeyNotFoundException("Data table does not contain type name information");

                        addedParam.SqlDbType = SqlDbType.Structured;
                        addedParam.TypeName = dt.ExtendedProperties[CommonConstants.DataAccess.DataTableCustomTypeKey].ToString();
                    }
                }
            }
        }
    }

    public void ExecuteNonQuery(string proc, Params parameters)
    {
        EnsureConnection();

        using (var command = Connection.CreateCommand())
        {
            if (Transaction != null)
            {
                command.Transaction = Transaction;
            }
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = proc;
            command.CommandTimeout = CommonConstants.DataAccess.DefaultCommandTimeout;

            if (parameters != null)
            {
                foreach (var p in parameters)
                {
                    if (p.Value is SqlParameter)
                    {
                        var parameter = (SqlParameter)p.Value;
                        if (!String.IsNullOrEmpty(p.Key))
                        {
                            parameter.ParameterName = p.Key;
                        }
                        command.Parameters.Add(parameter);
                    }
                    else
                    {
                        command.Parameters.AddWithValue(p.Key, p.Value);
                    }
                }
            }

            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                var err = String.Format("Error executing stored procedure '{0}' - {1}", proc, ex.Message);
                if (ex.Message.StartsWith(CommonConstants.DataAccess.ReservedWordForSQLError))
                    err = ex.Message;
                throw new DataAccessException(err, ex);
            }
        }
    }

    public Nullable<T> ExecuteScalar<T>(string proc, Params parameters) where T : struct
    {
        return ExecuteScalar<T>(proc, parameters, CommandType.StoredProcedure);
    }

    public Nullable<T> ExecuteScalar<T>(string proc, Params parameters, CommandType commandType) where T : struct
    {
        EnsureConnection();

        using (var command = Connection.CreateCommand())
        {
            if (Transaction != null)
            {
                command.Transaction = Transaction;
            }
            command.CommandType = commandType;
            command.CommandText = proc;
            command.CommandTimeout = CommonConstants.DataAccess.DefaultCommandTimeout;

            if (parameters != null)
            {
                foreach (var p in parameters)
                {
                    command.Parameters.AddWithValue(p.Key, p.Value);
                }
            }

            Object o;
            try
            {
                o = command.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                var err = String.Format("Error executing stored procedure '{0}'.", proc);
                throw new DataAccessException(err, ex);
            }

            return o == DBNull.Value ? (Nullable<T>)null : (Nullable<T>)o;
        }
    }

    public String ExecuteScalarStr(string proc, Params parameters, CommandType commandType)
    {
        EnsureConnection();

        using (var command = Connection.CreateCommand())
        {
            if (Transaction != null)
                command.Transaction = Transaction;

            command.CommandType = commandType;
            command.CommandText = proc;
            command.CommandTimeout = CommonConstants.DataAccess.DefaultCommandTimeout;

            parameters.ToList().ForEach(obj => command.Parameters.AddWithValue(obj.Key, obj.Value));

            Object o;
            try
            {
                o = command.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                var err = String.Format("Error executing stored procedure '{0}'.", proc);
                throw new DataAccessException(err, ex);
            }

            return o == DBNull.Value ? (String)null : Convert.ToString(o);
        }
    }

    public String ExecuteScalarStr(string proc, Params parameters)
    {
        return ExecuteScalarStr(proc, parameters, CommandType.StoredProcedure);
    }
    #endregion

    #region Read
    private SqlDataReader getReader(string proc, Params parameters, SqlCommand command)
    {
        if (Transaction != null)
        {
            command.Transaction = Transaction;
        }

        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = proc;
        command.CommandTimeout = CommonConstants.DataAccess.BigCommandTimeout;

        ConstructCommandParameters(command, parameters);

        SqlDataReader reader;
        try
        {
            reader = command.ExecuteReader();
            return reader;
        }
        catch (SqlException ex)
        {
            var err = String.Format("Error executing stored procedure or query '{0}'.", proc);
            throw new DataAccessException(err, ex);
        }
        finally
        {
        }
    }

    public IEnumerable<T> ReadRows<T>(string proc, Params parameters, Func<SqlDataReader, T> readRow)
    {
        EnsureConnection();

        using (var command = Connection.CreateCommand())
        {
            using (var reader = getReader(proc, parameters, command))
            {
                while (reader.Read())
                {
                    yield return readRow(reader);
                }
            }
        }
    }

    public void ReadRows(string proc, Params parameters, Action<SqlDataReader> readRow)
    {
        EnsureConnection();

        using (var command = Connection.CreateCommand())
        {
            using (var reader = getReader(proc, parameters, command))
            {
                while (reader.Read())
                {
                    readRow(reader);
                }
            }
        }
    }

    public DataTable GetDataTable(String proc, Dictionary<String, object> parameters)
    {
        return GetDataTable(proc, parameters, CommandType.StoredProcedure);
    }

    public DataTable GetDataTable(String proc, Dictionary<String, object> parameters, CommandType commandType)
    {
        EnsureConnection();

        using (var command = Connection.CreateCommand())
        {
            if (Transaction != null)
                command.Transaction = Transaction;

            SqlDataAdapter adapter = new SqlDataAdapter(proc, Connection);
            adapter.SelectCommand.CommandTimeout = CommonConstants.DataAccess.DefaultCommandTimeout;
            adapter.SelectCommand.CommandType = commandType;

            if (Transaction != null)
                adapter.SelectCommand.Transaction = Transaction;

            ConstructCommandParameters(adapter.SelectCommand, parameters);

            DataTable dt = new DataTable();
            try
            {
                adapter.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                var err = String.Format("Error executing stored procedure '{0}' - {1}.", proc, ex.Message);
                throw new DataAccessException(err, ex);
            }
        }
    }

    public DataSet GetDataSet(String proc, Dictionary<String, object> parameters)
    {
        EnsureConnection();

        using (var command = Connection.CreateCommand())
        {
            if (Transaction != null)
                command.Transaction = Transaction;

            SqlDataAdapter adapter = new SqlDataAdapter(proc, Connection);
            adapter.SelectCommand.CommandTimeout = CommonConstants.DataAccess.DefaultCommandTimeout;
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

            if (Transaction != null)
                adapter.SelectCommand.Transaction = Transaction;

            ConstructCommandParameters(adapter.SelectCommand, parameters);

            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
                return ds;
            }
            catch (SqlException ex)
            {
                var err = String.Format("Error executing stored procedure '{0}' - {1}.", proc, ex.Message);
                throw new DataAccessException(err, ex);
            }
        }
    }

    public List<T> GetEntityList<T>(String proc, Dictionary<String, object> parameters)
    {
        List<T> list = new List<T>();
        DataTable dt = GetDataTable(proc, parameters);
        list.LoadFromDataTable<T>(dt);
        return list;
    }

    public List<T> GetEntityList<T>(String proc)
    {
        return GetEntityList<T>(proc, new Dictionary<String, object>());
    }
    #endregion

    #region Write
    private void SaveEntityConstructCommand<T>(SqlCommand command, T entity, String procName, Dictionary<String, object> fixedParameters)
    {
        var properties = typeof(T).GetProperties().Where(prop => prop.IsDefined(typeof(WriteToParamAttribute), false));
        if (!properties.Any())
            throw new ArgumentException("No WriteToParam attributes defined on provided instance object");

        if (Transaction != null)
            command.Transaction = Transaction;

        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = procName;
        command.CommandTimeout = CommonConstants.DataAccess.DefaultCommandTimeout;

        // constructing parameters
        ConstructCommandParameters(command, fixedParameters);
        foreach (var pi in properties)
        {
            var attributes = (WriteToParamAttribute[])pi.GetCustomAttributes(typeof(WriteToParamAttribute), false);
            // should not happen
            if (!attributes.Any())
                continue;

            SqlParameter param = new SqlParameter("@" + attributes[0].ParamName, pi.GetValue(entity, null));
            command.Parameters.Add(param);
        }
    }

    /// <summary>
    /// saves a single data contract using a stored procedure
    /// </summary>
    /// <typeparam name="T">entity type</typeparam>
    /// <typeparam name="TRet">return value type</typeparam>
    /// <param name="entity">entity to be saved</param>
    /// <param name="procName">procedure to be used to save the entity</param>
    /// <param name="fixedParameters">procedure parameters that do not have a correspondence to entity's properties. See remarks</param>
    /// <returns>value returned by the procedure</returns>
    /// <remarks>entity is saved by direct mapping of WriteToParamAttribute values to stored procedure parameters</remarks>
    public Nullable<TRet> SaveEntity<T, TRet>(T entity, String procName, Dictionary<String, object> fixedParameters)
        where T: BaseDataContract
        where TRet: struct
    {
        if (entity == null || String.IsNullOrEmpty(procName))
            throw new ArgumentNullException("Null argument provided for SaveEntity function");

        EnsureConnection();

        try
        {
            using (var command = Connection.CreateCommand())
            {
                SaveEntityConstructCommand(command, entity, procName, fixedParameters);

                object o = command.ExecuteScalar();
                return o == DBNull.Value ? (Nullable<TRet>)null : (Nullable<TRet>)o;
            }
        }
        catch (SqlException ex)
        {
            var err = String.Format("Error executing stored procedure '{0}' - {1}.", procName, ex.Message);
            throw new DataAccessException(err, ex);
        }
    }

    public void SaveEntity<T>(T entity, String procName, Dictionary<String, object> fixedParameters)
        where T : BaseDataContract
    {
        if (entity == null || String.IsNullOrEmpty(procName))
            throw new ArgumentNullException("Null argument provided for SaveEntity function");

        EnsureConnection();

        try
        {
            using (var command = Connection.CreateCommand())
            {
                SaveEntityConstructCommand(command, entity, procName, fixedParameters);
                command.ExecuteNonQuery();
            }
        }
        catch (SqlException ex)
        {
            var err = String.Format("Error executing stored procedure '{0}' - {1}.", procName, ex.Message);
            throw new DataAccessException(err, ex);
        }
    }

    /// <summary>
    /// gets information related to a user defined table type
    /// </summary>
    /// <param name="typeName">udtt full name</param>
    /// <returns>a sorted dictionary containing column information for that user defined table type</returns>
    private SortedDictionary<int, String> getUddtInfo(String typeName)
    {
        lock (udttLock)
        {
            if (!userDefinedTableTypesCache.ContainsKey(typeName))
            {
                SortedDictionary<int, string> columnsDict = new SortedDictionary<int, string>();

                using (var command = Connection.CreateCommand())
                {
                    if (Transaction != null)
                        command.Transaction = Transaction;

                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = CommonConstants.Procedures.Generic.SpGetUserDefinedTableTypeColumnInfo;

                    command.Parameters.AddWithValue("@ObjectFullName", typeName);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            columnsDict.Add(Convert.ToInt32(reader["column_id"]), Convert.ToString(reader["name"]));
                        }
                    }

                    // checking if any data was fetched
                    if (columnsDict.Count < 1)
                        throw new ApplicationException(String.Format(Strings.NoDataForUdtt, typeName));

                    userDefinedTableTypesCache.Add(typeName, columnsDict);
                }
            }

            return userDefinedTableTypesCache[typeName];
        }
    }

    /// <summary>
    /// validates persistance info against the user defined table type used with the persistance procedure. If column count or names do not match, an exception is thrown
    /// </summary>
    /// <param name="persistInfo">persistence information</param>
    private void CheckStructureParameter(PersistInfo persistInfo)
    {
        SortedDictionary<int, string> columnsDict = getUddtInfo(persistInfo.TableParamType);

        // same column count
        if (persistInfo.PersistData.Columns.Count != columnsDict.Keys.Count)
            throw new ApplicationException(String.Format(Strings.WriteColumnsCountDoNotMatchUserDefinedTableTypeColumnCount, persistInfo.PersistData.Columns.Count, columnsDict.Keys.Count));

        // same column names
        // keys start at 1 (SQL stores them that way)
        foreach (int key in columnsDict.Keys)
        {
            String writeColName = persistInfo.PersistData.Columns[key - 1].ColumnName;
            String typeColName = columnsDict[key];

            if (!writeColName.Equals(typeColName))
                throw new ApplicationException(String.Format(Strings.WriteColumnNameDoesNotMatchUdttColumnName, writeColName, typeColName));
        }
    }

    /// <summary>
    /// saves a list of entities using type defined metadata (procedure name and so on)
    /// </summary>
    /// <typeparam name="T">entity type</typeparam>
    /// <param name="entityList">list of entities</param>
    /// <param name="overrideProcName">procedure to use instead of the one provided by attribute on list entity procedure</param>
    /// <param name="parameters">procedure parameters besides table parameter used to store data to persist</param>
    public void SaveEntityList<T>(List<T> entityList, String overrideProcName, Dictionary<String, object> parameters)
    {
        if (entityList == null || entityList.Count < 1)
            return;

        EnsureConnection();

        PersistInfo persistInfo = entityList.GetPersistInfo<T>();

        try
        {
            // checking mapping between persistance info and custom type, so that errors would be easier to understand
            CheckStructureParameter(persistInfo);

            using (var command = Connection.CreateCommand())
            {
                if (Transaction != null)
                    command.Transaction = Transaction;

                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = overrideProcName ?? persistInfo.ProcedureName;
                command.CommandTimeout = CommonConstants.DataAccess.DefaultCommandTimeout;

                ConstructCommandParameters(command, parameters);

                // adding table parameter
                var tableParam = command.Parameters.AddWithValue(persistInfo.TableParamName, persistInfo.PersistData);
                tableParam.SqlDbType = SqlDbType.Structured;
                tableParam.TypeName = persistInfo.TableParamType;

                command.ExecuteNonQuery();
            }
        }
        catch (SqlException ex)
        {
            var err = String.Format("Error executing stored procedure '{0}' - {1}.", persistInfo.ProcedureName, ex.Message);
            throw new DataAccessException(err, ex);
        }
    }

    /// <summary>
    /// saves a list of entities using type defined metadata (procedure name and so on)
    /// </summary>
    /// <typeparam name="T">entity type</typeparam>
    /// <param name="entityList">list of entities</param>
    /// <param name="parameters">procedure parameters besides table parameter used to store data to persist</param>
    public void SaveEntityList<T>(List<T> entityList, Dictionary<String, object> parameters)
    {
        SaveEntityList<T>(entityList, null, parameters);
    }
    #endregion

    public override object Clone()
    {
        return new SqlDataAccess(ConnectionString, Country);
    }
    #endregion
}

4. Extension methods to perform various mapping operations

    /// <summary>
    /// loads data in a typed list from a <see cref="System.Data.DataTable">DataTable</see> object
    /// </summary>
    /// <typeparam name="T">list object type</typeparam>
    /// <param name="dest">list to be filled in</param>
    /// <param name="dt">data source</param>
    /// <param name="minExpectedColumnCount">specifies the minimum columns the data table must have in order to load data. 
    /// This should be specified in order to avoid errors when the cube returns no actual data</param>
    public static void LoadFromDataTable<T>(this List<T> dest, DataTable dt, int minExpectedColumnCount)
    {
        if (dest == null)
            throw new NullReferenceException(Strings.LoadFromDataTableNullReference);

        // not enough columns - doing nothing
        if (dt.Columns.Count < minExpectedColumnCount)
            return;

        // for each row in data table
        foreach (DataRow dr in dt.Rows)
        {
            T newObj = Activator.CreateInstance<T>();
            dest.Add(newObj);

            // getting properties having Column attribute - only for those load is performed
            var properties = typeof(T).GetProperties().Where(prop => prop.IsDefined(typeof(ReadColumnAttribute), false));

            // getting attributes values for each property
            foreach (PropertyInfo pi in properties)
            {
                var optionalAttributes = (OptionalColumnAttribute[])pi.GetCustomAttributes(typeof(OptionalColumnAttribute), false);
                var colAttributes = (ReadColumnAttribute[])pi.GetCustomAttributes(typeof(ReadColumnAttribute), false);
                var defaultAttributes = (DefaultValueAttribute[])pi.GetCustomAttributes(typeof(DefaultValueAttribute), false);

                // should not happen
                if (colAttributes.Length < 1)
                    continue;

                #region Column name handling
                String actualColumnName = colAttributes[0].ColumnName;

                // if property is marked as optional value and column is not provided, skipping this property/column
                if (optionalAttributes.Length >= 1 && (actualColumnName == null || !dt.Columns.Contains(actualColumnName)))
                    continue;

                if (actualColumnName == null)
                    throw new ApplicationException(String.Format(Strings.DataTableToListColumnMissing, columnName + " / " + (altColumnName ?? String.Empty)));

                object currValue = (dr[actualColumnName] == DBNull.Value) ? null : dr[actualColumnName];

                // if default value is provided, using that value
                if ((currValue == null || currValue.Equals(String.Empty)) && defaultAttributes.Length >= 1)
                    currValue = defaultAttributes[0].Value;
                #endregion

                // if current value is null, then the cell computation has some kind of error - skipping setting of the value
                if (currValue == null)
                    continue;

                try
                {
                    #region checking for decimal conversion attribute
                    ConvertToDecimalAttribute[] decimalConvAttrs = (ConvertToDecimalAttribute[])pi.GetCustomAttributes(typeof(ConvertToDecimalAttribute), false);

                    if (decimalConvAttrs.Length > 0)
                    {
                        Decimal? decimalVal = decimalConvAttrs[0].Abs ? CommonUtils.ConvertToDecimalABS(currValue) : CommonUtils.ConvertToDecimal(currValue);
                        pi.SetValue(newObj, decimalVal, null);
                        continue;
                    }
                    #endregion

                    #region checking if string should be trimmed
                    TrimStringAttribute[] trimStrAttr = (TrimStringAttribute[])pi.GetCustomAttributes(typeof(TrimStringAttribute), false);

                    if ((currValue is String) && trimStrAttr.Length > 0)
                    {
                        String trimStr = ((String)currValue).Trim();
                        pi.SetValue(newObj, trimStr, null);
                        continue;
                    }
                    #endregion

                    // other attributes handling removed for brevity

                    #region received value is not nullable and property is nullable
                    if (Nullable.GetUnderlyingType(pi.PropertyType) != null 
                        && pi.PropertyType != currValue.GetType() 
                        && Nullable.GetUnderlyingType(pi.PropertyType) == currValue.GetType())
                    {
                        pi.SetValue(newObj, currValue, null);
                        continue;
                    }
                    #endregion

                    // other types come here
                    object currValueTyped = Convert.ChangeType(currValue, pi.PropertyType, CultureInfo.InvariantCulture);
                    pi.SetValue(newObj, currValueTyped, null);
                }
                catch (Exception exc)
                {
                    throw new ApplicationException(String.Format(Strings.DataTableToListSetValueError, pi.Name, exc.Message));
                }
            }
        }
    }

    /// <summary>
    /// loads data in a typed list from a <see cref="System.Data.DataTable">DataTable</see> object. If data table has less then 2 columns, no data will 
    /// be loaded. This is useful when dealing with mdx which do not provide the whole columns when returning no actual values
    /// </summary>
    /// <typeparam name="T">list object type</typeparam>
    /// <param name="dest">list to be filled in</param>
    /// <param name="dt">data source</param>
    public static void LoadFromDataTable<T>(this List<T> dest, DataTable dt)
    {
        dest.LoadFromDataTable<T>(dt, 2);
    }

    /// <summary>
    /// gets the datatable corresponding to a list of objects. Columns are built using WriteColumn attributes. Table type is assigned to data table
    /// as an extended property that is recognized by sql data access when calling a procedure using the data table as input parameter
    /// </summary>
    /// <typeparam name="T">objects type</typeparam>
    /// <param name="list">list of objects</param>
    /// <param name="tableType">table type name. Use null if no table type is required to be attached to the data table</param>
    /// <returns>a datatable containing data from the list of objects</returns>
    public static DataTable GetDataTable<T>(this IEnumerable<T> list, String tableType)
    {
        Debug.Assert(list != null, "Null list provided to GetDataTable extension");

        DataTable dt = new DataTable() { Locale = CultureInfo.InvariantCulture };

        // getting properties having WriteColumn attribute - only for those load is performed
        // ordering properties using index, as DataTable columns are not mapped to user defined table by name, but by
        var properties = typeof(T)
            .GetProperties()
            .Where(prop => prop.IsDefined(typeof(WriteColumnAttribute), false))
            .OrderBy(p => ((WriteColumnAttribute[])p.GetCustomAttributes(typeof(WriteColumnAttribute), false))[0].ColumnIndex);

        // computing data table columns
        foreach (PropertyInfo pi in properties)
        {
            var colAttributes = (WriteColumnAttribute[])pi.GetCustomAttributes(typeof(WriteColumnAttribute), false);

            Type actualType = Nullable.GetUnderlyingType(pi.PropertyType);
            dt.Columns.Add(colAttributes[0].ColumnName, actualType ?? pi.PropertyType);
        }

        // populating data table
        foreach (T obj in list)
        {
            object[] values = properties.Select<PropertyInfo, object>(pi => pi.GetValue(obj, null)).ToArray();
            dt.Rows.Add(values);
        }

        if (tableType != null)
            dt.ExtendedProperties.Add(CommonConstants.DataAccess.DataTableCustomTypeKey, tableType);

        return dt;
    }

    /// <summary>
    /// gets the datatable corresponding to a list of objects. Columns are built using WriteColumn attributes
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="list"></param>
    /// <returns></returns>
    public static DataTable GetDataTable<T>(this IEnumerable<T> list)
    {
        return GetDataTable<T>(list, null);
    }

    /// <summary>
    /// construct a PersistInfo object from a list of objects
    /// </summary>
    /// <typeparam name="T">object type</typeparam>
    /// <param name="list">list of objects</param>
    /// <returns>persist information constructed</returns>
    public static PersistInfo GetPersistInfo<T>(this IEnumerable<T> list)
    {
        Debug.Assert(list != null, "Null list provided to GetPersistInfo extension");

        // getting class metadata (procedure name and so on)
        object[] persistInfoAttrs = typeof(T).GetCustomAttributes(typeof(PersistInfoAttribute), true);
        if (persistInfoAttrs == null || persistInfoAttrs.Length < 1)
            throw new ApplicationException(String.Format(Strings.TypeDoesNotContaintPersistInfo, typeof(T).ToString()));

        PersistInfoAttribute persistInfo = (PersistInfoAttribute)persistInfoAttrs[0];
        DataTable dt = list.GetDataTable<T>();

        return new PersistInfo()
        {
            ProcedureName = persistInfo.ProcedureName,
            TableParamName = persistInfo.TableParamName,
            TableParamType = persistInfo.TableParamType,
            PersistData = dt
        };
    }

5. An example of data contract using annotations for fetch and persist

[DataContract]
[PersistInfo(Constants.Procedures.ArticleGroups.SpWriteArticleGroupArticles, "@table", Constants.TableTypes.ArtGroup)]
public class Article : BaseDataContract
{
    /// <summary>
    /// The article ID.
    /// </summary>
    [DataMember]
    [ReadColumn("art_no")]
    [WriteColumn("art_no", 1)]
    [WriteToParam("art_no")]
    public int ID { get; set; }

    /// <summary>
    /// The article name.
    /// </summary>
    [DataMember]
    [ReadColumn("art_name")]
    [WriteToParam("art_name")]
    public string Name { get; set; }

    // other properties not included for brevity

}

Can this code be improved? As already mentioned, performance is not an issue, so I am asking mostly for an architectural perspective.

\$\endgroup\$

Your Answer

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

Browse other questions tagged or ask your own question.