C# 数据库事物

来源：互联网发布：淘宝代销手动分账编辑：程序博客网时间：2024/06/12 01:07

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

1.数据库表

USE [DBDemo]GO/****** Object:  Table [dbo].[T_Employee]    Script Date: 11/27/2014 21:18:58 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGO

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这条数据不见了，我们查询数据库也可以得到相同的结果。

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

0 0