internal static string CreateTable(ICustomRepository repository, Type tableType, List <Type> createdTables = null, bool commit = true, bool force = false, Dictionary <string, Tuple <string, ForeignKey> > keys = null) { if (createdTables == null) { createdTables = new List <Type>(); } var tableData = ObjectColumns(repository, tableType); if (createdTables.Any(x => x == tableType)) { return(null); } if (!force && tableData.Rows.Any()) { return(null); } repository.CreateTransaction(); RemoveTable(repository, tableType); createdTables.Add(tableType); if (keys == null) { keys = new Dictionary <string, Tuple <string, ForeignKey> >(); } List <string> sqlList = new List <string>(); try { var isSqllite = repository.GetDataBaseType() == DataBaseTypes.Sqllight; var props = DeepCloner.GetFastDeepClonerProperties(tableType); var tableName = tableType.GetCustomAttribute <Table>()?.Name ?? tableType.Name; var sql = new StringBuilder("CREATE TABLE " + (!isSqllite ? "[dbo]." : "") + "[" + tableName + "]("); var isPrimaryKey = ""; foreach (var prop in props.Where(x => x.PropertyType.GetDbTypeByType() != null && !x.ContainAttribute <ExcludeFromAbstract>() && x.IsInternalType).GroupBy(x => x.Name).Select(x => x.First())) { isPrimaryKey = prop.ContainAttribute <PrimaryKey>() ? prop.GetPropertyName() : isPrimaryKey; var forgenKey = prop.GetCustomAttribute <ForeignKey>(); var dbType = prop.PropertyType.GetDbTypeByType(); var propName = prop.GetPropertyName(); sql.Append(propName + " "); if (!prop.ContainAttribute <PrimaryKey>() || !isSqllite) { sql.Append(dbType + " "); } if (forgenKey != null) { sqlList.Add(CreateTable(repository, forgenKey.Type, createdTables, false, force, keys)); } if (prop.ContainAttribute <PrimaryKey>()) { if (!isSqllite) { sql.Append("IDENTITY(1,1) NOT NULL,"); } else { sql.Append(" Integer PRIMARY KEY AUTOINCREMENT,"); } continue; } if (forgenKey != null) { keys.Add(propName, new Tuple <string, ForeignKey>(tableName, forgenKey)); } sql.Append(Nullable.GetUnderlyingType(prop.PropertyType) != null ? " NULL," : " NOT NULL,"); } if (keys.Any() && isSqllite) { foreach (var key in keys) { var type = key.Value.Item2.Type.GetActualType(); var keyPrimary = type.GetPrimaryKey().GetPropertyName(); var tb = type.GetCustomAttribute <Table>().Name ?? type.Name; sql.Append("FOREIGN KEY(" + key.Key + ") REFERENCES " + tb + "(" + keyPrimary + "),"); } keys.Clear(); } if (!string.IsNullOrEmpty(isPrimaryKey) && !isSqllite) { sql.Append(" CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED"); sql.Append(" ([" + isPrimaryKey + "] ASC"); sql.Append(")"); sql.Append( "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]"); sql.Append(") ON [PRIMARY]"); } else { if (isSqllite) { sql = new StringBuilder(sql.ToString().TrimEnd(',')); } sql.Append(")"); } if (!commit) { return(sql.ToString()); } foreach (var prop in props.Where(x => !x.IsInternalType && !x.ContainAttribute <ExcludeFromAbstract>()).GroupBy(x => x.Name).Select(x => x.First())) { var type = prop.PropertyType.GetActualType(); sqlList.Add(CreateTable(repository, type, createdTables, false, force, keys)); } sqlList.Insert(0, sql.ToString()); sqlList.RemoveAll(x => string.IsNullOrEmpty(x)); var c = sqlList.Count; while (c > 0) { for (var i = sqlList.Count - 1; i >= 0; i--) { try { var s = sqlList[i]; var cmd = repository.GetSqlCommand(s); repository.ExecuteNonQuery(cmd); c--; } catch (Exception ex) { var test = ex; } } } sql = new StringBuilder(); if (keys.Any() && !isSqllite) { foreach (var key in keys) { var type = key.Value.Item2.Type.GetActualType(); var keyPrimary = type.GetPrimaryKey().GetPropertyName(); var tb = type.GetCustomAttribute <Table>()?.Name ?? type.Name; sql.Append("ALTER TABLE [" + key.Value.Item1 + "] ADD FOREIGN KEY (" + key.Key + ") REFERENCES [" + tb + "](" + keyPrimary + ");"); } var s = sql.ToString(); var cmd = repository.GetSqlCommand(s); repository.ExecuteNonQuery(cmd); } repository.Commit(); } catch (Exception ex) { repository.Rollback(); throw ex; } return(string.Empty); }
internal static long SaveObject(ICustomRepository repository, InterFace.IDbEntity o, bool isIndependentData = false, bool commit = false) { repository.CreateTransaction(); try { var updateOnly = o.State == ItemState.Changed; o.State = ItemState.Added;// reset State var props = DeepCloner.GetFastDeepClonerProperties(o.GetType()); var primaryKey = MethodHelper.GetPrimaryKey(o); var availableColumns = repository.ObjectColumns(o.GetType()); var objectRules = o.GetType().GetCustomAttribute <Rule>(); var tableName = o.GetType().GetCustomAttribute <Table>()?.Name ?? o.GetType().Name; object dbTrigger = null; if (objectRules != null && !CachedIDbRuleTrigger.ContainsKey(o.GetType())) { dbTrigger = Activator.CreateInstance(objectRules.RuleType) as object; CachedIDbRuleTrigger.Add(o.GetType(), dbTrigger); } else if (objectRules != null) { dbTrigger = CachedIDbRuleTrigger[o.GetType()]; } if (primaryKey == null) { return(0); } var value = primaryKey.GetValue(o).ConvertValue <long?>(); if (value <= 0) { value = null; } else if (value.HasValue && !updateOnly) { var data = Select(repository, o.GetType(), Querys.Where(repository.GetDataBaseType() == DataBaseTypes.Sqllight).Column(primaryKey.GetPropertyName()).Equal(value.Value).ToQueryItem).Rows.FirstOrDefault(); if (data != null) { o.Merge(data.ToObject(o.GetType()) as InterFace.IDbEntity); } } if (!updateOnly) { dbTrigger?.GetType().GetMethod("BeforeSave").Invoke(dbTrigger, new List <object>() { repository, o }.ToArray()); // Check the Rule before save } o.State = ItemState.Added; // reset State var sql = "UPDATE [" + (o.GetType().GetCustomAttribute <Table>()?.Name ?? o.GetType().Name) + "] SET "; var cols = props.Where(x => availableColumns.FindByPrimaryKey <bool>(x.GetPropertyName()) && x.IsInternalType && x.GetCustomAttribute <ExcludeFromAbstract>() == null && x.GetCustomAttribute <PrimaryKey>() == null); if (!value.HasValue) { sql = "INSERT INTO [" + tableName + "](" + string.Join(",", cols.Select(x => "[" + x.GetPropertyName() + "]")) + ") Values("; sql += string.Join(",", cols.Select(x => "@" + x.GetPropertyName())) + ");"; sql += repository.GetDataBaseType() == DataBaseTypes.Sqllight ? " select last_insert_rowid();" : " SELECT IDENT_CURRENT('" + tableName + "');"; } else { sql += string.Join(",", cols.Select(x => "[" + x.GetPropertyName() + "]" + " = @" + MethodHelper.GetPropertyName(x))); sql += Querys.Where(repository.GetDataBaseType() == DataBaseTypes.Sqllight).Column(primaryKey.GetPropertyName()).Equal(value).Execute(); } var cmd = repository.GetSqlCommand(sql); foreach (var col in cols) { var v = col.GetValue(o); if (col.ContainAttribute <ForeignKey>() && MethodHelper.ConvertValue <long?>(v) == 0) { var ob = props.FirstOrDefault(x => x.PropertyType == col.GetCustomAttribute <ForeignKey>().Type); var obValue = ob?.GetValue(o) as InterFace.IDbEntity; var independentData = ob?.GetCustomAttribute <IndependentData>() != null; if (obValue != null) { v = MethodHelper.ConvertValue <long>(MethodHelper.GetPrimaryKey(obValue).GetValue(obValue)) <= 0 ? SaveObject(repository, obValue, independentData) : MethodHelper.ConvertValue <long>(MethodHelper.GetPrimaryKey(obValue).GetValue(obValue)); } } repository.AddInnerParameter(cmd, col.GetPropertyName(), v, repository.GetSqlType(col.PropertyType)); } if (!value.HasValue) { value = MethodHelper.ConvertValue <long>(repository.ExecuteScalar(cmd)); } else { repository.ExecuteNonQuery(cmd); } if (updateOnly) { return(value.Value); } dbTrigger?.GetType().GetMethod("AfterSave").Invoke(dbTrigger, new List <object>() { repository, o, value.Value }.ToArray()); // Check the Rule before save primaryKey.SetValue(o, value); foreach (var prop in props.Where(x => !x.IsInternalType && x.GetCustomAttribute <ExcludeFromAbstract>() == null)) { var independentData = prop.GetCustomAttribute <IndependentData>() != null; var type = prop.PropertyType.GetActualType(); var oValue = prop.GetValue(o); if (oValue == null) { continue; } if (oValue is IList) { foreach (var item in (IList)oValue) { if (DeepCloner.GetFastDeepClonerProperties(item.GetType()).Any(x => x.GetCustomAttribute <ForeignKey>()?.Type == o.GetType())) { DeepCloner.GetFastDeepClonerProperties(item.GetType()).First(x => x.GetCustomAttribute <ForeignKey>()?.Type == o.GetType()).SetValue(item, value); } var res = SaveObject(repository, item as InterFace.IDbEntity, independentData); var foreignKey = FastDeepCloner.DeepCloner.GetFastDeepClonerProperties(o.GetType()).FirstOrDefault(x => x.GetCustomAttribute <ForeignKey>()?.Type == type); if (foreignKey == null || MethodHelper.ConvertValue <long>(foreignKey.GetValue(o)) > 0) { continue; } foreignKey.SetValue(o, res); o.State = ItemState.Changed; } } else { if (DeepCloner.GetFastDeepClonerProperties(oValue.GetType()).Any(x => x.GetCustomAttribute <ForeignKey>()?.Type == o.GetType())) { DeepCloner.GetFastDeepClonerProperties(oValue.GetType()).First(x => x.GetCustomAttribute <ForeignKey>()?.Type == o.GetType()).SetValue(oValue, value); } var res = SaveObject(repository, oValue as InterFace.IDbEntity, independentData); var foreignKey = FastDeepCloner.DeepCloner.GetFastDeepClonerProperties(o.GetType()).FirstOrDefault(x => x.GetCustomAttribute <ForeignKey>()?.Type == type); if (foreignKey == null || MethodHelper.ConvertValue <long>(foreignKey.GetValue(o)) > 0) { continue; } foreignKey.SetValue(o, res); o.State = ItemState.Changed; } } if (o.State == ItemState.Changed) // a change has been made outside the function Save { SaveObject(repository, o, false); } if (!commit) { return(value.Value); } repository.Commit(); return(value.Value); } catch (Exception e) { repository.Rollback(); throw e; } }
internal static void RemoveTable(ICustomRepository repository, Type tableType, bool commit = false, List <Type> tableRemoved = null, bool remove = true) { if (commit) { repository.CreateTransaction(); } if (tableRemoved == null) { tableRemoved = new List <Type>(); } if (tableRemoved.Any(x => x == tableType)) { return; } tableRemoved.Insert(0, tableType); var props = DeepCloner.GetFastDeepClonerProperties(tableType); foreach (var prop in props.Where(x => (!x.IsInternalType || x.ContainAttribute <ForeignKey>()) && !x.ContainAttribute <ExcludeFromAbstract>())) { var key = prop.GetCustomAttribute <ForeignKey>(); if (key != null && tableRemoved.Any(x => x == key.Type)) { continue; } if (key != null) { RemoveTable(repository, key.Type, commit, tableRemoved, false); } else { RemoveTable(repository, prop.PropertyType.GetActualType(), commit, tableRemoved, false); } } if (!remove) { return; } var tableData = ObjectColumns(repository, tableType); if (!tableData.Rows.Any()) { return; } var c = tableRemoved.Count; while (c > 0) { for (var i = tableRemoved.Count - 1; i >= 0; i--) { try { var tType = tableRemoved[i]; CachedObjectColumn.Remove(tType); var tableName = tType.GetCustomAttribute <Table>()?.Name ?? tType.Name; var cmd = repository.GetSqlCommand("DROP TABLE [" + tableName + "];"); repository.ExecuteNonQuery(cmd); c--; } catch { } } } if (commit) { repository.Commit(); } }