public virtual int ExecuteSqlCommand(RawSqlString sql, bool doNotEnsureTransaction = false, int?timeout = null, params object[] parameters)
{
var previousTimeout = this.Database.GetCommandTimeout();
Database.SetCommandTimeout(timeout);
var result = 0;
if (!doNotEnsureTransaction)
{
using (var transaction = Database.BeginTransaction())
{
result = Database.ExecuteSqlCommand(sql, parameters);
transaction.Commit();
}
}
else
{
result = this.Database.ExecuteSqlCommand(sql, parameters);
}
Database.SetCommandTimeout(previousTimeout);
return(result);
}
public IActionResult Index()
{
var query = new RawSqlString("SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS Message");
var message = _context.Updateability.FromSql(query);
ViewData["Message"] = message.First().Message;
var productSalesByStore = (from h in _context.SalesOrderHeaders
join p in _context.SalesPeople on h.SalesPersonID equals p.BusinessEntityID
join s in _context.Stores on p.BusinessEntityID equals s.SalesPersonID
join o in _context.SalesOrderDetails on h.SalesOrderID equals o.SalesOrderID
join pd in _context.Products on o.ProductID equals pd.ProductID
group h by new { Store = s.Name, Product = pd.Name } into g
select new ProductSalesByStore
{
Store = g.Key.Store,
Product = g.Key.Product,
SalesTotal = g.Sum(h => h.TotalDue)
});
var products = _context.Products;
return(View(productSalesByStore.Take(200)));
}
/// <summary>
/// 使用原始 SQL 字符串和参数来执行 SQL。
/// </summary>
/// <param name="sql"></param>
/// <param name="timeout"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public Task <int> ExecuteSqlCommandAsync(RawSqlString sql, TimeSpan?timeout = null, params object[] parameters)
{
return(this._context.ExecuteCommandAsync(sql, parameters, timeout));
}
private RawSqlString NormalizeDelimeters(RawSqlString sql) => Fixture.TestStore.NormalizeDelimeters(sql);
/// <summary>
/// Deletes the <typeparamref name="TEntity"/> from the database by <paramref name="condition"/>.
/// </summary>
/// <typeparam name="TEntity">Entity type.</typeparam>
/// <param name="database"><see cref="IDatabase"/> instance.</param>
/// <param name="condition">The delete condition.</param>
/// <param name="parameters">Condition parameters.</param>
/// <param name="cancellationToken">The token to monitor for cancellation requests.</param>
public static async Task DeleteAsync <TEntity>(
this IDatabase database,
RawSqlString condition,
CancellationToken cancellationToken = default,
params object[] parameters) where TEntity : class
=> await CommitChangesAsync(database, (IDbSet <TEntity> dbSet) => dbSet.Delete(condition, parameters), cancellationToken);
public virtual RawSqlString NormalizeDelimeters(RawSqlString sql) => NormalizeDelimeters(sql.Format);
public async Task <ApiResult <long> > Put([FromBody] Client value)
{
if (!ModelState.IsValid)
{
return(new ApiResult <long>(l,
BasicControllerCodes.UnprocessableEntity,
ModelErrors()));
}
if (!await exists(value.Id))
{
return(new ApiResult <long>(l, BasicControllerCodes.NotFound));
}
using (var tran = idsDB.Database.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
#region Update Entity
// 需要先更新value，否则更新如claims等属性会有并发问题
idsDB.Update(value);
idsDB.SaveChanges();
#endregion
#region Find Entity.Source
var source = await idsDB.Clients.Where(x => x.Id == value.Id)
.Include(x => x.Claims)
.Include(x => x.AllowedGrantTypes)
.Include(x => x.AllowedScopes)
.Include(x => x.ClientSecrets)
.Include(x => x.AllowedCorsOrigins)
.Include(x => x.RedirectUris)
.Include(x => x.PostLogoutRedirectUris)
.Include(x => x.IdentityProviderRestrictions)
.Include(x => x.Properties)
.AsNoTracking()
.FirstOrDefaultAsync();
#endregion
#region Update Entity.Claims
if (value.Claims != null && value.Claims.Count > 0)
{
#region delete
var EntityIDs = value.Claims.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.Claims.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ClientClaims WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
idsDB.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.Claims.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("UPDATE ClientClaims SET [Type]=@Type,[Value]=@Value WHERE Id = " + x.Id),
new SqlParameter("@Type", x.Type),
new SqlParameter("@Value", x.Value));
});
}
#endregion
#region insert
var NewEntities = value.Claims.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("INSERT INTO ClientClaims VALUES (@ClientId,@Type,@Value)"),
new SqlParameter("@ClientId", source.Id),
new SqlParameter("@Type", x.Type),
new SqlParameter("@Value", x.Value));
});
}
#endregion
}
#endregion
#region Update Entity.AllowedGrantTypes
if (value.AllowedGrantTypes != null && value.AllowedGrantTypes.Count > 0)
{
#region delete
var EntityIDs = value.AllowedGrantTypes.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.AllowedGrantTypes.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ClientGrantTypes WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
idsDB.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.AllowedGrantTypes.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("UPDATE ClientGrantTypes SET [GrantType]=@GrantType WHERE Id = " + x.Id),
new SqlParameter("@GrantType", x.GrantType));
});
}
#endregion
#region insert
var NewEntities = value.AllowedGrantTypes.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("INSERT INTO ClientGrantTypes VALUES (@ClientId,@GrantType)"),
new SqlParameter("@ClientId", source.Id),
new SqlParameter("@GrantType", x.GrantType));
});
}
#endregion
}
#endregion
#region Update Entity.AllowedScopes
if (value.AllowedScopes != null && value.AllowedScopes.Count > 0)
{
#region delete
var EntityIDs = value.AllowedScopes.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.AllowedScopes.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ClientScopes WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
idsDB.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.AllowedScopes.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("UPDATE ClientScopes SET [Scope]=@Scope WHERE Id = " + x.Id),
new SqlParameter("@Scope", x.Scope));
});
}
#endregion
#region insert
var NewEntities = value.AllowedScopes.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("INSERT INTO ClientScopes VALUES (@ClientId,@Scope)"),
new SqlParameter("@ClientId", source.Id),
new SqlParameter("@Scope", x.Scope));
});
}
#endregion
}
#endregion
#region Update Entity.ClientSecrets
if (value.ClientSecrets != null && value.ClientSecrets.Count > 0)
{
#region delete
var EntityIDs = value.ClientSecrets.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.ClientSecrets.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ClientSecrets WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
idsDB.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.ClientSecrets.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
var sql = new RawSqlString("UPDATE ClientSecrets SET [Description]=@Description,[Expiration]=@Expiration,[Type]=@Type,[Value]=@Value WHERE Id = " + x.Id);
var _params = new SqlParameter[]
{
new SqlParameter("@Description", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Expiration", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Type", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Value", DBNull.Value)
{
IsNullable = true
},
};
if (!string.IsNullOrWhiteSpace(x.Description))
{
_params[0].Value = x.Description;
}
if (x.Expiration.HasValue)
{
_params[1].Value = x.Expiration;
}
if (!string.IsNullOrWhiteSpace(x.Type))
{
_params[2].Value = x.Type;
}
if (!string.IsNullOrWhiteSpace(x.Value))
{
_params[3].Value = x.Value;
}
idsDB.Database.ExecuteSqlCommand(sql, _params);
});
}
#endregion
#region insert
var NewEntities = value.ClientSecrets.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
var sql = new RawSqlString("INSERT INTO ClientSecrets VALUES (@ClientId,@Description,@Expiration,@Type,@Value)");
var _params = new SqlParameter[]
{
new SqlParameter("@ClientId", source.Id),
new SqlParameter("@Description", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Expiration", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Type", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Value", DBNull.Value)
{
IsNullable = true
},
};
if (!string.IsNullOrWhiteSpace(x.Description))
{
_params[1].Value = x.Description;
}
if (x.Expiration.HasValue)
{
_params[2].Value = x.Expiration;
}
if (!string.IsNullOrWhiteSpace(x.Type))
{
_params[3].Value = x.Type;
}
if (!string.IsNullOrWhiteSpace(x.Value))
{
_params[4].Value = x.Value;
}
idsDB.Database.ExecuteSqlCommand(sql, _params);
});
}
#endregion
}
#endregion
#region Update Entity.AllowedCorsOrigins
if (value.AllowedCorsOrigins != null && value.AllowedCorsOrigins.Count > 0)
{
#region delete
var EntityIDs = value.AllowedCorsOrigins.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.AllowedCorsOrigins.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ClientCorsOrigins WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
idsDB.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.AllowedCorsOrigins.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("UPDATE ClientCorsOrigins SET [Origin]=@Origin WHERE Id = " + x.Id),
new SqlParameter("@Origin", x.Origin));
});
}
#endregion
#region insert
var NewEntities = value.AllowedCorsOrigins.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("INSERT INTO ClientCorsOrigins VALUES (@ClientId,@Origin)"),
new SqlParameter("@ClientId", source.Id),
new SqlParameter("@Origin", x.Origin));
});
}
#endregion
}
#endregion
#region Update Entity.RedirectUris
if (value.RedirectUris != null && value.RedirectUris.Count > 0)
{
#region delete
var EntityIDs = value.RedirectUris.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.RedirectUris.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ClientRedirectUris WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
idsDB.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.RedirectUris.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("UPDATE ClientRedirectUris SET [RedirectUri]=@RedirectUri WHERE Id = " + x.Id),
new SqlParameter("@RedirectUri", x.RedirectUri));
});
}
#endregion
#region insert
var NewEntities = value.RedirectUris.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("INSERT INTO ClientRedirectUris VALUES (@ClientId,@RedirectUri)"),
new SqlParameter("@ClientId", source.Id),
new SqlParameter("@RedirectUri", x.RedirectUri));
});
}
#endregion
}
#endregion
#region Update Entity.PostLogoutRedirectUris
if (value.PostLogoutRedirectUris != null && value.PostLogoutRedirectUris.Count > 0)
{
#region delete
var EntityIDs = value.PostLogoutRedirectUris.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.PostLogoutRedirectUris.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ClientPostLogoutRedirectUris WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
idsDB.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.PostLogoutRedirectUris.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("UPDATE ClientPostLogoutRedirectUris SET [PostLogoutRedirectUri]=@PostLogoutRedirectUri WHERE Id = " + x.Id),
new SqlParameter("@PostLogoutRedirectUri", x.PostLogoutRedirectUri));
});
}
#endregion
#region insert
var NewEntities = value.PostLogoutRedirectUris.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("INSERT INTO ClientPostLogoutRedirectUris VALUES (@ClientId,@PostLogoutRedirectUri)"),
new SqlParameter("@ClientId", source.Id),
new SqlParameter("@PostLogoutRedirectUri", x.PostLogoutRedirectUri));
});
}
#endregion
}
#endregion
#region Update Entity.IdentityProviderRestrictions
if (value.IdentityProviderRestrictions != null && value.IdentityProviderRestrictions.Count > 0)
{
#region delete
var EntityIDs = value.IdentityProviderRestrictions.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.IdentityProviderRestrictions.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ClientIdPRestrictions WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
idsDB.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.IdentityProviderRestrictions.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("UPDATE ClientIdPRestrictions SET [Provider]=@Provider WHERE Id = " + x.Id),
new SqlParameter("@Provider", x.Provider));
});
}
#endregion
#region insert
var NewEntities = value.IdentityProviderRestrictions.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("INSERT INTO ClientIdPRestrictions VALUES (@ClientId,@Provider)"),
new SqlParameter("@ClientId", source.Id),
new SqlParameter("@Provider", x.Provider));
});
}
#endregion
}
#endregion
#region Update Entity.Properties
if (value.Properties != null && value.Properties.Count > 0)
{
#region delete
var EntityIDs = value.Properties.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.Properties.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ClientProperties WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
idsDB.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.Properties.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("UPDATE ClientProperties SET [Key]=@Key,[Value]=@Value WHERE Id = " + x.Id),
new SqlParameter("@Key", x.Key),
new SqlParameter("@Value", x.Value));
});
}
#endregion
#region insert
var NewEntities = value.Properties.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
idsDB.Database.ExecuteSqlCommand(
new RawSqlString("INSERT INTO ClientProperties VALUES (@ClientId,@Key,@Value)"),
new SqlParameter("@ClientId", source.Id),
new SqlParameter("@Key", x.Key),
new SqlParameter("@Value", x.Value));
});
}
#endregion
}
#endregion
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
return(new ApiResult <long>(l,
BasicControllerCodes.ExpectationFailed,
ex.Message));
}
}
return(new ApiResult <long>(value.Id));
}
/// <summary>
/// Excecutes (custom) raw stored-procedures, use return parameters to retrieve information back from the STP.
/// </summary>
/// <param name="stp">The STP.</param>
/// <param name="parameters">Array of SqlParameter.</param>
/// <returns>int result.</returns>
public async Task <int> ExecuteSqlCommandAsync(RawSqlString stp, params object[] parameters)
{
return(await Database.ExecuteSqlCommandAsync(stp, parameters));
}
protected int Implement(IApplicationDbBase dataContext, RawSqlString QueryString, params object[] parameters) => dataContext.Database.ExecuteSqlCommand(QueryString, parameters);
public Task ExecuteQuery(RawSqlString sql, IEnumerable <Object> parameters, CancellationToken cancellationToken = default(CancellationToken))
{
return(dbContext.Database.ExecuteSqlCommandAsync(sql, parameters, cancellationToken));
}
public Task ExecuteQuery(RawSqlString sql, CancellationToken cancellationToken = default(CancellationToken))
{
return(dbContext.Database.ExecuteSqlCommandAsync(sql, cancellationToken));
}
public IQueryable <T> FromSql <T>([NotParameterized] RawSqlString sql,
params object[] parameters)
where T : class
{
return(GetDbContext(typeof(T)).Set <T>().FromSql(sql, parameters));
}
public async Task <ApiResult <long> > Put([FromBody] ApiResource value)
{
if (!ModelState.IsValid)
{
return(new ApiResult <long>(l,
BasicControllerCodes.UnprocessableEntity,
ModelErrors()));
}
if (!await exists(value.Id))
{
return(new ApiResult <long>(l, BasicControllerCodes.NotFound));
}
using (var tran = db.Database.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
#region Update Entity
// 需要先更新value，否则更新如claims等属性会有并发问题
db.Update(value);
db.SaveChanges();
#endregion
#region Find Entity.Source
var source = await db.ApiResources.Where(x => x.Id == value.Id)
.Include(x => x.Scopes).ThenInclude(x => x.UserClaims)
.Include(x => x.Secrets)
.Include(x => x.UserClaims)
.AsNoTracking()
.FirstOrDefaultAsync();
#endregion
#region Update Entity.Claims
if (value.UserClaims != null && value.UserClaims.Count > 0)
{
#region delete
var EntityIDs = value.UserClaims.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.UserClaims.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ApiClaims WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
db.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.UserClaims.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
db.Database.ExecuteSqlCommand(
new RawSqlString("UPDATE ApiClaims SET [Type]=@Type WHERE Id = " + x.Id),
new SqlParameter("@Type", x.Type));
});
}
#endregion
#region insert
var NewEntities = value.UserClaims.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
db.Database.ExecuteSqlCommand(
new RawSqlString("INSERT INTO ApiClaims VALUES (@ApiResourceId,@Type)"),
new SqlParameter("@ApiResourceId", source.Id),
new SqlParameter("@Type", x.Type));
});
}
#endregion
}
#endregion
#region Update Entity.Secrets
if (value.Secrets != null && value.Secrets.Count > 0)
{
#region delete
var EntityIDs = value.Secrets.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.Secrets.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ApiSecrets WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
db.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.Secrets.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
var _params = new SqlParameter[] {
new SqlParameter("@Description", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Expiration", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Type", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Value", DBNull.Value)
{
IsNullable = true
},
};
if (!string.IsNullOrWhiteSpace(x.Description))
{
_params[0].Value = x.Description;
}
if (x.Expiration.HasValue)
{
_params[1].Value = x.Expiration;
}
if (!string.IsNullOrWhiteSpace(x.Type))
{
_params[2].Value = x.Type;
}
if (!string.IsNullOrWhiteSpace(x.Value))
{
_params[3].Value = x.Value;
}
var sql = new RawSqlString("UPDATE ApiSecrets SET [Description]=@Description,[Expiration]=@Expiration,[Type]=@Type,[Value]=@Value WHERE Id = " + x.Id);
db.Database.ExecuteSqlCommand(sql, _params);
});
}
#endregion
#region insert
var NewEntities = value.Secrets.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
var _params = new SqlParameter[] {
new SqlParameter("@ApiResourceId", source.Id),
new SqlParameter("@Description", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Expiration", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Type", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Value", DBNull.Value)
{
IsNullable = true
},
};
if (!string.IsNullOrWhiteSpace(x.Description))
{
_params[0].Value = x.Description;
}
if (x.Expiration.HasValue)
{
_params[1].Value = x.Expiration;
}
if (!string.IsNullOrWhiteSpace(x.Type))
{
_params[2].Value = x.Type;
}
if (!string.IsNullOrWhiteSpace(x.Value))
{
_params[3].Value = x.Value;
}
var sql = new RawSqlString("INSERT INTO ApiSecrets VALUES (@ApiResourceId,@Description,@Expiration,@Type,@Value)");
db.Database.ExecuteSqlCommand(sql, _params);
});
}
#endregion
}
#endregion
#region Update Entity.Scopes
if (value.Scopes != null && value.Scopes.Count > 0)
{
#region delete
var EntityIDs = value.Scopes.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.Scopes.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE ApiScopeClaims WHERE ApiScopeId IN ({0})",
string.Join(",", DeleteEntities));
db.Database.ExecuteSqlCommand(new RawSqlString(sql));
sql = string.Format("DELETE ApiScopes WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
db.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.Scopes.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
var _params = new SqlParameter[] {
new SqlParameter("@Description", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@DisplayName", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Emphasize", x.Emphasize),
new SqlParameter("@Name", x.Name),
new SqlParameter("@Required", x.Required),
new SqlParameter("@ShowInDiscoveryDocument", x.ShowInDiscoveryDocument)
};
if (!string.IsNullOrWhiteSpace(x.Description))
{
_params[0].Value = x.Description;
}
if (!string.IsNullOrWhiteSpace(x.DisplayName))
{
_params[1].Value = x.DisplayName;
}
var sql = new RawSqlString("UPDATE ApiScopes SET [Description]=@Description,[DisplayName]=@DisplayName,[Emphasize]=@Emphasize,[Name]=@Name,[Required]=@Required,[ShowInDiscoveryDocument]=@ShowInDiscoveryDocument WHERE Id = " + x.Id);
db.Database.ExecuteSqlCommand(sql, _params);
db.Database.ExecuteSqlCommand(
new RawSqlString("DELETE ApiScopeClaims WHERE ApiScopeId =" + x.Id));
x.UserClaims.ForEach(claim =>
{
db.Database.ExecuteSqlCommand(
new RawSqlString("INSERT INTO ApiScopeClaims VALUES (@ApiScopeId,@Type)"),
new SqlParameter("@ApiScopeId", x.Id),
new SqlParameter("@Type", claim.Type));
});
});
}
#endregion
#region insert
var NewEntities = value.Scopes.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
var _params = new SqlParameter[]
{
new SqlParameter("@Description", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@DisplayName", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@Emphasize", x.Emphasize),
new SqlParameter("@Name", x.Name),
new SqlParameter("@Required", x.Required),
new SqlParameter("@ShowInDiscoveryDocument", x.ShowInDiscoveryDocument),
new SqlParameter()
{
Direction = ParameterDirection.ReturnValue
},
};
if (!string.IsNullOrWhiteSpace(x.Description))
{
_params[0].Value = x.Description;
}
if (!string.IsNullOrWhiteSpace(x.DisplayName))
{
_params[1].Value = x.DisplayName;
}
var sql = new RawSqlString("INSERT INTO ApiScopes VALUES (@ApiResourceId,@Description,@DisplayName,@Emphasize,@Name,@Required,@ShowInDiscoveryDocument)\r\n" +
"SELECT @@identity");
db.Database.ExecuteSqlCommand(sql, _params);
if (_params[_params.Length - 1].Value != null)
{
var _ApiScopeId = long.Parse(_params[_params.Length - 1].Value.ToString());
x.UserClaims.ForEach(claim =>
{
db.Database.ExecuteSqlCommand(
new RawSqlString("INSERT INTO ApiScopeClaims VALUES (@ApiScopeId,@Type)"),
new SqlParameter("@ApiScopeId", _ApiScopeId),
new SqlParameter("@Type", claim.Type));
});
}
});
}
#endregion
}
#endregion
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
return(new ApiResult <long>(l,
BasicControllerCodes.ExpectationFailed,
ex.Message));
}
}
return(new ApiResult <long>(value.Id));
}
public async Task ExecuteSqlCommandAsync(RawSqlString sql)
{
await this.Database.ExecuteSqlCommandAsync(sql);
}
public async Task ExecuteSqlCommandAsync(RawSqlString sql)
{
await _context.ExecuteSqlCommandAsync(sql);
}
/// <summary>
/// Creates a <see cref="SelectQuery"/> based on a raw SQL query.
/// </summary>
/// <param name="sql">The raw SQL query.</param>
/// <param name="parameters">The values to be assigned to parameters.</param>
/// <returns>prepared select query</returns>
/// <remarks>Semantics of this method is similar to EF Core DbSet.FromSql. Any parameter values you supply will automatically be converted to a DbParameter:
/// <code>dbAdapter.Select("SELECT * FROM [dbo].[SearchBlogs]({0})", userSuppliedSearchTerm).ToRecordSet()</code>.
/// <para>You can also construct a DbParameter and supply it to as a parameter value. This allows you to use named
/// parameters in the SQL query string -
/// <code>dbAdapter.Select("SELECT * FROM [dbo].[SearchBlogs]({@searchTerm})", new SqlParameter("@searchTerm", userSuppliedSearchTerm)).ToRecordSet()</code>
/// </para>
/// </remarks>
public SelectQuery Select(RawSqlString sql, params object[] parameters)
{
return(new SelectQueryBySql(this, sql.Format, parameters));
}
public void ExecuteSqlCommand(RawSqlString sql, params object[] parameters)
{
this.Database.ExecuteSqlCommand(sql, parameters);
}
public Task ExecuteQueryAsync(RawSqlString query, params object[] parameters)
{
return(_context.Database.ExecuteSqlCommandAsync(query, parameters));
}
protected IEnumerable <TModel> Implement <TModel>(IApplicationDbBase dataContext, RawSqlString QueryString, params object[] parameters) where TModel : new()
{
List <TModel> results = new List <TModel>();
var concurrencyDetector = dataContext.Database.GetService <IConcurrencyDetector>();
using (concurrencyDetector.EnterCriticalSection())
{
var rawSqlCommand = dataContext.Database
.GetService <IRawSqlCommandBuilder>()
.Build(QueryString.Format, parameters);
var reader = rawSqlCommand.RelationalCommand
.ExecuteReader(
dataContext.Database.GetService <IRelationalConnection>(),
rawSqlCommand.ParameterValues
).DbDataReader;
List <PropertyInfo> properties = new List <PropertyInfo>();
typeof(TModel).GetProperties().ForEach(a => { try { reader.GetOrdinal(a.Name); properties.Add(a); } catch { } });
while (reader.Read())
{
var item = new TModel();
properties.ForEach(prop => {
try {
var ordinal = reader.GetOrdinal(prop.Name);
if (reader.IsDBNull(ordinal))
{
return;
}
if (reader.GetValue(ordinal) == DBNull.Value)
{
return;
}
prop.SetValue(item, reader.GetValue(ordinal));
} catch (Exception e) {
}
});
results.Add(item);
}
}
return(results);
}
public int ExecuteSqlCommand(RawSqlString sql, bool doNotEnsureTransaction = false, int?timeout = null, params object[] parameters)
{
using var transaction = Database.BeginTransaction();
return(Database.ExecuteSqlCommand(sql, parameters));
}
public static ICollection <T> SqlQuery <T>(this DbContext db, CommandType type, RawSqlString SQL, params object[] parameters)
{
var conn = db.Database.GetDbConnection();
try
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
using (var cmd = conn.CreateCommand())
{
#region Set CMD
cmd.CommandType = type;
cmd.CommandText = SQL.Format;
#region Set Params
if (parameters != null && parameters.Length > 0)
{
if (parameters[0].GetType() == typeof(string))
{
int i = 0;
foreach (var param in parameters)
{
DbParameter p = cmd.CreateParameter();
p.ParameterName = "@p" + i++;
p.DbType = DbType.String;
p.Value = param;
cmd.Parameters.Add(p);
}
}
else
{
{
foreach (SqlParameter item in parameters as SqlParameter[])
{
DbParameter p = cmd.CreateParameter();
p.DbType = item.DbType;
p.ParameterName = item.ParameterName;
p.Value = item.Value;
cmd.Parameters.Add(p);
}
}
}
}
#endregion
#endregion
var rtnList = new List <T>();
object val = new object();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
rtnList.Add((T)reader.GetValue(0));
}
}
return(rtnList);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
public int ExecuteSqlCommand(RawSqlString sql, bool doNotEnsureTransaction = false, int?timeout = null, params object[] parameters)
{
throw new System.NotImplementedException();
}
public IQueryable <TEntity> FromSql(RawSqlString sql, params object[] paramters)
{
return(_dbSet.FromSql(sql, paramters));
}
public IQueryable <T> FromSql <T>(RawSqlString sql, params object[] parameters) where T : class
{
throw new NotImplementedException();
}
private RawSqlString NormalizeDelimeters(RawSqlString sql) => ((RelationalTestStore)Fixture.TestStore).NormalizeDelimeters(sql);
public int ExecuteSqlCommand(RawSqlString sql, params object[] parameters)
{
return(db.Database.ExecuteSqlCommand(sql, parameters));
}
public async Task <ApiResult <long> > Put([FromBody] AppRole value)
{
if (!ModelState.IsValid)
{
return(new ApiResult <long>(l,
BasicControllerEnums.UnprocessableEntity,
ModelErrors()));
}
using (var tran = db.Database.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
#region Update Entity
// 需要先更新value，否则更新如claims等属性会有并发问题
db.Update(value);
db.SaveChanges();
#endregion
#region Find Entity.Source
var source = await db.Roles.Where(x => x.Id == value.Id)
.Include(x => x.Claims)
.AsNoTracking()
.FirstOrDefaultAsync();
#endregion
#region Update Entity.Claims
if (value.Claims != null && value.Claims.Count > 0)
{
#region delete
var EntityIDs = value.Claims.Select(x => x.Id).ToList();
if (EntityIDs.Count > 0)
{
var DeleteEntities = source.Claims.Where(x => !EntityIDs.Contains(x.Id)).Select(x => x.Id).ToArray();
if (DeleteEntities.Count() > 0)
{
var sql = string.Format("DELETE AspNetRoleClaims WHERE ID IN ({0})",
string.Join(",", DeleteEntities));
db.Database.ExecuteSqlCommand(new RawSqlString(sql));
}
}
#endregion
#region update
var UpdateEntities = value.Claims.Where(x => x.Id > 0).ToList();
if (UpdateEntities.Count > 0)
{
UpdateEntities.ForEach(x =>
{
var sql = new RawSqlString("UPDATE AspNetRoleClaims SET [ClaimType]=@ClaimType,[ClaimValue]=@ClaimValue WHERE Id = " + x.Id);
var _params = new SqlParameter[]
{
new SqlParameter("@ClaimType", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@ClaimValue", DBNull.Value)
{
IsNullable = true
},
};
if (!string.IsNullOrWhiteSpace(x.ClaimType))
{
_params[0].Value = x.ClaimType;
}
if (!string.IsNullOrWhiteSpace(x.ClaimValue))
{
_params[1].Value = x.ClaimValue;
}
db.Database.ExecuteSqlCommand(sql, _params);
});
}
#endregion
#region insert
var NewEntities = value.Claims.Where(x => x.Id == 0).ToList();
if (NewEntities.Count > 0)
{
NewEntities.ForEach(x =>
{
var sql = new RawSqlString("INSERT INTO AspNetRoleClaims VALUES (@ClaimType,@ClaimValue,@RoleId)");
var _params = new SqlParameter[]
{
new SqlParameter("@RoleId", source.Id),
new SqlParameter("@ClaimType", DBNull.Value)
{
IsNullable = true
},
new SqlParameter("@ClaimValue", DBNull.Value)
{
IsNullable = true
},
};
if (!string.IsNullOrWhiteSpace(x.ClaimType))
{
_params[1].Value = x.ClaimType;
}
if (!string.IsNullOrWhiteSpace(x.ClaimValue))
{
_params[2].Value = x.ClaimValue;
}
db.Database.ExecuteSqlCommand(sql, _params);
});
}
#endregion
}
#endregion
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
return(new ApiResult <long>(l,
BasicControllerEnums.ExpectationFailed,
ex.Message));
}
}
return(new ApiResult <long>(value.Id));
}
/// <inheritdoc />
/// <summary>
/// Get objects from database with raw sql syntext
/// </summary>
/// <param name="sql">Sql syntext.</param>
/// <param name="parameters">Parameter.</param>
/// <returns></returns>
public virtual async Task <IQueryable <TEntity> > GetFromSqlAsync(RawSqlString sql, params object[] parameters)
{
return(await Task.Factory.StartNew(() => _dbSet.FromSql(sql, parameters)));
}
public async Task <int> ExecuteSqlCommandAsync(RawSqlString sql, CancellationToken token = default(CancellationToken))
{
return(await _context.Database.ExecuteSqlCommandAsync(sql, token));
}
public async Task <IDataReader> ExecuteReaderAsync(RawSqlString sql, params object[] parameters)
{
using (await Database.GetService <IConcurrencyDetector>().EnterCriticalSectionAsync(default))
