using System;
using System.Data;
using SubSonic.Utilities;
using System.Text;
namespace SubSonic
{
[Serializable]
public abstract class ActiveRecord<T> : AbstractRecord<T> where T : AbstractRecord<T>, new()
{
protected ActiveRecord()
{
MarkNew();
}
#region Fetchers
/// <summary>
/// Returns a record for this keyValue
/// </summary>
/// <param name="keyValue">Key Value</param>
/// <returns></returns>
public static T FetchByID(int keyValue)
{
return fetchByID(keyValue);
}
/// <summary>
/// Returns a record for this keyValue
/// </summary>
/// <param name="keyValue">Key Value</param>
/// <returns></returns>
public static T FetchByID(int? keyValue)
{
return fetchByID(keyValue);
}
/// <summary>
/// Returns a record for this keyValue
/// </summary>
/// <param name="keyValue">Key Value</param>
/// <returns></returns>
public static T FetchByID(long keyValue)
{
return fetchByID(keyValue);
}
/// <summary>
/// Returns a record for this keyValue
/// </summary>
/// <param name="keyValue">Key Value</param>
/// <returns></returns>
public static T FetchByID(long? keyValue)
{
return fetchByID(keyValue);
}
/// <summary>
/// Returns a record for this keyValue
/// </summary>
/// <param name="keyValue">Key Value</param>
/// <returns></returns>
public static T FetchByID(decimal keyValue)
{
return fetchByID(keyValue);
}
/// <summary>
/// Returns a record for this keyValue
/// </summary>
/// <param name="keyValue">Key Value</param>
/// <returns></returns>
public static T FetchByID(decimal? keyValue)
{
return fetchByID(keyValue);
}
/// <summary>
/// Returns a record for this keyValue
/// </summary>
/// <param name="keyValue">Key Value</param>
/// <returns></returns>
public static T FetchByID(Guid keyValue)
{
return fetchByID(keyValue);
}
/// <summary>
/// Returns a record for this keyValue
/// </summary>
/// <param name="keyValue">Key Value</param>
/// <returns></returns>
public static T FetchByID(Guid? keyValue)
{
return fetchByID(keyValue);
}
/// <summary>
/// Returns a record for this keyValue
/// </summary>
/// <param name="keyValue">Key Value</param>
/// <returns></returns>
public static T FetchByID(string keyValue)
{
return fetchByID(keyValue);
}
/// <summary>
/// Returns a record for this keyValue
/// </summary>
/// <param name="keyValue">Key Value</param>
/// <returns></returns>
private static T fetchByID(object keyValue)
{
if (keyValue == null)
{
return null;
}
//makes sure the table schema is loaded
T item = new T();
//build the query
Query q = new Query(BaseSchema);
q.AddWhere(BaseSchema.PrimaryKey.ColumnName, Comparison.Equals, keyValue);
//CheckLogicalDelete(q);
//load the reader
IDataReader rdr = DataService.GetReader(q.BuildSelectCommand());
if (rdr.Read())
{
item.Load(rdr);
}
rdr.Close();
if (!item._isNew && item.IsLoaded)
{
return item;
}
return null;
}
#endregion
#region CommandMethods
protected static string ParameterPrefix
{
get
{
return BaseSchema.Provider.GetParameterPrefix();
}
}
/// <summary>
/// Made Public for use with transactions
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public QueryCommand GetInsertCommand(string userName)
{
Query q = new Query(BaseSchema);
q.QueryType = QueryType.Insert;
QueryCommand cmd = new QueryCommand(DataService.GetSql(q), ProviderName);
cmd.ProviderName = BaseSchema.Provider.Name;
//loop the Columns and addin the params
foreach (TableSchema.TableColumn column in BaseSchema.Columns)
{
if (!column.AutoIncrement && !column.IsReadOnly)
{
object oVal;
bool insertValue = true;
if(Utility.IsMatch(column.ColumnName, ReservedColumnName.CREATED_BY) || Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_BY))
{
oVal = userName;
}
else if(Utility.IsMatch(column.ColumnName, ReservedColumnName.CREATED_ON) || Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_ON))
{
oVal = DateTime.Now;
}
else if(column.DataType == DbType.Guid)
{
if (!Utility.IsMatch(column.DefaultSetting, SqlSchemaVariable.DEFAULT))
{
oVal = GetColumnValue<Guid>(column.ColumnName);
bool isEmptyGuid = Utility.IsMatch(oVal.ToString(), Guid.Empty.ToString());
if(column.IsNullable && isEmptyGuid)
{
oVal = null;
}
else if(column.IsPrimaryKey && isEmptyGuid)
{
oVal = Guid.NewGuid();
}
}
else
{
oVal = null;
insertValue = false;
}
//else if(column.DataBaseHasDefaultValue)
//{
// insertValue = false;
//}
}
else
{
oVal = GetColumnValue<object>(column.ColumnName);
//if the value is a boolean, it can be read improperly
//reset to 0 or 1
if(oVal != null && column.DataType == DbType.Boolean)
{
if(Utility.IsMatch(oVal.ToString(), Boolean.FalseString))
{
oVal = 0;
}
else if(Utility.IsMatch(oVal.ToString(), Boolean.TrueString))
{
oVal = 1;
}
}
}
if(oVal == null)
{
oVal = DBNull.Value;
}
if(insertValue)
{
cmd.Parameters.Add(ParameterPrefix + column.ColumnName, oVal, column.DataType);
}
}
}
return cmd;
}
public QueryCommand GetUpdateCommand(string userName)
{
Query q = new Query(BaseSchema);
q.QueryType = QueryType.Update;
QueryCommand cmd = new QueryCommand(DataService.GetSql(q), ProviderName);
cmd.ProviderName = BaseSchema.Provider.Name;
//loop the Columns and addin the params
foreach (TableSchema.TableColumn column in BaseSchema.Columns)
{
if(!column.IsReadOnly)
{
object oVal;
if(Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_BY))
{
oVal = userName;
}
else if(Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_ON))
{
oVal = DateTime.Now;
}
else if(column.DataType == DbType.Guid)
{
oVal = GetColumnValue<Guid>(column.ColumnName);
if(column.IsNullable && Utility.IsMatch(oVal.ToString(), Guid.Empty.ToString()))
{
oVal = null;
}
}
else
{
oVal = GetColumnValue<object>(column.ColumnName);
}
if(oVal == null)
{
oVal = DBNull.Value;
}
cmd.Parameters.Add(ParameterPrefix + column.ColumnName, oVal, column.DataType);
}
}
return cmd;
}
public static QueryCommand GetDeleteCommand(object keyID)
{
Query q = new Query(BaseSchema);
q.QueryType = QueryType.Delete;
q.AddWhere(BaseSchema.PrimaryKey.ColumnName, keyID);
return DataService.BuildCommand(q);
}
public static QueryCommand GetDeleteCommand(string columnName, object oValue)
{
Query q = new Query(BaseSchema);
q.QueryType = QueryType.Delete;
q.AddWhere(columnName, oValue);
return DataService.BuildCommand(q);
}
#endregion
#region Persistence
protected virtual void PreUpdate()
{
}
protected virtual void PostUpdate()
{
}
/// <summary>
/// Saves this object's state to the selected Database.
/// </summary>
public void Save()
{
Save(String.Empty);
}
/// <summary>
/// Saves this object's state to the selected Database.
/// </summary>
/// <param name="userID"></param>
public void Save(int userID)
{
Save(userID.ToString());
}
/// <summary>
/// Saves this object's state to the selected Database.
/// </summary>
/// <param name="userID"></param>
public void Save(Guid userID)
{
string sUserID = userID.ToString();
Save(sUserID);
}
public virtual bool Validate() {
this.ValidateColumnSettings();
return this.Errors.Count==0;
}
/// <summary>
/// Saves this object's state to the selected Database.
/// </summary>
/// <param name="userName"></param>
public void Save(string userName)
{
bool isValid = Validate();
if(isValid)
{
QueryCommand cmd = GetSaveCommand(userName);
if (cmd == null) return;
PreUpdate();
//reset the Primary Key with the id passed back by the operation
object pkVal = DataService.ExecuteScalar(cmd);
if(pkVal != null)
{
if(pkVal.GetType() == typeof(decimal))
{
pkVal = Convert.ToInt32(pkVal);
}
//set the primaryKey, only if an auto-increment
//if (table.PrimaryKey.AutoIncrement)
// HACK: GUID fix
if(BaseSchema.PrimaryKey.AutoIncrement || BaseSchema.PrimaryKey.DataType == DbType.Guid)
{
try
{
SetPrimaryKey(pkVal);
}
catch
{
//this will happen if there is no PK defined on a table. Catch this and notify
throw new Exception("No Primary Key is defined for this table. A primary key is required to use SubSonic");
}
}
}
//set this object as old
MarkOld();
MarkClean();
PostUpdate();
}
else
{
//throw an Exception
string notification = string.Empty;
foreach(string message in this.Errors)
{
notification += message + Environment.NewLine;
}
throw new Exception("Can't save: " + notification);
}
}
public QueryCommand GetSaveCommand()
{
return GetSaveCommand(string.Empty);
}
public QueryCommand GetSaveCommand(string userName)
{
if (IsNew)
return GetInsertCommand(userName);
if (IsDirty)
return GetUpdateCommand(userName);
return null;
}
/// <summary>
/// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()
/// </summary>
/// <returns>Number of rows affected by the operation</returns>
public static int Delete(object keyID)
{
return DeleteByParameter(BaseSchema.PrimaryKey.ColumnName, keyID, null);
}
/// <summary>
/// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()
/// </summary>
/// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>
/// <param name="oValue">The value that will be compared against columnName to determine deletion</param>
/// <returns>Number of rows affected by the operation</returns>
public static int Delete(string columnName, object oValue)
{
return DeleteByParameter(columnName, oValue, null);
}
/// <summary>
/// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()
/// </summary>
/// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>
/// <param name="oValue">The value that will be compared against columnName to determine deletion</param>
/// <param name="userName">The userName that the record will be updated with. Only relevant if the record contains Deleted or IsDeleted properties</param>
/// <returns>Number of rows affected by the operation</returns>
public static int Delete(string columnName, object oValue, string userName)
{
return DeleteByParameter(columnName, oValue, userName);
}
/// <summary>
/// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()
/// </summary>
/// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>
/// <param name="oValue">The value that will be compared against columnName to determine deletion</param>
/// <param name="userName">The userName that the record will be updated with. Only relevant if the record contains Deleted or IsDeleted properties</param>
/// <returns>Number of rows affected by the operation</returns>
private static int DeleteByParameter(string columnName, object oValue, string userName)
{
int iOut = 0;
bool containsDeleted = BaseSchema.Columns.Contains(ReservedColumnName.DELETED);
bool containsIsDeleted = BaseSchema.Columns.Contains(ReservedColumnName.IS_DELETED);
bool containsModifiedBy = BaseSchema.Columns.Contains(ReservedColumnName.MODIFIED_BY);
bool containsModifiedOn = BaseSchema.Columns.Contains(ReservedColumnName.MODIFIED_ON);
if (containsDeleted || containsIsDeleted)
{
//update the column and set deleted=true;
//new T();
Query qry = new Query(BaseSchema);
if (containsDeleted)
{
qry.AddUpdateSetting(ReservedColumnName.DELETED, true);
}
if (containsIsDeleted)
{
qry.AddUpdateSetting(ReservedColumnName.IS_DELETED, true);
}
if (containsModifiedBy && !String.IsNullOrEmpty(userName))
{
qry.AddUpdateSetting(ReservedColumnName.MODIFIED_BY, userName);
}
if (containsModifiedOn)
{
qry.AddUpdateSetting(ReservedColumnName.MODIFIED_ON, DateTime.Now);
}
qry.AddWhere(columnName, oValue);
qry.Execute();
}
else
{
iOut = DestroyByParameter(columnName, oValue);
}
return iOut;
}
/// <summary>
/// Deletes the record in the table, even if it contains Deleted or IsDeleted flag columns
/// </summary>
/// <returns>Number of rows affected by the operation</returns>
public static int Destroy(object keyID)
{
return DestroyByParameter(BaseSchema.PrimaryKey.ColumnName, keyID);
}
/// <summary>
/// Deletes the record in the table, even if it contains Deleted or IsDeleted flag columns
/// </summary>
/// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>
/// <param name="oValue">The value that will be compared against columnName to determine deletion</param>
/// <returns>Number of rows affected by the operation</returns>
public static int Destroy(string columnName, object oValue)
{
return DestroyByParameter(columnName, oValue);
}
/// <summary>
/// Deletes the record in the table, even if it contains Deleted or IsDeleted flag columns
/// </summary>
/// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>
/// <param name="oValue">The value that will be compared against columnName to determine deletion</param>
/// <returns>Number of rows affected by the operation</returns>
private static int DestroyByParameter(string columnName, object oValue)
{
QueryCommand cmd = GetDeleteCommand(columnName, oValue);
cmd.ProviderName = BaseSchema.Provider.Name;
return DataService.ExecuteQuery(cmd);
}
#endregion
}
}