public string ToSql(DBCommandSet set, IDbCommand command)
{
string realJoinTableName = set.ToRealTableName(_db.Application, joinTableName);
string realOriginTableName = set.ToRealTableName(_db.Application, originTableName);
return($"{JoinType} {realJoinTableName} ON {realJoinTableName}.{set.QuotesBegin}{joinColumnName}{set.QuotesEnd}={realOriginTableName}.{set.QuotesBegin}{originColumnName}{set.QuotesEnd}");
}
public RecoveryService()
{
_db = DBCommandSet.GetDBCommandSet(Entitron.DefaultDBType);
_connectionString = Entitron.EntityConnectionString;
_queue = new Queue <Type>(Roots);
_ids = new Dictionary <Type, Dictionary <int, int> >();
_optionalValues = new Dictionary <Type, Dictionary <int, Dictionary <string, object> > >();
}
public string ToSql(DBCommandSet set, IDbCommand command)
{
string column = this.column;
if (!columnNameWithTable && column.Contains("."))
{
column = column.Substring(column.IndexOf('.') + 1);
}
switch (operation)
{
// no params
case "IS NULL":
case "IS NOT NULL":
return($"{concat} {columnWithFunction(column, operation_columnFunction, set)} {operation}");
// 2 params
case "BETWEEN":
case "NOT BETWEEN":
string value0 = command.AddParam("value", operation_params[0]);
string value1 = command.AddParam("value", operation_params[1]);
return($"{concat} {columnWithFunction(column, operation_columnFunction, set)} {operation} {columnWithFunction("@value0", operation_columnFunction)} AND {columnWithFunction("@value0", operation_columnFunction)}");
// list
case "IN":
case "NOT IN":
// empty
if (!operation_params.Any())
{
if (operation == "IN")
{
return($"{concat} 1=0");
}
else
{
return($"{concat} 1=1");
}
}
//
return($"{concat} {columnWithFunction(column, operation_columnFunction, set)} {operation} ({string.Join(",", operation_params.Select(p => $"@{command.AddParam("list", p)}"))})");
// 1 param
default:
if (string.IsNullOrEmpty(operation))
{
operation = "=";
}
return($"{concat} {columnWithFunction(column, operation_columnFunction, set)} {operation} {columnWithFunction($"@{command.AddParam("value", operation_params.First())}", operation_columnFunction)}");
}
}
private string columnWithFunction(string column, string function, DBCommandSet set = null)
{
string result = column;
if (set != null)
{
result = $"{set.QuotesBegin}{result}{set.QuotesEnd}";
}
if (!string.IsNullOrEmpty(function))
{
result = $"{function}({result})";
}
return(result);
}
public string ToSql(DBCommandSet set, IDbCommand command)
{
return(string.Join(i.Separator, _parts.Select(p => p.ToSql(set, command))));
}
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
}));
}
public string ToSql(DBCommandSet set, IDbCommand command)
{
return($"ORDER BY {string.Join(",", Columns.Select(c => set.AddQuote(c)))} {AscDesc}");
}
public static void ParseConnectionString(string connectionStringName)
{
DefaultConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
DefaultDBType = DBCommandSet.GetSqlType(ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName);
}
