Esempio n. 1
0
 public static TimeSheetContext Create(TimeSheetContext context)
 {
     if (context == null)
     {
         return new TimeSheetContext();
     }
     return context;
 }
Esempio n. 2
0
        public bool Delete(TimeSheetContext context)
        {
            bool result = false;
            if (!_IsDirty)
            {
                return false;
            }
            result = GetDataLink().Delete(this, context);

            _IsLoaded = false;
            _IsDirty = false;
            _IsNew = false;
            _IsDeleted = true;

            return result;
        }
Esempio n. 3
0
 public void CloseConnection(TimeSheetContext context)
 {
     try
     {
         _sqlCon.Close();
     }
     catch
     {
         //Catch if
     }
 }
Esempio n. 4
0
        public int Count(TimeSheetContext contextParam, string whereClause, params SqlParameter[] sqlParams)
        {
            TimeSheetContext context = TimeSheetContext.Create(contextParam);

            SqlCommand command = null;
            StringBuilder sql = null;

            int count = 0;
            try
            {
                sql = new StringBuilder();
                sql.Append("SELECT count(*) FROM ").Append(TableName);
                sql.Append(LockingHintClause);
                sql.Append(" WHERE ").Append(whereClause);
                _log.Debug("Count: sql = " + sql);

                command = context.CreateCommand(sql.ToString());
                if (sqlParams != null)
                {
                    foreach (SqlParameter param in sqlParams)
                    {
                        command.Parameters.Add(param);
                    }
                }

                context.OpenConnection();

                object result = ExecuteScalar(command);
                if (result == null || (result is DBNull))
                {
                    count = 0;
                }
                else
                {
                    count = (int)result;
                }
            }
            catch (Exception e)
            {
                if (sql != null)
                {
                    LogSqlCommand(LogLevel.Error, sql.ToString(), command);
                }
                if (e is SqlException)
                {
                    LogSqlException("Exception caught in Count().", (SqlException)e);
                }
            }
            finally
            {
                context.CloseConnection(contextParam);
            }
            return count;
        }
Esempio n. 5
0
        public bool Update(TimeSheetBase o, TimeSheetContext contextParam)
        {
            bool result = false;
            TimeSheetContext context = TimeSheetContext.Create(contextParam);
            SqlCommand command = null;
            string sql = string.Empty;
            try
            {
                command = context.CreateCommand(string.Empty);

                TimeSheetBase clean = o.CleanObject;
                ArrayList fieldNamesToSet = new ArrayList();
                object guidvalue = null;

                foreach (TimeSheetFieldMap fieldMap in _fieldMappings.Values)
                {
                    _log.Debug("Update: fieldMap.AttributeName = " + fieldMap.AttributeName);
                    if (fieldMap.FieldName == PrimaryKey)
                    {
                        guidvalue = fieldMap.FieldInfo.GetValue(o);
                    }
                    // if the attribute value is the same as the clean object's value, don't add the parameter
                    object attributeValue = fieldMap.FieldInfo.GetValue(o);
                    if (clean == null ||
                        !object.Equals(attributeValue, fieldMap.FieldInfo.GetValue(clean)))
                    {
                        fieldNamesToSet.Add(fieldMap.FieldName);
                        command.Parameters.Add(new SqlParameter("@" + fieldMap.FieldName, attributeValue));
                        _log.Debug("Update(): Fieldname: " + fieldMap.FieldName + ", Value: " + attributeValue);
                    }
                }

                if (fieldNamesToSet.Count == 0)
                {
                    return false;  // No changes persisted
                }

                // Add the guidfield parameter
                SqlParameter guidParam = new SqlParameter("@guidfield", SqlDbType.UniqueIdentifier, 40);
                guidParam.Value = guidvalue;
                command.Parameters.Add(guidParam);

                // Set the sql for the command to run
                command.CommandText = CreateUpdateStatement(o, fieldNamesToSet);
                context.OpenConnection();
                ExecuteNonQuery(command);
                result = true;
            }
            catch (SqlException e)
            {
                LogSqlException("Exception caught in Update(TimeSheetBase, TimeSheetContext).", e);
            }
            catch (Exception)
            {
                LogSqlCommand(LogLevel.Error, sql, command);
            }
            finally
            {
                context.CloseConnection(contextParam);
            }

            return result;
        }
Esempio n. 6
0
 public void Refresh(TimeSheetContext context)
 {
     _log.Debug("Refresh: loading " + GetType().FullName);
     GetDataLink().LoadSingle(this, context, "");
     SetLoaded();
 }
Esempio n. 7
0
        public bool Update(TimeSheetContext context)
        {
            if (!_IsDirty)
            {
                return false;
            }

            bool result = GetDataLink().Update(this, context);
            _IsLoaded = true;
            _IsDirty = false;
            _IsNew = false;
            _IsDeleted = false;

            _cleanObject = null;
            return result;
        }
Esempio n. 8
0
        public TimeSheetBase FindSingle(TimeSheetContext context, string whereClause, params SqlParameter[] sqlParams)
        {
            TimeSheetBase obj = null;

            IList matches = Find(context, whereClause, null, sqlParams);
            if (matches.Count > 1)
            {
                throw new ApplicationException("More than one record was matched.");
            }

            if (matches.Count == 1)
            {
                obj = (TimeSheetBase)matches[0];
            }

            return obj;
        }
Esempio n. 9
0
        // Inserts a MQ object into the database
        public bool Insert(TimeSheetBase o, TimeSheetContext contextParam)
        {
            bool result = false;
            TimeSheetContext context = TimeSheetContext.Create(contextParam);
            SqlCommand command = null;
            string sql = string.Empty;

            try
            {
                TimeSheetBase clean = o.CleanObject;
                sql = CreateInsertStatement(o);
                command = context.CreateCommand(sql);
                foreach (TimeSheetFieldMap mapping in _fieldMappings.Values)
                {
                    _log.Debug("Create(): Getting direct value for " + mapping.FieldName);
                    object value = mapping.FieldInfo.GetValue(o);
                    if (clean == null ||
                        !object.Equals(value, mapping.FieldInfo.GetValue(clean)))
                    {
                        if (value == null)
                        {
                            value = DBNull.Value;
                        }
                        string paramName = "@" + mapping.FieldName;
                        SqlParameter param = new SqlParameter(paramName, value);
                        _log.Debug("Create(): SqlParam = " + param);
                        command.Parameters.Add(param);
                        _log.Debug("Create(): SqlParameter added");
                    }
                }

                context.OpenConnection();
                ExecuteNonQuery(command);
                result = true;
            }
            catch (SqlException e)
            {
                LogSqlException("Exception caught in Create().", e);
            }
            catch (Exception)
            {
                LogSqlCommand(LogLevel.Error, sql, command);
            }
            finally
            {
                context.CloseConnection(contextParam);
            }

            return result;
        }
Esempio n. 10
0
        public IList Find(TimeSheetContext contextParam, string whereClause, string orderByClause, params SqlParameter[] sqlParams)
        {
            TimeSheetContext context = TimeSheetContext.Create(contextParam);

            IDataReader dataReader = null;
            SqlCommand command = null;
            string sql = string.Empty;

            ArrayList list = new ArrayList();

            try
            {
                sql = CreateSelectStatement(whereClause, orderByClause);
                _log.Debug("Find: sql = " + sql);

                command = context.CreateCommand(sql);
                if (sqlParams != null)
                {
                    foreach (SqlParameter param in sqlParams)
                    {
                        command.Parameters.Add(param);
                    }
                }

                context.OpenConnection();

                dataReader = ExecuteReader(command);
                while (dataReader.Read())
                {
                    TimeSheetBase obj = Init();
                    obj.SetAttributes(dataReader);

                    obj.SetLoaded();
                    list.Add(obj);
                }
            }
            catch (Exception e)
            {
                LogSqlCommand(LogLevel.Error, sql, command);
                if (e is SqlException)
                {
                    LogSqlException("Exception caught in Find().", (SqlException)e);
                }
            }
            finally
            {
                dataReader.Close();
                context.CloseConnection(contextParam);
            }

            _log.Debug("Find: returning " + list.Count + " items from '" + sql + "'");
            return list;
        }
Esempio n. 11
0
 public virtual ICollection FindReferencesTo(TimeSheetContext context, TimeSheetBase obj)
 {
     return EmptyList;
 }
Esempio n. 12
0
 public int DeleteAll(TimeSheetContext context)
 {
     return Delete(context, string.Empty);
 }
Esempio n. 13
0
        public int Delete(TimeSheetContext contextParam, string whereClause, params SqlParameter[] sqlParams)
        {
            TimeSheetContext context = TimeSheetContext.Create(contextParam);

            SqlCommand command = null;
            StringBuilder sql = null;

            int count = 0;
            try
            {
                sql = new StringBuilder();
                sql.Append("DELETE FROM ").Append(TableName);
                sql.Append(LockingHintClause);
                if (whereClause.Length > 0)
                {
                    sql.Append(" WHERE ").Append(whereClause);
                }
                _log.Debug("Delete: sql = " + sql);

                command = context.CreateCommand(sql.ToString());
                if (sqlParams != null)
                {
                    foreach (SqlParameter param in sqlParams)
                    {
                        command.Parameters.Add(param);
                    }
                }

                context.OpenConnection();
                count = ExecuteNonQuery(command);

            }
            catch (Exception e)
            {
                if (sql != null)
                {
                    LogSqlCommand(LogLevel.Error, sql.ToString(), command);
                }
                if (e is SqlException)
                {
                    LogSqlException("Exception caught in Delete().", (SqlException)e);
                }
            }
            finally
            {
                context.CloseConnection(contextParam);
            }
            return count;
        }
Esempio n. 14
0
        public bool Delete(TimeSheetBase o, TimeSheetContext contextParam)
        {
            bool result = false;
            TimeSheetContext context = TimeSheetContext.Create(contextParam);
            SqlCommand command = null;
            string deleteStatement = string.Empty;
            try
            {
                deleteStatement = CreateDeleteStatement();
                command = context.CreateCommand(deleteStatement);
                SqlParameter guidParam = new SqlParameter("@guidfield", SqlDbType.UniqueIdentifier, 40);
                guidParam.Value = this.fieldValue(o, this.PrimaryKey);
                command.Parameters.Add(guidParam);

                context.OpenConnection();
                ExecuteNonQuery(command);
                result = true;
            }
            catch (SqlException e)
            {
                LogSqlException("Exception caught in Delete().", e);
            }
            catch (Exception)
            {
                LogSqlCommand(LogLevel.Error, deleteStatement, command);
            }
            finally
            {
                context.CloseConnection(contextParam);
            }

            return result;
        }
Esempio n. 15
0
        public virtual bool Insert(TimeSheetContext context)
        {
            bool result = false;
            result = GetDataLink().Insert(this, context);

            _IsLoaded = true;
            _IsDirty = false;
            _IsNew = false;
            _IsDeleted = false;

            _cleanObject = null;

            return result;
        }
Esempio n. 16
0
        public List<TimeSheetBase> Load(TimeSheetBase baseObj, TimeSheetContext contextParam, string where, string orderby, params object[] args)
        {
            List<TimeSheetBase> Result = new List<TimeSheetBase>();
            TimeSheetContext context = TimeSheetContext.Create(contextParam);
            string sql = string.Empty;
            try
            {
                sql = CreateSelectStatement(where, orderby);
                SqlCommand command = context.CreateCommand(sql);
                if (args.Length > 1)
                {
                    int maxLength = (args.Length / 2);
                    maxLength = maxLength != 0 ? maxLength - 1 : 0;
                    int SCnt = 0;
                    for (int PCnt = 0; PCnt <= maxLength; PCnt++)
                    {
                        SqlParameter oParam = new SqlParameter("@" + args[SCnt].ToString(), args[SCnt + 1]);
                        command.Parameters.Add(oParam);
                        SCnt += 2;
                    }
                }
                context.OpenConnection();

                using (IDataReader dataReader = ExecuteReader(command))
                {
                    while (dataReader.Read())
                    {
                        TimeSheetBase obj = (TimeSheetBase)Activator.CreateInstance(baseObj.GetType(), new object[] { baseObj.User });
                        obj.SetAttributes(dataReader);
                        obj.SetLoaded();
                        Result.Add(obj);
                    }
                }
            }
            catch (SqlException e)
            {
                LogSqlException("Exception caught in Load().", e);
            }
            catch (Exception)
            {
                LogSqlCommand(LogLevel.Error, sql, null);
            }
            finally
            {
                context.CloseConnection(contextParam);
            }
            return Result;
        }
Esempio n. 17
0
        public void Load(TimeSheetContext context)
        {
            if (_IsLoaded)
            {
                return;
            }

            Refresh(context);
        }
Esempio n. 18
0
        public void LoadSingle(TimeSheetBase o, TimeSheetContext contextParam, string where, params object[] args)
        {
            _log.Debug("Load stack trace: " + new StackTrace(true));
            TimeSheetContext context = TimeSheetContext.Create(contextParam);
            string sql = string.Empty;
            try
            {
                sql = CreateSelectStatement(where);
                SqlCommand command = context.CreateCommand(sql);
                if (args.Length > 1)
                {
                    int maxLength = (args.Length / 2);
                    maxLength = maxLength != 0 ? maxLength - 1 : 0;
                    int Cnt = 0;
                    for (int PCnt = 0; PCnt <= maxLength; PCnt++)
                    {
                        SqlParameter oParam = new SqlParameter("@" + args[Cnt].ToString(), args[Cnt + 1]);
                        command.Parameters.Add(oParam);
                        Cnt += 2;
                    }
                }
                context.OpenConnection();

                using (IDataReader dataReader = ExecuteReader(command))
                {
                    if (dataReader.Read())
                    {
                        o.SetAttributes(dataReader);
                        o.SetLoaded();
                    }
                    else
                    {
                        string message = string.Format("No object read from database; table = {0}, guidfield = {1}", TableName, PrimaryKey);
                    }
                }
            }
            catch (SqlException e)
            {
                LogSqlException("Exception caught in Load().", e);
            }
            catch (Exception)
            {
                LogSqlCommand(LogLevel.Error, sql, null);
            }
            finally
            {
                context.CloseConnection(contextParam);
            }
        }
Esempio n. 19
0
 public bool Save(TimeSheetContext context)
 {
     bool result = false;
     if (_IsNew)
     {
         result = Insert(context);
     }
     else
     {
         result = Update(context);
     }
     return result;
 }
Esempio n. 20
0
        public decimal Sum(TimeSheetContext contextParam, string whereClause, string sumField, params SqlParameter[] sqlParams)
        {
            TimeSheetContext context = TimeSheetContext.Create(contextParam);

            SqlCommand command = null;
            StringBuilder sql = null;

            decimal sum = 0m;
            try
            {
                sql = new StringBuilder();
                sql.Append("SELECT sum(").Append(sumField).Append(") FROM ").Append(TableName);
                sql.Append(LockingHintClause);
                sql.Append(" WHERE ").Append(whereClause);
                _log.Debug("Sum: sql = " + sql);

                command = context.CreateCommand(sql.ToString());
                foreach (SqlParameter sqlParam in sqlParams)
                {
                    command.Parameters.Add(sqlParam);
                }

                context.OpenConnection();

                object result = ExecuteScalar(command);
                if (result == null || (result is DBNull))
                {
                    sum = 0m;
                }
                else
                {
                    sum = (decimal)result;
                }
            }
            catch (Exception e)
            {
                if (sql != null)
                {
                    LogSqlCommand(LogLevel.Error, sql.ToString(), command);
                }
                if (e is SqlException)
                {
                    LogSqlException("Exception caught in Sum().", (SqlException)e);
                }
            }
            finally
            {
                context.CloseConnection(contextParam);
            }
            return sum;
        }
Esempio n. 21
0
        private void MakeManagerReport(string sql)
        {
            TimeSheetContext context = new TimeSheetContext();
            SqlCommand dbCmd = context.CreateCommand(sql);
            context.OpenConnection();
            SqlDataReader reader = dbCmd.ExecuteReader();

            while (reader.Read())
            {
                managerreportlist fdep = new managerreportlist();

                fdep.taskDate = reader.GetDateTime(0);
                fdep.project = reader.GetString(1);
                fdep.task = reader.GetString(2);
                fdep.status = reader.GetString(3);

                managerreportlist.Add(fdep);
            }
        }
Esempio n. 22
0
        public static int Update(TimeSheetContext contextParam, string updateStatement, params SqlParameter[] sqlParams)
        {
            TimeSheetContext context = TimeSheetContext.Create(contextParam);

            int rowsUpdated = 0;
            SqlCommand command = null;

            try
            {
                command = context.CreateCommand(updateStatement);
                if (sqlParams != null)
                {
                    foreach (SqlParameter param in sqlParams)
                    {
                        command.Parameters.Add(param);
                    }
                }

                context.OpenConnection();

                rowsUpdated = ExecuteNonQuery(command);
            }
            catch (Exception e)
            {
                LogSqlCommand(LogLevel.Error, updateStatement, command);
                if (e is SqlException)
                {
                    LogSqlException("Exception caught in Update(TimeSheetContext, string).", (SqlException)e);
                }
            }
            finally
            {
                context.CloseConnection(contextParam);
            }

            return rowsUpdated;
        }