/// <summary> /// get optional-link properties /// </summary> private IEnumerable <PropertyInfo> getOptionalProperties(Type type) { return(type.GetProperties().Where(p => { ImportExportAttribute attr = p.GetCustomAttribute <ImportExportAttribute>(); return attr != null && attr.KeyFor.Any() && attr.Type == ELinkType.LinkOptional; })); }
/// <summary> /// get parent & required-link properties /// </summary> private IEnumerable <PropertyInfo> getRequiredProperties(Type type) { // ignores object link properties return(type.GetProperties().Where(p => { ImportExportAttribute attr = p.GetCustomAttribute <ImportExportAttribute>(); return attr != null && attr.KeyFor.Any() && (attr.Type == ELinkType.Parent || attr.Type == ELinkType.LinkRequired); })); }
/// <summary> /// get writable non-link properties (without primary key), required-link and parent-link properties /// </summary> private IEnumerable <PropertyInfo> getNonOptionalProperties(Type type) { return(type.GetProperties().Where(p => { ImportExportAttribute attr = p.GetCustomAttribute <ImportExportAttribute>(); return p.Name != PrimaryKey && (DataType.BaseTypes.Contains(p.PropertyType) || p.PropertyType.IsEnum) && p.CanWrite && (attr == null || (attr.KeyFor.Any() && attr.Type != ELinkType.LinkOptional)); })); }
//This method will take a json String and return Application object public void RecoverApplication(string jsonInput, bool force) { JToken inputJson = JToken.Parse(jsonInput); /// change app name JToken application = inputJson["Application"].First; string applicationName = (string)(application["Name"] as JValue).Value; string applicationDisplayName = (string)(application["DisplayName"] as JValue).Value; string tempAppName = $"{applicationName}_importing"; (application["Name"] as JValue).Value = tempAppName; (application["DisplayName"] as JValue).Value = $"{applicationDisplayName} - importing"; /// get context COREobject core = COREobject.i; core.Application = core.Context.Applications.SingleOrDefault(a => a.Name == applicationName); _context = COREobject.i.AppContext; /// if temp app exists Application tempApp = _context.Applications.SingleOrDefault(a => a.Name == tempAppName); if (tempApp != null) { if (force) { _context.Applications.Remove(tempApp); _context.SaveChanges(); } else { throw new Exception("Temporary application already exists!"); } } /// all types Type currentType = _queue.Dequeue(); Type cycleDetector = null; while (currentType != null) { try { // no data to import if (inputJson[currentType.Name] == null) { throw new NextType(); } /// dependency & cycle if (!getRequiredProperties(currentType).All(t => t.GetCustomAttribute <ImportExportAttribute>().KeyFor.All(tt => _ids.ContainsKey(tt)) || t.PropertyType == currentType)) { if (cycleDetector == currentType) { throw new Exception($"Cycle detected [{currentType}, {string.Join(", ", _queue.Select(t => t.Name))}]"); } else if (cycleDetector == null) { cycleDetector = currentType; } // next item _queue.Enqueue(currentType); throw new NextType(); } else { cycleDetector = null; } /// Foreach entity: Get object & Change required Ids _ids[currentType] = new Dictionary <int, int>(); // normal createEntity(inputJson[currentType.Name], currentType); /// Children properties IEnumerable <PropertyInfo> childProperties = getChildProperties(currentType); foreach (PropertyInfo prop in childProperties) { Type propType = prop.PropertyType.GetGenericArguments().Count() > 0 ? prop.PropertyType.GetGenericArguments()[0] : prop.PropertyType; _queue.Enqueue(propType); } /// next item throw new NextType(); } catch (NextType) { try { currentType = _queue.Dequeue(); } catch (InvalidOperationException) { currentType = null; } } catch (Exception ex) { throw new Exception($"Error in type [{currentType.Name}]", ex); } } /// Optional links foreach (var typePair in _optionalValues) { // optional columns string sql = $"UPDATE {_db.AddQuote(typePair.Key.GetCustomAttribute<TableAttribute>().Name)} SET {string.Join(",", getOptionalProperties(typePair.Key).Select(p => $"{_db.AddQuote(p.Name)} = @{p.Name}"))} WHERE {_db.AddQuote(PrimaryKey)} = @{PrimaryKey}"; // data foreach (var oldIdPair in typePair.Value) { IDbCommand command = _db.Command; command.CommandText = sql; command.AddParam(PrimaryKey, _ids[typePair.Key][oldIdPair.Key]); foreach (PropertyInfo prop in getOptionalProperties(typePair.Key)) { ImportExportAttribute attr = prop.GetCustomAttribute <ImportExportAttribute>(); try { object originValue = oldIdPair.Value[prop.Name]; //if (originValue == null) // continue; // single key for multiple property if (attr.KeyForMultiple_property != null) { #warning TODO: KeyForMultiple_property //int separator = (int)type.GetProperties().SingleOrDefault(p => p.Name == attr.KeyForMultiple_property).GetValue(pair.Key); //prop.SetValue(pair.Key, _ids[attr.KeyFor[separator]][(originValue as int?).Value]); } else { Type targetType = attr.KeyFor.Single(); // multiple ids separated by comma if (attr.MultipleIdInString) { string ids = (string)originValue; if (!string.IsNullOrWhiteSpace(ids)) { IEnumerable <int> idsInt = ids.Split(',').Select(id => Convert.ToInt32(id)); IEnumerable <int> newIds = idsInt.Select(i => _ids[targetType][i]); command.AddParam(prop.Name, string.Join(",", newIds)); } else { command.AddParam(prop.Name, DBNull.Value); } } // typical id else { if (originValue != null) { command.AddParam(prop.Name, _ids[targetType][Convert.ToInt32(originValue)]); } else { command.AddParam(prop.Name, DBNull.Value); } } } } catch (Exception ex) { throw new Exception($"Exception in Type[{typePair.Key.FullName}], optional param[{prop.Name}], {(oldIdPair.Value.ContainsKey(prop.Name) ? $"value[{oldIdPair.Value[prop.Name]}]" : "no value")}", ex); } } using (IDbConnection connection = _db.Connection) { connection.ConnectionString = _connectionString; connection.Open(); command.Connection = connection; command.ExecuteNonQuery(); } } } _context.SaveChanges(); /// everything ok -> merge Application Application newApp = _context.Applications.SingleOrDefault(a => a.Name == tempAppName); Application oldApp = _context.Applications.SingleOrDefault(a => a.Name == applicationName); // app exists -> merge if (oldApp != null) { // store User_Role var userRoles = _context.Users_Roles.Where(ur => ur.ApplicationId == oldApp.Id).ToList(); IEnumerable <PropertyInfo> appChildProperties = getChildProperties(typeof(Application)); foreach (PropertyInfo prop in appChildProperties) { try { Type propType = prop.PropertyType.GetGenericArguments().Count() > 0 ? prop.PropertyType.GetGenericArguments()[0] : prop.PropertyType; if (inputJson[propType.Name] != null) { // remove old IEnumerable <dynamic> items = (IEnumerable <dynamic>)prop.GetValue(oldApp); if (items != null) { _context.Set(propType).RemoveRange(items); _context.SaveChanges(); } // move new PropertyInfo parentProperty = propType.GetProperties().Single(p => { ImportExportAttribute attr = p.GetCustomAttribute <ImportExportAttribute>(); return(attr != null && attr.Type == ELinkType.Parent && attr.KeyFor.FirstOrDefault() == typeof(Application)); }); foreach (var idPair in _ids[propType]) { using (IDbConnection connection = _db.Connection) { connection.ConnectionString = _connectionString; connection.Open(); IDbCommand command = _db.Command; command.CommandText = $"UPDATE {_db.AddQuote(propType.GetCustomAttribute<TableAttribute>().Name)} SET {_db.AddQuote(parentProperty.Name)} = @{prop.Name} WHERE {_db.AddQuote(PrimaryKey)} = @{PrimaryKey}"; command.Connection = connection; command.AddParam(prop.Name, oldApp.Id); command.AddParam(PrimaryKey, idPair.Value); command.ExecuteNonQuery(); } } } } catch (Exception ex) { throw new Exception($"Error merging applications: property[{prop.Name}]. See inner exception.", ex); } } _context.Users_Roles.AddRange(userRoles); _context.Applications.Remove(newApp); } // app doesn't exists -> rename else { newApp.Name = applicationName; newApp.DisplayName = applicationDisplayName; } _context.SaveChanges(); }
private void createEntity(IEnumerable <JToken> jsonEntities, Type currentType) { /// init IEnumerable <PropertyInfo> currentRequiredProperties = getRequiredProperties(currentType); IEnumerable <PropertyInfo> optionalProperties = getOptionalProperties(currentType); IEnumerable <PropertyInfo> nonOptionalProperties = getNonOptionalProperties(currentType); List <object> rootKeyPropertyValues = null; PropertyInfo keyProperty = currentType.GetProperties().SingleOrDefault(p => p.GetCustomAttribute <ImportExportPropertyAttribute>()?.IsKey == true); /// generate sql string tableName = _db.AddQuote(currentType.GetCustomAttribute <TableAttribute>().Name); string sql = _db.Type == ESqlType.MSSQL ? $"INSERT INTO {tableName}({string.Join(",", nonOptionalProperties.Select(p => _db.AddQuote(p.Name)))}) OUTPUT inserted.{_db.AddQuote(PrimaryKey)} VALUES({string.Join(",", nonOptionalProperties.Select(c => $"@{c.Name}"))});" : $"INSERT INTO {tableName}({string.Join(",", nonOptionalProperties.Select(p => _db.AddQuote(p.Name)))}) VALUES({string.Join(",", nonOptionalProperties.Select(c => $"@{c.Name}"))}); SELECT LAST_INSERT_ID() {PrimaryKey}"; string updateSql = keyProperty == null ? "" : _db.Type == ESqlType.MSSQL ? $"UPDATE {tableName} SET {string.Join(",", nonOptionalProperties.Select(p => $"{_db.AddQuote(p.Name)} = @{p.Name}"))} OUTPUT inserted.{_db.AddQuote(PrimaryKey)} WHERE {_db.AddQuote(keyProperty.Name)} = @{keyProperty.Name}" : $"UPDATE {tableName} SET {string.Join(",", nonOptionalProperties.Select(p => $"{_db.AddQuote(p.Name)} = @{p.Name}"))} WHERE {_db.AddQuote(keyProperty.Name)} = @{keyProperty.Name}; SELECT {PrimaryKey} FROM {tableName} WHERE {_db.AddQuote(keyProperty.Name)} = @{keyProperty.Name}"; /// updating Root entity if (Roots.Contains(currentType) && keyProperty != null) { // read from json var jsonValues = jsonEntities.Select(e => e[keyProperty.Name].ToObject(keyProperty.PropertyType)); // read from db using (IDbConnection connection = _db.Connection) { connection.ConnectionString = _connectionString; connection.Open(); IDbCommand command = _db.Command; List <string> paramNames = new List <string>(); int i = 0; foreach (var jsonValue in jsonValues) { string param = $"param{i}"; command.AddParam(param, jsonValue); paramNames.Add(param); i++; } command.CommandText = $"SELECT {_db.AddQuote(keyProperty.Name)} FROM {_db.AddQuote(currentType.GetCustomAttribute<TableAttribute>().Name)} WHERE {_db.AddQuote(keyProperty.Name)} IN ({string.Join(",", paramNames.Select(p => $"@{p}"))})"; command.Connection = connection; using (var reader = command.ExecuteReader()) { rootKeyPropertyValues = new List <object>(); while (reader.Read()) { rootKeyPropertyValues.Add(reader[keyProperty.Name]); } } } } /// create entity using (IDbConnection connection = _db.Connection) { connection.ConnectionString = _connectionString; connection.Open(); foreach (JToken jsonEntity in jsonEntities) { try { int oldId = jsonEntity[PrimaryKey].ToObject <int>(); /// change required ids HashSet <string> propertiesWithCorrectValues = new HashSet <string>(); foreach (PropertyInfo prop in currentRequiredProperties) { try { ImportExportAttribute attr = prop.GetCustomAttribute <ImportExportAttribute>(); int?originId = jsonEntity[prop.Name].ToObject <int?>(); // skip items without required items // this item has null or invalid FK if (attr.skipItem && (originId == null || !_ids[attr.KeyFor.Single()].ContainsKey(originId.Value))) { bool otherPropHasValue = false; foreach (string pairPropName in attr.skipPair) { PropertyInfo pairProp = currentType.GetProperty(pairPropName); int? otherPropValue = jsonEntity[pairPropName].ToObject <int?>(); if (propertiesWithCorrectValues.Contains(pairPropName) || (otherPropValue != null && _ids[pairProp.GetCustomAttribute <ImportExportAttribute>().KeyFor.Single()].ContainsKey(otherPropValue.Value))) { otherPropHasValue = true; break; } } // has pair property correct value? if (otherPropHasValue) { jsonEntity[prop.Name] = null; } // any pair property hasn't correct value -> skip else { throw new NextEntity(); } } // property has correct value || you shouldn't skip it else { jsonEntity[prop.Name] = _ids[attr.KeyFor.Single()][originId.Value]; propertiesWithCorrectValues.Add(prop.Name); } } catch (NextEntity) { throw; } catch (Exception ex) { throw new Exception($"Exception in Type[{currentType.FullName}], optional param[{prop.Name}], entity", ex); } } /// save optional property ids foreach (PropertyInfo prop in optionalProperties) { object originValue = jsonEntity[prop.Name].ToObject <object>(); //if (originValue != null) //{ if (!_optionalValues.ContainsKey(currentType)) { _optionalValues[currentType] = new Dictionary <int, Dictionary <string, object> >(); } if (!_optionalValues[currentType].ContainsKey(oldId)) { _optionalValues[currentType][oldId] = new Dictionary <string, object>(); } _optionalValues[currentType][oldId].Add(prop.Name, originValue); //} } /// insert IDbCommand command = _db.Command; command.Connection = connection; command.CommandText = (rootKeyPropertyValues != null && rootKeyPropertyValues.Contains(jsonEntity[keyProperty.Name].ToObject(keyProperty.PropertyType))) ? updateSql // update root : sql; // insert foreach (PropertyInfo prop in nonOptionalProperties) { command.AddParam(prop.Name, jsonEntity[prop.Name].ToObject <object>() ?? DBNull.Value); } using (IDataReader reader = command.ExecuteReader()) { /// get Id reader.Read(); _ids[currentType].Add(jsonEntity[PrimaryKey].ToObject <int>(), Convert.ToInt32(reader[PrimaryKey])); } } catch (NextEntity) { } } } }
public string ExportApplication(int id, NameValueCollection form) { /// INIT string[] toExport = form.AllKeys; _db = DBCommandSet.GetDBCommandSet(_context.Applications.Find(id).DB_Type); JObject result = new JObject(); Queue <Type> queue = new Queue <Type>(RecoveryService.Roots); Dictionary <Type, HashSet <int> > ids = new Dictionary <Type, HashSet <int> >(); /// add roots foreach (Type root in RecoveryService.Roots) { // app -> by id if (root == typeof(Application)) { ids.Add(root, new HashSet <int> { id }); } // others -> all else { ids.Add(root, new HashSet <int>()); foreach (IEntity ent in _context.Set(root)) { ids[root].Add(ent.GetId()); } } } /// each type Type currentType = queue.Dequeue(); while (currentType != null) { /// parent string parentPropKey; ImportExportAttribute parentAttribute; if (RecoveryService.Roots.Contains(currentType)) { parentPropKey = RecoveryService.PrimaryKey; parentAttribute = new ImportExportAttribute(ELinkType.Parent, currentType); } else { try { PropertyInfo parentProp = currentType.GetProperties().SingleOrDefault(p => p.GetCustomAttribute <ImportExportAttribute>()?.Type == ELinkType.Parent && p.GetCustomAttribute <ImportExportAttribute>().KeyFor.Any()); parentPropKey = parentProp.Name; parentAttribute = parentProp.GetCustomAttribute <ImportExportAttribute>(); } catch (Exception ex) { throw new Exception($"Could not find parent of type [{currentType.Name}]", ex); } } Type parentType = parentAttribute.KeyFor.First(); // we don't have required types if (!ids.ContainsKey(parentType)) { throw new Exception($"We don't have parent type[{parentType.Name}]!"); } /// get items // skip if there are no parent (skip also children) if (ids[parentType].Any()) { string tableName = currentType.GetCustomAttribute <TableAttribute>().Name; string sqlQuery = parentAttribute.exportCount != 0 ? $"SELECT * " + $"FROM {_db.AddQuote(tableName)} {_db.AddQuote("table1")} " + $"WHERE {_db.AddQuote("table1")}.{_db.AddQuote(parentPropKey)} IN ({string.Join(",", ids[parentType])}) AND {_db.AddQuote("table1")}.{_db.AddQuote(RecoveryService.PrimaryKey)} IN " + $"(SELECT TOP({parentAttribute.exportCount}) {_db.AddQuote(RecoveryService.PrimaryKey)} FROM {_db.AddQuote(tableName)} {_db.AddQuote("table2")} " + $" WHERE {_db.AddQuote("table2")}.{_db.AddQuote(parentPropKey)} = {_db.AddQuote("table1")}.{_db.AddQuote(parentPropKey)} " + $" ORDER BY {_db.AddQuote("table2")}.{_db.AddQuote(parentAttribute.exportOrderColumn)} {(parentAttribute.exportOrderDesc ? "DESC" : "ASC")})" : $"SELECT * " + $"FROM {_db.AddQuote(tableName)} " + $"WHERE {_db.AddQuote(parentPropKey)} IN ({string.Join(",", ids[parentType])})"; try { var query = _context.Database.SqlQuery(currentType, sqlQuery); ids[currentType] = new HashSet <int>(); JArray items = new JArray(); foreach (IEntity row in query) { ids[currentType].Add(row.GetId()); items.Add(row.ToJson()); } result.Add(currentType.Name, items); } catch (Exception ex) { throw new Exception($"Sql exception - Type[{currentType}]; query [{sqlQuery}]", ex); } } // insert empty array if there is no parent else { ids[currentType] = new HashSet <int>(); result.Add(currentType.Name, new JArray()); } /// child types IEnumerable <PropertyInfo> childProperties = currentType.GetProperties().Where(p => { var attr = p.GetCustomAttribute <ImportExportAttribute>(); return(attr != null && attr.Type == ELinkType.Child && (attr.Branch == null || toExport.Contains(attr.Branch))); }); foreach (PropertyInfo prop in childProperties) { if (prop.PropertyType.GetGenericArguments().Count() > 0) { queue.Enqueue(prop.PropertyType.GetGenericArguments()[0]); } else { queue.Enqueue(prop.PropertyType); } } /// next item try { currentType = queue.Dequeue(); } catch (InvalidOperationException) { currentType = null; } } return(JsonConvert.SerializeObject(result, Formatting.Indented, new JsonSerializerSettings() { StringEscapeHandling = StringEscapeHandling.EscapeNonAscii })); }