/// <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
}));
}
