public static TimeSheetContext Create(TimeSheetContext context) { if (context == null) { return new TimeSheetContext(); } return context; }
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; }
public void CloseConnection(TimeSheetContext context) { try { _sqlCon.Close(); } catch { //Catch if } }
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; }
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; }
public void Refresh(TimeSheetContext context) { _log.Debug("Refresh: loading " + GetType().FullName); GetDataLink().LoadSingle(this, context, ""); SetLoaded(); }
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; }
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; }
// 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; }
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; }
public virtual ICollection FindReferencesTo(TimeSheetContext context, TimeSheetBase obj) { return EmptyList; }
public int DeleteAll(TimeSheetContext context) { return Delete(context, string.Empty); }
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; }
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; }
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; }
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; }
public void Load(TimeSheetContext context) { if (_IsLoaded) { return; } Refresh(context); }
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); } }
public bool Save(TimeSheetContext context) { bool result = false; if (_IsNew) { result = Insert(context); } else { result = Update(context); } return result; }
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; }
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); } }
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; }