我有一个包含所有数据访问代码的类，用于我的ASP.NET 4.0应用程序。该类中有两种方法可将数据插入数据库。我想将这些插入操作加入到SqlTransaction中，并在其中一个插入操作失败时回滚事务。但是由于我的编码方式，我不确定如何实现它。以下是我的数据访问代码：
public class DBUtil
{
private static readonly string _connectionString;
static DBUtil()
{
_connectionString = WebConfigurationManager.ConnectionStrings["MooDB"].ConnectionString;
if (string.IsNullOrEmpty(_connectionString))
throw new Exception("Connection string not configured in Web.Config file");
}
public int InsertTrade(
string symbol,
string tradeSetupId,
int tradeTypeId,
decimal lotsPerUnit,
string chartTimeFrame,
decimal pctAccountRisked,
int? tradeGrade = null,
int? executionGrade = null,
int? MFEPips = null,
int? MAEPips = null
)
{
SqlCommand cmd = new SqlCommand("usp_InsertTrade");
// required parameters
cmd.Parameters.AddWithValue("@symbol", symbol);
cmd.Parameters.AddWithValue("@tradeSetupId", tradeSetupId);
cmd.Parameters.AddWithValue("@tradeTypeId", tradeTypeId);
cmd.Parameters.AddWithValue("@lotsPerUnit", lotsPerUnit);
cmd.Parameters.AddWithValue("@chartTimeFrame", chartTimeFrame);
cmd.Parameters.AddWithValue("@pctAccountRisked", pctAccountRisked);
// optional parameters
if (MAEPips.HasValue)
cmd.Parameters.AddWithValue("@MAEPips", MAEPips);
if (MFEPips.HasValue)
cmd.Parameters.AddWithValue("@MFEPips", MFEPips);
if (tradeGrade.HasValue)
cmd.Parameters.AddWithValue("@tradeGrade", tradeGrade);
if (executionGrade.HasValue)
cmd.Parameters.AddWithValue("@executionGrade", executionGrade);
return (InsertData(cmd, "trade"));
}
public int InsertOrder(
int tradeId,
int units,
string side,
decimal price,
decimal spread,
int strategyId,
string signalTypeId,
int brokerId,
string orderTypeId,
DateTime orderDateTime,
string comment,
int? accountId = null
)
{
SqlCommand cmd = new SqlCommand("usp_InsertOrder");
// required parameters
cmd.Parameters.Add(new SqlParameter("@tradeId", tradeId));
cmd.Parameters.Add(new SqlParameter("@units", units));
cmd.Parameters.Add(new SqlParameter("@side", side));
cmd.Parameters.Add(new SqlParameter("@price", price));
cmd.Parameters.Add(new SqlParameter("@spread", spread));
cmd.Parameters.Add(new SqlParameter("@strategyId", strategyId));
cmd.Parameters.Add(new SqlParameter("@signalTypeId", signalTypeId));
cmd.Parameters.Add(new SqlParameter("@brokerId", brokerId));
cmd.Parameters.Add(new SqlParameter("@orderTypeId", orderTypeId));
cmd.Parameters.Add(new SqlParameter("@orderDateTime", orderDateTime));
cmd.Parameters.Add(new SqlParameter("@comment", comment));
// optional parameters
if (accountId.HasValue)
cmd.Parameters.Add(new SqlParameter("@accountId", accountId));
return (InsertData(cmd, "order"));
}
private int InsertData(SqlCommand cmd, string tableName)
{
SqlConnection con = new SqlConnection(_connectionString);
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
int rc = -1;
try
{
con.Open();
rc = (int) cmd.ExecuteScalar();
}
finally
{
con.Close();
}
return rc;
}
}
我正在ASP.NET页面中使用以下方式访问该代码：
int tradeId = DB.InsertTrade (
ddlSymbols.SelectedValue,
ddlTradeSetups.SelectedValue,
int.Parse(ddlTradeTypes.SelectedValue),
decimal.Parse(txtLotsPerUnit.Text),
ddlTimeFrames.Text,
decimal.Parse(txtAcctRisk.Text));
int orderId = DB.InsertOrder (
tradeId,
int.Parse(txtUnits.Text),
radSide.SelectedValue,
Decimal.Parse(txtEntryPrice.Text),
Decimal.Parse(txtSpread.Text),
int.Parse(ddlStrategies.SelectedValue),
"IE",
int.Parse(ddlBrokers.SelectedValue),
radSide.SelectedValue + radOrderType.SelectedValue,
DateTime.Parse(txtEntryDate.Text + " " + txtEntryTime.Text),
txtEntryComments.Text,
int.Parse(ddlAccounts.SelectedValue));
我想要做的是将 ASP.NET 页面中的调用封装在 SqlTransaction 中。最好的方法是什么？我是否需要稍微重构我的代码？
非常感谢。