private string GetSelectClauseStatement <T>() { DatabaseEntityDef modelDef = _entityDefFactory.GetDef <T>(); string cacheKey = string.Format(GlobalSettings.Culture, "{0}_{1}_SELECT", modelDef.DatabaseName, modelDef.TableName); if (_sqlStatementDict.ContainsKey(cacheKey)) { return(_sqlStatementDict[cacheKey]); } StringBuilder argsBuilder = new StringBuilder(); foreach (DatabaseEntityPropertyDef info in modelDef.Properties) { if (info.IsTableProperty) { argsBuilder.AppendFormat(GlobalSettings.Culture, "{0}.{1},", modelDef.DbTableReservedName, info.DbReservedName); //argsBuilder.AppendFormat("{0},", info.DbReservedName); } } if (argsBuilder.Length > 0) { argsBuilder.Remove(argsBuilder.Length - 1, 1); } string selectClause = string.Format(GlobalSettings.Culture, "SELECT {0} ", argsBuilder.ToString()); _sqlStatementDict.TryAdd(cacheKey, selectClause); return(selectClause); }
public long Count <T>(SelectExpression <T> selectCondition, FromExpression <T> fromCondition, WhereExpression <T> whereCondition, TransactionContext transContext) where T : DatabaseEntity, new() { #region Argument Adjusting if (selectCondition != null) { selectCondition.Select(t => t.Id).Select(t => t.Deleted).Select(t => t.LastTime).Select(t => t.LastUser).Select(t => t.Version); } if (whereCondition == null) { whereCondition = Where <T>(); } whereCondition.And(t => t.Deleted == false); #endregion long count = -1; DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>(); try { IDbCommand command = _sqlBuilder.CreateCountCommand(fromCondition, whereCondition); object countObj = _databaseEngine.ExecuteCommandScalar(transContext?.Transaction, entityDef.DatabaseName, command, transContext != null); count = Convert.ToInt32(countObj, GlobalSettings.Culture); } catch (DbException ex) { throw ex;// _logger.LogCritical(ex.Message); } return(count); }
public IList <Tuple <TSource, TTarget1, TTarget2> > Retrieve <TSource, TTarget1, TTarget2>(FromExpression <TSource> fromCondition, WhereExpression <TSource> whereCondition, TransactionContext transContext) where TSource : DatabaseEntity, new() where TTarget1 : DatabaseEntity, new() where TTarget2 : DatabaseEntity, new() { if (whereCondition == null) { whereCondition = Where <TSource>(); } switch (fromCondition.JoinType) { case SqlJoinType.LEFT: whereCondition.And(t => t.Deleted == false); break; case SqlJoinType.RIGHT: whereCondition.And <TTarget2>(t => t.Deleted == false); break; case SqlJoinType.INNER: whereCondition.And(t => t.Deleted == false).And <TTarget1>(t => t.Deleted == false).And <TTarget2>(t => t.Deleted == false); break; case SqlJoinType.FULL: break; case SqlJoinType.CROSS: whereCondition.And(t => t.Deleted == false).And <TTarget1>(t => t.Deleted == false).And <TTarget2>(t => t.Deleted == false); break; } IList <Tuple <TSource, TTarget1, TTarget2> > result = null; IDataReader reader = null; DatabaseEntityDef entityDef = _entityDefFactory.GetDef <TSource>(); try { IDbCommand command = _sqlBuilder.CreateRetrieveCommand <TSource, TTarget1, TTarget2>(fromCondition, whereCondition); reader = _databaseEngine.ExecuteCommandReader(transContext?.Transaction, entityDef.DatabaseName, command, transContext != null); result = _modelMapper.ToList <TSource, TTarget1, TTarget2>(reader); } //catch (DbException ex) //{ // result = new List<Tuple<TSource, TTarget1, TTarget2>>(); // _logger.LogCritical(ex.Message); //} finally { if (reader != null) { reader.Dispose(); } } return(result); }
public static string CreateUpdateTemplate(DatabaseEntityDef modelDef) { StringBuilder args = new StringBuilder(); foreach (DatabaseEntityPropertyDef info in modelDef.Properties) { if (info.IsTableProperty) { if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime" || info.PropertyName == "Deleted") { continue; } args.AppendFormat(GlobalSettings.Culture, " {0}={1},", info.DbReservedName, info.DbParameterizedName); } } if (args.Length > 0) { args.Remove(args.Length - 1, 1); } string statement = string.Format(GlobalSettings.Culture, "UPDATE {0} SET {1}", modelDef.DbTableReservedName, args.ToString()); return(statement); }
public static string CreateDeleteTemplate(DatabaseEntityDef modelDef) { DatabaseEntityPropertyDef deletedProperty = modelDef.GetProperty("Deleted") !; DatabaseEntityPropertyDef lastUserProperty = modelDef.GetProperty("LastUser") !; StringBuilder args = new StringBuilder(); args.Append($"{deletedProperty.DbReservedName}=1,"); args.Append($"{lastUserProperty.DbReservedName}={lastUserProperty.DbParameterizedName}"); return($"UPDATE {modelDef.DbTableReservedName} SET {args} "); }
private FromExpression <T> InternalJoin <Target>(string joinType, Expression joinExpr) { DatabaseEntityDef targetDef = entityDefFactory.GetDef(typeof(Target)); _statementBuilder.Append(" "); _statementBuilder.Append(joinType); _statementBuilder.Append(" "); _statementBuilder.Append(targetDef.DbTableReservedName); _statementBuilder.Append(" ON "); _statementBuilder.Append(joinExpr.ToStatement(expressionContext)); _statementBuilder.Append(" "); return(this); }
private string SQLite_Table_Create_Statement(Type type, bool addDropStatement) { StringBuilder sql = new StringBuilder(); DatabaseEntityDef definition = _entityDefFactory.GetDef(type); if (definition.DbTableReservedName.IsNullOrEmpty()) { throw new DatabaseException($"Type : {definition.EntityFullName} has null or empty DbTableReservedName"); } foreach (DatabaseEntityPropertyDef info in definition.Properties) { if (!info.IsTableProperty) { continue; } if (info.PropertyName.IsIn("Id", "Deleted", "LastUser", "LastTime", "Version")) { continue; } string dbTypeStatement = info.TypeConverter == null ? _databaseEngine.GetDbTypeStatement(info.PropertyType) : info.TypeConverter.TypeToDbTypeStatement(info.PropertyType); string nullable = info.IsNullable ? "" : " NOT NULL "; string defaultValue = info.DbDefaultValue.IsNullOrEmpty() ? "" : " DEFAULT " + info.DbDefaultValue; string unique = info.IsUnique ? " UNIQUE " : ""; sql.AppendLine($" {info.DbReservedName} {dbTypeStatement} {nullable} {unique} {defaultValue} ,"); } string dropStatement = addDropStatement ? $"Drop table if exists {definition.DbTableReservedName};" : string.Empty; return ($@"{dropStatement} CREATE TABLE {definition.DbTableReservedName} ( ""Id"" INTEGER PRIMARY KEY AUTOINCREMENT, {sql} ""Deleted"" NUMERIC NOT NULL DEFAULT 0, ""LastUser"" TEXT, ""LastTime"" NUMERIC, ""Version"" INTEGER NOT NULL );"); }
public async Task <IList <TSelect> > RetrieveAsync <TSelect, TFrom, TWhere>(SelectExpression <TSelect> selectCondition, FromExpression <TFrom> fromCondition, WhereExpression <TWhere> whereCondition, TransactionContext transContext = null) where TSelect : DatabaseEntity, new() where TFrom : DatabaseEntity, new() where TWhere : DatabaseEntity, new() { #region Argument Adjusting if (selectCondition != null) { selectCondition.Select(t => t.Id).Select(t => t.Deleted).Select(t => t.LastTime).Select(t => t.LastUser).Select(t => t.Version); } if (whereCondition == null) { whereCondition = Where <TWhere>(); } whereCondition.And(t => t.Deleted == false).And <TSelect>(ts => ts.Deleted == false).And <TFrom>(tf => tf.Deleted == false); #endregion IList <TSelect> result = null; IDataReader reader = null; DatabaseEntityDef selectDef = _entityDefFactory.GetDef <TSelect>(); try { IDbCommand command = _sqlBuilder.CreateRetrieveCommand(selectCondition, fromCondition, whereCondition); reader = await _databaseEngine.ExecuteCommandReaderAsync(transContext?.Transaction, selectDef.DatabaseName, command, transContext != null).ConfigureAwait(false); result = _modelMapper.ToList <TSelect>(reader); } //catch (DbException ex) //{ // result = new List<TSelect>(); // _logger.LogCritical(ex.Message); //} finally { if (reader != null) { reader.Dispose(); } } return(result); }
/// <summary> /// 修改,建议每次修改前先select,并放置在一个事务中。 /// 版本控制,如果item中Version未赋值,会无法更改 /// </summary> public DatabaseResult Update <T>(T item, TransactionContext transContext) where T : DatabaseEntity, new() { if (!item.IsValid()) { return(DatabaseResult.Fail("entity check failed.")); } DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>(); if (!entityDef.DatabaseWriteable) { return(DatabaseResult.NotWriteable()); } WhereExpression <T> condition = Where <T>(); long id = item.Id; long version = item.Version; condition.Where(t => t.Id == id).And(t => t.Deleted == false); //版本控制 condition.And(t => t.Version == version); try { IDbCommand dbCommand = _sqlBuilder.CreateUpdateCommand(condition, item, "default"); long rows = _databaseEngine.ExecuteCommandNonQuery(transContext?.Transaction, entityDef.DatabaseName, dbCommand); if (rows == 1) { item.Version++; return(DatabaseResult.Succeeded()); } else if (rows == 0) { return(DatabaseResult.NotFound()); } throw new Exception("Multiple Rows Affected instead of one. Something go wrong."); } catch (DbException ex) { //_logger.LogCritical(ex.Message); return(DatabaseResult.Fail(ex)); } }
public IList <T> Retrieve <T>(SelectExpression <T> selectCondition, FromExpression <T> fromCondition, WhereExpression <T> whereCondition, TransactionContext transContext) where T : DatabaseEntity, new() { #region Argument Adjusting if (selectCondition != null) { selectCondition.Select(t => t.Id).Select(t => t.Deleted).Select(t => t.LastTime).Select(t => t.LastUser).Select(t => t.Version); } if (whereCondition == null) { whereCondition = Where <T>(); } whereCondition.And(t => t.Deleted == false); #endregion IList <T> result = null; IDataReader reader = null; DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>(); try { IDbCommand command = _sqlBuilder.CreateRetrieveCommand <T>(selectCondition, fromCondition, whereCondition); reader = _databaseEngine.ExecuteCommandReader(transContext?.Transaction, entityDef.DatabaseName, command, transContext != null); result = _modelMapper.ToList <T>(reader); } //catch (DbException ex) //{ // result = new List<T>(); // _logger.LogCritical(ex.Message); //} finally { if (reader != null) { reader.Dispose(); } } return(result); }
public IDbCommand CreateAddOrUpdateCommand <T>(T entity, string lastUser) where T : Entity, new() { DatabaseEntityDef modelDef = _entityDefFactory.GetDef <T>(); List <IDataParameter> parameters = new List <IDataParameter>(); string cacheKey = GetCreateAddOrUpdateTemplateCacheKey(modelDef); if (!_sqlStatementDict.TryGetValue(cacheKey, out string addOrUpdateTemplate)) { addOrUpdateTemplate = CreateAddOrUpdateTemplate(modelDef, _databaseEngine.EngineType); _sqlStatementDict.TryAdd(cacheKey, addOrUpdateTemplate); } foreach (DatabaseEntityPropertyDef info in modelDef.Properties) { if (info.IsTableProperty) { if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime") { continue; } //当IsTableProperty为true时,DbParameterizedName一定不为null if (info.PropertyName == "Version") { parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, entity.Version + 1, info.DbFieldType)); } else if (info.PropertyName == "Deleted") { parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, 0, info.DbFieldType)); } else if (info.PropertyName == "LastUser") { parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, DbParameterValue_Statement(lastUser, info), info.DbFieldType)); } else { parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, DbParameterValue_Statement(info.GetValue(entity), info), info.DbFieldType)); } } } return(AssembleCommand <T, T>(false, addOrUpdateTemplate, null, null, parameters)); }
public static string CreateAddTemplate(DatabaseEntityDef modelDef, DatabaseEngineType engineType) { StringBuilder args = new StringBuilder(); StringBuilder selectArgs = new StringBuilder(); StringBuilder values = new StringBuilder(); foreach (DatabaseEntityPropertyDef info in modelDef.Properties) { if (info.IsTableProperty) { selectArgs.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName); if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime") { continue; } args.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName); values.AppendFormat(GlobalSettings.Culture, " {0},", info.DbParameterizedName); } } if (selectArgs.Length > 0) { selectArgs.Remove(selectArgs.Length - 1, 1); } if (args.Length > 0) { args.Remove(args.Length - 1, 1); } if (values.Length > 0) { values.Remove(values.Length - 1, 1); } DatabaseEntityPropertyDef idProperty = modelDef.GetProperty("Id") !; return($"insert into {modelDef.DbTableReservedName}({args}) values({values});select {selectArgs} from {modelDef.DbTableReservedName} where {idProperty.DbReservedName} = {GetLastInsertIdStatement(engineType)};"); }
public IDbCommand CreateDeleteCommand <T>(WhereExpression <T> condition, string lastUser) where T : DatabaseEntity, new() { DatabaseEntityDef definition = _entityDefFactory.GetDef <T>(); string cacheKey = definition.DatabaseName + ":" + definition.TableName + ":DELETE"; if (!_sqlStatementDict.TryGetValue(cacheKey, out string deleteTemplate)) { deleteTemplate = CreateDeleteTemplate(definition); _sqlStatementDict.TryAdd(cacheKey, deleteTemplate); } DatabaseEntityPropertyDef lastUserProperty = definition.GetProperty("LastUser"); List <IDataParameter> parameters = new List <IDataParameter>(); parameters.Add(_databaseEngine.CreateParameter(lastUserProperty.DbParameterizedName, DbParameterValue_Statement(lastUser, lastUserProperty), lastUserProperty.DbFieldType)); return(AssembleCommand <T, T>(false, deleteTemplate, null, condition, parameters)); }
public IDbCommand CreateBatchDeleteCommand <T>(IEnumerable <T> entities, string lastUser) where T : Entity, new() { ThrowIf.Empty(entities, nameof(entities)); StringBuilder innerBuilder = new StringBuilder(); DatabaseEntityDef definition = _entityDefFactory.GetDef <T>(); string tempTableName = "HBD" + DateTimeOffset.UtcNow.Ticks.ToString(GlobalSettings.Culture); foreach (T entity in entities) { string lastUserValue = lastUser == null ? "null" : _databaseEngine.GetDbValueStatement(lastUser, needQuoted: true); string args = $"`Deleted` = 1, `LastUser` = {lastUserValue}, `Version` = {entity.Version + 1}"; innerBuilder.Append( $"UPDATE {definition.DbTableReservedName} set {args} WHERE `Id`={entity.Id} AND `Version`={entity.Version} and `Deleted`=0;{TempTable_Insert(tempTableName, FoundChanges_Statement(_databaseEngine.EngineType), _databaseEngine.EngineType)}"); } string sql = $"{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}{TempTable_Create(tempTableName, _databaseEngine.EngineType)}{innerBuilder}{TempTable_Select_All(tempTableName, _databaseEngine.EngineType)}{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}"; return(AssembleCommand <T, T>(false, sql, null, null, null)); }
/// <summary> /// 增加,并且item被重新赋值 /// </summary> public async Task <DatabaseResult> AddAsync <T>(T item, TransactionContext transContext) where T : DatabaseEntity, new() { if (!item.IsValid()) { //TODO: 给所有使用到IsValid()方法的地方,都加上GetValidateErrorMessage输出 return(DatabaseResult.Fail($"entity check failed.{item.GetValidateErrorMessage()}")); } DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>(); if (!entityDef.DatabaseWriteable) { return(DatabaseResult.NotWriteable()); } IDataReader reader = null; try { IDbCommand dbCommand = _sqlBuilder.CreateAddCommand(item, "default"); reader = await _databaseEngine.ExecuteCommandReaderAsync(transContext?.Transaction, entityDef.DatabaseName, dbCommand, true).ConfigureAwait(false); _modelMapper.ToObject(reader, item); return(DatabaseResult.Succeeded()); } catch (DbException ex) { //_logger.LogCritical(ex.Message); return(DatabaseResult.Fail(ex)); } finally { if (reader != null) { reader.Dispose(); } } }
public IDbCommand CreateUpdateCommand <T>(WhereExpression <T> condition, T entity, string lastUser) where T : Entity, new() { DatabaseEntityDef definition = _entityDefFactory.GetDef <T>(); List <IDataParameter> parameters = new List <IDataParameter>(); string cacheKey = definition.DatabaseName + ":" + definition.TableName + ":UPDATE"; if (!_sqlStatementDict.TryGetValue(cacheKey, out string updateTemplate)) { updateTemplate = CreateUpdateTemplate(definition); _sqlStatementDict.TryAdd(cacheKey, updateTemplate); } foreach (DatabaseEntityPropertyDef info in definition.Properties) { if (info.IsTableProperty) { if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime" || info.PropertyName == "Deleted") { continue; } //当IsTableProperty为true时,DbParameterizedName一定不为null if (info.PropertyName == "Version") { parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, entity.Version + 1, info.DbFieldType)); } else if (info.PropertyName == "LastUser") { parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, DbParameterValue_Statement(lastUser, info), info.DbFieldType)); } else { parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, DbParameterValue_Statement(info.GetValue(entity), info), info.DbFieldType)); } } } return(AssembleCommand <T, T>(false, updateTemplate, null, condition, parameters)); }
/// <summary> /// 增加,并且item被重新赋值 /// </summary> public DatabaseResult Add <T>(T item, TransactionContext transContext) where T : DatabaseEntity, new() { if (!item.IsValid()) { return(DatabaseResult.Fail("entity check failed.")); } DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>(); if (!entityDef.DatabaseWriteable) { return(DatabaseResult.NotWriteable()); } IDataReader reader = null; try { IDbCommand dbCommand = _sqlBuilder.CreateAddCommand(item, "default"); reader = _databaseEngine.ExecuteCommandReader(transContext?.Transaction, entityDef.DatabaseName, dbCommand, true); _modelMapper.ToObject(reader, item); return(DatabaseResult.Succeeded()); } catch (DbException ex) { //_logger.LogCritical(ex.Message); return(DatabaseResult.Fail(ex)); } finally { if (reader != null) { reader.Dispose(); } } }
public static string CreateUpdateTemplate(DatabaseEntityDef modelDef) { StringBuilder args = new StringBuilder(); foreach (DatabaseEntityPropertyDef info in modelDef.Properties) { if (info.IsTableProperty) { if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime" || info.PropertyName == "Deleted") { continue; } args.Append($" {info.DbReservedName}={info.DbParameterizedName},"); } } if (args.Length > 0) { args.Remove(args.Length - 1, 1); } return($"UPDATE {modelDef.DbTableReservedName} SET {args}"); }
/// <summary> /// 批量更改 /// </summary> /// <param name="items"></param> /// <returns></returns> public DatabaseResult BatchUpdate <T>(IEnumerable <T> items, string lastUser, TransactionContext transContext) where T : DatabaseEntity, new() { if (transContext == null) { return(DatabaseResult.Fail(new ArgumentNullException(nameof(transContext)))); } if (!CheckEntities <T>(items)) { return(DatabaseResult.Fail("entities not valid.")); } DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>(); if (!entityDef.DatabaseWriteable) { return(DatabaseResult.NotWriteable()); } IDataReader reader = null; try { IDbCommand dbCommand = _sqlBuilder.CreateBatchUpdateStatement(items, lastUser); reader = _databaseEngine.ExecuteCommandReader( transContext.Transaction, entityDef.DatabaseName, dbCommand, true); int count = 0; while (reader.Read()) { int matched = reader.GetInt32(0); if (matched != 1) { throw new DatabaseException("BatchUpdate wrong, not find the {" + count + "}th data item. "); } count++; } if (count != items.Count()) { throw new DatabaseException("BatchUpdate wrong number return. Some Rows not exists."); } return(DatabaseResult.Succeeded()); } catch (Exception ex) { //_logger.LogCritical(ex.Message); return(DatabaseResult.Fail(ex)); } finally { if (reader != null) { reader.Dispose(); } } }
public static string CreateAddOrUpdateTemplate(DatabaseEntityDef modelDef, DatabaseEngineType engineType) { StringBuilder args = new StringBuilder(); StringBuilder selectArgs = new StringBuilder(); StringBuilder values = new StringBuilder(); StringBuilder exceptGuidAndFixedVersionUpdatePairs = new StringBuilder(); foreach (DatabaseEntityPropertyDef info in modelDef.Properties) { if (info.IsTableProperty) { selectArgs.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName); if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime") { continue; } args.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName); values.AppendFormat(GlobalSettings.Culture, " {0},", info.DbParameterizedName); } } foreach (DatabaseEntityPropertyDef info in modelDef.Properties) { if (info.IsTableProperty) { if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "Version" || info.PropertyName == "Guid" || info.PropertyName == "LastTime" || info.PropertyName == "Deleted") { continue; } exceptGuidAndFixedVersionUpdatePairs.Append($" {info.DbReservedName}={info.DbParameterizedName},"); } } DatabaseEntityPropertyDef versionPropertyDef = modelDef.GetProperty("Version") !; exceptGuidAndFixedVersionUpdatePairs.Append($" {versionPropertyDef.DbReservedName}={versionPropertyDef.DbReservedName}+1,"); if (selectArgs.Length > 0) { selectArgs.Remove(selectArgs.Length - 1, 1); } if (args.Length > 0) { args.Remove(args.Length - 1, 1); } if (values.Length > 0) { values.Remove(values.Length - 1, 1); } if (exceptGuidAndFixedVersionUpdatePairs.Length > 0) { exceptGuidAndFixedVersionUpdatePairs.Remove(exceptGuidAndFixedVersionUpdatePairs.Length - 1, 1); } DatabaseEntityPropertyDef guidProperty = modelDef.GetProperty("Guid") !; return($"insert into {modelDef.DbTableReservedName}({args}) values({values}) {OnDuplicateKeyUpdateStatement(engineType)} {exceptGuidAndFixedVersionUpdatePairs};select {selectArgs} from {modelDef.DbTableReservedName} where {guidProperty.DbReservedName} = {guidProperty.DbParameterizedName};"); }
public async Task <DatabaseResult> BatchDeleteAsync <T>(IEnumerable <T> items, string lastUser, TransactionContext transContext) where T : DatabaseEntity, new() { if (transContext == null) { return(DatabaseResult.Fail(new ArgumentNullException(nameof(transContext)))); } if (!CheckEntities <T>(items)) { return(DatabaseResult.Fail("Entities not valid")); } DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>(); if (!entityDef.DatabaseWriteable) { return(DatabaseResult.NotWriteable()); } IDataReader reader = null; try { IDbCommand dbCommand = _sqlBuilder.CreateBatchDeleteStatement(items, lastUser); reader = await _databaseEngine.ExecuteCommandReaderAsync( transContext.Transaction, entityDef.DatabaseName, dbCommand, true); int count = 0; while (reader.Read()) { int affected = reader.GetInt32(0); if (affected != 1) { throw new DatabaseException("BatchDelete wrong, not found the {" + count + "}th data item. "); } count++; } if (count != items.Count()) { throw new DatabaseException("BatchDelete wrong number return. Some data is not found."); } return(DatabaseResult.Succeeded()); } catch (Exception ex) { //_logger.Error_BatchDelete_Thrown(ex, lastUser); return(DatabaseResult.Fail(ex)); } finally { if (reader != null) { reader.Dispose(); } } }
private string MySQL_Table_Create_Statement(Type type, bool addDropStatement) { StringBuilder sql = new StringBuilder(); DatabaseEntityDef definition = _entityDefFactory.GetDef(type); if (definition.DbTableReservedName.IsNullOrEmpty()) { throw new DatabaseException($"Type : {definition.EntityFullName} has null or empty DbTableReservedName"); } foreach (DatabaseEntityPropertyDef info in definition.Properties) { if (!info.IsTableProperty) { continue; } if (info.PropertyName.IsIn("Id", "Deleted", "LastUser", "LastTime", "Version")) { continue; } int length = 0; if (info.DbLength == null || info.DbLength == 0) { if (info.DbFieldType == DbType.String || info.PropertyType == typeof(string) || info.PropertyType == typeof(char) || info.PropertyType.IsEnum || info.PropertyType.IsAssignableFrom(typeof(IList <string>)) || info.PropertyType.IsAssignableFrom(typeof(IDictionary <string, string>))) { length = _entityDefFactory.GetVarcharDefaultLength(); } } else { length = info.DbLength.Value; } string binary = ""; if (info.PropertyType == typeof(string) || info.PropertyType == typeof(char) || info.PropertyType == typeof(char?)) { binary = ""; } string dbTypeStatement = info.TypeConverter == null ? _databaseEngine.GetDbTypeStatement(info.PropertyType) : info.TypeConverter.TypeToDbTypeStatement(info.PropertyType); if (length >= 16383) //因为utf8mb4编码,一个汉字4个字节 { dbTypeStatement = "MEDIUMTEXT"; } if (length >= 4194303) { throw new DatabaseException($"字段长度太长。{info.EntityDef.EntityFullName} : {info.PropertyName}"); } if (info.IsLengthFixed) { dbTypeStatement = "CHAR"; } sql.AppendFormat(GlobalSettings.Culture, " {0} {1}{2} {6} {3} {4} {5},", info.DbReservedName, dbTypeStatement, (length == 0 || dbTypeStatement == "MEDIUMTEXT") ? "" : "(" + length + ")", info.IsNullable == true ? "" : " NOT NULL ", string.IsNullOrEmpty(info.DbDefaultValue) ? "" : "DEFAULT " + info.DbDefaultValue, !info.IsAutoIncrementPrimaryKey && !info.IsForeignKey && info.IsUnique ? " UNIQUE " : "", binary ); sql.AppendLine(); } string dropStatement = string.Empty; if (addDropStatement) { dropStatement = string.Format(GlobalSettings.Culture, "Drop table if exists {0};" + Environment.NewLine, definition.DbTableReservedName); } return(string.Format(GlobalSettings.Culture, "{2}" + "CREATE TABLE {0} (" + Environment.NewLine + "`Id` bigint(20) NOT NULL AUTO_INCREMENT," + Environment.NewLine + "`Deleted` bit(1) NOT NULL DEFAULT b'0'," + Environment.NewLine + "`LastUser` varchar(100) DEFAULT NULL," + Environment.NewLine + "`LastTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," + Environment.NewLine + "`Version` int(11) NOT NULL DEFAULT '0'," + Environment.NewLine + " {1} " + " PRIMARY KEY (`Id`) " + Environment.NewLine + " ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;", definition.DbTableReservedName, sql.ToString(), dropStatement)); }
private int CreateTable(DatabaseEntityDef def, TransactionContext transContext) { string sql = GetTableCreateStatement(def.EntityType, false); return(_databaseEngine.ExecuteSqlNonQuery(transContext.Transaction, def.DatabaseName, sql)); }
public IDbCommand CreateBatchUpdateCommand <T>(IEnumerable <T> entities, string lastUser) where T : Entity, new() { ThrowIf.Empty(entities, nameof(entities)); StringBuilder innerBuilder = new StringBuilder(); DatabaseEntityDef definition = _entityDefFactory.GetDef <T>(); string tempTableName = "HBU" + DateTimeOffset.UtcNow.Ticks.ToString(GlobalSettings.Culture); IList <IDataParameter> parameters = new List <IDataParameter>(); int number = 0; foreach (T entity in entities) { StringBuilder args = new StringBuilder(); foreach (DatabaseEntityPropertyDef info in definition.Properties) { string parameterizedName = info.DbParameterizedName + number.ToString(GlobalSettings.Culture); if (info.IsTableProperty) { if (info.IsAutoIncrementPrimaryKey) { continue; } if (info.PropertyName == "LastTime") { continue; } if (info.PropertyName == "Deleted") { continue; } if (info.PropertyName == "Version") { args.AppendFormat(GlobalSettings.Culture, " {0}={1},", info.DbReservedName, parameterizedName); parameters.Add(_databaseEngine.CreateParameter(parameterizedName, entity.Version + 1, info.DbFieldType)); } else if (info.PropertyName == "LastUser") { args.AppendFormat(GlobalSettings.Culture, " {0}={1},", info.DbReservedName, parameterizedName); parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(lastUser, info), info.DbFieldType)); } else { args.AppendFormat(GlobalSettings.Culture, " {0}={1},", info.DbReservedName, parameterizedName); parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(info.GetValue(entity), info), info.DbFieldType)); } } } if (args.Length > 0) { args.Remove(args.Length - 1, 1); } innerBuilder.Append($"update {definition.DbTableReservedName} set {args} WHERE `Id`={entity.Id} and `Version`={entity.Version} and `Deleted`=0;{TempTable_Insert(tempTableName, FoundChanges_Statement(_databaseEngine.EngineType), _databaseEngine.EngineType)}"); number++; } string sql = $"{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}{TempTable_Create(tempTableName, _databaseEngine.EngineType)}{innerBuilder}{TempTable_Select_All(tempTableName, _databaseEngine.EngineType)}{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}"; return(AssembleCommand <T, T>(false, sql, null, null, parameters)); }
public IDbCommand CreateBatchAddOrUpdateCommand <T>(IEnumerable <T> entities, string lastUser) where T : Entity, new() { ThrowIf.Empty(entities, nameof(entities)); DatabaseEntityDef modelDef = _entityDefFactory.GetDef <T>(); DatabaseEntityPropertyDef versionPropertyDef = modelDef.GetProperty("Version") !; StringBuilder innerBuilder = new StringBuilder(); string tempTableName = "HBARU" + DateTimeOffset.UtcNow.Ticks.ToString(GlobalSettings.Culture); IList <IDataParameter> parameters = new List <IDataParameter>(); int number = 0; foreach (T entity in entities) { StringBuilder args = new StringBuilder(); StringBuilder values = new StringBuilder(); StringBuilder exceptGuidAndFixedVersionUpdatePairs = new StringBuilder(); foreach (DatabaseEntityPropertyDef info in modelDef.Properties) { string parameterizedName = info.DbParameterizedName + number.ToString(GlobalSettings.Culture); if (info.IsTableProperty) { if (info.IsAutoIncrementPrimaryKey) { continue; } if (info.PropertyName == "LastTime") { continue; } args.AppendFormat(GlobalSettings.Culture, " {0},", info.DbReservedName); if (info.PropertyName == "Version") { values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName); parameters.Add(_databaseEngine.CreateParameter(parameterizedName, entity.Version + 1, info.DbFieldType)); } else if (info.PropertyName == "Deleted") { values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName); parameters.Add(_databaseEngine.CreateParameter(parameterizedName, 0, info.DbFieldType)); } else if (info.PropertyName == "LastUser") { values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName); parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(lastUser, info), info.DbFieldType)); } else { values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName); parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(info.GetValue(entity), info), info.DbFieldType)); } //update pairs if (info.PropertyName == "Version" || info.PropertyName == "Guid" || info.PropertyName == "Deleted") { continue; } exceptGuidAndFixedVersionUpdatePairs.Append($" {info.DbReservedName}={parameterizedName},"); } } exceptGuidAndFixedVersionUpdatePairs.Append($" {versionPropertyDef.DbReservedName}={versionPropertyDef.DbReservedName}+1,"); if (args.Length > 0) { args.Remove(args.Length - 1, 1); } if (values.Length > 0) { values.Remove(values.Length - 1, 1); } if (exceptGuidAndFixedVersionUpdatePairs.Length > 0) { exceptGuidAndFixedVersionUpdatePairs.Remove(exceptGuidAndFixedVersionUpdatePairs.Length - 1, 1); } innerBuilder.Append($"insert into {modelDef.DbTableReservedName}({args}) values ({values}) {OnDuplicateKeyUpdateStatement(_databaseEngine.EngineType)} {exceptGuidAndFixedVersionUpdatePairs};{TempTable_Insert(tempTableName, FoundChanges_Statement(_databaseEngine.EngineType), _databaseEngine.EngineType)}"); number++; } string sql = $"{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}{TempTable_Create(tempTableName, _databaseEngine.EngineType)}{innerBuilder}{TempTable_Select_All(tempTableName, _databaseEngine.EngineType)}{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}"; return(AssembleCommand <T, T>(false, sql, null, null, parameters)); }
private static string GetCreateAddOrUpdateTemplateCacheKey(DatabaseEntityDef modelDef) { return(modelDef.DatabaseName + ":" + modelDef.TableName + ":ADDORUPDATE"); }
public TransactionContext BeginTransaction <T>(IsolationLevel isolationLevel) where T : DatabaseEntity { DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>(); return(BeginTransaction(entityDef.DatabaseName, isolationLevel)); }
public IDbCommand CreateBatchAddStatement <T>(IEnumerable <T> entities, string lastUser) where T : DatabaseEntity, new() { if (entities == null || entities.Count() == 0) { throw new ArgumentNullException(nameof(entities)); } StringBuilder innerBuilder = new StringBuilder(); DatabaseEntityDef definition = _entityDefFactory.GetDef <T>(); string tempTableName = "HBA" + DateTimeOffset.UtcNow.Ticks.ToString(); IList <IDataParameter> parameters = new List <IDataParameter>(); int number = 0; foreach (T entity in entities) { StringBuilder args = new StringBuilder(); StringBuilder values = new StringBuilder(); foreach (DatabaseEntityPropertyDef info in definition.Properties) { string parameterizedName = info.DbParameterizedName + number.ToString(); if (info.IsTableProperty) { if (info.IsAutoIncrementPrimaryKey) { continue; } if (info.PropertyName == "LastTime") { continue; } args.AppendFormat(GlobalSettings.Culture, " {0},", info.DbReservedName); if (info.PropertyName == "Version") { values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName); parameters.Add(_databaseEngine.CreateParameter(parameterizedName, entity.Version + 1, info.DbFieldType)); } else if (info.PropertyName == "Deleted") { values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName); parameters.Add(_databaseEngine.CreateParameter(parameterizedName, 0, info.DbFieldType)); } else if (info.PropertyName == "LastUser") { values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName); parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(lastUser, info), info.DbFieldType)); } else { values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName); parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(info.GetValue(entity), info), info.DbFieldType)); } } } if (args.Length > 0) { args.Remove(args.Length - 1, 1); } if (values.Length > 0) { values.Remove(values.Length - 1, 1); } innerBuilder.Append($"insert into {definition.DbTableReservedName}({args.ToString()}) values ({values.ToString()});{TempTable_Insert(tempTableName, GetLastInsertIdStatement(_databaseEngine.EngineType), _databaseEngine.EngineType)}"); number++; } string sql = $"{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}{TempTable_Create(tempTableName, _databaseEngine.EngineType)}{innerBuilder.ToString()}{TempTable_Select_All(tempTableName, _databaseEngine.EngineType)}{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}"; return(AssembleCommand <T, T>(false, sql, null, null, parameters)); }
/// <summary> /// 批量添加,返回新产生的ID列表 /// </summary> /// <param name="items"></param> /// <returns></returns> public DatabaseResult BatchAdd <T>(IEnumerable <T> items, string lastUser, TransactionContext transContext) where T : DatabaseEntity, new() { if (transContext == null) { return(DatabaseResult.Fail(new ArgumentNullException(nameof(transContext)))); } if (!CheckEntities <T>(items)) { return(DatabaseResult.Fail("entities not valid.")); } DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>(); if (!entityDef.DatabaseWriteable) { return(DatabaseResult.NotWriteable()); } IDataReader reader = null; try { DatabaseResult result = DatabaseResult.Succeeded(); IDbCommand dbCommand = _sqlBuilder.CreateBatchAddStatement(items, lastUser); reader = _databaseEngine.ExecuteCommandReader( transContext.Transaction, entityDef.DatabaseName, dbCommand, true); while (reader.Read()) { int newId = reader.GetInt32(0); if (newId <= 0) { throw new DatabaseException("BatchAdd wrong new id return."); } result.AddId(newId); } if (result.Ids.Count != items.Count()) { throw new DatabaseException("BatchAdd wrong new id number return."); } return(result); } catch (Exception ex) { //_logger.LogCritical(ex.Message); return(DatabaseResult.Fail(ex)); } finally { if (reader != null) { reader.Dispose(); } } }