4
\$\begingroup\$

I would like to have advice on this use of base class and derived class. This all works but is it a good way to achieve a SOLID class?

  public class ProductRepository : GenericDataExport
  {
               private const string DB_TABLE = "[dbo].[ReplenEdit]";
               private int rowsUpdated;
               private int primaryKey;
               private string columnName;
               private int replenishAmount;

               public void SetUpdateReplenAmount(int primaryKey, int replenishAmount)
               {
                   this.primaryKey = primaryKey;
                   this.columnName = "ReplenishAmount";
                   this.replenishAmount = replenishAmount;
               }

               /// <summary> Method to check if database has been updated
               /// <para>Checks to see if the the Rows Updated field is greather than 0 
               /// This indicates that the database has had a least one row updated.</para>
               /// </summary>
               /// <returns>True or False</returns>
               public bool HasDatabaseBeenUpdated()
               {
                   return UpdateTableHelper.HasDatabaseBeenUpdated(this.rowsUpdated);
               }

               /// <summary> Create Update SQL statement and then update table using SQL Command
               /// </summary>
               /// <param name="conn">SQLConnection</param>
               protected override void ActionUpdateCommands(SqlConnection conn)
               {
                   string sql = CreateSQLStatement();

                   this.rowsUpdated = 0;

                   UpdateTable(conn, sql);
               }

           #region Class Implementation

                /// <summary> Method to update Database table with Replenish Amount
                /// </summary>
                /// <param name="conn">SQLConnection object</param>
                /// <param name="sql">SQL Statement</param>
                private void UpdateTable(SqlConnection conn, string sql)
                {
                    using (SqlCommand command = new SqlCommand(sql, conn))
                    {
                        command.Parameters.Add("@replenishAmt", SqlDbType.Int);
                        command.Parameters["@replenishAmt"].Value = this.replenishAmount;

                        this.rowsUpdated = command.ExecuteNonQuery();
                    }
                }

                /// <summary> Method to create a SQL Statement to be used in the Update
                /// </summary>
                /// <returns>SQL Statement</returns>
                private string CreateSQLStatement()
                {
                    string updateValue = "@replenishAmt";

                    string updateTableSQL = UpdateTableHelper.CreateUpdateTableValue(updateValue, DB_TABLE,
                        this.columnName, this.primaryKey.ToString());
                    return updateTableSQL;
                }

          #endregion
            }

            public abstract class GenericDataExport
            {
                protected string dataExportError;
                readonly ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["PiaReplen"];
                private static readonly log4net.ILog log = log4net.LogManager.GetLogger
                (System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

                /// <summary> Gets the last error thrown by the UpdateDatabase method
                /// </summary>
                /// <returns>Data Export Error</returns>
                public string GetDataExportError()
                {
                    return dataExportError;
                }

                /// <summary> Checks to see if there is an error available after 
                /// UpdateDatabase has been called.
                /// </summary>
                /// <returns>True or False</returns>
                public bool HasDataExportFailed()
                {
                    if (string.IsNullOrEmpty(dataExportError))
                    {
                        return false;
                    }
                    else
                    {
                        return true;
                    }
                }

                /// <summary> Open a connection to a SQL database
                ///  <para>as specified and call a virtual method
                /// (ActionUpdateCommand) that is overriden in the derived class</para>
                /// </summary>
                public void UpdateDatabase()
                {
                    using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
                    {
                        try
                        {
                            conn.Open();
                            ActionUpdateCommands(conn);
                            log.Debug("Database Update Complete");
                        }
                        catch (SqlException ex)
                        {
                            this.dataExportError = string.Format("Error updating Database {0}", ex.Message);
                            log.Error("Error updating Database", ex);
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }

                /// <summary> A virtual stub, that is defined in the derived class
                /// <para>Is where the update/insert commands are placed and called.</para>
                /// </summary>
                /// <param name="conn">SQLConnection object</param>
                protected virtual void ActionUpdateCommands(SqlConnection conn)
                {}

            }

        public static class UpdateTableHelper
        {
            /// <summary>
            /// Creates the SQL statement to use in the Update command
            /// </summary>
            /// <param name="updateValue">The parameter placeholder to use in the update statement</param>
            /// <returns>SQL update statement</returns>
            public static string CreateUpdateTableValue(string updateValue, string dbTable, 
                string columnName, string primaryKey )
            {
                updateValue.ThrowIfNullOrEmpty("updateValue");

                dbTable.ThrowIfNullOrEmpty("dbTable");

                columnName.ThrowIfNullOrEmpty("columnName");

                primaryKey.ThrowIfNullOrEmpty("primaryKey");

                return string.Format("UPDATE {0} SET {1} = {2} where ID = {3}",
                    dbTable, columnName, updateValue, primaryKey);
            }

            /// <summary>
            /// Gets a parameter placeholder for a SQL update statement 
            /// depending on the Column Name passed.
            /// This is always the column name to lowercase prefixed with @
            /// <example>GetUpdateValue("ProductName") would return @productname</example>
            /// </summary>
            /// <param name="columnName">Column name that parameter placeholder is for</param>
            /// <returns>Parameter Placeholder</returns>
            public static string GetUpdateValue(string columnName)
            {
                columnName.ThrowIfNullOrEmpty("columnName");

                return string.Format("@{0}", columnName.ToLower());
            }

            /// <summary>
            /// Checks to see if the the Rows Updated field is greather than 0
            /// This indicates that the database has had a least one row updated.
            /// </summary>
            /// <returns>True or False</returns>
            public static bool HasDatabaseBeenUpdated(int rowsUpdated)
            {
                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

     public static class StringExtensions
        {
            /// <summary>
            /// Extension methof that throws exception if string is null
            /// </summary>
            /// <param name="field">This string</param>
            public static void ThrowIfNull(this string field, string fieldName)
            {
                if (field ==  null)
                {
                    throw new ArgumentNullException(string.Format("{0} cannot be null", fieldName));
                }
            }

            /// <summary>
            /// Extension methof that throws exception if string is null or empty
            /// </summary>
            /// <param name="field">This string</param>
            public static void ThrowIfNullOrEmpty(this string field, string fieldName)
            {
                if (string.IsNullOrEmpty(field))
                {
                    throw new ArgumentNullException(string.Format("{0} cannot be null or empty", fieldName));
                }
            }

            /// <summary>
            /// Converts a string to sentence case.
            /// </summary>
            /// <param name="input">The string to convert.</param>
            /// <returns>A string</returns>
            public static string ToSentenceCase(this string input)
            {
                if (input.Length < 1)
                    return input;

                string sentence = input.ToLower();
                return sentence[0].ToString().ToUpper() +
                   sentence.Substring(1);
            }
        }
\$\endgroup\$
3
\$\begingroup\$

ProductRepository is 60 lines, you don't need a region.


Don't abbreviate when you don't need to: there's no need to shorten ReplenishAmount to Replen in SetUpdateReplenAmount.


In UpdateTable you spend two lines on doing something that can be done in one:

command.Parameters.Add("@replenishAmt", SqlDbType.Int).Value = this.replenishAmount;

But IMHO it's even worse that the parameter name -- "@replenishAmt" -- is repeated twice here and is also defined in CreateSQLStatement, when it clearly should be a const at the class level.

Moreover, why isn't columnName a const either?


But more importantly: all of your code is solving a problem that has already been solved by ORMs like NHibernate or Entity Framework. You have written a 60 line class that can do one single thing: update the field ReplenishAmount in the table ReplenEdit.

My advice: throw all of this away, implement a decent ORM and focus on the actual business logic.

\$\endgroup\$
  • 1
    \$\begingroup\$ I take your point on the constants, the ORM I avoid initially as the database was not setup when I started coding, but in hindsight I should maybe have used EF with Code First. \$\endgroup\$ – Peter Campbell Jun 2 '15 at 9:28
  • 1
    \$\begingroup\$ @PeterCampbell you may still be in luck, as F# type providers for SQL can work off DBML files you can generate with SqlMetal. If you confine the repository code to a separate binary, you can write it in F# and still call it from your C# code in other binaries. \$\endgroup\$ – Dan Lyons Jun 2 '15 at 17:47
2
\$\begingroup\$

It's a relatively small note, but this if statement can be replaced with a boolean return.

           public bool HasDataExportFailed()
            {
                if (string.IsNullOrEmpty(dataExportError))
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
public bool HasDataExportFailed()
{
    return !string.IsNullOrEmpty(dataExportError);
}

And again here.

public static bool HasDatabaseBeenUpdated(int rowsUpdated)
{
    return (rowsUpdated > 0);
}
\$\endgroup\$
  • 1
    \$\begingroup\$ thanks, pretty obvious when you look at it, but I'll keep an eye out for this sort of thing in the future. \$\endgroup\$ – Peter Campbell Jun 2 '15 at 11:30

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.