C# 数据库事物

SqlServer 数据库本身是支持事物的，但通常我们在C# 代码中也可以做事物。今天写了一个小例子，供初学者参考，也作为自己的笔记。

1.数据库表

USE [DBDemo]
GO

/****** Object:  Table [dbo].[T_Employee]    Script Date: 11/27/2014 21:18:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[T_Employee](
	[EmployeeID] [int] NOT NULL,
	[EmployeeName] [varchar](50) NULL,
	[EmployeeSalary] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

2.C# Code

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Transactions;

namespace TransactionDemo
{
    class Program
    {
        private static SqlConnection _sqlConn;
        private static SqlCommand _cmd;
        private const string SqlConnStr = "Data Source=fuhui-pc;Initial Catalog=DBDemo;Integrated Security=True";

        private static bool Execute(string sql)
        {
            try
            {
                _sqlConn = new SqlConnection(SqlConnStr);
                if (_sqlConn.State == ConnectionState.Closed)
                    _sqlConn.Open();
                _cmd = new SqlCommand
                {
                    Connection = _sqlConn,
                    CommandType = CommandType.Text,
                    CommandText = sql
                };
                return _cmd.ExecuteNonQuery() > 0;
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
            finally
            {
                if(_sqlConn.State==ConnectionState.Open)
                    _sqlConn.Close();
            }
        }

        private static DataTable Query(string sql)
        {
            try
            {
                _sqlConn = new SqlConnection(SqlConnStr);
                if (_sqlConn.State == ConnectionState.Closed)
                    _sqlConn.Open();
                var sda = new SqlDataAdapter(sql, _sqlConn);
                var ds = new DataSet("ds");
                sda.Fill(ds);
                return ds.Tables.Count > 0 ? ds.Tables[0] : null;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                if (_sqlConn.State == ConnectionState.Open)
                    _sqlConn.Close();
            }
        }

        static void Main(string[] args)
        {
            using (var scope = new TransactionScope())
            {
                try
                {
                    string str1 = "insert into T_Employee values(8,'Employee008',8000);";

                    string str2 = "insert into T_Employee values(7,'Employee007',7000);";

                    string str3 = "select * from T_Employee where EmployeeID = 8;";

                    bool r1 = Execute(str1);

                    Console.WriteLine("Insert Employee008 successfully.");

                    string name = Query(str3).Rows[0][1].ToString();

                    Console.WriteLine("Query Employee name = {0}", name);

                    bool r2 = Execute(str2); //will fail

                    Console.ReadKey();
                }
                catch (Exception)
                {
                    scope.Complete();
                }
                
            }
        }
    }
}

插入示例数据

select * from dbo.T_Employee;

insert into T_Employee values(1,'Employee001',1000);
insert into T_Employee values(2,'Employee002',2000);
insert into T_Employee values(3,'Employee003',3000);
insert into T_Employee values(4,'Employee004',4000);
insert into T_Employee values(5,'Employee005',5000);
insert into T_Employee values(6,'Employee006',6000);
insert into T_Employee values(7,'Employee007',7000);

select * from T_Employee where EmployeeID = 8;

从代码中我们可以看到，先插入Employee008数据已经成功了，我们可以查询到数据，但是下面插入Employee007失败了，由于使用了事物，发生了回滚，执行完之后，008这条数据不见了，我们查询数据库也可以得到相同的结果。

这只是实现事物的一种方式，还有其他方式，今后再介绍。