C# · 12月 30, 2021

c# – 在事务中调用多个SQL Server存储过程

为了在我目前的项目中使用,我创建了一个类,它允许我调用sql Server异步.

我的代码如下所示:

internal class CommandAndCallback<TCallback,TError>{ public sqlCommand sql { get; set; } public TCallback Callback { get; set; } public TError Error { get; set; }}class MyCodes:SingletonBase<MyCodes>{ private static string _connString = @”Data Source=MyDB;Initial Catalog=ED;Integrated Security=True;Asynchronous Processing=true;Connection Timeout=0;Application Name=TEST”; private MyCodes() { } public void SetSystem(bool production) { _connString = string.Format(@”Data Source=MyDB;Initial Catalog={0};Integrated Security=True;Asynchronous Processing=true;Connection Timeout=0;Application Name=TEST”,production ? “ED” : “TEST_ED”); } public void Add(string newCode,Action<int> callback,Action<string> error) { var conn = new sqlConnection(_connString); sqlCommand cmd = conn.CreateCommand(); cmd.CommandTimeout = 0; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = @”ADD_CODE”; cmd.Parameters.Add(“@NEW”,sqlDbType.NVarChar).Value = newCode; cmd.Parameters.Add(“@NewId”,sqlDbType.Int).Direction = ParameterDirection.Output; try { cmd.Connection.open(); } catch (Exception ex) { error(ex.ToString()); return; } var ar = new CommandAndCallback<Action<int>,Action<string>> { Callback = callback,Error = error,sql = cmd }; cmd.BeginExecuteReader(Add_Handler,ar,CommandBehavior.CloseConnection); } private static void Add_Handler(IAsyncResult result) { var ar = (CommandAndCallback<Action<int>,Action<string>>)result.AsyncState; if (result.IsCompleted) { try { ar.sql.EndExecuteReader(result); ar.Callback(Convert.ToInt32(ar.sql.Parameters[“@NewId”].Value)); } catch (Exception ex) { ar.Error(ex.Message); } } else { ar.Error(“Error executing sql”); } }public void Update(int codeId,string newCode,Action callback,Action<string> error) { var conn = new sqlConnection(_connString); sqlCommand cmd = conn.CreateCommand(); cmd.CommandTimeout = 0; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = @”UPDATE_CODE”; cmd.Parameters.Add(“@CODE_ID”,sqlDbType.Int).Value = codeId; cmd.Parameters.Add(“@NEW”,sqlDbType.NVarChar).Value = newCode; try { cmd.Connection.open(); } catch (Exception ex) { error(ex.ToString()); return; } var ar = new CommandAndCallback<Action,sql = cmd }; cmd.BeginExecuteReader(Update_Handler,CommandBehavior.CloseConnection); } private static void Update_Handler(IAsyncResult result) { var ar = (CommandAndCallback<Action,Action<string>>)result.AsyncState; if (result.IsCompleted) { try { ar.sql.EndExecuteReader(result); ar.Callback(); } catch (Exception ex) { ar.Error(ex.Message); } } else { ar.Error(“Error executing sql”); } }}

这可能看起来像太多的代码,但它可以让我这样称呼:

private void Add_Click(object sender,EventArgs e){ MyCodes.Instance.Add(“Test”,Success,Error)}private void Success(int newId){ Message@R_670_2419@.Show(newId.ToString(),”Success”,Message@R_670_2419@Buttons.OK,Message@R_670_2419@Icon.Information);}private void Error(string error){ Message@R_670_2419@.Show(error,”Error”,Message@R_670_2419@Icon.Error);}

上面的代码对我来说很好,我可以做每个调用异步.

我现在的问题是做多个调用作为事务 – 我想更新2个代码并添加一个新的.

通常我会调用更新,然后在成功处理程序调用第二次更新,并在处理程序中第二次更新我将调用add将返回新的id.

就像是:

-UPDATE CODE |-UPDATE CODE |-ADD CODE (only this one return something)

但是我想将所有这些都称为事务,所以如果添加代码会中断更新将会回滚.

题:

可以作为事务调用多个异步查询吗?

我可以将我的上述方法称为事务,还是必须创建单独的方法来将我的过程称为一个? (我想避免这个,因为它只是将相同的代码从一种方法复制到另一种方法)

我想补充说,我使用.NET 3.5等待和其他不错的功能不是一个选项.

解决方法 string cnnString =WebConfigurationManager.ConnectionStrings[“MyString”].ConnectionString; sqlConnection cnn = new sqlConnection(cnnString); sqlTransaction transaction; cnn.open(); transaction = cnn.BeginTransaction(); try { // Command Objects for the transaction sqlCommand cmd1 = new sqlCommand(“sproc1”,cnn); sqlCommand cmd2 = new sqlCommand(“sproc2”,cnn); cmd1.CommandType = CommandType.StoredProcedure; cmd2.CommandType = CommandType.StoredProcedure; cmd1.Parameters.Add(new sqlParameter(“@Param1”,sqlDbType.NVarChar,50)); cmd1.Parameters[“@Param1”].Value = paramValue1; cmd1.Parameters.Add(new sqlParameter(“@Param2”,50)); cmd1.Parameters[“@Param2”].Value = paramValue2; cmd2.Parameters.Add(new sqlParameter(“@Param3”,50)); cmd2.Parameters[“@Param3”].Value = paramValue3; cmd2.Parameters.Add(new sqlParameter(“@Param4”,50)); cmd2.Parameters[“@Param4”].Value = paramValue4; cmd1.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); transaction.Commit(); } catch (sqlException sqlEx) { transaction.Rollback(); } finally { cnn.Close(); cnn.Dispose(); }