C# 中的转换和执行非查询错误
本文关键字:查询 错误 执行 转换 | 更新日期: 2023-09-27 18:36:55
我有以下错误:
当分配给命令的连接位于挂起的本地事务中时,ExecuteNonQuery 要求命令具有事务。 命令的事务属性尚未初始化。
此错误出现在函数执行科曼多
我的函数保存:
public void save(string[] arr)//, int lista)
{
SqlConnection conexao = new SqlConnection(WebConfigurationManager.ConnectionStrings["strConexao"].ToString());
conexao.Open();
SqlTransaction trx = conexao.BeginTransaction();
try
{
//Truncate cliente_recurso
BUProjetosDAL dal = new BUProjetosDAL();
dal.excluirClientesRecurso(conexao);
dtsRecursoClienteTableAdapters.RECURSO_CLIENTETabelaTableAdapter tabela = new dtsRecursoClienteTableAdapters.RECURSO_CLIENTETabelaTableAdapter();
for (int j = 0; j <= 10; j++) { // <- here
tabela.Insert(arr);
}
trx.Commit();
}
catch (SqlException ex)
{
try
{
trx.Rollback();
}
catch (Exception exRollback)
{
Response.Write(exRollback.Message);
}
}
}
Function excluirClientesRecurso
public void excluirClientesRecurso(SqlConnection conexao)
{
executaComando("TRUNCATE TABLE dbo.RECURSO_CLIENTE", conexao);
}
函数 executaComando (----错误出现在此处----)
public void executaComando(string query, SqlConnection conexao)
{
try
{
SqlCommand cmd = new SqlCommand(query);
cmd.Connection = conexao;
cmd.ExecuteNonQuery();
conexao.Close();
}
catch (Exception ex)
{
throw ex;
}
}
嗯,正如错误所说。您打开了一个事务,但未将其分配给命令。
一个简单的解决方案是简单地将其传递给executaComando
:
public void excluirClientesRecurso(SqlConnection conexao,
SqlTransaction transaction)
{
executaComando("TRUNCATE TABLE dbo.RECURSO_CLIENTE", conexao);
}
public void executaComando(string query, SqlConnection conexao,
SqlTransaction transaction)
{
SqlCommand cmd = new SqlCommand(query);
cmd.Connection = conexao;
cmd.Transaction = transaction
cmd.ExecuteNonQuery();
conexao.Close();
}
// Usage
dal.excluirClientesRecurso(conexao, trx);
请注意,我在 executaComando
中删除了 try-catch,因为它没有任何好处。事实上,它做了坏事:它搞砸了异常的堆栈跟踪。
是的,确实如此。所以把交易传进去。这基本上是你说服它"我知道这里有一笔交易"的方式。所以:
dal.excluirClientesRecurso(conexao, trx);
// ...
public void excluirClientesRecurso(SqlConnection conexao,
SqlTransaction transaction = null)
{
executaComando("TRUNCATE TABLE dbo.RECURSO_CLIENTE",
conexao, transaction);
}
public void executaComando(string query, SqlConnection conexao,
SqlTransaction trasaction = null)
{
try
{
SqlCommand cmd = new SqlCommand(query);
cmd.Trasnaction = transaction;
// ...
试试这段代码
public bool updateusertable(string UserName,string Password,string Datetime)
{
bool bResult = false;
SqlTransaction tx;
try
{
tx=Sqlconnect.Begintransaction;
SqlCommand Ocmd = new SqlCommand();
Sqlconnect= Cconnect.OpenSqlConnection();
Ocmd.Connection = Sqlconnect;
Ocmd.CommandType = CommandType.StoredProcedure;
Ocmd.CommandText = "SP_User_login_Update";
Ocmd.Parameters.Add("@UserName", SqlDbType.VarChar, 100).Value = UserName;
Ocmd.Parameters.Add("@Password", SqlDbType.VarChar, 100).Value = Password;
Ocmd.Parameters.Add("@lastlogin", SqlDbType.VarChar, 100).Value = Datetime;
int i = Ocmd.ExecuteNonQuery();
if (i <= 1)
{
bResult = true;
}else
{
tx.Rollback();
}
}
catch (Exception ex)
{
string msg = ex.Message.ToString();
tx.Rollback();
}
finally
{
tx.Commit();
}
return bResult;
}