Beispiel #1
0
        public override string CreateTableQuery(Type entity)
        {
            TableAttribute tableInfo = EntityCache.Get(entity);

            StringBuilder createSQL = new StringBuilder($"CREATE TABLE {tableInfo.FullName} (");

            for (int i = 0; i < tableInfo.Columns.Count; i++)
            {
                ColumnAttribute col = tableInfo.Columns.ElementAt(i).Value;

                if (tableInfo.PrimaryKeyColumn.Name == col.Name)
                {
                    if (tableInfo.PrimaryKeyAttribute.IsIdentity)
                    {
                        createSQL.Append($"{col.Name} INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT");
                    }
                    else
                    {
                        createSQL.Append($"{col.Name} {DbTypeString[col.ColumnDbType]} NOT NULL PRIMARY KEY");
                    }
                    createSQL.Append(",");
                }
                else if (col.IgnoreInfo.Insert || col.IgnoreInfo.Update)
                {
                    continue;
                }
                else
                {
                    createSQL.Append($"{col.Name} {GetDBTypeWithSize(col.ColumnDbType, col.NumericPrecision, col.NumericScale)}");

                    if (col.Name == Config.CREATEDON_COLUMN.Name || col.Name == Config.UPDATEDON_COLUMN.Name)
                    {
                        createSQL.Append(" DEFAULT (" + CURRENTDATETIMESQL + ")");
                    }
                    createSQL.Append(",");
                }
            }
            createSQL.RemoveLastComma(); //Remove last comma if exists

            createSQL.Append(");");

            return(createSQL.ToString());
        }
Beispiel #2
0
        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());
        }
Beispiel #3
0
        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);
            }
        }
Beispiel #4
0
        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));
        }
Beispiel #5
0
        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);
        }
Beispiel #6
0
        public virtual string DropTableQuery(Type entity)
        {
            TableAttribute tableInfo = EntityCache.Get(entity);

            return($"DROP TABLE {tableInfo.FullName}");
        }
Beispiel #7
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);
            }
        }
Beispiel #8
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);
        }
Beispiel #9
0
        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());
        }
Beispiel #10
0
        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);
            }
        }
Beispiel #11
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="tran"></param>
 public AuditTrailKeyValueRepository(IDbTransaction tran) : base(tran)
 {
     entityTableInfo = EntityCache.Get(typeof(Entity));
     detailRepo      = new Repository <AuditTrailKeyValueDetail>(tran);
 }
Beispiel #12
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="con"></param>
 public AuditTrailKeyValueRepository(IDbConnection con) : base(con)
 {
     entityTableInfo = EntityCache.Get(typeof(Entity));
     detailRepo      = new Repository <AuditTrailKeyValueDetail>(con);
 }
Beispiel #13
0
        /// <summary>
        /// Read and Return chain of Record from insert to update till now for a given Record of an Entity
        /// </summary>
        /// <param name="id"></param>
        /// <returns>List of Entity with modification</returns>
        public IEnumerable <Entity> ReadAll(object id)
        {
            var lstAudit = ReadAll("*", $"tablename=@TableName AND recordid=@RecordId", new { TableName = entityTableInfo.Name, RecordId = id.ToString() }, Config.CreatedOnColumnName + " ASC").ToList();

            Entity current = default;

            foreach (AuditTrailKeyValue audit in lstAudit)
            {
                if (current == null)
                {
                    //create new object
                    current = new Entity();

                    if (!entityTableInfo.NoCreatedBy)
                    {
                        entityTableInfo.SetCreatedBy(current, audit.CreatedBy);
                    }

                    if (!entityTableInfo.NoCreatedOn)
                    {
                        entityTableInfo.SetCreatedOn(current, audit.CreatedOn);
                    }

                    entityTableInfo.PkColumn.SetAction(current, audit.RecordId.ConvertTo(entityTableInfo.PkColumn.Property.PropertyType));
                }
                else
                {
                    current = EntityCache.CloneObjectWithIL(current);
                }

                var lstAuditTrailDetail = detailRepo.ReadAll(null, "audittrailid=@audittrailid", new { audittrailid = audit.AuditTrailId }).ToList();

                audit.lstAuditTrailDetail = lstAuditTrailDetail.Cast <IAuditTrailDetail>().ToList();
                //render modified values
                foreach (AuditTrailKeyValueDetail detail in audit.lstAuditTrailDetail)
                {
                    if (detail.NewValue == null)
                    {
                        continue;
                    }

                    //find column
                    entityTableInfo.Columns.TryGetValue(detail.ColumnName, out ColumnAttribute col);
                    if (col == null)
                    {
                        continue;
                    }

                    object convertedValue = null;

                    if (col.Property.PropertyType == typeof(bool) || col.Property.PropertyType == typeof(bool?))
                    {
                        convertedValue = (detail.NewValue.ToString() == "1" ? true : false);
                    }
                    else if (col.Property.PropertyType == typeof(DateTime) || col.Property.PropertyType == typeof(DateTime?))
                    {
                        convertedValue = detail.NewValue.ToString().FromSQLDateTime();
                    }
                    else
                    {
                        convertedValue = detail.NewValue.ConvertTo(col.Property.PropertyType);
                    }

                    col.SetAction(current, convertedValue);
                }

                if (!entityTableInfo.NoVersionNo)
                {
                    entityTableInfo.SetVersionNo(current, audit.RecordVersionNo);
                }

                if (!entityTableInfo.NoUpdatedBy)
                {
                    entityTableInfo.SetUpdatedOn(current, audit.CreatedOn);
                }

                if (!entityTableInfo.NoUpdatedBy)
                {
                    entityTableInfo.SetUpdatedBy(current, audit.CreatedBy);
                }

                yield return(current);
            }
        }
Beispiel #14
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="tran"></param>
 public AuditTrailRepository(IDbTransaction tran) : base(tran)
 {
     entityTableInfo = EntityCache.Get(typeof(Entity));
 }
Beispiel #15
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="con"></param>
 public AuditTrailRepository(IDbConnection con) : base(con)
 {
     entityTableInfo = EntityCache.Get(typeof(Entity));
 }