在ADO.NET编写事务

来源：互联网发布：linux 磁盘挂载编辑：程序博客网时间：2024/05/22 04:55

1新建一个Windows应用程序项目

2向窗体添加3个标签、3个textbox和一个buttom

3为buttom按钮添加一个click事件。

添加以下代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace chapter_8
{
    public partial class Transaction : Form
    {
        public Transaction()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(@"
            data source = ./WB_JAMES;
            integrated security = SSPI;
            database = master"
            );
            //insert statement
            string sqlins = @"
                insert into Students(Sno,Sname)
                values(@newsno,@newsname)";
            //////////////
            //delete statement
            string sqldel = @"
                delete from Students
                where Sage = @oldsage";

            //open connection
            conn.Open();
            //begin transaction
            SqlTransaction sqltrans = conn.BeginTransaction();
            //////////////
            try
            {

                SqlCommand cmdins = conn.CreateCommand();
                cmdins.CommandText = sqlins;
                cmdins.Transaction = sqltrans;
                cmdins.Parameters.Add("@newsno", System.Data.SqlDbType.NVarChar, 5);
                cmdins.Parameters.Add("@newsname", System.Data.SqlDbType.NVarChar, 30);
                //create delete command
                SqlCommand cmddel = conn.CreateCommand();
                cmddel.CommandText = sqldel;
                cmddel.Transaction = sqltrans;
                cmddel.Parameters.Add("@oldsage", System.Data.SqlDbType.Int);

                //add student
                cmdins.Parameters["@newsno"].Value = textBox1.Text;
                cmdins.Parameters["@newsname"].Value = textBox2.Text;
                cmdins.ExecuteNonQuery();

                //delete student
                cmddel.Parameters["@oldsage"].Value = textBox3.Text;
                cmddel.ExecuteNonQuery();

                //commit transaction
                sqltrans.Commit();
                MessageBox.Show("Transaction Commit");
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
            //roll back
                sqltrans.Rollback();
                MessageBox.Show("Transaction rolled back/n" + ex.Message,
                    "Rollback Transaction");
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("System Error/n" + ex.Message, "Error");
            }
            finally
            {
                conn.Close();
            }
        }
    }
}