dataviews.cs

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

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

namespace DataViews
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class frmDataViews : System.Windows.Forms.Form
	{
		private System.Windows.Forms.Label lblEmployees;
		private System.Windows.Forms.Label lblClients;
		private System.Windows.Forms.Label lblOrders;
		private System.Windows.Forms.ListBox lbEmployees;
		private System.Windows.Forms.ListBox lbClients;
		private System.Windows.Forms.DataGrid dgOrders;
		private System.Windows.Forms.Button btnCreate;
		private System.Windows.Forms.Button btnRowState;
		private System.Windows.Forms.Button btnSort;
		private System.Windows.Forms.Button btnFind;
		private System.Data.SqlClient.SqlConnection cnNorthwind;
		private System.Data.SqlClient.SqlCommand cmdSelectCustomers;
		private System.Data.SqlClient.SqlDataAdapter daCustomers;
		private System.Data.SqlClient.SqlCommand cmdSelectEmployees;
		private System.Data.SqlClient.SqlDataAdapter daEmployees;
		private System.Data.SqlClient.SqlCommand cmdSelectOrders;
		private System.Data.SqlClient.SqlDataAdapter daOrders;
		private DataViews.dsMaster dsMaster1;
		private System.Data.DataView dvOrders;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

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

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
			this.daCustomers.Fill(this.dsMaster1.CustomerList);
			this.daOrders.Fill(this.dsMaster1.OrderTotals);
			this.daEmployees.Fill(this.dsMaster1.EmployeeList);

			this.btnSort.Click += new EventHandler(this.btnSort_Click);
			this.btnRowState.Click += new EventHandler(this.btnRowState_Click);
			this.btnFind.Click += new EventHandler(this.btnFind_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.lblEmployees = new System.Windows.Forms.Label();
			this.lblClients = new System.Windows.Forms.Label();
			this.lblOrders = new System.Windows.Forms.Label();
			this.lbEmployees = new System.Windows.Forms.ListBox();
			this.dsMaster1 = new DataViews.dsMaster();
			this.lbClients = new System.Windows.Forms.ListBox();
			this.dgOrders = new System.Windows.Forms.DataGrid();
			this.btnCreate = new System.Windows.Forms.Button();
			this.btnRowState = new System.Windows.Forms.Button();
			this.btnSort = new System.Windows.Forms.Button();
			this.btnFind = new System.Windows.Forms.Button();
			this.cnNorthwind = new System.Data.SqlClient.SqlConnection();
			this.cmdSelectCustomers = new System.Data.SqlClient.SqlCommand();
			this.daCustomers = new System.Data.SqlClient.SqlDataAdapter();
			this.cmdSelectEmployees = new System.Data.SqlClient.SqlCommand();
			this.daEmployees = new System.Data.SqlClient.SqlDataAdapter();
			this.cmdSelectOrders = new System.Data.SqlClient.SqlCommand();
			this.daOrders = new System.Data.SqlClient.SqlDataAdapter();
			this.dvOrders = new System.Data.DataView();
			((System.ComponentModel.ISupportInitialize)(this.dsMaster1)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.dgOrders)).BeginInit();
			((System.ComponentModel.ISupportInitialize)(this.dvOrders)).BeginInit();
			this.SuspendLayout();
			// 
			// lblEmployees
			// 
			this.lblEmployees.AutoSize = true;
			this.lblEmployees.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.lblEmployees.Location = new System.Drawing.Point(8, 8);
			this.lblEmployees.Name = "lblEmployees";
			this.lblEmployees.Size = new System.Drawing.Size(66, 13);
			this.lblEmployees.TabIndex = 0;
			this.lblEmployees.Text = "Employees:";
			// 
			// lblClients
			// 
			this.lblClients.AutoSize = true;
			this.lblClients.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.lblClients.Location = new System.Drawing.Point(200, 8);
			this.lblClients.Name = "lblClients";
			this.lblClients.Size = new System.Drawing.Size(44, 13);
			this.lblClients.TabIndex = 1;
			this.lblClients.Text = "Clients:";
			// 
			// lblOrders
			// 
			this.lblOrders.AutoSize = true;
			this.lblOrders.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.lblOrders.Location = new System.Drawing.Point(8, 136);
			this.lblOrders.Name = "lblOrders";
			this.lblOrders.Size = new System.Drawing.Size(43, 13);
			this.lblOrders.TabIndex = 2;
			this.lblOrders.Text = "Orders:";
			// 
			// lbEmployees
			// 
			this.lbEmployees.DataSource = this.dsMaster1.EmployeeList;
			this.lbEmployees.DisplayMember = "FullName";
			this.lbEmployees.Location = new System.Drawing.Point(8, 24);
			this.lbEmployees.Name = "lbEmployees";
			this.lbEmployees.Size = new System.Drawing.Size(184, 95);
			this.lbEmployees.TabIndex = 3;
			this.lbEmployees.ValueMember = "EmployeeID";
			// 
			// dsMaster1
			// 
			this.dsMaster1.DataSetName = "dsMaster";
			this.dsMaster1.Locale = new System.Globalization.CultureInfo("en-US");
			this.dsMaster1.Namespace = "http://www.tempuri.org/dsMaster.xsd";
			// 
			// lbClients
			// 
			this.lbClients.DataSource = this.dsMaster1.CustomerList;
			this.lbClients.DisplayMember = "CompanyName";
			this.lbClients.Location = new System.Drawing.Point(200, 24);
			this.lbClients.Name = "lbClients";
			this.lbClients.Size = new System.Drawing.Size(200, 95);
			this.lbClients.TabIndex = 4;
			this.lbClients.ValueMember = "CustomerID";
			// 
			// dgOrders
			// 
			this.dgOrders.DataMember = "";
			this.dgOrders.DataSource = this.dvOrders;
			this.dgOrders.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dgOrders.Location = new System.Drawing.Point(8, 152);
			this.dgOrders.Name = "dgOrders";
			this.dgOrders.Size = new System.Drawing.Size(392, 208);
			this.dgOrders.TabIndex = 5;
			// 
			// btnCreate
			// 
			this.btnCreate.Location = new System.Drawing.Point(416, 24);
			this.btnCreate.Name = "btnCreate";
			this.btnCreate.Size = new System.Drawing.Size(96, 24);
			this.btnCreate.TabIndex = 6;
			this.btnCreate.Text = "Create";
			this.btnCreate.Click += new System.EventHandler(this.btnCreate_Click);
			// 
			// btnRowState
			// 
			this.btnRowState.Location = new System.Drawing.Point(416, 56);
			this.btnRowState.Name = "btnRowState";
			this.btnRowState.Size = new System.Drawing.Size(96, 24);
			this.btnRowState.TabIndex = 7;
			this.btnRowState.Text = "Row State";
			// 
			// btnSort
			// 
			this.btnSort.Location = new System.Drawing.Point(416, 88);
			this.btnSort.Name = "btnSort";
			this.btnSort.Size = new System.Drawing.Size(96, 23);
			this.btnSort.TabIndex = 8;
			this.btnSort.Text = "Sort";
			// 
			// btnFind
			// 
			this.btnFind.Location = new System.Drawing.Point(416, 120);
			this.btnFind.Name = "btnFind";
			this.btnFind.Size = new System.Drawing.Size(96, 23);
			this.btnFind.TabIndex = 9;
			this.btnFind.Text = "Find";
			// 
			// cnNorthwind
			// 
			this.cnNorthwind.ConnectionString = "data source=localhost;initial catalog=nwind;integrated security=SSPI;persist " +
				"security info=False;packet size=4096";
			// 
			// cmdSelectCustomers
			// 
			this.cmdSelectCustomers.CommandText = "SELECT CustomerID, CompanyName, City, Country, PostalCode, Region FROM CustomerLi" +
				"st";
			this.cmdSelectCustomers.Connection = this.cnNorthwind;
			// 
			// daCustomers
			// 
			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("City", "City"),
																																																				  new System.Data.Common.DataColumnMapping("Country", "Country"),
																																																				  new System.Data.Common.DataColumnMapping("PostalCode", "PostalCode"),
																																																				  new System.Data.Common.DataColumnMapping("Region", "Region")})});
			// 
			// cmdSelectEmployees
			// 
			this.cmdSelectEmployees.CommandText = "SELECT EmployeeID, FirstName, LastName FROM EmployeeList";
			this.cmdSelectEmployees.Connection = this.cnNorthwind;
			// 
			// daEmployees
			// 
			this.daEmployees.SelectCommand = this.cmdSelectEmployees;
			this.daEmployees.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
																								  new System.Data.Common.DataTableMapping("Table", "EmployeeList", new System.Data.Common.DataColumnMapping[] {
																																																				  new System.Data.Common.DataColumnMapping("EmployeeID", "EmployeeID"),
																																																				  new System.Data.Common.DataColumnMapping("FirstName", "FirstName"),
																																																				  new System.Data.Common.DataColumnMapping("LastName", "LastName")})});
			// 
			// cmdSelectOrders
			// 
			this.cmdSelectOrders.CommandText = "SELECT EmployeeID, CustomerID, OrderID, OrderDate, Subtotal FROM OrderTotals";
			this.cmdSelectOrders.Connection = this.cnNorthwind;
			// 
			// daOrders
			// 
			this.daOrders.SelectCommand = this.cmdSelectOrders;
			this.daOrders.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
																							   new System.Data.Common.DataTableMapping("Table", "OrderTotals", new System.Data.Common.DataColumnMapping[] {
																																																			  new System.Data.Common.DataColumnMapping("EmployeeID", "EmployeeID"),
																																																			  new System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"),
																																																			  new System.Data.Common.DataColumnMapping("OrderID", "OrderID"),
																																																			  new System.Data.Common.DataColumnMapping("OrderDate", "OrderDate"),
																																																			  new System.Data.Common.DataColumnMapping("Subtotal", "Subtotal")})});
			// 
			// dvOrders
			// 
			this.dvOrders.Sort = "OrderID";
			this.dvOrders.Table = this.dsMaster1.OrderTotals;
			// 
			// frmDataViews
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(520, 373);
			this.Controls.AddRange(new System.Windows.Forms.Control[] {
																		  this.btnFind,
																		  this.btnSort,
																		  this.btnRowState,
																		  this.btnCreate,
																		  this.dgOrders,
																		  this.lbClients,
																		  this.lbEmployees,
																		  this.lblOrders,
																		  this.lblClients,
																		  this.lblEmployees});
			this.Name = "frmDataViews";
			this.Text = "DataViews";
			((System.ComponentModel.ISupportInitialize)(this.dsMaster1)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dgOrders)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dvOrders)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

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

		private void btnCreate_Click(object sender, System.EventArgs e)
		{
			System.Data.DataRowView drCurrent;
			System.Data.DataView dvNew;

			dvNew = new System.Data.DataView();

			//retrieve the selected row in lbOrders
			drCurrent = (System.Data.DataRowView)this.lbClients.SelectedItem;
		
			//configure the dataview
			dvNew.Table = this.dsMaster1.OrderTotals;
			dvNew.RowFilter = "CustomerID = '" + drCurrent[0] + "'";

			//rebind the datagrid
			this.dgOrders.DataSource = dvNew;

		}


		private void btnSort_Click (object sender, System.EventArgs e)
		{
			//Change the sort order
			this.dvOrders.Sort = "EmployeeID, CustomerID, OrderID DESC";

			//Refresh the datagrid
			this.dgOrders.Refresh();
		}


		private void btnRowState_Click (object sender, System.EventArgs e)
		{
			System.Data.DataRowView drNew;

			//Add a new row
			drNew = this.dvOrders.AddNew();
			drNew["CustomerID"] = "AFLKI";
			drNew["EmployeeID"] = 1;
			drNew["OrderID"] = 0;

			//Set the RowStateFilter
			this.dvOrders.RowStateFilter = DataViewRowState.Added;

			//Refresh the datagrid
			this.dgOrders.Refresh();
		}

		private void btnFind_Click (object sender, System.EventArgs e)
		{
			int idxFound;
			string strMessage;

			idxFound = this.dvOrders.Find(10255);

			strMessage = "The OrderID is " + 
				this.dvOrders[idxFound]["OrderID"];
			strMessage += "\nThe CustomerID is " +
				this.dvOrders[idxFound]["CustomerID"];
			strMessage += "\nThe EmployeeID is " +
				this.dvOrders[idxFound]["EmployeeID"];
			MessageBox.Show(strMessage);
		}
	}
}