/// <summary>
/// Get all by object
/// PrimaryKey attr must be set
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="repository"></param>
/// <param name="quary"></param>
/// <param name="type"></param>
/// <returns></returns>
internal static IList GetSqlAll(ICustomRepository repository, Type type)
{
var sqlKey = type.FullName + "GetSqlAll";
if (!CachedSql.ContainsKey(sqlKey))
{
CachedSql.Add(sqlKey, Querys.Select(type, repository.GetDataBaseType() == DataBaseTypes.Sqllight).Execute());
}
return(repository.GetLightDataTable(repository.GetSqlCommand(CachedSql[sqlKey])).Rows.ToObject(type));
}
internal static ILightDataTable Select(ICustomRepository repository, Type type, QueryItem quary = null)
{
var sql = new StringBuilder();
sql.Append(Querys.Select(type, repository.GetDataBaseType() == DataBaseTypes.Sqllight).Execute());
if (quary != null && quary.HasValue())
{
sql.Append(quary.Execute());
}
return(repository.GetLightDataTable(repository.GetSqlCommand(sql.ToString())));
}
/// <summary>
/// Get all by object
/// PrimaryKey attr must be set ins Where
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="repository"></param>
/// <param name="quary"></param>
/// <returns></returns>
internal static List <T> Where <T>(ICustomRepository repository, string quary = null) where T : class
{
var type = typeof(T);
var sql = new StringBuilder();
sql.Append(Querys.Select(type, repository.GetDataBaseType() == DataBaseTypes.Sqllight).Execute());
if (!string.IsNullOrEmpty(quary))
{
sql = new StringBuilder(quary);
}
return(repository.GetLightDataTable(repository.GetSqlCommand(sql.ToString())).Rows.ToObject <T>());
}
/// <summary>
/// Get all by object
/// PrimaryKey attr must be set ins Where
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="repository"></param>
/// <param name="quary"></param>
/// <returns></returns>
internal static List <T> Select <T>(ICustomRepository repository, QueryItem quary = null) where T : class
{
var type = typeof(T);
var sql = new StringBuilder();
sql.Append(Querys.Select(type, repository.GetDataBaseType() == DataBaseTypes.Sqllight).Execute());
if (quary != null && quary.HasValue())
{
sql.Append(quary.Execute());
}
return(repository.GetLightDataTable(repository.GetSqlCommand(sql.ToString())).Rows.ToObject <T>());
}
/// <summary>
/// Get object by ID
/// Primary Key attribute must be set
/// </summary>
/// <param name="id"></param>
/// <param name="repository"></param>
/// <param name="type"></param>
/// <returns></returns>
internal static object GetSqlById(long id, ICustomRepository repository, Type type)
{
var sqlKey = type.GetActualType().FullName + "GetById";
if (!CachedSql.ContainsKey(sqlKey))
{
var key = type.GetActualType().GetPrimaryKey().GetPropertyName();
CachedSql.Add(sqlKey, Querys.Select(type.GetActualType(), repository.GetDataBaseType() == DataBaseTypes.Sqllight).Where.Column <long>(key).Equal("@ID", true).Execute());
}
var cmd = repository.GetSqlCommand(CachedSql[sqlKey]);
repository.AddInnerParameter(cmd, "@ID", id, System.Data.SqlDbType.BigInt);
if (type.IsGenericType && type.GetGenericTypeDefinition()?.Name == "List`1" && type.GenericTypeArguments.Length > 0)
{
return(repository.GetLightDataTable(cmd).Rows.ToObject(type));
}
return(repository.GetLightDataTable(cmd).Rows.FirstOrDefault()?.ToObject(type));
}
private static ILightDataTable ObjectColumns(this ICustomRepository repository, Type type)
{
if (CachedObjectColumn.ContainsKey(type))
{
return(CachedObjectColumn[type]);
}
var table = type.GetCustomAttribute <Table>()?.Name ?? type.Name;
var cmd = repository.GetSqlCommand(repository.GetDataBaseType() == DataBaseTypes.Mssql ?
"SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'" + table + "'"
: "SELECT name as COLUMN_NAME, type as DATA_TYPE FROM pragma_table_info('" + table + "');");
var data = repository.GetLightDataTable(cmd, "COLUMN_NAME");
if (data.Rows.Any())
{
CachedObjectColumn.Add(type, data);
}
else
{
return(data);
}
return(CachedObjectColumn[type]);
}
public List <T> Execute()
{
if (_executed)
{
return(this.ToList <T>());
}
else
{
_expression.IsSqlLight = _repository.GetDataBaseType() == Helper.DataBaseTypes.Sqllight;
foreach (var exp in _matches)
{
_expression.Translate(exp);
}
ParsedLinqToSql = _expression.Quary;
if (!_partExecuted)
{
this.AddRange(!string.IsNullOrEmpty(_expression.Quary) ? _repository.Where <T>(_expression) : _repository.GetAbstractAll <T>());
}
if (_childrenToLoad.Any() || _loadcholdrenOnlyFirstLevel.HasValue)
{
foreach (var item in this)
{
if (_childrenToLoad.Any())
{
_repository.LoadChildren(item, false, _ignoreActions, _childrenToLoad.ToArray());
}
else
{
_repository.LoadChildren(item, _loadcholdrenOnlyFirstLevel.Value, _ignoreActions);
}
}
}
_executed = true;
}
return(this.ToList <T>());
}
internal static DbCommand ProcessSql(this ICustomRepository repository, DbConnection connection, DbTransaction tran, string sql)
{
var stringExp = new Regex(@"String\[.*?\]|String\[.?\]");
var DateExp = new Regex(@"Date\[.*?\]|Date\[.?\]");
var i = 0;
var dicCols = new Dictionary <string, Tuple <object, SqlDbType> >();
MatchCollection matches = null;
while ((matches = stringExp.Matches(sql)).Count > 0)
{
Match exp = matches[0];
var col = "@CO" + i + "L";
object str = exp.Value.TrimEnd(']').Substring(@"String\[".Length - 1);
sql = sql.Remove(exp.Index, exp.Value.Length);
sql = sql.Insert(exp.Index, col);
dicCols.Add(col, new Tuple <object, SqlDbType>(str.ConvertValue <string>(), SqlDbType.NVarChar));
i++;
}
while ((matches = DateExp.Matches(sql)).Count > 0)
{
Match exp = matches[0];
var col = "@CO" + i + "L";
object str = exp.Value.TrimEnd(']').Substring(@"Date\[".Length - 1);
sql = sql.Remove(exp.Index, exp.Value.Length);
sql = sql.Insert(exp.Index, col);
dicCols.Add(col, new Tuple <object, SqlDbType>(str.ConvertValue <DateTime>(), SqlDbType.DateTime));
i++;
}
DbCommand cmd = null;
#if NET461 || NET451 || NET46
if (repository.GetDataBaseType() == Helper.DataBaseTypes.Mssql)
{
cmd = tran != null ? new SqlCommand(sql, connection as SqlConnection, tran as SqlTransaction) : new SqlCommand(sql, connection as SqlConnection);
}
else
{
cmd = tran == null ? new SQLiteCommand(sql, connection as SQLiteConnection) : new SQLiteCommand(sql, connection as SQLiteConnection, tran as SQLiteTransaction);
}
foreach (var dic in dicCols)
{
repository.AddInnerParameter(cmd, dic.Key, dic.Value.Item1, dic.Value.Item2);
}
return(cmd);
#elif NETCOREAPP2_0 || NETSTANDARD2_0
if (repository.GetDataBaseType() == Helper.DataBaseTypes.Mssql)
{
cmd = tran != null ? new SqlCommand(sql, connection as SqlConnection, tran as SqlTransaction) : new SqlCommand(sql, connection as SqlConnection);
}
else
{
cmd = tran == null ? new SqliteCommand(sql, connection as SqliteConnection) : new SqliteCommand(sql, connection as SqliteConnection, tran as SqliteTransaction);
}
foreach (var dic in dicCols)
{
repository.AddInnerParameter(cmd, dic.Key, dic.Value.Item1, dic.Value.Item2);
}
return(cmd);
#endif
}
internal static List <string> DeleteAbstract(ICustomRepository repository, object o, bool save = false)
{
var type = o.GetType().GetActualType();
var props = FastDeepCloner.DeepCloner.GetFastDeepClonerProperties(type);
var table = "[" + type.GetCustomAttribute <Table>()?.Name ?? type.Name + "]";
var primaryKey = MethodHelper.GetPrimaryKey(o as IDbEntity);
var primaryKeyValue = MethodHelper.ConvertValue <long>(primaryKey.GetValue(o));
if (primaryKeyValue <= 0)
{
return(new List <string>());
}
var sql = new List <string>()
{
"DELETE " + table + Querys.Where(repository.GetDataBaseType() == DataBaseTypes.Sqllight).Column(primaryKey.GetPropertyName()).Equal(primaryKeyValue).Execute()
};
foreach (var prop in props.Where(x => !x.IsInternalType && x.GetCustomAttribute <IndependentData>() == null && x.GetCustomAttribute <ExcludeFromAbstract>() == null))
{
var value = prop.GetValue(o);
if (value == null)
{
continue;
}
var subSql = new List <string>();
var propType = prop.PropertyType.GetActualType();
var insertBefore = props.Any(x => x.GetCustomAttribute <ForeignKey>()?.Type == propType);
if (FastDeepCloner.DeepCloner.GetFastDeepClonerProperties(propType).All(x => x.GetCustomAttribute <ForeignKey>()?.Type != type))
{
if (!insertBefore)
{
continue;
}
}
if (value is IList)
{
foreach (var item in value as IList)
{
subSql.AddRange(DeleteAbstract(repository, item));
}
}
else
{
subSql.AddRange(DeleteAbstract(repository, value));
}
if (insertBefore)
{
sql.InsertRange(sql.Count - 1, subSql);
}
else
{
sql.AddRange(subSql);
}
}
if (!save)
{
return(sql);
}
try
{
repository.CreateTransaction();
// begin deleting all object that refer to the requasted object
for (var i = sql.Count - 1; i >= 0; i--)
{
var cmd = repository.GetSqlCommand(sql[i]);
cmd.ExecuteNonQuery();
}
repository.Commit();
}
catch (Exception e)
{
repository.Rollback();
throw e;
}
return(sql);
}
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 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);
}
