datatables.cs

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

			this.MasterID.AllowDBNull = false;
			this.MasterID.AutoIncrement = true;
			this.MasterID.Caption = "MasterID";
			this.MasterID.ColumnName = "MasterID";
			this.MasterID.DataType = typeof(int);
			// 
			// MasterValue
			// 
			this.MasterValue.Caption = "MasterValue";
			this.MasterValue.ColumnName = "MasterValue";
			// 
			// dtChild
			// 
			this.dtChild.Columns.AddRange(new System.Data.DataColumn[] {
																		   this.ChildID,
																		   this.MasterLink,
																		   this.ChildValue});
			this.dtChild.TableName = "dtChild";
			// 
			// ChildID
			// 
			this.ChildID.AllowDBNull = false;
			this.ChildID.AutoIncrement = true;
			this.ChildID.Caption = "ChildID";
			this.ChildID.ColumnName = "ChildID";
			this.ChildID.DataType = typeof(int);
			// 
			// MasterLink
			// 
			this.MasterLink.AllowDBNull = false;
			this.MasterLink.Caption = "MasterLink";
			this.MasterLink.ColumnName = "MasterLink";
			this.MasterLink.DataType = typeof(int);
			// 
			// ChildValue
			// 
			this.ChildValue.Caption = "ChildValue";
			this.ChildValue.ColumnName = "ChildValue";
			// 
			// 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")})});
			// 
			// cmdSelectEmployees
			// 
			this.cmdSelectEmployees.CommandText = "SELECT EmployeeID, FirstName, LastName FROM EmployeeList";
			this.cmdSelectEmployees.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("CustomerID", "CustomerID"),
																																																			  new System.Data.Common.DataColumnMapping("OrderID", "OrderID"),
																																																			  new System.Data.Common.DataColumnMapping("OrderDate", "OrderDate"),
																																																			  new System.Data.Common.DataColumnMapping("Subtotal", "Subtotal")})});
			// 
			// cmdSelectOrders
			// 
			this.cmdSelectOrders.CommandText = "SELECT EmployeeID, CustomerID, OrderID, OrderDate, Subtotal FROM OrderTotals";
			this.cmdSelectOrders.Connection = this.cnNorthwind;
			// 
			// btnTable
			// 
			this.btnTable.Location = new System.Drawing.Point(416, 24);
			this.btnTable.Name = "btnTable";
			this.btnTable.Size = new System.Drawing.Size(96, 24);
			this.btnTable.TabIndex = 14;
			this.btnTable.Text = "Add Table";
			this.btnTable.Click += new System.EventHandler(this.btnTable_Click);
			// 
			// frmDataSets
			// 
			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.btnTable,
																		  this.btnCalculate,
																		  this.btnAddRow,
																		  this.btnVersion,
																		  this.btnSchema,
																		  this.btnForeign,
																		  this.btnUnique,
																		  this.btnSelect,
																		  this.btnDataSet,
																		  this.dgOrders,
																		  this.lbClients,
																		  this.lbEmployees,
																		  this.label3,
																		  this.label2,
																		  this.label1});
			this.Name = "frmDataSets";
			this.Text = "DataTables";
			((System.ComponentModel.ISupportInitialize)(this.dsMaster1)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dgOrders)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dsUntyped)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dtMaster)).EndInit();
			((System.ComponentModel.ISupportInitialize)(this.dtChild)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

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

		private void btnTable_Click(object sender, System.EventArgs e)
		{
			string strMessage;

			//Create the table
			System.Data.DataTable dtEmployees;
			dtEmployees = new System.Data.DataTable("Employees");

			strMessage = "The table name is ";
			strMessage += dtEmployees.TableName.ToString();

			MessageBox.Show(strMessage);
		}


		private void btnDataSet_Click(object sender, System.EventArgs e)
		{
			string strMessage;

			//Create the table
			this.dsEmployees.Tables.Add();

			strMessage = "The table name is ";
			strMessage += this.dsEmployees.Tables[0].TableName.ToString();
			MessageBox.Show(strMessage);
		}


		private void btnSchema_Click(object sender, System.EventArgs e)
		{
			string strMessage;
			System.Data.DataTable dt;

			dt = this.dsEmployees.Tables.Add("Employees");
			
			this.daEmployees.FillSchema(dt,
				SchemaType.Source);

			strMessage = "Primary Key:  ";
			strMessage += dt.PrimaryKey[0].ColumnName.ToString();
			strMessage += "\nConstraint Count: ";
			strMessage += dt.Constraints[0].ConstraintName.ToString();
			MessageBox.Show(strMessage);		
		}


		private void btnCalculate_Click(object sender, System.EventArgs e)
		{
			System.Data.DataColumn dcName;

			//Create the table
			this.dsEmployees.Tables.Add("Employees");

			//Fill the data from the dataset
			this.daEmployees.Fill(this.dsEmployees.Tables[0]);

			//Create the column
			dcName = new System.Data.DataColumn("Name");
			dcName.DataType = System.Type.GetType("System.String");
			dcName.Expression = "FirstName + ' ' + LastName";

			//Add the calculated column
			this.dsEmployees.Tables["Employees"].Columns.Add(dcName);

			//Bind to the listbox
			this.lbEmployees.DataSource = this.dsEmployees.Tables["Employees"];
			this.lbEmployees.DisplayMember = "Name";
		}


		private void btnAddRow_Click(object sender, System.EventArgs e)
		{
			System.Data.DataRow drNew;

			//Create the new row
			drNew = this.dsMaster1.CustomerList.NewRow();
			drNew["CustomerID"] = "ANEWR";
			drNew["CompanyName"] = "A New Row";

			//Add row to table
			this.dsMaster1.CustomerList.Rows.Add(drNew);

			//Refresh the display
			this.lbClients.Refresh();
		}

		
		private void btnVersion_Click(object sender, System.EventArgs e)
		{
			string strMessage;
			System.Data.DataRow dr;

			dr = this.dsMaster1.CustomerList.Rows[0];
			dr["CustomerID"] = "NEWVAL";

			strMessage = "The RowState is " + dr.RowState.ToString();
			strMessage += "\nThe original value was ";
			strMessage += dr["CustomerID", DataRowVersion.Original];
			strMessage += "\nThe new value is ";
			strMessage += dr["CustomerID", DataRowVersion.Current];
			
			MessageBox.Show(strMessage);

		}


		private void btnForeign_Click(object sender, System.EventArgs e)
		{
			string strMessage;
			System.Data.ForeignKeyConstraint fkNew;
			System.Data.DataSet ds = this.dsUntyped;

		    fkNew = new System.Data.ForeignKeyConstraint("NewFK", 
				ds.Tables["dtMaster"].Columns["MasterID"], 
				ds.Tables["dtChild"].Columns["MasterLink"]);
			ds.Tables["dtChild"].Constraints.Add(fkNew);

			strMessage = "The new constraint is called ";
		    strMessage += 
				ds.Tables["dtChild"].Constraints[0].ConstraintName.ToString();
			MessageBox.Show(strMessage);
		}


		private void btnUnique_Click(object sender, System.EventArgs e)
		{
			string strMessage;
			System.Data.UniqueConstraint ucNew;
			System.Data.DataTable dt = this.dsUntyped.Tables["dtMaster"];

			ucNew = new System.Data.UniqueConstraint("NewUnique",
				dt.Columns["MasterValue"]);
			dt.Constraints.Add(ucNew);

			strMessage = "The new constraint is called ";
			strMessage += dt.Constraints["NewUnique"].ConstraintName.ToString();
			MessageBox.Show(strMessage);
		}


		private void btnSelect_Click(object sender, System.EventArgs e)
		{
			System.Data.DataRow[] drFound;
			
			drFound = this.dsMaster1.CustomerList.Select("CustomerID LIKE 'A*'");

            this.lbClients.DataSource = null;
			this.lbClients.Items.Clear();

			foreach (System.Data.DataRow dr in drFound)
			{
				this.lbClients.Items.Add(dr["CompanyName"]);
			}
			
			this.lbClients.Refresh();
		}

		private void lbClients_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			System.Data.DataRowView drCurrent;
			System.Data.DataSet dsCustOrders;
			
			dsCustOrders = new System.Data.DataSet();

			//Create the relation if necessary
			if (this.dsMaster1.Relations.Count == 0)
			{
				this.dsMaster1.Relations.Add("CustomerOrders",
					this.dsMaster1.CustomerList.CustomerIDColumn,
					this.dsMaster1.OrderTotals.CustomerIDColumn);
			}

			drCurrent = (System.Data.DataRowView) this.lbClients.SelectedItem;
			dsCustOrders.Merge(drCurrent.Row.GetChildRows("CustomerOrders"));

			this.dgOrders.SetDataBinding(dsCustOrders, "OrderTotals");
			this.dgOrders.Refresh();
		}

	}
}