internal virtual void CreateAddCommand(IDbCommand cmd, object entity, IAuditTrail audit = null, string columnNames = null, bool doNotAppendCommonFields = false, bool overrideCreatedUpdatedOn = false)
{
TableAttribute tableInfo = EntityCache.Get(entity.GetType());
if (tableInfo.NeedsHistory && tableInfo.IsCreatedByEmpty(entity))
{
throw new MissingFieldException("CreatedBy is required when Audit Trail is enabled");
}
List <string> columns = new List <string>();
if (!string.IsNullOrEmpty(columnNames))
{
columns.AddRange(columnNames.Split(','));
}
else
{
columns.AddRange(tableInfo.DefaultInsertColumns); //Get columns from Entity attributes loaded in TableInfo
}
bool isPrimaryKeyEmpty = false;
foreach (ColumnAttribute pkCol in tableInfo.Columns.Where(p => p.Value.IsPrimaryKey).Select(p => p.Value))
{
if (pkCol.PrimaryKeyInfo.IsIdentity && tableInfo.IsKeyIdEmpty(entity, pkCol))
{
isPrimaryKeyEmpty = true;
//if identity remove keyfield if added in field list
columns.Remove(pkCol.Name);
}
else if (pkCol.Property.PropertyType == typeof(Guid) && tableInfo.IsKeyIdEmpty(entity, pkCol))
{
isPrimaryKeyEmpty = true;
//if not identity and key not generated, generate before save
tableInfo.SetKeyId(entity, pkCol, Guid.NewGuid());
}
}
#region append common columns
if (!doNotAppendCommonFields)
{
if (!tableInfo.NoIsActive)
{
if (!columns.Contains(Config.ISACTIVE_COLUMN.Name))
{
columns.Add(Config.ISACTIVE_COLUMN.Name);
}
bool isActive = tableInfo.GetIsActive(entity) ?? true;
//when IsActive is not set then true for insert
cmd.AddInParameter("@" + Config.ISACTIVE_COLUMN.Name, Config.ISACTIVE_COLUMN.ColumnDbType, isActive);
if (tableInfo.NeedsHistory)
{
audit.AppendDetail(Config.ISACTIVE_COLUMN.Name, isActive, DbType.Boolean, null);
}
tableInfo.SetIsActive(entity, isActive); //Set IsActive value
}
if (!tableInfo.NoVersionNo)
{
int versionNo = tableInfo.GetVersionNo(entity) ?? 1; //set defualt versionno 1 for Insert
if (versionNo == 0)
{
versionNo = 1; //set defualt versionno 1 for Insert even if its zero or null
}
if (!columns.Contains(Config.VERSIONNO_COLUMN.Name))
{
columns.Add(Config.VERSIONNO_COLUMN.Name);
}
cmd.AddInParameter("@" + Config.VERSIONNO_COLUMN.Name, Config.VERSIONNO_COLUMN.ColumnDbType, versionNo);
tableInfo.SetVersionNo(entity, versionNo); //Set VersionNo value
}
if (!tableInfo.NoCreatedBy)
{
if (!columns.Contains(Config.CREATEDBY_COLUMN.Name))
{
columns.Add(Config.CREATEDBY_COLUMN.Name);
}
cmd.AddInParameter("@" + Config.CREATEDBY_COLUMN.Name, Config.CREATEDBY_COLUMN.ColumnDbType, tableInfo.GetCreatedBy(entity));
}
if (!tableInfo.NoCreatedOn & !columns.Contains(Config.CREATEDON_COLUMN.Name))
{
columns.Add(Config.CREATEDON_COLUMN.Name);
}
if (!tableInfo.NoUpdatedBy)
{
if (!columns.Contains(Config.UPDATEDBY_COLUMN.Name))
{
columns.Add(Config.UPDATEDBY_COLUMN.Name);
}
cmd.AddInParameter("@" + Config.UPDATEDBY_COLUMN.Name, Config.UPDATEDBY_COLUMN.ColumnDbType, tableInfo.GetCreatedBy(entity));
}
if (!tableInfo.NoUpdatedOn & !columns.Contains(Config.UPDATEDON_COLUMN.Name))
{
columns.Add(Config.UPDATEDON_COLUMN.Name);
}
}
#endregion
//append @ before each fields to add as parameter
List <string> parameters = columns.Select(c => "@" + c).ToList();
int pIndex = parameters.FindIndex(c => c == "@" + Config.CREATEDON_COLUMN.Name);
if (pIndex >= 0)
{
var createdOn = Helper.GetDateTimeOrDatabaseDateTimeSQL(tableInfo.GetCreatedOn(entity), this, overrideCreatedUpdatedOn);
if (createdOn is string)
{
parameters[pIndex] = (string)createdOn;
}
else
{
cmd.AddInParameter(parameters[pIndex], Config.CREATEDON_COLUMN.ColumnDbType, createdOn);
}
//parameters[pIndex] = (string)Helper.GetDateTimeOrDatabaseDateTimeSQL(tableInfo.GetCreatedOn(entity), this, overrideCreatedUpdatedOn);
}
pIndex = parameters.FindIndex(c => c == "@" + Config.UPDATEDON_COLUMN.Name);
if (pIndex >= 0)
{
var updatedOn = Helper.GetDateTimeOrDatabaseDateTimeSQL(tableInfo.GetUpdatedOn(entity), this, overrideCreatedUpdatedOn);
if (updatedOn is string)
{
parameters[pIndex] = (string)updatedOn;
}
else
{
cmd.AddInParameter(parameters[pIndex], Config.CREATEDON_COLUMN.ColumnDbType, updatedOn);
}
//parameters[pIndex] = (string)Helper.GetDateTimeOrDatabaseDateTimeSQL(tableInfo.GetUpdatedOn(entity), this, overrideCreatedUpdatedOn);
}
StringBuilder cmdText = new StringBuilder();
cmdText.Append($"INSERT INTO {tableInfo.FullName} ({string.Join(",", columns)}) VALUES({string.Join(",", parameters)});");
if (tableInfo.IsKeyIdentity() && isPrimaryKeyEmpty)
{
//add query to get inserted id
cmdText.Append(LASTINSERTEDROWIDSQL);
}
//remove common columns and parameters already added above
columns.RemoveAll(c => c == Config.CREATEDON_COLUMN.Name || c == Config.CREATEDBY_COLUMN.Name ||
c == Config.UPDATEDON_COLUMN.Name || c == Config.UPDATEDBY_COLUMN.Name ||
c == Config.VERSIONNO_COLUMN.Name || c == Config.ISACTIVE_COLUMN.Name);
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdText.ToString();
for (int i = 0; i < columns.Count(); i++)
{
tableInfo.Columns.TryGetValue(columns[i], out ColumnAttribute columnInfo); //find column attribute
DbType dbType = DbType.Object;
object columnValue = null;
if (columnInfo != null && columnInfo.GetMethod != null)
{
dbType = columnInfo.ColumnDbType;
columnValue = columnInfo.GetAction(entity);
if (tableInfo.NeedsHistory)
{
audit.AppendDetail(columns[i], columnValue, dbType, null);
}
}
cmd.AddInParameter("@" + columns[i], dbType, columnValue);
}
}
/// <summary>
/// Read and Return chain of Record from insert to update till now for a given Record of an Entity
/// </summary>
/// <param name="id"></param>
/// <returns>List of Entity with modification</returns>
public IEnumerable <Entity> ReadAll(object id)
{
var lstAudit = ReadAll("*", $"tablename=@TableName AND recordid=@RecordId", new { TableName = entityTableInfo.Name, RecordId = id.ToString() }, Config.CreatedOnColumnName + " ASC").ToList();
Entity current = default;
foreach (AuditTrailKeyValue audit in lstAudit)
{
if (current == null)
{
//create new object
current = new Entity();
if (!entityTableInfo.NoCreatedBy)
{
entityTableInfo.SetCreatedBy(current, audit.CreatedBy);
}
if (!entityTableInfo.NoCreatedOn)
{
entityTableInfo.SetCreatedOn(current, audit.CreatedOn);
}
entityTableInfo.PkColumn.SetAction(current, audit.RecordId.ConvertTo(entityTableInfo.PkColumn.Property.PropertyType));
}
else
{
current = EntityCache.CloneObjectWithIL(current);
}
var lstAuditTrailDetail = detailRepo.ReadAll(null, "audittrailid=@audittrailid", new { audittrailid = audit.AuditTrailId }).ToList();
audit.lstAuditTrailDetail = lstAuditTrailDetail.Cast <IAuditTrailDetail>().ToList();
//render modified values
foreach (AuditTrailKeyValueDetail detail in audit.lstAuditTrailDetail)
{
if (detail.NewValue == null)
{
continue;
}
//find column
entityTableInfo.Columns.TryGetValue(detail.ColumnName, out ColumnAttribute col);
if (col == null)
{
continue;
}
object convertedValue = null;
if (col.Property.PropertyType == typeof(bool) || col.Property.PropertyType == typeof(bool?))
{
convertedValue = (detail.NewValue.ToString() == "1" ? true : false);
}
else if (col.Property.PropertyType == typeof(DateTime) || col.Property.PropertyType == typeof(DateTime?))
{
convertedValue = detail.NewValue.ToString().FromSQLDateTime();
}
else
{
convertedValue = detail.NewValue.ConvertTo(col.Property.PropertyType);
}
col.SetAction(current, convertedValue);
}
if (!entityTableInfo.NoVersionNo)
{
entityTableInfo.SetVersionNo(current, audit.RecordVersionNo);
}
if (!entityTableInfo.NoUpdatedBy)
{
entityTableInfo.SetUpdatedOn(current, audit.CreatedOn);
}
if (!entityTableInfo.NoUpdatedBy)
{
entityTableInfo.SetUpdatedBy(current, audit.CreatedBy);
}
yield return(current);
}
}