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