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)}");
}
}
/// <summary>
/// Inserts the specified SQL.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">The SQL.</param>
/// <param name="args">The arguments.</param>
/// <returns>
/// The scalar value cast to T.
/// </returns>
/// </returns>
/// <example> please use below syntax for parameters:
/// sql - (INSERT INTO table (column1, column2) VALUES (@column1, @column2))
/// args - (new {column1 = value}, new {column2 = value})
/// </example>
public virtual T Insert <T>(string sql, params object[] args) where T : class
{
OpenSharedConnection();
try
{
using (IDbCommand cmd = _sharedConnection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.AddParam(args);
object val = cmd.ExecuteScalar();
// Handle nullable types
Type u = Nullable.GetUnderlyingType(typeof(T));
if (u != null && (val == null || val == DBNull.Value))
{
return(default(T));
}
return((T)Convert.ChangeType(val, u == null ? typeof(T) : u));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseSharedConnection();
}
}
/// <summary>
/// Queries the specified SQL.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">The SQL.</param>
/// <param name="args">The arguments.</param>
/// <returns>
/// An enumerable collection of result records.
/// </returns>
/// <example> please use below syntax for parameters:
/// sql - (select * from table where column1 = @column1 and column2 = @column2)
/// args - (new {column1 = value}, new {column2 = value})
/// </example>
public virtual IEnumerable <T> Query <T>(string sql, params object[] args) where T : class
{
OpenSharedConnection();
try
{
using (IDbCommand cmd = _sharedConnection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.AddParam(args);
IDataReader reader = cmd.ExecuteReader();
return(reader.DataReaderMapToList <T>());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseSharedConnection();
}
}
public void Test3()
{
using (IDbConnection db = _dbFactory.Open())
{
List <Customer> list = db.Select <Customer>("");
}
using (IDbConnection db = _dbFactory.Open())
{
Driver d = new Driver()
{
Firstname = "ted"
};
long id = db.Insert <Driver>(d, selectIdentity: true);
string tableName = d.GetType().GetModelMetadata().ModelName;
Dictionary <string, object> bastard = new Dictionary <string, object>();
bastard.Add("Kolumn17", 123);
bastard.Add("Kolumn18", 8888L);
IDbCommand cmd = db.CreateCommand();
List <string> sets = new List <string>();
foreach (KeyValuePair <string, object> kvp in bastard)
{
sets.Add($"{kvp.Key}=@{kvp.Key}");
cmd.AddParam($"@{kvp.Key}", kvp.Value);
}
string sql = $"UPDATE {tableName} SET {string.Join(", ", sets)} WHERE id={id}";
cmd.CommandText = sql;
int nbr = cmd.ExecNonQuery();
InsertTest <Customer>(c => c.Username == "Ted");
}
}
public static void AddUpdateParamaters <T>(this IDbCommand command, T domainObject)
{
foreach (PropertyInfo property in domainObject.Properties())
{
command.AddParam(property.Name, property.GetValue(domainObject), property.PropertyType);
}
}
public static IDbCommand AddParams(this IDbCommand cmd, object parameters)
{
if (parameters.GetType().IsValueType)
{
cmd.AddParam("p0", parameters);
return(cmd);
}
var values = parameters.GetObjectValues();
foreach (var item in values)
{
cmd.AddParam(item.Key, item.Value);
}
return(cmd);
}
public virtual IDbCommand INSERT_withoutId(DBConnection db, string tableName, Dictionary <string, object> item)
{
IDbCommand command = Command;
IEnumerable <string> columnNames = item.Keys.Except(new string[] { PrimaryKey, FullPrimaryKey(tableName, false) });
command.CommandText =
$"INSERT INTO {ToRealTableName(db.Application, tableName)}({string.Join(",", columnNames.Select(c => AddQuote(c)))}) " +
$"VALUES ({string.Join(",", columnNames.Select(key => $"@{command.AddParam(key, item[key])}"))});";
return(command);
}
public virtual IDbCommand INSERT_range(DBConnection db, string tableName, IEnumerable <DBItem> items)
{
IDbCommand command = Command;
foreach (DBItem item in items)
{
IEnumerable <string> columnNames = item.getColumnNames().Except(new string[] { PrimaryKey, FullPrimaryKey(tableName, false) });
command.CommandText +=
$"INSERT INTO {ToRealTableName(db.Application, tableName)}({string.Join(",", columnNames.Select(c => AddQuote(c)))}) " +
$"VALUES ({string.Join(",", columnNames.Select(key => $"@{command.AddParam(key, item[key])}"))});";
}
return(command);
}
/// <summary>
/// Sets the db context info.
/// </summary>
public void SetDbContextInfo()
{
#if DEBUG
Log.Debug("Setting context info");
#endif
if (this.Session.TenantIdGuid.HasValue || this.Session.UserIdGuid.HasValue)
{
byte[] tenant = this.Session.TenantIdGuid.HasValue ? this.Session.TenantIdGuid.Value.ToByteArray() : Guid.Empty.ToByteArray();
byte[] endpoint = new byte[] { (byte)Context.EndpointType.Office };
byte[] userId = this.Session.UserIdGuid.HasValue ? this.Session.UserIdGuid.Value.ToByteArray() : Guid.Empty.ToByteArray();
byte[] rola = (this.Session.AdminLevel == AdminLevel.SysAdmin) ? "A".ToAsciiBytes() : ((this.Session.AdminLevel == AdminLevel.CfeAdmin) ? "M".ToAsciiBytes() : "U".ToAsciiBytes());
byte[] context = tenant.Concat(endpoint).Concat(userId).Concat(rola).ToArray();
if (Session.OrsPermissions != null)
{
context = context.Concat((new string('.', 16)).ToAsciiBytes()).Concat(Session.OrsPermissions.ToAsciiBytes()).ToArray();
}
IDbCommand cmd = db.CreateCommand();
cmd.CommandText = "SET CONTEXT_INFO @context";
cmd.AddParam("context", context, ParameterDirection.Input, DbType.Binary);
cmd.ExecuteNonQuery();
#if DEBUG
Log.Debug(string.Format("SET CONTEXT_INFO {0};", BitConverter.ToString(context)));
Log.Debug(string.Format("Value - tenant: {0};", this.Session.TenantId));
Log.Debug(string.Format("Value - (string)endpoint: {0};", Context.EndpointType.Office));
Log.Debug(string.Format("Value - DcomId: {0};", this.Session.UserId));
#endif
if (Session.OrsElementPermisions != null)
{
foreach (var elPerm in Session.OrsElementPermisions)
{
cmd = db.CreateCommand();
cmd.CommandText = $"EXEC sys.sp_set_session_context @key = N'{elPerm.Key}', @value = '{elPerm.Value}'";
cmd.ExecuteNonQuery();
#if DEBUG
Log.Debug(cmd.CommandText);
#endif
}
}
}
else
{
ClearDbContextInfo();
}
}
internal static string SetIdsInSqlParams(this IDbCommand dbCmd, IEnumerable idValues)
{
var inArgs = Sql.Flatten(idValues);
var sbParams = StringBuilderCache.Allocate();
foreach (var item in inArgs)
{
if (sbParams.Length > 0)
{
sbParams.Append(",");
}
sbParams.Append(dbCmd.AddParam(dbCmd.Parameters.Count.ToString(), item).ParameterName);
}
var sqlIn = StringBuilderCache.ReturnAndFree(sbParams);
return(sqlIn);
}
/// <summary>
/// Executes the specified SQL.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="args">The arguments.</param>
/// <returns>
/// The number of rows affected.
/// </returns>
/// <example> please use below syntax for parameters:
/// sql - (Update table set column1 = @column1 where column2 = @column2)
/// args - (new {column1 = value}, new {column2 = value})
/// </example>
public virtual int Execute(string sql, params object[] args)
{
OpenSharedConnection();
try
{
using (IDbCommand cmd = _sharedConnection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.AddParam(args);
return(cmd.ExecuteNonQuery());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseSharedConnection();
}
}
//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 static void AddSelectByIDParamaters <T>(this IDbCommand command, object Oid) where T : IEntity, new()
{
T obj = new T();
command.AddParam(obj.PrimaryKeyName(), Oid, obj.PrimaryKeyType());
}
public static void AddDeleteParamaters <T>(this IDbCommand command, T domainObject)
{
command.AddParam(domainObject.PrimaryKeyName(), domainObject.PrimaryKeyValue(), domainObject.PrimaryKeyType());
}