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); } }
public static IDbDataParameter AddInParameter(this IDbCommand command, string paramName, DbType paramType, int paramSize, object paramValue) { IDbDataParameter parameter = command.AddInParameter(paramName, paramType, paramValue); parameter.Size = paramSize; return(parameter); }
public static bool GetUser(string userName, string userPassword) { IDbCommand command = SqlHelper.GetDbCommand("sp_GetUser"); command.AddInParameter("@UserName", DbType.String, 250, userName); command.AddInParameter("@UserPassword", DbType.String, 50, userPassword); object userIdObject = SqlHelper.ExecuteScalar(command); if (userIdObject == null) { return(false); } int userId = userIdObject != DBNull.Value ? Convert.ToInt32(userIdObject) : 0; return(userId != 0); }
private static string addParameter(string name, string value, List <string> whereList, IDbCommand command) { name = name.ToLower(); int pos = whereList.IndexOf(name); if (pos >= 0) { return("@_val" + (pos + 1).ToString(CultureInfo.InvariantCulture)); } pos = whereList.Count; whereList.Add(name); name = "@_val" + (pos + 1).ToString(CultureInfo.InvariantCulture); command.AddInParameter(name, value); return(name); }
internal static void AddParameters(object param, IDbCommand cmd) { if (param == null) { return; } Type pType = param.GetType(); foreach (PropertyInfo property in pType.GetProperties()) { Type type = GetTypeOfDynamicProperty(property, param); if (type.IsEnum) { type = Enum.GetUnderlyingType(type); } if (!TypeCache.TypeToDbType.TryGetValue(type, out DbType dbType)) { dbType = DbType.String; //TODO: fix when unkown type } cmd.AddInParameter(property.Name, dbType, property.GetValue(param)); } }
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); }
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 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); } }