问题描述:一个公共模块,一个窗体,窗体有个新定义的datatable(dt),这个dt是传入一条SQL语句到公共模块的一个函数中获得数据的,之后又对这个dt进行了很多操作(如删行,更新值等等),现在我想在这个公共模块中写一个通用的函数,只要传入一个datatable到这个函数中,然后由这个函数将这个datatable更新到数据库中
Public Function UpdateTable(ByVal adt As DataTable) As Boolean
'更新DataTable到数据库中 OpenConn()
Try mcmb.DataAdapter = mda 'mcmb 是一个模块级的SqlCommandBuilder mda 是一个模块级的 SqlDataAdapter
mda.Update(adt)
Return True Catch ex As Exception MsgBox("错误代码:" & Err.Number & vbCrLf & Err.Description, MsgBoxStyle.Information, "提示")
Return False
End Try End Function我现在的更新datatable的函数代码就是这些,在没有加入事务的情况下是没有问题的,但事务该如何加呢?
Public Function UpdateTable(ByVal adt As DataTable) As Boolean
'更新DataTable到数据库中 OpenConn()
Try mcmb.DataAdapter = mda 'mcmb 是一个模块级的SqlCommandBuilder mda 是一个模块级的 SqlDataAdapter
mda.Update(adt)
Return True Catch ex As Exception MsgBox("错误代码:" & Err.Number & vbCrLf & Err.Description, MsgBoxStyle.Information, "提示")
Return False
End Try End Function我现在的更新datatable的函数代码就是这些,在没有加入事务的情况下是没有问题的,但事务该如何加呢?
// This function takes arguments for the 2 connection strings and commands in order
// to create a transaction involving two SQL Servers. It returns a value > 0 if the
// transaction committed, 0 if the transaction rolled back. To test this code, you can
// connect to two different databases on the same server by altering the connection string,
// or to another RDBMS such as Oracle by altering the code in the connection2 code block.
static public int CreateTransactionScope(
string connectString1, string connectString2,
string commandText1, string commandText2)
{
// Initialize the return value to zero and create a StringWriter to display results.
int returnValue = 0;
System.IO.StringWriter writer = new System.IO.StringWriter(); // Create the TransactionScope in which to execute the commands, guaranteeing
// that both commands will commit or roll back as a single unit of work.
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection1 = new SqlConnection(connectString1))
{
try
{
// Opening the connection automatically enlists it in the
// TransactionScope as a lightweight transaction.
connection1.Open(); // Create the SqlCommand object and execute the first command.
SqlCommand command1 = new SqlCommand(commandText1, connection1);
returnValue = command1.ExecuteNonQuery();
writer.WriteLine("Rows to be affected by command1: {0}", returnValue); // if you get here, this means that command1 succeeded. By nesting
// the using block for connection2 inside that of connection1, you
// conserve server and network resources by opening connection2
// only when there is a chance that the transaction can commit.
using (SqlConnection connection2 = new SqlConnection(connectString2))
try
{
// The transaction is promoted to a full distributed
// transaction when connection2 is opened.
connection2.Open(); // Execute the second command in the second database.
returnValue = 0;
SqlCommand command2 = new SqlCommand(commandText2, connection2);
returnValue = command2.ExecuteNonQuery();
writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
}
catch (Exception ex)
{
// Display information that command2 failed.
writer.WriteLine("returnValue for command2: {0}", returnValue);
writer.WriteLine("Exception Message2: {0}", ex.Message);
}
}
catch (Exception ex)
{
// Display information that command1 failed.
writer.WriteLine("returnValue for command1: {0}", returnValue);
writer.WriteLine("Exception Message1: {0}", ex.Message);
}
} // If an exception has been thrown, Complete will not
// be called and the transaction is rolled back.
scope.Complete();
} // The returnValue is greater than 0 if the transaction committed.
if (returnValue > 0)
{
writer.WriteLine("Transaction was committed.");
}
else
{
// You could write additional business logic here, notify the caller by
// throwing a TransactionAbortedException, or log the failure.
writer.WriteLine("Transaction rolled back.");
} // Display messages.
Console.WriteLine(writer.ToString()); return returnValue;
}
#region 此方法完成【普通的Sql】保存,更新，删除数据工作
/// <summary>
/// 此方法完成【普通的Sql】保存,更新，删除数据工作
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static void SqlOperator(string safeStatement)
{
using (SqlConnection con = new SqlConnection(conn--它我就不多说了))
{
con.Open();
SqlTransaction trans = con.BeginTransaction();
SqlCommand cmd = new SqlCommand(safeStatement, con);
cmd.Transaction = trans;
try
{
int val = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
}
#endregion