/// <summary>
/// Given an AbstractRecord object, returns a SELECT QueryCommand object used to generate SQL.
/// </summary>
/// <param name="item">The AbstractRecord object</param>
/// <returns></returns>
public static QueryCommand GetSelectCommand(RecordBase <T> item)
{
Query q = new Query(item.GetSchema());
q.QueryType = QueryType.Select;
QueryCommand cmd = DataService.BuildCommand(q);
return(cmd);
}
/// <summary>
/// Given an AbstractRecord object, returns a INSERT QueryCommand object used to generate SQL.
/// </summary>
/// <param name="item">The AbstractRecord object</param>
/// <param name="userName">An optional username to be used if audit fields are present</param>
/// <returns></returns>
public static QueryCommand GetInsertCommand(RecordBase <T> item, string userName)
{
TableSchema.Table BaseSchema = item.GetSchema();
Query q = new Query(BaseSchema)
{
QueryType = QueryType.Insert
};
QueryCommand cmd = new QueryCommand(DataService.GetSql(q), BaseSchema.Provider.Name)
{
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.MatchesOne(column.ColumnName, ReservedColumnName.CREATED_BY, ReservedColumnName.MODIFIED_BY))
{
oVal = userName;
}
else if (Utility.MatchesOne(column.ColumnName, ReservedColumnName.CREATED_ON, ReservedColumnName.MODIFIED_ON))
{
oVal = BaseSchema.Provider.Now;
}
else if (column.DataType == DbType.Guid)
{
if (!Utility.IsMatch(column.DefaultSetting, SqlSchemaVariable.DEFAULT))
{
oVal = item.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
{
oVal = item.GetColumnValue <object>(column.ColumnName);
// if the value is a boolean, it can be read improperly so reset it 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(column.ParameterName, oVal, column.DataType);
}
}
}
return(cmd);
}
/// <summary>
/// Given an AbstractRecord object, returns a UPDATE QueryCommand object used to generate SQL.
/// </summary>
/// <param name="item">The AbstractRecord object</param>
/// <param name="userName">An optional username to be used if audit fields are present</param>
/// <returns></returns>
public static QueryCommand GetUpdateCommand(RecordBase <T> item, string userName)
{
TableSchema.Table BaseSchema = item.GetSchema();
Query q = new Query(BaseSchema)
{
QueryType = QueryType.Update
};
string sql = DataService.Providers[BaseSchema.Provider.Name].GetUpdateSql(q, item.DirtyColumns);
QueryCommand cmd = new QueryCommand(sql, BaseSchema.Provider.Name)
{
ProviderName = BaseSchema.Provider.Name
};
bool foundModifiedBy = false;
bool foundModifiedOn = false;
// loop the Columns and addin the params
foreach (TableSchema.TableColumn column in item.DirtyColumns)
{
if (!column.IsReadOnly)
{
object oVal;
if (Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_BY))
{
oVal = userName;
foundModifiedBy = true;
}
else if (Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_ON))
{
oVal = BaseSchema.Provider.Now;
foundModifiedOn = true;
}
else if (column.DataType == DbType.Guid)
{
oVal = item.GetColumnValue <Guid>(column.ColumnName);
if (column.IsNullable && Utility.IsMatch(oVal.ToString(), Guid.Empty.ToString()))
{
oVal = null;
}
}
else
{
oVal = item.GetColumnValue <object>(column.ColumnName);
}
if (oVal == null)
{
oVal = DBNull.Value;
}
//escape this so we don't set the value to NULL
cmd.Parameters.Add(column.ParameterName, oVal, column.DataType);
}
}
// if there are ModifiedOn and ModifiedBy, add them in as well
if (BaseSchema.Columns.Contains(ReservedColumnName.MODIFIED_ON) && !foundModifiedOn)
{
string modOnParamName = String.Concat(BaseSchema.Provider.GetParameterPrefix(), ReservedColumnName.MODIFIED_ON);
if (!cmd.Parameters.Contains(modOnParamName))
{
cmd.Parameters.Add(modOnParamName, BaseSchema.Provider.Now, DbType.DateTime);
}
}
if (BaseSchema.Columns.Contains(ReservedColumnName.MODIFIED_BY) && !foundModifiedBy)
{
string modByParamName = String.Concat(BaseSchema.Provider.GetParameterPrefix(), ReservedColumnName.MODIFIED_BY);
if (!cmd.Parameters.Contains(modByParamName))
{
cmd.Parameters.Add(modByParamName, userName, DbType.String);
}
}
//if nothing has changed...
if (cmd.Parameters.Count == 0)
{
return(null);
}
// make sure to set the PKs
foreach (TableSchema.TableColumn col in BaseSchema.PrimaryKeys)
{
cmd.Parameters.Add(col.ParameterName, item.GetColumnValue <object>(col.ColumnName), col.DataType);
}
return(cmd);
}