internal virtual void CreateSelectCommandForReadOne <T>(IDbCommand cmd, object id, string columns)
{
TableAttribute tableInfo = EntityCache.Get(typeof(T));
cmd.CommandType = CommandType.Text;
if (tableInfo.PkColumnList.Count > 1)
{
if (!(id is T))
{
throw new InvalidOperationException("Entity has multiple primary keys. Pass entity setting Primary Key attributes.");
}
StringBuilder cmdText = new StringBuilder($"SELECT {columns} FROM {tableInfo.FullName} WHERE ");
int index = 0;
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
cmdText.Append($" {(index > 0 ? " AND " : "")} {pkCol.Name}=@{pkCol.Name}");
cmd.AddInParameter("@" + pkCol.Name, pkCol.ColumnDbType, tableInfo.GetKeyId(id, pkCol));
index++;
}
cmd.CommandText = cmdText.ToString();
}
else
{
cmd.CommandText = $"SELECT {columns} FROM {tableInfo.FullName} WHERE {tableInfo.PkColumn.Name}=@{tableInfo.PkColumn.Name}";
cmd.AddInParameter(tableInfo.PkColumn.Name, tableInfo.PkColumn.ColumnDbType, id);
}
}
/// <summary>
/// Add record to AuditTrail. Insert or Update action on a Table
/// </summary>
/// <param name="entity">Entity which was modified</param>
/// <param name="operation">Insert or Update operation</param>
/// <param name="audit">Audit Trail object of type IAuditTrail</param>
/// <returns>true if success, False if fail</returns>
public bool Add(Entity entity, RecordOperationEnum operation, IAuditTrail audit)
{
CreateTableIfNotExist();
audit.OperationType = operation;
audit.RecordId = entityTableInfo.GetKeyId(entity).ToString();
if (!entityTableInfo.NoVersionNo)
{
audit.RecordVersionNo = (operation == RecordOperationEnum.Insert ? 1 : entityTableInfo.GetVersionNo(entity)); //always 1 for new insert
}
audit.TableName = entityTableInfo.Name;
return(AddAuditTrail((AuditTrailKeyValue)audit));
}
/// <summary>
/// Add record to AuditTrail. Insert or Update action on a Table
/// </summary>
/// <param name="entity">Entity which was modified</param>
/// <param name="operation">Insert or Update operation</param>
/// <param name="audit">Audit Trail object of type IAuditTrail</param>
/// <returns>true if success, False if fail</returns>
public bool Add(Entity entity, RecordOperationEnum operation, IAuditTrail audit)
{
CreateTableIfNotExist();
AuditTrail auditTrail = (AuditTrail)audit;
auditTrail.OperationType = operation;
//Remove EntityBase 12-Apr-19
auditTrail.RecordId = entityTableInfo.GetKeyId(entity).ToString();
//Remove EntityBase 12-Apr-19
if (!entityTableInfo.NoVersionNo)
{
auditTrail.RecordVersionNo = (operation == RecordOperationEnum.Insert ? 1 : entityTableInfo.GetVersionNo(entity)); //always 1 for new insert
}
auditTrail.TableName = entityTableInfo.Name;
auditTrail.Details = auditTrail.GenerateString();
auditTrail.AuditTrailId = (long)Add(auditTrail);
return(true);
}
internal virtual void CreateReadAllPagedNoOffsetCommand <T>(IDbCommand cmd, string query, string orderBy, int pageSize, PageNavigationEnum navigation, object[] lastOrderByColumnValues = null, object lastKeyId = null, object parameters = null)
{
string[] orderByColumns = orderBy.Split(',');
string[] orderByDirection = new string[orderByColumns.Length];
for (int i = 0; i < orderByColumns.Length; i++)
{
if (orderByColumns[i].ToLowerInvariant().Contains("desc"))
{
orderByDirection[i] = "DESC";
orderByColumns[i] = orderByColumns[i].ToLowerInvariant().Replace("desc", "").Trim();
}
else
{
orderByDirection[i] = "ASC";
orderByColumns[i] = orderByColumns[i].ToLowerInvariant().Replace("asc", "").Trim();
}
}
if (orderByColumns.Length == 0)
{
throw new MissingMemberException("Orderby column(s) is missing");
}
if ((navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous) && lastOrderByColumnValues.Length != orderByColumns.Length)
{
throw new MissingMemberException("For Next and Previous Navigation Length of Last Values must be equal to orderby columns length");
}
if ((navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous) && lastKeyId == null)
{
throw new MissingMemberException("For Next and Previous Navigation Last KeyId is required");
}
TableAttribute tableInfo = EntityCache.Get(typeof(T));
bool hasWhere = query.ToLowerInvariant().Contains("where");
StringBuilder pagedCriteria = new StringBuilder();
StringBuilder pagedOrderBy = new StringBuilder();
if (!hasWhere)
{
pagedCriteria.Append(" WHERE 1=1");
}
for (int i = 0; i < orderByColumns.Length; i++)
{
string applyEquals = (i <= orderByColumns.Length - 2 ? "=" : "");
if (navigation == PageNavigationEnum.Next)
{
//when multiple orderbycolumn - apply '>=' or '<=' till second last column
if (orderByDirection[i] == "ASC")
{
pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo,">")}) OR {orderByColumns[i]} >{applyEquals} @p_{orderByColumns[i]})");
}
else
{
pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo, "<")}) OR ({orderByColumns[i]} IS NULL OR {orderByColumns[i]} <{applyEquals} @p_{orderByColumns[i]}))");
}
}
else if (navigation == PageNavigationEnum.Previous)
{
if (orderByDirection[i] == "ASC")
{
pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo, "<")}) OR ({orderByColumns[i]} IS NULL OR {orderByColumns[i]} <{applyEquals} @p_{orderByColumns[i]}))");
}
else
{
pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo, ">")}) OR {orderByColumns[i]} >{applyEquals} @p_{orderByColumns[i]})");
}
}
if (navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous)
{
//add Parameter for Last value of ordered column
DbType dbType;
//see if column exists in TableInfo
tableInfo.Columns.TryGetValue(orderByColumns[i], out ColumnAttribute orderByColumn);
if (orderByColumn != null)
{
dbType = orderByColumn.ColumnDbType;
}
else
{
TypeCache.TypeToDbType.TryGetValue(lastOrderByColumnValues[i].GetType(), out dbType);
}
cmd.AddInParameter("@p_" + orderByColumns[i], dbType, lastOrderByColumnValues[i]);
}
if (i > 0)
{
pagedOrderBy.Append(",");
}
if (navigation == PageNavigationEnum.Last || navigation == PageNavigationEnum.Previous)
{
//reverse sort as we are going backward
pagedOrderBy.Append($"{orderByColumns[i]} {(orderByDirection[i] == "ASC" ? "DESC" : "ASC")}");
}
else
{
pagedOrderBy.Append($"{orderByColumns[i]} {orderByDirection[i]}");
}
}
//add keyfield parameter for Next and Previous navigation
if (navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous)
{
//add LastKeyId Parameter
if (tableInfo.PkColumnList.Count > 1)
{
if (!(lastKeyId is T))
{
throw new InvalidOperationException("Entity has multiple primary keys. Pass entity setting Primary Key attributes.");
}
int index = 0;
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
cmd.AddInParameter("@p_" + pkCol.Name, pkCol.ColumnDbType, tableInfo.GetKeyId(lastKeyId, pkCol));
index++;
}
}
else
{
cmd.AddInParameter("@p_" + tableInfo.PkColumn.Name, tableInfo.PkColumn.ColumnDbType, lastKeyId);
}
}
//add keyfield in orderby clause. Direction will be taken from 1st orderby column
if (navigation == PageNavigationEnum.Last || navigation == PageNavigationEnum.Previous)
{
//reverse sort as we are going backward
if (tableInfo.PkColumnList.Count > 1)
{
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
pagedOrderBy.Append($",{pkCol.Name} {(orderByDirection[0] == "ASC" ? "DESC" : "ASC")}");
}
}
else
{
pagedOrderBy.Append($",{tableInfo.PkColumn.Name} {(orderByDirection[0] == "ASC" ? "DESC" : "ASC")}");
}
}
else
{
if (tableInfo.PkColumnList.Count > 1)
{
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
pagedOrderBy.Append($",{pkCol.Name} {orderByDirection[0]}");
}
}
else
{
pagedOrderBy.Append($",{tableInfo.PkColumn.Name} {orderByDirection[0]}");
}
}
cmd.CommandType = CommandType.Text;
if (this is MsSqlDatabase)
{
cmd.CommandText = $"SELECT * FROM (SELECT TOP {pageSize} * FROM ({query} {pagedCriteria.ToString()}) AS r1 ORDER BY {pagedOrderBy}) AS r2 ORDER BY {orderBy}";
}
else
{
cmd.CommandText = $"SELECT * FROM ({query} {pagedCriteria.ToString()} ORDER BY {pagedOrderBy} LIMIT {pageSize}) AS r ORDER BY {orderBy}";
}
ParameterCache.AddParameters(parameters, cmd); //ParameterCache.GetFromCache(parameters, cmd).Invoke(parameters, cmd);
}
internal virtual bool CreateUpdateCommand(IDbCommand cmd, object entity, object oldEntity, IAuditTrail audit = null, string columnNames = null, bool doNotAppendCommonFields = false, bool overrideCreatedUpdatedOn = false)
{
bool isUpdateNeeded = false;
TableAttribute tableInfo = EntityCache.Get(entity.GetType());
if (!tableInfo.NoUpdatedBy && tableInfo.IsUpdatedByEmpty(entity))
{
throw new MissingFieldException("Updated By is required");
}
List <string> columns = new List <string>();
if (!string.IsNullOrEmpty(columnNames))
{
columns.AddRange(columnNames.Split(','));
}
else
{
columns.AddRange(tableInfo.DefaultUpdateColumns); //Get columns from Entity attributes loaded in TableInfo
}
StringBuilder cmdText = new StringBuilder();
cmdText.Append($"UPDATE {tableInfo.FullName} SET ");
//add default columns if doesn't exists
if (!doNotAppendCommonFields)
{
if (!tableInfo.NoVersionNo && !columns.Contains(Config.VERSIONNO_COLUMN.Name))
{
columns.Add(Config.VERSIONNO_COLUMN.Name);
}
if (!tableInfo.NoUpdatedBy && !columns.Contains(Config.UPDATEDBY_COLUMN.Name))
{
columns.Add(Config.UPDATEDBY_COLUMN.Name);
}
if (!tableInfo.NoUpdatedOn && !columns.Contains(Config.UPDATEDON_COLUMN.Name))
{
columns.Add(Config.UPDATEDON_COLUMN.Name);
}
}
//remove primarykey, createdon and createdby columns if exists
columns.RemoveAll(c => tableInfo.PkColumnList.Select(p => p.Name).Contains(c));
columns.RemoveAll(c => c == Config.CREATEDON_COLUMN.Name ||
c == Config.CREATEDBY_COLUMN.Name);
for (int i = 0; i < columns.Count(); i++)
{
if (columns[i].Equals(Config.VERSIONNO_COLUMN.Name, StringComparison.OrdinalIgnoreCase))
{
cmdText.Append($"{columns[i]} = {columns[i]}+1");
cmdText.Append(",");
}
else if (columns[i].Equals(Config.UPDATEDBY_COLUMN.Name, StringComparison.OrdinalIgnoreCase))
{
cmdText.Append($"{columns[i]} = @{columns[i]}");
cmdText.Append(",");
cmd.AddInParameter("@" + columns[i], Config.UPDATEDBY_COLUMN.ColumnDbType, tableInfo.GetUpdatedBy(entity));
}
else if (columns[i].Equals(Config.UPDATEDON_COLUMN.Name, StringComparison.OrdinalIgnoreCase))
{
var updatedOn = Helper.GetDateTimeOrDatabaseDateTimeSQL(tableInfo.GetUpdatedOn(entity), this, overrideCreatedUpdatedOn);
if (updatedOn is string)
{
cmdText.Append($"{columns[i]} = {CURRENTDATETIMESQL}");
}
else
{
cmdText.Append($"{columns[i]} = @{columns[i]}");
cmd.AddInParameter("@" + columns[i], Config.UPDATEDON_COLUMN.ColumnDbType, updatedOn);
}
cmdText.Append(",");
}
else
{
bool includeInUpdate = true;
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);
includeInUpdate = oldEntity == null; //include in update when oldEntity not available
//compare with old object to check whether update is needed or not
object oldColumnValue = null;
if (oldEntity != null)
{
oldColumnValue = columnInfo.GetAction(oldEntity);
if (oldColumnValue != null && columnValue != null)
{
if (!oldColumnValue.Equals(columnValue)) //add to history only if property is modified
{
includeInUpdate = true;
}
}
else if (oldColumnValue == null && columnValue != null)
{
includeInUpdate = true;
}
else if (oldColumnValue != null)
{
includeInUpdate = true;
}
}
if (tableInfo.NeedsHistory && includeInUpdate)
{
audit.AppendDetail(columns[i], columnValue, dbType, oldColumnValue);
}
}
if (includeInUpdate)
{
isUpdateNeeded = true;
cmdText.Append($"{columns[i]} = @{columns[i]}");
cmdText.Append(",");
cmd.AddInParameter("@" + columns[i], dbType, columnValue);
}
}
}
cmdText.RemoveLastComma(); //Remove last comma if exists
cmdText.Append(" WHERE ");
if (tableInfo.PkColumnList.Count > 1)
{
int index = 0;
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
cmdText.Append($" {(index > 0 ? " AND " : "")} {pkCol.Name}=@{pkCol.Name}");
cmd.AddInParameter("@" + pkCol.Name, pkCol.ColumnDbType, tableInfo.GetKeyId(entity, pkCol));
index++;
}
}
else
{
cmdText.Append($" {tableInfo.PkColumn.Name}=@{tableInfo.PkColumn.Name}");
cmd.AddInParameter("@" + tableInfo.PkColumn.Name, tableInfo.PkColumn.ColumnDbType, tableInfo.GetKeyId(entity));
}
if (Config.DbConcurrencyCheck && !tableInfo.NoVersionNo)
{
cmdText.Append($" AND {Config.VERSIONNO_COLUMN.Name}=@{Config.VERSIONNO_COLUMN.Name}");
cmd.AddInParameter("@" + Config.VERSIONNO_COLUMN.Name, Config.VERSIONNO_COLUMN.ColumnDbType, tableInfo.GetVersionNo(entity));
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdText.ToString();
return(isUpdateNeeded);
}