为了在我目前的项目中使用,我创建了一个类,它允许我调用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)
{
MessageBox.Show(newId.ToString(),"Success",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
private void Error(string error)
{
MessageBox.Show(error,"Error",MessageBoxIcon.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();
}
