internal IEnumerable <T> ReadAll <T>(string tableName, object id) where T : EntityBase, new()
{
TableAttribute tableInfo = EntityCache.Get(typeof(T));
var lstAudit = ReadAll(null, $"{Config.VegaConfig.AuditTableNameColumnName}=@TableName AND {Config.VegaConfig.AuditRecordIdColumnName}=@RecordId", new { TableName = tableName, RecordId = id.ToString() }, Config.VegaConfig.CreatedOnColumnName + " ASC");
T current = null;
foreach (AuditTrial audit in lstAudit)
{
audit.Split();
if (current == null)
{
//create new object
current = new T
{
CreatedBy = audit.CreatedBy,
CreatedOn = audit.CreatedOn,
};
current.KeyId = audit.RecordId.ConvertTo(tableInfo.PrimaryKeyColumn.Property.PropertyType);
}
else
{
current = (T)current.ShallowCopy(); //create copy of current object
}
//render modified values
foreach (AuditTrailDetail detail in audit.lstAuditDetails)
{
if (detail.Value == null)
{
continue;
}
//find column
tableInfo.Columns.TryGetValue(detail.Column, out ColumnAttribute col);
if (col == null)
{
continue;
}
object convertedValue = null;
if (col.Property.PropertyType == typeof(bool))
{
convertedValue = (detail.Value.ToString() == "1" ? true : false);
}
else if (col.Property.PropertyType == typeof(DateTime))
{
convertedValue = detail.Value.ToString().FromSQLDateTime();
}
else
{
convertedValue = detail.Value.ConvertTo(col.Property.PropertyType);
}
col.SetMethod.Invoke(current, new object[] { convertedValue });
}
current.VersionNo = audit.RecordVersionNo;
current.UpdatedOn = audit.CreatedOn;
current.UpdatedBy = audit.CreatedBy;
yield return(current);
}
}
public override string CreateTableQuery(Type entity)
{
TableAttribute tableInfo = EntityCache.Get(entity);
StringBuilder createSQL = new StringBuilder($"CREATE TABLE {tableInfo.FullName} (");
string primaryKeyCols = string.Empty;
for (int i = 0; i < tableInfo.Columns.Count; i++)
{
ColumnAttribute col = tableInfo.Columns.ElementAt(i).Value;
if (col.IsPrimaryKey)
{
primaryKeyCols += col.Name + ",";
//13-May-15 Ritesh - fixed bug when primary key type is varchar size was ignored
createSQL.Append($"{col.Name} {GetDBTypeWithSize(col.ColumnDbType, col.Size)} NOT NULL ");
if (col.PrimaryKeyInfo.IsIdentity)
{
createSQL.Append(" IDENTITY ");
}
createSQL.Append(",");
}
else if (col.IgnoreInfo != null && col.IgnoreInfo.Insert && col.IgnoreInfo.Update)
{
continue;
}
else
{
createSQL.Append($"{col.Name} {GetDBTypeWithSize(col.ColumnDbType, col.Size, col.NumericScale)}");
if (IsNullableType(col.Property.PropertyType))
{
createSQL.Append(" NULL ");
}
if (col.Name == Config.CREATEDON_COLUMN.Name || col.Name == Config.UPDATEDON_COLUMN.Name)
{
createSQL.Append(" DEFAULT " + CURRENTDATETIMESQL);
}
createSQL.Append(",");
}
}
if (!string.IsNullOrEmpty(primaryKeyCols))
{
primaryKeyCols = primaryKeyCols.RemoveLastComma();
createSQL.Append($"PRIMARY KEY CLUSTERED({primaryKeyCols})");
}
createSQL.RemoveLastComma(); //Remove last comma if exists
createSQL.Append(");");
if (tableInfo.IsKeyIdentity())
{
createSQL.Append($"SET IDENTITY_INSERT {tableInfo.FullName} OFF");
}
return(createSQL.ToString());
}
internal static Func <IDataReader, T> ReaderToObject(IDataReader rdr)
{
MethodInfo rdrGetValueMethod = rdr.GetType().GetMethod("get_Item", new Type[] { typeof(int) });
Type[] args = { typeof(IDataReader) };
DynamicMethod method = new DynamicMethod("DynamicRead" + Guid.NewGuid().ToString(), typeof(T), args, typeof(Repository <T>).Module, true);
ILGenerator il = method.GetILGenerator();
LocalBuilder result = il.DeclareLocal(typeof(T)); //loc_0
il.Emit(OpCodes.Newobj, typeof(T).GetConstructor(Type.EmptyTypes));
il.Emit(OpCodes.Stloc_0, result); //Pops the current value from the top of the evaluation stack and stores it in a the local variable list at a specified index.
Label tryBlock = il.BeginExceptionBlock();
LocalBuilder valueCopy = il.DeclareLocal(typeof(object)); //declare local variable to store object value. loc_1
il.DeclareLocal(typeof(int)); //declare local variable to store index //loc_2
il.Emit(OpCodes.Ldc_I4_0); //load 0 in index
il.Emit(OpCodes.Stloc_2); //pop and save to local variable loc 2
//get FieldInfo of all properties
TableAttribute tableInfo = EntityCache.Get(typeof(T));
for (int i = 0; i < rdr.FieldCount; i++)
{
tableInfo.Columns.TryGetValue(rdr.GetName(i), out ColumnAttribute columnInfo);
if (columnInfo != null && columnInfo.SetMethod != null)
{
Label endIfLabel = il.DefineLabel();
il.Emit(OpCodes.Ldarg_0); //load the argument. Loads the argument at index 0 onto the evaluation stack.
il.Emit(OpCodes.Ldc_I4, i); //push field index as int32 to the stack. Pushes a supplied value of type int32 onto the evaluation stack as an int32.
il.Emit(OpCodes.Dup); //copy value
il.Emit(OpCodes.Stloc_2); //pop and save value to loc 2
il.Emit(OpCodes.Callvirt, rdrGetValueMethod); //Call rdr[i] method - Calls a late - bound method on an object, pushing the return value onto the evaluation stack.
//TODO: dynamic location using valueCopyLocal
il.Emit(OpCodes.Stloc_1); //pop the value and push in stack location 1
il.Emit(OpCodes.Ldloc_1); //load the variable in location 1
il.Emit(OpCodes.Isinst, typeof(DBNull)); //check whether value is null - Tests whether an object reference (type O) is an instance of a particular class.
il.Emit(OpCodes.Brtrue, endIfLabel); //go to end block if value is null
il.Emit(OpCodes.Ldloc_0); //load T result
il.Emit(OpCodes.Ldloc_1); //TODO: dynamic location using valueCopyLocal
//when Enum are without number values
if (columnInfo.Property.PropertyType.IsEnum)
{
Type numericType = Enum.GetUnderlyingType(columnInfo.Property.PropertyType);
if (rdr.GetFieldType(i) == typeof(string))
{
LocalBuilder stringEnumLocal = il.DeclareLocal(typeof(string));
il.Emit(OpCodes.Castclass, typeof(string)); // stack is now [...][string]
il.Emit(OpCodes.Stloc, stringEnumLocal); // stack is now [...]
il.Emit(OpCodes.Ldtoken, columnInfo.Property.PropertyType); // stack is now [...][enum-type-token]
il.EmitCall(OpCodes.Call, typeof(Type).GetMethod(nameof(Type.GetTypeFromHandle)), null); // stack is now [...][enum-type]
il.Emit(OpCodes.Ldloc, stringEnumLocal); // stack is now [...][enum-type][string]
il.Emit(OpCodes.Ldc_I4_1); // stack is now [...][enum-type][string][true]
il.EmitCall(OpCodes.Call, enumParse, null); // stack is now [...][enum-as-object]
il.Emit(OpCodes.Unbox_Any, columnInfo.Property.PropertyType); // stack is now [...][typed-value]
}
else
{
ConvertValueToEnum(il, rdr.GetFieldType(i), columnInfo.Property.PropertyType, numericType);
}
}
else if (columnInfo.Property.PropertyType.IsValueType)
{
il.Emit(OpCodes.Unbox_Any, rdr.GetFieldType(i)); //type cast
}
// for nullable type fields
if (columnInfo.Property.PropertyType.IsGenericType && columnInfo.Property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
var underlyingType = Nullable.GetUnderlyingType(columnInfo.Property.PropertyType);
il.Emit(OpCodes.Newobj, columnInfo.Property.PropertyType.GetConstructor(new Type[] { underlyingType }));
}
il.Emit(OpCodes.Callvirt, columnInfo.SetMethod);
il.Emit(OpCodes.Nop);
il.MarkLabel(endIfLabel);
}
}
il.BeginCatchBlock(typeof(Exception)); //begin try block. exception is in stack
il.Emit(OpCodes.Ldloc_2); //load index
il.Emit(OpCodes.Ldarg_0); //load argument reader
il.Emit(OpCodes.Ldloc_1); //load value //TODO: dynamic location using valueCopyLocal
il.EmitCall(OpCodes.Call, typeof(ReaderCache <T>).GetMethod(nameof(ReaderCache <T> .HandleException)), null); //call exception handler
il.EndExceptionBlock();
il.Emit(OpCodes.Ldloc, result);
il.Emit(OpCodes.Ret);
var funcType = System.Linq.Expressions.Expression.GetFuncType(typeof(IDataReader), typeof(T));
return((Func <IDataReader, T>)method.CreateDelegate(funcType));
}
//for delete & restore
internal bool Add(object recordId, int recordVersionNo, object updatedBy, RecordOperationEnum operation, TableAttribute tableInfo)
{
if (operation != RecordOperationEnum.Delete && operation != RecordOperationEnum.Recover)
{
throw new InvalidOperationException("Invalid call to this method. This method shall be call for Delete and Recover operation only.");
}
CreateTableIfNotExist();
AuditTrial audit = new AuditTrial
{
OperationType = operation,
RecordId = recordId.ToString(),
RecordVersionNo = recordVersionNo + 1,
TableName = tableInfo.Name,
CreatedBy = updatedBy
};
audit.AppendDetail(Config.ISACTIVE_COLUMN.Name, !(operation == RecordOperationEnum.Delete), DbType.Boolean);
audit.Details = audit.GenerateString();
Add(audit);
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);
}
public virtual string DropTableQuery(Type entity)
{
TableAttribute tableInfo = EntityCache.Get(entity);
return($"DROP TABLE {tableInfo.FullName}");
}
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);
}
}
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);
}
internal virtual void CreateAddCommand(IDbCommand command, EntityBase entity, AuditTrial audit = null, string columnNames = null, bool doNotAppendCommonFields = false)
{
TableAttribute tableInfo = EntityCache.Get(entity.GetType());
if (!tableInfo.NoCreatedBy && entity.IsCreatedByEmpty())
{
throw new MissingFieldException("CreatedBy is required");
}
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;
if (tableInfo.PrimaryKeyAttribute.IsIdentity && entity.IsKeyIdEmpty())
{
isPrimaryKeyEmpty = true;
//if identity remove keyfield if added in field list
columns.Remove(tableInfo.PrimaryKeyColumn.Name);
}
else if (entity.KeyId is Guid && entity.IsKeyIdEmpty())
{
isPrimaryKeyEmpty = true;
//if not identity and key not generated, generate before save
entity.KeyId = Guid.NewGuid();
}
#region append common columns
if (!doNotAppendCommonFields)
{
if (!tableInfo.NoIsActive)
{
if (!columns.Contains(Config.ISACTIVE_COLUMN.Name))
{
columns.Add(Config.ISACTIVE_COLUMN.Name);
}
command.AddInParameter("@" + Config.ISACTIVE_COLUMN.Name, Config.ISACTIVE_COLUMN.ColumnDbType, true);
if (tableInfo.NeedsHistory)
{
audit.AppendDetail(Config.ISACTIVE_COLUMN.Name, true, DbType.Boolean);
}
}
if (!tableInfo.NoVersionNo)
{
if (!columns.Contains(Config.VERSIONNO_COLUMN.Name))
{
columns.Add(Config.VERSIONNO_COLUMN.Name);
}
command.AddInParameter("@" + Config.VERSIONNO_COLUMN.Name, Config.VERSIONNO_COLUMN.ColumnDbType, 1);
}
if (!tableInfo.NoCreatedBy)
{
if (!columns.Contains(Config.CREATEDBY_COLUMN.Name))
{
columns.Add(Config.CREATEDBY_COLUMN.Name);
}
command.AddInParameter("@" + Config.CREATEDBY_COLUMN.Name, Config.CREATEDBY_COLUMN.ColumnDbType, entity.CreatedBy);
}
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);
}
command.AddInParameter("@" + Config.UPDATEDBY_COLUMN.Name, Config.UPDATEDBY_COLUMN.ColumnDbType, entity.CreatedBy);
}
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)
{
parameters[pIndex] = CURRENTDATETIMESQL;
}
pIndex = parameters.FindIndex(c => c == "@" + Config.UPDATEDON_COLUMN.Name);
if (pIndex >= 0)
{
parameters[pIndex] = CURRENTDATETIMESQL;
}
StringBuilder commandText = new StringBuilder();
commandText.Append($"INSERT INTO {tableInfo.FullName} ({string.Join(",", columns)}) VALUES({string.Join(",", parameters)});");
if (tableInfo.PrimaryKeyAttribute.IsIdentity && isPrimaryKeyEmpty)
{
//add query to get inserted id
commandText.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);
command.CommandType = CommandType.Text;
command.CommandText = commandText.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);
}
}
command.AddInParameter("@" + columns[i], dbType, columnValue);
}
}
//to prepare TableAttribute
internal static TableAttribute PrepareTableAttribute(Type entity)
{
TableAttribute result = (TableAttribute)entity.GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault();
if (result == null)
{
result = new TableAttribute
{
Name = entity.Name, //assuming entity class name is table name
NeedsHistory = Config.NeedsHistory,
NoCreatedBy = Config.NoCreatedBy,
NoCreatedOn = Config.NoCreatedOn,
NoUpdatedBy = Config.NoUpdatedBy,
NoUpdatedOn = Config.NoUpdatedOn,
NoVersionNo = Config.NoVersionNo,
NoIsActive = Config.NoIsActive
};
}
if (string.IsNullOrEmpty(result.Name))
{
result.Name = entity.Name;
}
//find all properties
var properties = entity.GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo property in properties)
{
//TODO: check for valid property types to be added in list
if ((property.Name.Equals("keyid", StringComparison.OrdinalIgnoreCase) ||
property.Name.Equals("operation", StringComparison.OrdinalIgnoreCase)))
{
continue;
}
//check for ignore property attribute
var ignoreInfo = (IgnoreColumnAttribute)property.GetCustomAttribute(typeof(IgnoreColumnAttribute));
var primaryKey = (PrimaryKeyAttribute)property.GetCustomAttribute(typeof(PrimaryKeyAttribute));
var column = (ColumnAttribute)property.GetCustomAttribute(typeof(ColumnAttribute));
if (column == null)
{
column = new ColumnAttribute();
}
if (string.IsNullOrEmpty(column.Name))
{
column.Name = property.Name;
}
if (property.Name.Equals("CreatedBy", StringComparison.OrdinalIgnoreCase))
{
column.Name = Config.CreatedByColumnName;
}
else if (property.Name.Equals("CreatedByName"))
{
column.Name = Config.CreatedByNameColumnName;
}
else if (property.Name.Equals("CreatedOn"))
{
column.Name = Config.CreatedOnColumnName;
}
else if (property.Name.Equals("UpdatedBy"))
{
column.Name = Config.UpdatedByColumnName;
}
else if (property.Name.Equals("UpdatedByName"))
{
column.Name = Config.UpdatedByNameColumnName;
}
else if (property.Name.Equals("UpdatedOn"))
{
column.Name = Config.UpdatedOnColumnName;
}
else if (property.Name.Equals("VersionNo"))
{
column.Name = Config.VersionNoColumnName;
}
else if (property.Name.Equals("IsActive"))
{
column.Name = Config.IsActiveColumnName;
}
if (!column.IsColumnDbTypeDefined)
{
if (column.Name.Equals(Config.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase))
{
column.ColumnDbType = Config.CreatedUpdatedByColumnType;
}
else if (property.PropertyType.IsEnum)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType.GetEnumUnderlyingType()];
}
else if (property.PropertyType.IsValueType)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType];
}
else
{
TypeCache.TypeToDbType.TryGetValue(property.PropertyType, out DbType columnDbType);
column.ColumnDbType = columnDbType;
}
}
column.SetPropertyInfo(property, entity);
column.IgnoreInfo = ignoreInfo ?? new IgnoreColumnAttribute(false);
//Primary Key details
if (primaryKey != null)
{
column.PrimaryKeyInfo = primaryKey;
var virtualForeignKeys = (IEnumerable <ForeignKey>)property.GetCustomAttributes(typeof(ForeignKey));
if (virtualForeignKeys != null && virtualForeignKeys.Count() > 0)
{
if (result.VirtualForeignKeys == null)
{
result.VirtualForeignKeys = new List <ForeignKey>();
}
result.VirtualForeignKeys.AddRange(virtualForeignKeys);
}
}
if (result.NoCreatedBy && (column.Name.Equals(Config.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.CreatedByNameColumnName, StringComparison.OrdinalIgnoreCase)))
{
continue;
}
else if (result.NoCreatedOn && column.Name.Equals(Config.CreatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoUpdatedBy && ((column.Name.Equals(Config.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.UpdatedByNameColumnName, StringComparison.OrdinalIgnoreCase))))
{
continue;
}
else if (result.NoUpdatedOn && column.Name.Equals(Config.UpdatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoIsActive && column.Name.Equals(Config.IsActiveColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoVersionNo && column.Name.Equals(Config.VersionNoColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else
{
if (!column.IgnoreInfo.Insert)
{
result.DefaultInsertColumns.Add(column.Name);
}
//isactive,createdon,createdby column shall not be included in default update columns
if (!column.IgnoreInfo.Update &&
!column.Name.Equals(Config.IsActiveColumnName, StringComparison.OrdinalIgnoreCase) &&
!column.Name.Equals(Config.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) &&
!column.Name.Equals(Config.CreatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
result.DefaultUpdateColumns.Add(column.Name);
}
if (!column.IgnoreInfo.Read)
{
result.DefaultReadColumns.Add(column.Name);
}
result.Columns[column.Name] = column;
}
}
if (result.Columns.LongCount(p => p.Value.IsPrimaryKey && p.Value.PrimaryKeyInfo.IsIdentity) > 1)
{
throw new NotSupportedException("Primary key with multiple Identity is not supported on " + result.Name);
}
if (result.Columns.LongCount(p => p.Value.IsPrimaryKey) > 1 && result.NeedsHistory)
{
throw new NotSupportedException($"History for {result.Name} is not supported as it has composite Primary key");
}
return(result);
}
internal static TableAttribute Get(Type entity)
{
TableAttribute result;
try
{
cacheLock.EnterReadLock();
if (Entities.TryGetValue(entity, out result))
{
return(result);
}
}
finally
{
cacheLock.ExitReadLock();
}
//AuditTrial table attribute is configured based on configuration
if (entity == typeof(AuditTrial))
{
result = GetAuditTrialTableAttribute();
try
{
cacheLock.EnterWriteLock();
Entities[entity] = result;
}
finally
{
cacheLock.ExitWriteLock();
}
return(result);
}
result = (TableAttribute)entity.GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault();
if (result == null)
{
result = new TableAttribute
{
Name = entity.Name, //assuming entity class name is table name
NeedsHistory = Config.VegaConfig.NeedsHistory,
NoCreatedBy = Config.VegaConfig.NoCreatedBy,
NoCreatedOn = Config.VegaConfig.NoCreatedOn,
NoUpdatedBy = Config.VegaConfig.NoUpdatedBy,
NoUpdatedOn = Config.VegaConfig.NoUpdatedOn,
NoVersionNo = Config.VegaConfig.NoVersionNo,
NoIsActive = Config.VegaConfig.NoIsActive
};
}
if (string.IsNullOrEmpty(result.Name))
{
result.Name = entity.Name;
}
//find all properties
var properties = entity.GetProperties(BindingFlags.Public | BindingFlags.Instance);
//find primary key column attribute
var primaryKeyProperty = properties.FirstOrDefault(p => p.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Count() == 1);
ColumnAttribute primaryKeyColumn = null;
if (primaryKeyProperty != null)
{
result.PrimaryKeyAttribute = (PrimaryKeyAttribute)primaryKeyProperty.GetCustomAttributes(typeof(PrimaryKeyAttribute)).FirstOrDefault();
//find column attribute on this property
primaryKeyColumn = (ColumnAttribute)primaryKeyProperty.GetCustomAttributes(typeof(ColumnAttribute)).FirstOrDefault();
if (primaryKeyColumn == null)
{
//if no column attribute defined assume Propertyname is key column name
primaryKeyColumn = new ColumnAttribute
{
Name = primaryKeyProperty.Name,
ColumnDbType = TypeCache.TypeToDbType[primaryKeyProperty.PropertyType]
};
}
primaryKeyColumn.Property = primaryKeyProperty;
primaryKeyColumn.SetMethod = primaryKeyProperty.GetSetMethod();
primaryKeyColumn.GetMethod = primaryKeyProperty.GetGetMethod();
primaryKeyColumn.SetAction = Helper.CreateSetProperty(entity, primaryKeyProperty.Name);
primaryKeyColumn.GetAction = Helper.CreateGetProperty(entity, primaryKeyProperty.Name);
result.PrimaryKeyColumn = primaryKeyColumn;
//check for virtual foreign key
var virtualForeignKeys = (IEnumerable <ForeignKey>)primaryKeyProperty.GetCustomAttributes(typeof(ForeignKey));
if (virtualForeignKeys != null && virtualForeignKeys.Count() > 0)
{
if (result.VirtualForeignKeys == null)
{
result.VirtualForeignKeys = new List <ForeignKey>();
}
result.VirtualForeignKeys.AddRange(virtualForeignKeys);
}
}
foreach (PropertyInfo property in properties)
{
//TODO: check for valid property types to be added in list
if ((property.Name.Equals("keyid", StringComparison.OrdinalIgnoreCase) ||
property.Name.Equals("operation", StringComparison.OrdinalIgnoreCase)))
{
continue;
}
//check for ignore property attribute
var ignoreInfo = (IgnoreColumnAttribute)property.GetCustomAttribute(typeof(IgnoreColumnAttribute));
var column = (ColumnAttribute)property.GetCustomAttribute(typeof(ColumnAttribute));
if (column == null)
{
column = new ColumnAttribute();
}
if (string.IsNullOrEmpty(column.Name))
{
column.Name = property.Name;
}
if (property.Name.Equals("CreatedBy", StringComparison.OrdinalIgnoreCase))
{
column.Name = Config.VegaConfig.CreatedByColumnName;
}
else if (property.Name.Equals("CreatedByName"))
{
column.Name = Config.VegaConfig.CreatedByNameColumnName;
}
else if (property.Name.Equals("CreatedOn"))
{
column.Name = Config.VegaConfig.CreatedOnColumnName;
}
else if (property.Name.Equals("UpdatedBy"))
{
column.Name = Config.VegaConfig.UpdatedByColumnName;
}
else if (property.Name.Equals("UpdatedByName"))
{
column.Name = Config.VegaConfig.UpdatedByNameColumnName;
}
else if (property.Name.Equals("UpdatedOn"))
{
column.Name = Config.VegaConfig.UpdatedOnColumnName;
}
else if (property.Name.Equals("VersionNo"))
{
column.Name = Config.VegaConfig.VersionNoColumnName;
}
else if (property.Name.Equals("IsActive"))
{
column.Name = Config.VegaConfig.IsActiveColumnName;
}
if (!column.IsColumnDbTypeDefined)
{
if (column.Name.Equals(Config.VegaConfig.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase))
{
column.ColumnDbType = Config.VegaConfig.CreatedUpdatedByColumnType;
}
else if (property.PropertyType.IsEnum)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType.GetEnumUnderlyingType()];
}
else if (property.PropertyType.IsValueType)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType];
}
else
{
TypeCache.TypeToDbType.TryGetValue(property.PropertyType, out DbType columnDbType);
column.ColumnDbType = columnDbType;
}
}
column.Property = property;
column.SetMethod = property.GetSetMethod();
column.GetMethod = property.GetGetMethod();
column.SetAction = Helper.CreateSetProperty(entity, property.Name);
column.GetAction = Helper.CreateGetProperty(entity, property.Name);
column.IgnoreInfo = ignoreInfo ?? new IgnoreColumnAttribute(false);
if (result.NoCreatedBy && (column.Name.Equals(Config.VegaConfig.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.CreatedByNameColumnName, StringComparison.OrdinalIgnoreCase)))
{
continue;
}
else if (result.NoCreatedOn && column.Name.Equals(Config.VegaConfig.CreatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoUpdatedBy && ((column.Name.Equals(Config.VegaConfig.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.UpdatedByNameColumnName, StringComparison.OrdinalIgnoreCase))))
{
continue;
}
else if (result.NoUpdatedOn && column.Name.Equals(Config.VegaConfig.UpdatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoIsActive && column.Name.Equals(Config.VegaConfig.IsActiveColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoVersionNo && column.Name.Equals(Config.VegaConfig.VersionNoColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else
{
if (!column.IgnoreInfo.Insert)
{
result.DefaultInsertColumns.Add(column.Name);
}
if (!column.IgnoreInfo.Update)
{
result.DefaultUpdateColumns.Add(column.Name);
}
if (!column.IgnoreInfo.Read)
{
result.DefaultReadColumns.Add(column.Name);
}
result.Columns[column.Name] = column;
}
}
try
{
cacheLock.EnterWriteLock();
Entities[entity] = result;
}
finally
{
cacheLock.ExitWriteLock();
}
return(result);
}
internal static TableAttribute GetAuditTrialTableAttribute()
{
TableAttribute result = new TableAttribute
{
Name = Config.VegaConfig.AuditTableName,
NeedsHistory = false,
NoCreatedBy = false,
NoCreatedOn = false,
NoUpdatedBy = true,
NoUpdatedOn = true,
NoVersionNo = true,
NoIsActive = true
};
var type = typeof(AuditTrial);
var pkProperty = typeof(AuditTrial).GetProperty("AuditTrailId");
result.PrimaryKeyAttribute = (PrimaryKeyAttribute)pkProperty.GetCustomAttributes(typeof(PrimaryKeyAttribute)).FirstOrDefault();
result.PrimaryKeyColumn = new ColumnAttribute()
{
Name = Config.VegaConfig.AuditKeyColumnName,
ColumnDbType = DbType.Int64,
Property = pkProperty,
SetMethod = pkProperty.GetSetMethod(),
GetMethod = pkProperty.GetGetMethod(),
SetAction = Helper.CreateSetProperty(type, pkProperty.Name),
GetAction = Helper.CreateGetProperty(type, pkProperty.Name),
};
foreach (PropertyInfo property in type.GetProperties())
{
//TODO: check for valid property types to be added in list
if ((property.Name.Equals("keyid", StringComparison.OrdinalIgnoreCase) ||
property.Name.Equals("operation", StringComparison.OrdinalIgnoreCase)))
{
continue;
}
//check for ignore property attribute
var ignoreInfo = (IgnoreColumnAttribute)property.GetCustomAttribute(typeof(IgnoreColumnAttribute));
var column = (ColumnAttribute)property.GetCustomAttribute(typeof(ColumnAttribute));
if (column == null)
{
column = new ColumnAttribute();
}
if (property.Name == "AuditTrailId")
{
column.Name = Config.VegaConfig.AuditKeyColumnName;
}
else if (property.Name == "OperationType")
{
column.Name = Config.VegaConfig.AuditOperationTypeColumnName;
}
else if (property.Name == "TableName")
{
column.Name = Config.VegaConfig.AuditTableNameColumnName;
}
else if (property.Name == "RecordId")
{
column.Name = Config.VegaConfig.AuditRecordIdColumnName;
}
else if (property.Name == "Details")
{
column.Name = Config.VegaConfig.AuditDetailsColumnName;
}
else if (property.Name == "RecordVersionNo")
{
column.Name = Config.VegaConfig.AuditRecordVersionColumnName;
}
else if (property.Name.Equals("CreatedBy", StringComparison.OrdinalIgnoreCase))
{
column.Name = Config.VegaConfig.CreatedByColumnName;
}
else if (property.Name.Equals("CreatedOn"))
{
column.Name = Config.VegaConfig.CreatedOnColumnName;
}
else
{
column.Name = property.Name;
}
if (!column.IsColumnDbTypeDefined)
{
if (column.Name.Equals(Config.VegaConfig.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase))
{
column.ColumnDbType = Config.VegaConfig.CreatedUpdatedByColumnType;
}
else if (property.PropertyType.IsEnum)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType.GetEnumUnderlyingType()];
}
else if (property.PropertyType.IsValueType)
{
column.ColumnDbType = TypeCache.TypeToDbType[property.PropertyType];
}
else
{
TypeCache.TypeToDbType.TryGetValue(property.PropertyType, out DbType columnDbType);
column.ColumnDbType = columnDbType;
}
}
column.Property = property;
column.SetMethod = property.GetSetMethod();
column.GetMethod = property.GetGetMethod();
column.SetAction = Helper.CreateSetProperty(type, property.Name);
column.GetAction = Helper.CreateGetProperty(type, property.Name);
column.IgnoreInfo = ignoreInfo ?? new IgnoreColumnAttribute(false);
if (result.NoCreatedBy && (column.Name.Equals(Config.VegaConfig.CreatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.CreatedByNameColumnName, StringComparison.OrdinalIgnoreCase)))
{
continue;
}
else if (result.NoCreatedOn && column.Name.Equals(Config.VegaConfig.CreatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoUpdatedBy && ((column.Name.Equals(Config.VegaConfig.UpdatedByColumnName, StringComparison.OrdinalIgnoreCase) ||
column.Name.Equals(Config.VegaConfig.UpdatedByNameColumnName, StringComparison.OrdinalIgnoreCase))))
{
continue;
}
else if (result.NoUpdatedOn && column.Name.Equals(Config.VegaConfig.UpdatedOnColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoIsActive && column.Name.Equals(Config.VegaConfig.IsActiveColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else if (result.NoVersionNo && column.Name.Equals(Config.VegaConfig.VersionNoColumnName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
else
{
if (!column.IgnoreInfo.Insert)
{
result.DefaultInsertColumns.Add(column.Name);
}
if (!column.IgnoreInfo.Update)
{
result.DefaultUpdateColumns.Add(column.Name);
}
if (!column.IgnoreInfo.Read)
{
result.DefaultReadColumns.Add(column.Name);
}
result.Columns[column.Name] = column;
}
}
return(result);
}
public override string CreateTableQuery(Type entity)
{
TableAttribute tableInfo = EntityCache.Get(entity);
//check if composite primary key and have identity
//sqlite doesn't support composite primary key where one is autoincrement
//have to create unique index in such case
bool isCompositeKey = tableInfo.PkColumnList.Count > 1;
bool isCompositeKeyAndIdentity = tableInfo.PkColumnList.Count > 1 && tableInfo.PkColumnList.Exists(p => p.PrimaryKeyInfo.IsIdentity);
StringBuilder createSQL = new StringBuilder($"CREATE TABLE {tableInfo.FullName} (");
string primaryKeyCols = string.Empty;
for (int i = 0; i < tableInfo.Columns.Count; i++)
{
ColumnAttribute col = tableInfo.Columns.ElementAt(i).Value;
if (col.IsPrimaryKey)
{
primaryKeyCols += col.Name + ",";
if (col.PrimaryKeyInfo.IsIdentity)
{
createSQL.Append($"{col.Name} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT");
}
else if (!isCompositeKey)
{
createSQL.Append($"{col.Name} {DbTypeString[col.ColumnDbType]} NOT NULL PRIMARY KEY");
}
else
{
createSQL.Append($"{col.Name} {DbTypeString[col.ColumnDbType]} NOT NULL");
}
createSQL.Append(",");
}
else if (col.IgnoreInfo.Insert && col.IgnoreInfo.Update)
{
continue;
}
else
{
createSQL.Append($"{col.Name} {GetDBTypeWithSize(col.ColumnDbType, col.Size, col.NumericScale)}");
if (IsNullableType(col.Property.PropertyType))
{
createSQL.Append(" NULL ");
}
if (col.Name == Config.CREATEDON_COLUMN.Name || col.Name == Config.UPDATEDON_COLUMN.Name)
{
createSQL.Append($" DEFAULT ({CURRENTDATETIMESQL})");
}
createSQL.Append(",");
}
}
//SQLite doesn't support composite primary key with autoincrement
//https://stackoverflow.com/questions/6154730/sqlite-multi-primary-key-on-a-table-one-of-them-is-auto-increment
if (isCompositeKey && !isCompositeKeyAndIdentity)
{
primaryKeyCols = primaryKeyCols.RemoveLastComma();
createSQL.Append($"PRIMARY KEY ({primaryKeyCols})");
}
createSQL.RemoveLastComma(); //Remove last comma if exists
createSQL.Append(");");
//create unique index for compositekey with identity
if (isCompositeKey && isCompositeKeyAndIdentity)
{
primaryKeyCols = primaryKeyCols.RemoveLastComma();
createSQL.Append($"CREATE UNIQUE INDEX pk_{tableInfo.Name} ON {tableInfo.Name}({primaryKeyCols});");
}
return(createSQL.ToString());
}
