private DynamicParameters GetDeleteParameters(dynamic id)
{
PrimaryKeyValidate(id);
var dynParams = new DynamicParameters();
dynParams.Add(_sqlAdapter.AppendParameter("Id"), id);
return(dynParams);
}
public async Task <bool> DeleteAsync(dynamic id, IDbTransaction transaction = null)
{
PrimaryKeyValidate(id);
var dynParms = new DynamicParameters();
dynParms.Add(_sqlAdapter.AppendParameter("Id"), id);
return(await ExecuteAsync(_sqlStatement.DeleteSingle, dynParms, transaction) > 0);
}
private void AppendValue(object value)
{
if (value == null && !_isResolveUpdate)
{
var len = _sqlBuilder.Length;
if (_sqlBuilder[len - 1] == ' ' && _sqlBuilder[len - 2] == '>' && _sqlBuilder[len - 3] == '<')
{
_sqlBuilder.Remove(len - 3, 3);
_sqlBuilder.Append("IS NOT NULL");
return;
}
if (_sqlBuilder[len - 1] == ' ' && _sqlBuilder[len - 2] == '=')
{
_sqlBuilder.Remove(len - 2, 2);
_sqlBuilder.Append("IS NULL");
}
return;
}
var pName = _parameters.Add(value);
_sqlBuilder.Append(_sqlAdapter.AppendParameter(pName));
}
/// <summary>
/// 设置插入语句
/// </summary>
private void SetInsertSql()
{
var sb = new StringBuilder();
sb.Append("INSERT INTO ");
_sqlAdapter.AppendQuote(sb, _descriptor.TableName);
sb.Append("(");
var valuesSql = new StringBuilder(") VALUES(");
foreach (var col in _descriptor.Columns)
{
//排除自增主键
if (col.IsPrimaryKey && (_descriptor.PrimaryKeyType == PrimaryKeyType.Int || _descriptor.PrimaryKeyType == PrimaryKeyType.Long))
{
continue;
}
_sqlAdapter.AppendQuote(sb, col.Name);
sb.Append(",");
_sqlAdapter.AppendParameter(valuesSql, col.PropertyInfo.Name);
valuesSql.Append(",");
}
//删除最后一个","
sb.Remove(sb.Length - 1, 1);
//删除最后一个","
valuesSql.Remove(valuesSql.Length - 1, 1);
valuesSql.Append(");");
sb.Append(valuesSql);
Insert = sb.ToString();
}
public string SoftDeleteSqlBuild(out IQueryParameters parameters)
{
var tableName = _queryBody.JoinDescriptors.First().TableName;
Check.NotNull(tableName, nameof(tableName), "未指定更新表");
parameters = new QueryParameters();
var sqlBuilder = new StringBuilder($"UPDATE {_sqlAdapter.AppendQuote(tableName)} SET ");
sqlBuilder.AppendFormat("{0}=1,", _sqlAdapter.AppendQuote("Deleted"));
sqlBuilder.AppendFormat("{0}={1},", _sqlAdapter.AppendQuote("DeletedTime"), _sqlAdapter.AppendParameter("P1"));
parameters.Add(DateTime.Now);
sqlBuilder.AppendFormat("{0}={1} ", _sqlAdapter.AppendQuote("DeletedBy"), _sqlAdapter.AppendParameter("P2"));
var deleteBy = Guid.Empty;
if (_dbContext.LoginInfo != null)
{
deleteBy = _dbContext.LoginInfo.AccountId;
}
parameters.Add(deleteBy);
var whereSql = ResolveWhere(parameters);
Check.NotNull(whereSql, nameof(whereSql), "生成条件sql异常");
sqlBuilder.AppendFormat(" WHERE {0}", whereSql);
var sql = sqlBuilder.ToString();
_logger?.LogDebug("SoftDelete:" + sql);
return(sql);
}
public string SoftDeleteSqlBuild(out IQueryParameters parameters)
{
var entityDescriptor = _queryBody.JoinDescriptors.First().EntityDescriptor;
if (!entityDescriptor.SoftDelete)
{
throw new Exception("非软删除实体无法调用该方法");
}
var tableName = _queryBody.JoinDescriptors.First().TableName;
Check.NotNull(tableName, nameof(tableName), "未指定更新表");
var deletedColumnName = entityDescriptor.Columns.First(m => m.PropertyInfo.Name.Equals("Deleted")).Name;
var deletedTimeColumnName = entityDescriptor.Columns.First(m => m.PropertyInfo.Name.Equals("DeletedTime")).Name;
var deletedByTimeColumnName = entityDescriptor.Columns.First(m => m.PropertyInfo.Name.Equals("DeletedBy")).Name;
parameters = new QueryParameters();
var sqlBuilder = new StringBuilder($"UPDATE {GetTableName(tableName)} SET ");
sqlBuilder.AppendFormat("{0}={1},", _sqlAdapter.AppendQuote(deletedColumnName), _sqlAdapter.SqlDialect == SqlDialect.PostgreSQL ? "TRUE" : "1");
sqlBuilder.AppendFormat("{0}={1},", _sqlAdapter.AppendQuote(deletedTimeColumnName), _sqlAdapter.AppendParameter("P1"));
parameters.Add(DateTime.Now);
sqlBuilder.AppendFormat("{0}={1} ", _sqlAdapter.AppendQuote(deletedByTimeColumnName), _sqlAdapter.AppendParameter("P2"));
var deleteBy = Guid.Empty;
if (_dbContext.LoginInfo != null)
{
deleteBy = _dbContext.LoginInfo.AccountId;
}
parameters.Add(deleteBy);
var whereSql = ResolveWhere(parameters);
Check.NotNull(whereSql, nameof(whereSql), "生成条件sql异常");
sqlBuilder.AppendFormat(" WHERE {0}", whereSql);
var sql = sqlBuilder.ToString();
_logger?.LogDebug("SoftDelete:" + sql);
return(sql);
}
public string SoftDeleteSqlBuild(string tableName, out IQueryParameters parameters)
{
Check.NotNull(tableName, nameof(tableName), "未指定删除表");
parameters = new QueryParameters();
var sqlBuilder = new StringBuilder($"UPDATE {_sqlAdapter.AppendQuote(tableName)} SET ");
sqlBuilder.AppendFormat("{0}=1,", _sqlAdapter.AppendQuote("Deleted"));
sqlBuilder.AppendFormat("{0}={1},", _sqlAdapter.AppendQuote("DeletedTime"), _sqlAdapter.AppendParameter("P1"));
parameters.Add(DateTime.Now);
sqlBuilder.AppendFormat("{0}={1} ", _sqlAdapter.AppendQuote("DeletedBy"), _sqlAdapter.AppendParameter("P2"));
parameters.Add(_dbContext.AccountId);
var whereSql = ResolveWhere(parameters);
Check.NotNull(whereSql, nameof(whereSql), "生成条件sql异常");
sqlBuilder.AppendFormat(" WHERE {0}", whereSql);
var sql = sqlBuilder.ToString();
_logger?.LogDebug("SoftDelete:" + sql);
return(sql);
}
/// <summary>
/// Inserts an entity into table "Ts" asynchronously using .NET 4.5 Task and returns identity id.
/// </summary>
/// <typeparam name="T">The type being inserted.</typeparam>
/// <param name="connection">Open SqlConnection</param>
/// <param name="entityToInsert">Entity to insert</param>
/// <param name="transaction">The transaction to run under, null (the default) if none</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout</param>
/// <param name="sqlAdapter">The specific ISqlAdapter to use, auto-detected based on connection if null</param>
/// <returns>Identity of inserted entity</returns>
public static Task <int> InsertAsync <T>(this IDbConnection connection, T entityToInsert, IDbTransaction transaction = null,
int?commandTimeout = null, ISqlAdapter sqlAdapter = null) where T : class
{
var type = typeof(T);
sqlAdapter = sqlAdapter ?? GetFormatter(connection);
var isList = false;
if (type.IsArray)
{
isList = true;
type = type.GetElementType();
}
else if (type.IsGenericType())
{
var typeInfo = type.GetTypeInfo();
bool implementsGenericIEnumerableOrIsGenericIEnumerable =
typeInfo.ImplementedInterfaces.Any(ti => ti.IsGenericType() && ti.GetGenericTypeDefinition() == typeof(IEnumerable <>)) ||
typeInfo.GetGenericTypeDefinition() == typeof(IEnumerable <>);
if (implementsGenericIEnumerableOrIsGenericIEnumerable)
{
isList = true;
type = type.GetGenericArguments()[0];
}
}
var name = GetTableName(type);
var sbColumnList = new StringBuilder(null);
var allProperties = TypePropertiesCache(type);
var keyProperties = KeyPropertiesCache(type);
var computedProperties = ComputedPropertiesCache(type);
var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties)).ToList();
for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count; i++)
{
var property = allPropertiesExceptKeyAndComputed[i];
sqlAdapter.AppendColumnName(sbColumnList, property.Name);
if (i < allPropertiesExceptKeyAndComputed.Count - 1)
{
sbColumnList.Append(", ");
}
}
var sbParameterList = new StringBuilder(null);
for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count; i++)
{
var property = allPropertiesExceptKeyAndComputed[i];
sqlAdapter.AppendParameter(sbParameterList, property.Name);
if (i < allPropertiesExceptKeyAndComputed.Count - 1)
{
sbParameterList.Append(", ");
}
}
if (!isList) //single entity
{
return(sqlAdapter.InsertAsync(connection, transaction, commandTimeout, name, sbColumnList.ToString(),
sbParameterList.ToString(), keyProperties, entityToInsert));
}
//insert list of entities
var cmd = $"INSERT INTO {name} ({sbColumnList}) values ({sbParameterList})";
return(connection.ExecuteAsync(cmd, entityToInsert, transaction, commandTimeout));
}
private void AppendValue(StringBuilder sqlBuilder, QueryParameters parameters, object value)
{
var pName = parameters.Add(value);
sqlBuilder.Append(_sqlAdapter.AppendParameter(pName));
}