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))