transactions.cs

东软内部材料（六）ado .net相关

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

namespace Transactions
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class frmTransactions : System.Windows.Forms.Form
	{
		internal System.Windows.Forms.Label Label1;
		internal System.Windows.Forms.Label Label2;
		internal System.Data.OleDb.OleDbCommand cmdSelectOrders;
		internal System.Data.OleDb.OleDbConnection cnAccessNwind;
		internal System.Data.OleDb.OleDbDataAdapter daOrders;
		internal System.Data.OleDb.OleDbCommand cmdInsertOrders;
		internal System.Data.OleDb.OleDbCommand cmdSelectCustomers;
		internal System.Data.OleDb.OleDbDataAdapter daCustomers;
		internal System.Data.OleDb.OleDbCommand cmdInsertCustomers;
		private Transactions.dsCustomerOrders dsCustomerOrders1;
		internal System.Windows.Forms.ListBox lbCustomers;
		internal System.Windows.Forms.DataGrid dgOrders;
		internal System.Windows.Forms.Button btnRollback;
		internal System.Windows.Forms.Button btnCommit;
		internal System.Windows.Forms.Button btnNested;
		internal System.Windows.Forms.Button btnCreate;
		internal System.Windows.Forms.Button btnLoad;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public frmTransactions()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
			this.btnNested.Click += new EventHandler(this.btnNested_Click);
			this.btnCommit.Click += new EventHandler(this.btnCommit_Click);
			this.btnRollback.Click += new EventHandler(this.btnRollback_Click);
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.Label1 = new System.Windows.Forms.Label();
			this.Label2 = new System.Windows.Forms.Label();
			this.cmdSelectOrders = new System.Data.OleDb.OleDbCommand();
			this.cnAccessNwind = new System.Data.OleDb.OleDbConnection();
			this.daOrders = new System.Data.OleDb.OleDbDataAdapter();
			this.cmdInsertOrders = new System.Data.OleDb.OleDbCommand();
			this.cmdSelectCustomers = new System.Data.OleDb.OleDbCommand();
			this.daCustomers = new System.Data.OleDb.OleDbDataAdapter();
			this.cmdInsertCustomers = new System.Data.OleDb.OleDbCommand();
			this.dsCustomerOrders1 = new Transactions.dsCustomerOrders();
			this.lbCustomers = new System.Windows.Forms.ListBox();
			this.dgOrders = new System.Windows.Forms.DataGrid();
			this.btnRollback = new System.Windows.Forms.Button();
			this.btnCommit = new System.Windows.Forms.Button();
			this.btnNested = new System.Windows.Forms.Button();
			this.btnCreate = new System.Windows.Forms.Button();
			this.btnLoad = new System.Windows.Forms.Button();
			((System.ComponentModel.ISupportInitialize)(this.dsCustomerOrders1)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.dgOrders)).BeginInit();
			this.SuspendLayout();
			// 
			// Label1
			// 
			this.Label1.AutoSize = true;
			this.Label1.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.Label1.Location = new System.Drawing.Point(8, 8);
			this.Label1.Name = "Label1";
			this.Label1.Size = new System.Drawing.Size(64, 13);
			this.Label1.TabIndex = 1;
			this.Label1.Text = "Customers:";
			// 
			// Label2
			// 
			this.Label2.AutoSize = true;
			this.Label2.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.Label2.Location = new System.Drawing.Point(8, 128);
			this.Label2.Name = "Label2";
			this.Label2.Size = new System.Drawing.Size(43, 13);
			this.Label2.TabIndex = 2;
			this.Label2.Text = "Orders:";
			// 
			// cmdSelectOrders
			// 
			this.cmdSelectOrders.CommandText = "SELECT OrderID, CustomerID, OrderDate FROM Orders";
			this.cmdSelectOrders.Connection = this.cnAccessNwind;
			// 
			// cnAccessNwind
			// 
			this.cnAccessNwind.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\adonetsbs\SampleDBs\nwind.mdb;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
			// 
			// daOrders
			// 
			this.daOrders.InsertCommand = this.cmdInsertOrders;
			this.daOrders.SelectCommand = this.cmdSelectOrders;
			this.daOrders.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
																							   new System.Data.Common.DataTableMapping("Table", "Orders", new System.Data.Common.DataColumnMapping[] {
																																																		 new System.Data.Common.DataColumnMapping("OrderID", "OrderID"),
																																																		 new System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"),
																																																		 new System.Data.Common.DataColumnMapping("OrderDate", "OrderDate")})});
			// 
			// cmdInsertOrders
			// 
			this.cmdInsertOrders.CommandText = "INSERT INTO Jan01Orders (CustomerID, OrderDate) VALUES (?, ?)";
			this.cmdInsertOrders.Connection = this.cnAccessNwind;
			this.cmdInsertOrders.Parameters.Add(new System.Data.OleDb.OleDbParameter("CustomerID", System.Data.OleDb.OleDbType.Char, 5, "CustomerID"));
			this.cmdInsertOrders.Parameters.Add(new System.Data.OleDb.OleDbParameter("OrderDate", System.Data.OleDb.OleDbType.Date, 0, "OrderDate"));
			// 
			// cmdSelectCustomers
			// 
			this.cmdSelectCustomers.CommandText = "SELECT CompanyName, CustomerID, FullID FROM CustomerList";
			this.cmdSelectCustomers.Connection = this.cnAccessNwind;
			// 
			// daCustomers
			// 
			this.daCustomers.InsertCommand = this.cmdInsertCustomers;
			this.daCustomers.SelectCommand = this.cmdSelectCustomers;
			this.daCustomers.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
																								  new System.Data.Common.DataTableMapping("Table", "CustomerList", new System.Data.Common.DataColumnMapping[] {
																																																				  new System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"),
																																																				  new System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"),
																																																				  new System.Data.Common.DataColumnMapping("FullID", "FullID")})});
			// 
			// cmdInsertCustomers
			// 
			this.cmdInsertCustomers.CommandText = "INSERT INTO Customers (CompanyName, CustomerID) VALUES (?, ?)";
			this.cmdInsertCustomers.Connection = this.cnAccessNwind;
			this.cmdInsertCustomers.Parameters.Add(new System.Data.OleDb.OleDbParameter("CompanyName", System.Data.OleDb.OleDbType.Char, 40, "CompanyName"));
			this.cmdInsertCustomers.Parameters.Add(new System.Data.OleDb.OleDbParameter("CustomerID", System.Data.OleDb.OleDbType.Char, 5, "CustomerID"));
			// 
			// dsCustomerOrders1
			// 
			this.dsCustomerOrders1.DataSetName = "dsCustomerOrders";
			this.dsCustomerOrders1.Locale = new System.Globalization.CultureInfo("en-US");
			this.dsCustomerOrders1.Namespace = "http://www.tempuri.org/dsCustomerOrders.xsd";
			// 
			// lbCustomers
			// 
			this.lbCustomers.DataSource = this.dsCustomerOrders1.CustomerList;
			this.lbCustomers.DisplayMember = "FullID";
			this.lbCustomers.Location = new System.Drawing.Point(8, 24);
			this.lbCustomers.Name = "lbCustomers";
			this.lbCustomers.Size = new System.Drawing.Size(280, 95);
			this.lbCustomers.TabIndex = 3;
			this.lbCustomers.ValueMember = "CustomerID";
			// 
			// dgOrders
			// 
			this.dgOrders.DataMember = "CustomerList.CustomerListOrders";
			this.dgOrders.DataSource = this.dsCustomerOrders1;
			this.dgOrders.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dgOrders.Location = new System.Drawing.Point(8, 144);
			this.dgOrders.Name = "dgOrders";
			this.dgOrders.Size = new System.Drawing.Size(280, 128);
			this.dgOrders.TabIndex = 4;
			// 
			// btnRollback
			// 
			this.btnRollback.Location = new System.Drawing.Point(304, 152);
			this.btnRollback.Name = "btnRollback";
			this.btnRollback.Size = new System.Drawing.Size(96, 23);
			this.btnRollback.TabIndex = 12;
			this.btnRollback.Text = "Rollback";
			// 
			// btnCommit
			// 
			this.btnCommit.Location = new System.Drawing.Point(304, 120);
			this.btnCommit.Name = "btnCommit";
			this.btnCommit.Size = new System.Drawing.Size(96, 23);
			this.btnCommit.TabIndex = 11;
			this.btnCommit.Text = "Commit";
			// 
			// btnNested
			// 
			this.btnNested.Location = new System.Drawing.Point(304, 88);
			this.btnNested.Name = "btnNested";
			this.btnNested.Size = new System.Drawing.Size(96, 23);
			this.btnNested.TabIndex = 10;
			this.btnNested.Text = "Create Nested";
			// 
			// btnCreate
			// 
			this.btnCreate.Location = new System.Drawing.Point(304, 56);
			this.btnCreate.Name = "btnCreate";
			this.btnCreate.Size = new System.Drawing.Size(96, 23);
			this.btnCreate.TabIndex = 9;
			this.btnCreate.Text = "Create";
			this.btnCreate.Click += new System.EventHandler(this.btnCreate_Click);
			// 
			// btnLoad
			// 
			this.btnLoad.Location = new System.Drawing.Point(304, 24);
			this.btnLoad.Name = "btnLoad";
			this.btnLoad.Size = new System.Drawing.Size(96, 23);
			this.btnLoad.TabIndex = 8;
			this.btnLoad.Text = "Load Data";
			this.btnLoad.Click += new System.EventHandler(this.btnLoad_Click);
			// 
			// frmTransactions
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(408, 285);
			this.Controls.AddRange(new System.Windows.Forms.Control[] {
																		  this.btnRollback,
																		  this.btnCommit,
																		  this.btnNested,
																		  this.btnCreate,
																		  this.btnLoad,
																		  this.dgOrders,
																		  this.lbCustomers,
																		  this.Label2,
																		  this.Label1});
			this.Name = "frmTransactions";
			this.Text = "Transactions";
			((System.ComponentModel.ISupportInitialize)(this.dsCustomerOrders1)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dgOrders)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new frmTransactions());
		}



#region Utility Functions
		private void btnLoad_Click(object sender, System.EventArgs e)
		{
			this.dsCustomerOrders1.Clear();
			this.daCustomers.Fill(this.dsCustomerOrders1.CustomerList);
			this.daOrders.Fill(this.dsCustomerOrders1.Orders);
		}

		private void AddRows(string newCust)
		{
			System.Data.DataRow newRow;

			newRow = this.dsCustomerOrders1.CustomerList.NewRow();
            newRow["CompanyName"] = "A New Customer Record";
            newRow["CustomerID"] = newCust;
	        this.dsCustomerOrders1.CustomerList.Rows.Add(newRow);

		    newRow = this.dsCustomerOrders1.Orders.NewRow();
            newRow["CustomerID"] = newCust;
            newRow["OrderDate"] = System.DateTime.Today;
	        this.dsCustomerOrders1.Orders.Rows.Add(newRow);
		}
			
#endregion

		private void btnCreate_Click(object sender, System.EventArgs e)
		{
			string strMsg;
			System.Data.OleDb.OleDbTransaction trnNew;

			this.cnAccessNwind.Open();
			trnNew = this.cnAccessNwind.BeginTransaction();
			strMsg = "Isolation Level: ";
			strMsg += trnNew.IsolationLevel.ToString();
			MessageBox.Show(strMsg);
			this.cnAccessNwind.Close();
		}

		private void btnNested_Click(object sender, System.EventArgs e)
		{
			string strMsg;
			System.Data.OleDb.OleDbTransaction trnMaster;
			System.Data.OleDb.OleDbTransaction trnChild;

			this.cnAccessNwind.Open();

			trnMaster = this.cnAccessNwind.BeginTransaction();

			trnChild = trnMaster.Begin();
			strMsg = "Child Isolation Level: ";
			strMsg += trnChild.IsolationLevel.ToString();
			MessageBox.Show(strMsg);

			this.cnAccessNwind.Close();
		}

		private void btnCommit_Click(object sender, System.EventArgs e)
		{
			System.Data.OleDb.OleDbTransaction trnNew;

			AddRows("AAAA1");

			this.cnAccessNwind.Open();
			trnNew = this.cnAccessNwind.BeginTransaction();
			this.daCustomers.InsertCommand.Transaction = trnNew;
			this.daOrders.InsertCommand.Transaction = trnNew;
			try
			{
				this.daCustomers.Update(this.dsCustomerOrders1.CustomerList);
				this.daOrders.Update(this.dsCustomerOrders1.Orders);
				trnNew.Commit();
				MessageBox.Show("Transaction Committed");
			}
			catch (System.Data.OleDb.OleDbException err)
			{
				trnNew.Rollback();
				MessageBox.Show(err.Message.ToString());
			}
			catch
			{
				this.cnAccessNwind.Close();
			}
		}
		private void btnRollback_Click(object sender, System.EventArgs e)
		{
			System.Data.OleDb.OleDbTransaction trnNew;

			AddRows("AAAA2");

			this.cnAccessNwind.Open();
			trnNew = this.cnAccessNwind.BeginTransaction();
			this.daCustomers.InsertCommand.Transaction = trnNew;
			this.daOrders.InsertCommand.Transaction = trnNew;
			try
			{
				this.daOrders.Update(this.dsCustomerOrders1.Orders);
				this.daCustomers.Update(this.dsCustomerOrders1.CustomerList);
				trnNew.Commit();
				MessageBox.Show("Transaction Committed");
			}
			catch (System.Data.OleDb.OleDbException err)
			{
				trnNew.Rollback();
				MessageBox.Show(err.Message.ToString());
			}
			catch
			{
				this.cnAccessNwind.Close();
			}
		}
	}
}