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