This is how our other class is written

if (database.ExecuteSqlCommand(sqlCommand, out dsPrefix, ref messageText, ref statusCode))

This was written by my colleagues and I already implemented it in many methods.

I'll try to apply your other suggestions.


>Again, the code has stuff that smell bad.
>
>don't return a message if there is an error. Throw a new exception and handle it.
>don't return bool. Return a enum or better, return the thing that you're interested in (the actual datarow or datatable)
>don't pass by ref. It adds lots of overhead and slows down your app
>don't use names like databaseParam. use database
>
>>>So, I decided to create a new class for the above and here is what I've done so far (not much):
>>>
>>>

>>>public class PassInfo : MiddlewareBase
>>>    {
>>>        /// <summary>
>>>        /// PassInfo Constructor
>>>        /// </summary>
>>>        /// <param name="databaseParam"></param>
>>>        public PassInfo(Database databaseParam)
>>>        {
>>>            database = databaseParam;
>>>        }
>>>
>>>        public Decimal PassNo { get; set; }
>>>        public String PassTable { get; private set; }
>>>        public String WhichKey { get; private set; }
>>>        public String InfoTable { get; private set; }
>>>
>>>        /// <summary>
>>>        /// Sets pass table and WhichKey based on the prefix
>>>        /// </summary>
>>>        /// <param name="prefix"></param>
>>>        /// <param name="messageText"></param>
>>>        /// <param name="statusCode"></param>
>>>        public Boolean SetPassTable(String prefix, ref String messageText, ref Int32 statusCode, String whichKey = "")
>>>        {
>>>            SqlCommand sqlCommand = new SqlCommand();
>>>            sqlCommand.CommandText = @"select pass_table, whichkey, info_table
>>>        from dbo.prefix
>>>        where prefix=@prefix;";
>>>            sqlCommand.CommandType = CommandType.Text;
>>>            sqlCommand.Parameters.Add("@prefix", SqlDbType.Char, 1).Value = prefix;
>>>            DataSet dsPrefix;
>>>
>>>            if (database.ExecuteSqlCommand(sqlCommand, out dsPrefix, ref messageText, ref statusCode))
>>>            {
>>>                foreach (DataRow row in dsPrefix.Tables[0].Rows)
>>>                {
>>>                    PassTable = row["pass_table"].ToString().Trim();
>>>                    if (!String.IsNullOrWhiteSpace(whichKey))
>>>                        WhichKey = whichKey;
>>>                    else
>>>                        WhichKey = row["whichkey"].ToString().Trim();
>>>                    InfoTable = row["info_table"].ToString().Trim();
>>>                }
>>>                if ((0 == dsPrefix.Tables[0].Rows.Count) || String.IsNullOrEmpty(PassTable))
>>>                {
>>>                    statusCode = 300;
>>>                    messageText = "No Table Associated With " + prefix + " In The PREFIX Table";
>>>                    return false;
>>>                }
>>>                else
>>>                {
>>>                    if (String.IsNullOrEmpty(WhichKey))
>>>                        WhichKey = "pass_no";
>>>
>>>                    if (String.IsNullOrEmpty(InfoTable))
>>>                    {
>>>                        if ("access" == PassTable.ToLower())
>>>                            InfoTable = "access";
>>>                        else
>>>                            InfoTable = "guests";
>>>                    }
>>>                }
>>>                return true;
>>>            }
>>>            else
>>>                return false;
>>>        }
>>>
>>>        /// <summary>
>>>        /// Constructs SQL Command for the passTable and WhichKey
>>>        /// </summary>
>>>        /// <param name="passNo"></param>
>>>        /// <param name="cPassNo"></param>
>>>        /// <param name="tcDCI"></param>
>>>        /// <returns></returns>
>>>        public SqlCommand GetPassSqlCommand(Decimal passNo, String cPassNo, String tcDCI = "")
>>>        {
>>>            SqlCommand sqlCommand = new SqlCommand();
>>>            if ("swipe_no" == WhichKey.ToLower())
>>>                sqlCommand.Parameters.Add("@value", SqlDbType.VarChar).Value = cPassNo;
>>>            else
>>>                sqlCommand.Parameters.Add("@value", SqlDbType.Decimal).Value = passNo;
>>>            sqlCommand.CommandType = CommandType.Text;
>>>            StringBuilder whereClause = new StringBuilder("where ");
>>>            whereClause.Append(WhichKey);
>>>            whereClause.Append("= @value");
>>>
>>>            if (!String.IsNullOrEmpty(tcDCI))
>>>            {
>>>                tcDCI = tcDCI.PadRight(30);
>>>                String department = tcDCI.Substring(0, 10);
>>>                String category = tcDCI.Substring(10, 10);
>>>                String item = tcDCI.Substring(20, 10);
>>>                whereClause.Append(" and department = @department and category = @category and item = @item");
>>>                sqlCommand.Parameters.Add("@department", SqlDbType.Char, 10).Value = department;
>>>                sqlCommand.Parameters.Add("@category", SqlDbType.Char, 10).Value = category;
>>>                sqlCommand.Parameters.Add("@item", SqlDbType.Char, 10).Value = item;
>>>            }
>>>
>>>            sqlCommand.CommandText = String.Format("select *, '{0}' as passtable {1} from dbo.{0}{1} {2}", PassTable, Environment.NewLine, whereClause.ToString());
>>>            return sqlCommand;
>>>        }       
>>>    }