Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
            }
        }