C#&&SQL 事务

存储过程写法

use UserManage

ALTER PROC ADDUser 
AS
BEGIN
    begin tran adduser--开始事务
    begin try
            INSERT INTO USERINFO VALUES('Root','123','FALSE')
            INSERT INTO USERBED VALUES('1',@@IDENTITY)
            commit tran;--提交事务,注意加分号
    end try
    begin catch--当遇到错误时执行catch
        rollback tran;--回滚事务,注意加分号
    end catch
END

EXEC ADDUser--执行存储过程

---

C#写法

 private void button1_Click(object sender, EventArgs e)//回滚
        {
            string strCon = "Data Source=.;Initial Catalog=UserManage;Integrated Security=True";
            SqlConnection conn = new SqlConnection(strCon);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction tran;//创建事务对象
            tran = conn.BeginTransaction();//开始事务
            cmd.Transaction = tran;//为一个即将开始的事务指派对象
            cmd.Connection = conn;
            try
            {
                cmd.CommandText = "INSERT INTO USERINFO VALUES('Root','123','FALSE')";
                cmd.ExecuteNonQuery();//执行sql语句
                cmd.CommandText = "INSERT INTO USERBED VALUES('文字',@@IDENTITY)";//'文字'应为数字，类型不正确
                cmd.ExecuteNonQuery();
                tran.Commit();//提交事务

            }
            catch (Exception)
            {
                tran.Rollback();//回滚事务
                MessageBox.Show("插入数据有误，已回滚");
            }
            finally
            {
                // TODO:  这行代码将数据加载到表“userManageDataSet8.UserBed”中。
                this.userBedTableAdapter.Fill(this.userManageDataSet8.UserBed);
                // TODO:  这行代码将数据加载到表“userManageDataSet7.UserInfo”中。
                this.userInfoTableAdapter.Fill(this.userManageDataSet7.UserInfo);
                conn.Close();
            }
        }

数据库

--CREATE DATABASE UserManage
--use UserManage
--CREATE TABLE UserInfo
--(
--  UserID int primary key identity(1,1),
--  UserName nvarchar(20) not null,
--  UserPassword nvarchar(20) not null,
--  IsAllow BIT NOT NULL
--)

--CREATE TABLE UserBed
--(
--  RoomID int primary key identity(1,1),
--  BedID int not null,
--  UserID int not null 
--)
SELECT * FROM UserInfo
SELECT * FROM UserBed
Truncate table userinfo
truncate table userbed