1、创建事务的结构
SqlConnection sqlConnection = new SqlConnection(); ...初始化连接 // 开启事务 SqlTransaction sqlTransaction = sqlConnection.BeginTransaction(); // 将事务应用于Command SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = sqlConnection; sqlCommand.Transaction = sqlTransaction; try { // 利用sqlcommand进行数据操作 ... // 成功提交 sqlTransaction.Commit(); } catch(Exception ex) { // 出错回滚 sqlTransaction.Rollback(); }
2、简单例子
{ DataTable dt = new DataTable(); System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection("连接字符串"); System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand(); cm.Connection = cnn; cnn.Open(); System.Data.SqlClient.SqlTransaction trans = cnn.BeginTransaction(); try { foreach(DataRow dr in dt.Rows) { cm.CommandText = "update [表] set [数量] = @amount where productID = @productID"; cm.Parameters.Add("@amount",SqlDbType.Int); cm.Parameters["@amount"].Value = Convert.ToInt32(dr["amount"]); cm.Parameters.Add("@productID",SqlDbType.VarChar); cm.Parameters["@productID"].Value = dr["productID"].ToString(); cm.ExecuteNonQuery(); } trans.Commit(); } catch { trans.Rollback(); } finally { cnn.Close(); trans.Dispose(); cnn.Dispose(); } }
