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();
- }
- {
- 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();
- }
- }
- begin transaction
- save transaction A
- insert into demo values('BB','B term')
- rollback TRANSACTION A
- create table demo2(name varchar(10),age int)
- insert into demo2(name,age) values('lis',1)
- rollback transaction
- insert into demo values('BB','B term')
- commit TRANSACTION A
- commit TRANSACTION
4、注意
1。事务必须在连接打开后BeginTransaction();
2.事务添加到SqlCommand(sqlCommand.Transaction = sqlTransaction; )
3、其他数据库对应做相应调整
4、可以用微软提供的一个dll,很方便