在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
/// <summary> /// SQL Server事务样本类,演示Sql的事务操作 /// 作者:李斌(Camus) /// </summary> public class SQLServerTransactionDemo { private SQLServerTransactionDemo(){} /// <summary> /// 获取SQL Server事务样本类的实例方法 /// </summary> /// <returns>SQL Server事务样本类的实例</returns> public static SQLServerTransactionDemo GetHandle() { return new SQLServerTransactionDemo(); } #region 运行SQL Server事务Run方法 /// <summary> /// 运行SQL Server事务 /// </summary> public void Run() { // 访问Microsoft SQL Server样本数据库Northwind,假设Microsoft SQL Server的sa密码为空 string connectionString=@"Server=(Local);Database=Northwind;UID=sa;PWD=;Persist Security Info=false;"; //创建Connection对象 System.Data.SqlClient.SqlConnection sqlConnection = null; System.Data.SqlClient.SqlTransaction sqlTransaction = null; try { sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString); sqlConnection.Open();//打开Connection // 开始本地事务,Connection.BeginTransaction() // IsolationLevel.ReadCommitted: // 在正在读取数据时保持共享锁,以避免脏读,但是在事务结束之前可以更改数据,从而导致不可重复的读取或幻像数据。 sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted,"SQLTransaction"); //创建Command对象 System.Data.SqlClient.SqlCommand sqlCommand = sqlConnection.CreateCommand(); // 指派Connection和Transaction对象给Command对象 sqlCommand.Connection = sqlConnection; sqlCommand.Transaction = sqlTransaction; //开始执行事务,该事务由命令1 2 3组成. // 执行数据库命令1 sqlCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (200, \'Description\')"; sqlCommand.ExecuteNonQuery(); // 执行数据库命令2 sqlCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (201, \'Description\')"; sqlCommand.ExecuteNonQuery(); // 执行数据库命令3!!!与命令1相同,会出错 sqlCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (200, \'Description\')"; sqlCommand.ExecuteNonQuery(); // 提交事务 sqlTransaction.Commit(); Console.WriteLine("两条数据库命令已经执行完成."); } catch(Exception e) { try { if(sqlTransaction != null) { // 回滚事务 sqlTransaction.Rollback("SQLTransaction"); } } catch (System.Data.SqlClient.SqlException ex) { // 回滚事务失败 if (sqlTransaction.Connection != null) { Console.WriteLine("执行回滚事务时出现 " + ex.GetType() + " 违例!" + ex.Message); } } Console.WriteLine("在执行数据库命令时出现 " + e.GetType() + " 违例!" + e.Message); Console.WriteLine("两条数据库命令均未完成."); } finally { //关闭Connection if(sqlConnection != null) { sqlConnection.Close(); } } } #endregion public static void Main(string[] args) { SQLServerTransactionDemo.GetHandle().Run(); } } |
请发表评论