/// <summary>
/// Asynchronously executes the given DDL/DML command against the database.
///
/// As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter.
/// context.Database.ExecuteSqlCommandAsync("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @p0", userSuppliedAuthor);
/// Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named parameters in the SQL query string.
/// context.Database.ExecuteSqlCommandAsync("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));
/// </summary>
/// <remarks>
/// Multiple active operations on the same context instance are not supported. Use 'await' to ensure
/// that any asynchronous operations have completed before calling another method on this context.
/// </remarks>
/// <param name="transactionalBehavior"> Controls the creation of a transaction for this command. </param>
/// <param name="sql"> The command string. </param>
/// <param name="parameters"> The parameters to apply to the command string. </param>
/// <returns>
/// A task that represents the asynchronous operation.
/// The task result contains the result returned by the database after executing the command.
/// </returns>
public Task<int> ExecuteSqlCommandAsync(
TransactionalBehavior transactionalBehavior,
string sql,
params object[] parameters)
{
return this.ExecuteSqlCommandAsync(transactionalBehavior, sql, CancellationToken.None, parameters);
}
/// <summary>
/// Executes the given DDL/DML command against the database.
///
/// As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter.
/// context.Database.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @p0", userSuppliedAuthor);
/// Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named parameters in the SQL query string.
/// context.Database.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));
/// </summary>
/// <param name="transactionalBehavior"> Controls the creation of a transaction for this command. </param>
/// <param name="sql"> The command string. </param>
/// <param name="parameters"> The parameters to apply to the command string. </param>
/// <returns> The result returned by the database after executing the command. </returns>
public int ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, string sql, params object[] parameters)
{
Check.NotEmpty(sql, "sql");
Check.NotNull(parameters, "parameters");
return(_internalContext.ExecuteSqlCommand(transactionalBehavior, sql, parameters));
}
/// <summary>
/// 对数据库执行给定的 DDL/DML 命令。
/// 与接受 SQL 的任何 API 一样，对任何用户输入进行参数化以便避免 SQL 注入攻击是十分重要的。 您可以在 SQL 查询字符串中包含参数占位符，然后将参数值作为附加参数提供。
/// 您提供的任何参数值都将自动转换为 DbParameter。 unitOfWork.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @p0", userSuppliedAuthor);
/// 或者，您还可以构造一个 DbParameter 并将它提供给 SqlQuery。 这允许您在 SQL 查询字符串中使用命名参数。 unitOfWork.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));
/// </summary>
/// <param name="transactionalBehavior">对于此命令控制事务的创建。</param>
/// <param name="sql">命令字符串。</param>
/// <param name="parameters">要应用于命令字符串的参数。</param>
/// <returns>执行命令后由数据库返回的结果。</returns>
public virtual async Task <int> ExecuteSqlCommandAsync(TransactionalBehavior transactionalBehavior, string sql, params object[] parameters)
{
System.Data.Entity.TransactionalBehavior behavior = transactionalBehavior == TransactionalBehavior.DoNotEnsureTransaction
? System.Data.Entity.TransactionalBehavior.DoNotEnsureTransaction
: System.Data.Entity.TransactionalBehavior.EnsureTransaction;
return(await Database.ExecuteSqlCommandAsync(behavior, sql, parameters));
}
/// <summary>
/// Executes the given DDL/DML command against the database.
/// As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection
/// attack. You can include parameter place holders in the SQL query string and then supply parameter values as
/// additional arguments. Any parameter values you supply will automatically be converted to a DbParameter.
/// context.Database.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @p0", userSuppliedAuthor);
/// Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named
/// parameters in the SQL query string.
/// context.Database.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @author", new
/// SqlParameter("@author", userSuppliedAuthor));
/// </summary>
/// <param name="transactionalBehavior"> Controls the creation of a transaction for this command. </param>
/// <param name="sql"> The command string. </param>
/// <param name="parameters"> The parameters to apply to the command string. </param>
/// <returns> The result returned by the database after executing the command. </returns>
public virtual int ExecuteSqlCommand(
TransactionalBehavior transactionalBehavior,
string sql,
params object[] parameters)
{
return(_database.ExecuteSqlCommand(transactionalBehavior, sql, parameters));
}
/// <summary>
/// 对数据库执行给定的 DDL/DML 命令。
/// 与接受 SQL 的任何 API 一样，对任何用户输入进行参数化以便避免 SQL 注入攻击是十分重要的。 您可以在 SQL 查询字符串中包含参数占位符，然后将参数值作为附加参数提供。
/// 您提供的任何参数值都将自动转换为 DbParameter。 unitOfWork.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @p0", userSuppliedAuthor);
/// 或者，您还可以构造一个 DbParameter 并将它提供给 SqlQuery。 这允许您在 SQL 查询字符串中使用命名参数。 unitOfWork.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));
/// </summary>
/// <param name="transactionalBehavior">对于此命令控制事务的创建。</param>
/// <param name="sql">命令字符串。</param>
/// <param name="parameters">要应用于命令字符串的参数。</param>
/// <returns>执行命令后由数据库返回的结果。</returns>
public int ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, string sql, params object[] parameters)
{
System.Data.Entity.TransactionalBehavior behavior = transactionalBehavior == TransactionalBehavior.DoNotEnsureTransaction
? System.Data.Entity.TransactionalBehavior.DoNotEnsureTransaction
: System.Data.Entity.TransactionalBehavior.EnsureTransaction;
return(Database.ExecuteSqlCommand(behavior, sql, parameters));
}
public virtual int ExecuteSqlCommand(
TransactionalBehavior transactionalBehavior,
string sql,
object[] parameters)
{
this.Initialize();
return(this.ObjectContext.ExecuteStoreCommand(transactionalBehavior, sql, parameters));
}
/// <summary>
/// Asynchronously executes the given DDL/DML command against the database.
/// </summary>
/// <remarks>
/// Multiple active operations on the same context instance are not supported. Use 'await' to ensure
/// that any asynchronous operations have completed before calling another method on this context.
/// </remarks>
/// <param name="transactionalBehavior"> Controls the creation of a transaction for this command. </param>
/// <param name="sql"> The command string. </param>
/// <param name="cancellationToken">
/// A <see cref="CancellationToken" /> to observe while waiting for the task to complete.
/// </param>
/// <param name="parameters"> The parameters to apply to the command string. </param>
/// <returns>
/// A task that represents the asynchronous operation.
/// The task result contains the result returned by the database after executing the command.
/// </returns>
public Task <int> ExecuteSqlCommandAsync(
TransactionalBehavior transactionalBehavior, string sql, CancellationToken cancellationToken, params object[] parameters)
{
Check.NotEmpty(sql, "sql");
Check.NotNull(parameters, "parameters");
return(_internalContext.ExecuteSqlCommandAsync(transactionalBehavior, sql, cancellationToken, parameters));
}
/// <summary>
/// Asynchronously executes the given DDL/DML command against the database.
/// As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection
/// attack. You can include parameter place holders in the SQL query string and then supply parameter values as
/// additional arguments. Any parameter values you supply will automatically be converted to a DbParameter.
/// context.Database.ExecuteSqlCommandAsync("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @p0", userSuppliedAuthor);
/// Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named
/// parameters in the SQL query string.
/// context.Database.ExecuteSqlCommandAsync("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @author", new
/// SqlParameter("@author", userSuppliedAuthor));
/// </summary>
/// <remarks>
/// Multiple active operations on the same context instance are not supported. Use 'await' to ensure
/// that any asynchronous operations have completed before calling another method on this context.
/// </remarks>
/// <param name="transactionalBehavior"> Controls the creation of a transaction for this command. </param>
/// <param name="sql"> The command string. </param>
/// <param name="cancellationToken">
/// A <see cref="T:System.Threading.CancellationToken" /> to observe while waiting for the task to complete.
/// </param>
/// <param name="parameters"> The parameters to apply to the command string. </param>
/// <returns>
/// A task that represents the asynchronous operation.
/// The task result contains the result returned by the database after executing the command.
/// </returns>
public virtual Task <int> ExecuteSqlCommandAsync(
TransactionalBehavior transactionalBehavior,
string sql,
CancellationToken cancellationToken,
params object[] parameters)
{
return(_database.ExecuteSqlCommandAsync(transactionalBehavior, sql, cancellationToken, parameters));
}
/// <summary>
/// Executes a command against the database server that does not return a sequence of objects.
/// </summary>
/// <typeparam name="TInput">Input type parameter.</typeparam>
/// <param name="context"><c>DbContext</c> instance.</param>
/// <param name="transactionalBehavior">Controls the creation of a transaction for this command.</param>
/// <param name="commandText">Query to run a stored procedure.</param>
/// <param name="input">Input value.</param>
/// <returns>Returns the number of rows affected.</returns>
public static int ExecuteStoreCommand <TInput>(this DbContext context, TransactionalBehavior transactionalBehavior, string commandText, TInput input)
{
var parameters = ConvertHelper.ConvertToParameters(input);
var adapter = (IObjectContextAdapter)context;
var results = adapter.ObjectContext.ExecuteStoreCommand(transactionalBehavior, commandText, parameters);
return(results);
}
/// <summary>
/// 执行SQL命令
/// </summary>
/// <param name="sqlText"></param>
/// <returns></returns>
public int ExecuteSqlCommand(string sqlText, TransactionalBehavior behavior = TransactionalBehavior.DoNotEnsureTransaction)
{
//if (EFContext.DbContext.Database.CurrentTransaction == null)
//{
// EFContext.DbContext.Database.BeginTransaction();
//}
return(EFContext.DbContext.Database.ExecuteSqlCommand(behavior, sqlText));
}
public virtual Task <int> ExecuteSqlCommandAsync(
TransactionalBehavior transactionalBehavior,
string sql,
CancellationToken cancellationToken,
object[] parameters)
{
this.Initialize();
return(this.ObjectContext.ExecuteStoreCommandAsync(transactionalBehavior, sql, cancellationToken, parameters));
}
/// <summary>
/// Executes the given DDL/DML command against the database.
///
/// As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter.
/// context.Database.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @p0", userSuppliedAuthor);
/// Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named parameters in the SQL query string.
/// context.Database.ExecuteSqlCommand("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));
/// </summary>
/// <param name="transactionalBehavior"> Controls the creation of a transaction for this command. </param>
/// <param name="sql"> The command string. </param>
/// <param name="parameters"> The parameters to apply to the command string. </param>
/// <returns> The result returned by the database after executing the command. </returns>
public int ExecuteSqlCommand(
TransactionalBehavior transactionalBehavior,
string sql,
params object[] parameters)
{
Check.NotEmpty(sql, nameof (sql));
Check.NotNull<object[]>(parameters, nameof (parameters));
return this._internalContext.ExecuteSqlCommand(transactionalBehavior, sql, parameters);
}
// Execute SqlCommand With TransactionalBehavior
public int ExecuteNonQuery(TransactionalBehavior transactionalBehavior, string execQuery, params object[] parameters)
{
try
{
return(Context.Database.ExecuteSqlCommand(transactionalBehavior, execQuery, parameters));
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Asynchronously executes the given DDL/DML command against the database.
///
/// As with any API that accepts SQL it is important to parameterize any user input to protect against a SQL injection attack. You can include parameter place holders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter.
/// context.Database.ExecuteSqlCommandAsync("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @p0", userSuppliedAuthor);
/// Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named parameters in the SQL query string.
/// context.Database.ExecuteSqlCommandAsync("UPDATE dbo.Posts SET Rating = 5 WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));
/// </summary>
/// <remarks>
/// Multiple active operations on the same context instance are not supported. Use 'await' to ensure
/// that any asynchronous operations have completed before calling another method on this context.
/// </remarks>
/// <param name="transactionalBehavior"> Controls the creation of a transaction for this command. </param>
/// <param name="sql"> The command string. </param>
/// <param name="cancellationToken">
/// A <see cref="T:System.Threading.CancellationToken" /> to observe while waiting for the task to complete.
/// </param>
/// <param name="parameters"> The parameters to apply to the command string. </param>
/// <returns>
/// A task that represents the asynchronous operation.
/// The task result contains the result returned by the database after executing the command.
/// </returns>
public Task<int> ExecuteSqlCommandAsync(
TransactionalBehavior transactionalBehavior,
string sql,
CancellationToken cancellationToken,
params object[] parameters)
{
Check.NotEmpty(sql, nameof (sql));
Check.NotNull<object[]>(parameters, nameof (parameters));
cancellationToken.ThrowIfCancellationRequested();
return this._internalContext.ExecuteSqlCommandAsync(transactionalBehavior, sql, cancellationToken, parameters);
}
/// <summary>
/// 执行SQL，带事务
/// </summary>
/// <param name="transactionalBehavior">对于此命令控制事务的创建</param>
/// <param name="sql">命令字符串</param>
/// <param name="parameters">要应用于命令字符串的参数</param>
public static int ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, string sql, params object[] parameters)
{
try
{
using (var db = new DemoEntities())
{
return(db.Database.ExecuteSqlCommand(transactionalBehavior, sql, parameters));
}
}
catch (Exception ex)
{
WriteException(ex);
return(0);
}
}
/// <summary>
/// 执行非查询sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="behavior">事务属性</param>
/// <param name="isInsertIdentity">sql语句是否在自增字段插入值</param>
/// <param name="tableName">操作的表名（可空）</param>
/// <param name="parameters">参数列表</param>
/// <returns>返回影响的行数</returns>
public virtual int ExcuteNoQuery(string sql, TransactionalBehavior behavior = TransactionalBehavior.DoNotEnsureTransaction, bool isInsertIdentity = false, string tableName = "", params object[] parameters)
{
if (isInsertIdentity)
{
if (tableName == "")
{
tableName = typeof(TEntity).Name;
}
if (sql.TrimEnd().EndsWith(";") == false)
{
sql = sql + ";";
}
sql = "SET IDENTITY_INSERT " + tableName + " ON;" + sql + "SET IDENTITY_INSERT " + tableName + " OFF;";
}
return(EFContext.DbContext.Database.ExecuteSqlCommand(behavior, sql, parameters));
}
/// <summary>
/// 执行SQL命令而不返内容
/// </summary>
/// <param name="sql">需要执行的SQL语句，需要填写参数。如：EXEC sp_generate_dt @start, @finish</param>
/// <param name="doNotEnsureTransaction"></param>
/// <param name="timeout">超时时间，以秒为单位。超时将引发 SqlException：Timeout 时间已到</param>
/// <param name="parameters">参数列表，若包含输出参数，返回值在语句执行之后可以通过对应Parameter的Value取出</param>
/// <returns>影响的行数</returns>
public virtual int ExecuteSqlCommand(string sql, bool doNotEnsureTransaction = false, int?timeout = null, params object[] parameters)
{
int?previousTimeout = null;
if (timeout.HasValue)
{
previousTimeout = ((IObjectContextAdapter)this).ObjectContext.CommandTimeout;
((IObjectContextAdapter)this).ObjectContext.CommandTimeout = timeout;
}
TransactionalBehavior transactionalBehavior = doNotEnsureTransaction ? TransactionalBehavior.DoNotEnsureTransaction : TransactionalBehavior.EnsureTransaction;
int result = Database.ExecuteSqlCommand(transactionalBehavior, sql, parameters);
if (timeout.HasValue)
{
((IObjectContextAdapter)this).ObjectContext.CommandTimeout = previousTimeout;
}
return(result);
}
// Execute SqlCommand With TransactionalBehavior
public int ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, string execQuery, params object[] parameters)
{
try
{
string[] commands = execQuery.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
DbConnection conn = Context.Database.Connection; // Get Database connection
ConnectionState initialState = conn.State;
try
{
if (initialState != ConnectionState.Open)
{
conn.Open(); // open connection if not already open
}
using (DbCommand cmd = conn.CreateCommand())
{
// Iterate the string array and execute each one.
foreach (string thisCommand in commands)
{
cmd.CommandText = thisCommand;
cmd.ExecuteNonQuery();
}
}
}
finally
{
if (initialState != ConnectionState.Open)
{
conn.Close(); // only close connection if not initially open
}
}
}
catch (Exception)
{
return(0);
}
return(1);
}
public static async Task <int> ExecuteStoreCommandAsync(this DbContext db, TransactionalBehavior behavior, string commandText, params object[] parameters)
{
return(await((IObjectContextAdapter)db).ObjectContext.ExecuteStoreCommandAsync(behavior, commandText, parameters));
}
public Task <int> ExecuteSqlCommandAsync(TransactionalBehavior transactionalBehavior, string sql, params object[] parameters)
{
throw new NotImplementedException();
}
public Task <int> ExecuteSqlCommandAsync(TransactionalBehavior transactionalBehavior, string sql, System.Threading.CancellationToken cancellationToken, params object[] parameters)
{
throw new NotImplementedException();
}
/// <summary>
/// Executes a command against the database server that does not return a sequence of objects asynchronously.
/// </summary>
/// <param name="context"><c>DbContext</c> instance.</param>
/// <param name="transactionalBehavior">Controls the creation of a transaction for this command.</param>
/// <param name="commandText">Query to run a stored procedure.</param>
/// <param name="cancellationToken">A <c>CancellationToken</c> to observe while waiting for the task to complete.</param>
/// <param name="input">List of input values.</param>
/// <returns>Returns the number of rows affected.</returns>
public static async Task <int> ExecuteStoreCommandAsync(this DbContext context, TransactionalBehavior transactionalBehavior, string commandText, CancellationToken cancellationToken, IDictionary <string, object> input)
{
var parameters = ConvertHelper.ConvertToParameters(input);
var adapter = (IObjectContextAdapter)context;
var results = await adapter.ObjectContext.ExecuteStoreCommandAsync(transactionalBehavior, commandText, cancellationToken, parameters);
return(results);
}
public int ExecuteSqlCommand(TransactionalBehavior transactionakBehavior, String sql, params Object[] parameters)
{
return(m_DbContext.Database.ExecuteSqlCommand(transactionakBehavior, sql, parameters));
}
public int ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, string sql, params object[] parameters)
{
return(0);
}
public Task <int> ExecuteSqlCommandAsync(TransactionalBehavior transactionalBehavior, string sql, params object[] parameters)
{
return(base.Database.ExecuteSqlCommandAsync(transactionalBehavior, sql, parameters));
}
public static int ExecuteStoreCommand(this DbContext db, TransactionalBehavior behavior, string commandText, params object[] parameters)
{
return(((IObjectContextAdapter)db).ObjectContext.ExecuteStoreCommand(behavior, commandText, parameters));
}
