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;
}