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

namespace DBAzine_September
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{
		private DataSet dsNorthwind;

		private DataTable dtRegion;
		private DataColumn colRegionID;
		private DataColumn colRegionDescription;
		private Constraint pkRegion;

		private DataTable dtTerritories;
		private DataColumn colTerritoryID;
		private DataColumn colTerritoryDescription;
		private DataColumn colRegionIDFK;
		private Constraint pkTerritories;
		private Constraint fkTerritories;
		private System.Windows.Forms.Button btnRetrieveNorthwind;
		private System.Windows.Forms.Button btnReadXml;
		private System.Windows.Forms.Button btnWriteXml;
		private System.Windows.Forms.DataGrid grdNorthwind;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

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

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
			dsNorthwind = new DataSet("Northwind");
			dtRegion = new DataTable("Region");
			colRegionID = dtRegion.Columns.Add("RegionID", typeof(int));
			colRegionDescription = dtRegion.Columns.Add("RegionDescription", typeof(string));
			pkRegion = dtRegion.Constraints.Add("pkRegion", dtRegion.Columns["RegionID"], true);
			dtTerritories = new DataTable("Territories");
			colTerritoryID = dtTerritories.Columns.Add("TerritoryID", typeof(string));
			colTerritoryDescription = dtTerritories.Columns.Add("TerritoryDescription", typeof(string));
			colRegionIDFK = dtTerritories.Columns.Add("RegionID", typeof(int));
			pkTerritories = dtTerritories.Constraints.Add("pkTerritories", dtTerritories.Columns["TerritoryID"], true);
			fkTerritories = dtTerritories.Constraints.Add("fkRegionID", dtRegion.Columns["RegionID"], dtTerritories.Columns["RegionID"]);
		}

		/// <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.grdNorthwind = new System.Windows.Forms.DataGrid();
			this.btnRetrieveNorthwind = new System.Windows.Forms.Button();
			this.btnReadXml = new System.Windows.Forms.Button();
			this.btnWriteXml = new System.Windows.Forms.Button();
			((System.ComponentModel.ISupportInitialize)(this.grdNorthwind)).BeginInit();
			this.SuspendLayout();
			// 
			// grdNorthwind
			// 
			this.grdNorthwind.DataMember = "";
			this.grdNorthwind.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.grdNorthwind.Location = new System.Drawing.Point(8, 8);
			this.grdNorthwind.Name = "grdNorthwind";
			this.grdNorthwind.Size = new System.Drawing.Size(280, 192);
			this.grdNorthwind.TabIndex = 0;
			// 
			// btnRetrieveNorthwind
			// 
			this.btnRetrieveNorthwind.Location = new System.Drawing.Point(8, 208);
			this.btnRetrieveNorthwind.Name = "btnRetrieveNorthwind";
			this.btnRetrieveNorthwind.Size = new System.Drawing.Size(112, 23);
			this.btnRetrieveNorthwind.TabIndex = 1;
			this.btnRetrieveNorthwind.Text = "Retrieve Northwind";
			this.btnRetrieveNorthwind.Click += new System.EventHandler(this.btnRetrieveNorthwind_Click);
			// 
			// btnReadXml
			// 
			this.btnReadXml.Location = new System.Drawing.Point(8, 240);
			this.btnReadXml.Name = "btnReadXml";
			this.btnReadXml.Size = new System.Drawing.Size(112, 23);
			this.btnReadXml.TabIndex = 3;
			this.btnReadXml.Text = "Read XML";
			this.btnReadXml.Click += new System.EventHandler(this.btnReadXml_Click);
			// 
			// btnWriteXml
			// 
			this.btnWriteXml.Location = new System.Drawing.Point(176, 240);
			this.btnWriteXml.Name = "btnWriteXml";
			this.btnWriteXml.Size = new System.Drawing.Size(104, 23);
			this.btnWriteXml.TabIndex = 4;
			this.btnWriteXml.Text = "Write XML";
			this.btnWriteXml.Click += new System.EventHandler(this.btnWriteXml_Click);
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(292, 273);
			this.Controls.AddRange(new System.Windows.Forms.Control[] {
																		  this.btnWriteXml,
																		  this.btnReadXml,
																		  this.btnRetrieveNorthwind,
																		  this.grdNorthwind});
			this.Name = "Form1";
			this.Text = "Territories";
			this.Load += new System.EventHandler(this.Form1_Load);
			((System.ComponentModel.ISupportInitialize)(this.grdNorthwind)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

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

        private void Form1_Load(object sender, System.EventArgs e)
        {
            colRegionDescription.AllowDBNull = false;
            colRegionDescription.MaxLength = 50;
            colTerritoryDescription.AllowDBNull = false;
            colTerritoryID.MaxLength = 20;
            colTerritoryDescription.MaxLength = 50;
            dsNorthwind.Tables.Add(dtRegion);
            dsNorthwind.Tables.Add(dtTerritories);
            dsNorthwind.EnforceConstraints = true;
            dsNorthwind.Relations.Add("fkRegionID", dtRegion.Columns["RegionID"], dtTerritories.Columns["RegionID"], true);

			grdNorthwind.DataSource = dsNorthwind.Tables["Region"];
        }

        private void btnRetrieveNorthwind_Click(object sender, System.EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection("server=(local);Trusted_Connection=yes;database=Northwind");
            SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter("select * from region", sqlConnection);
            SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter("select * from territories", sqlConnection);
            sqlDataAdapter1.Fill(dtRegion);
            sqlDataAdapter2.Fill(dtTerritories);
            sqlConnection.Close();
		}

        private void btnModifyNorthwind_Click(object sender, System.EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection("server=(local);Trusted_Connection=yes;database=Northwind");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("select * from territories", sqlConnection);
            SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
            sqlDataAdapter.DeleteCommand = new SqlCommand("DELETE FROM Territories WHERE TerritoryID = @TerritoryID", sqlConnection);
            sqlDataAdapter.DeleteCommand.Parameters.Add("@TerritoryID", SqlDbType.NVarChar, 20, "TerritoryID").SourceVersion = DataRowVersion.Original;
            sqlDataAdapter.Update(dsNorthwind, "Territories");
            sqlConnection.Close();
		}

        private void btnReadXml_Click(object sender, System.EventArgs e)
        {
            dsNorthwind.ReadXml("C:/DBAzine.xml");
        }

        private void btnWriteXml_Click(object sender, System.EventArgs e)
        {
            dsNorthwind.WriteXml("C:/DBAzine.xml", XmlWriteMode.DiffGram);
        }
	}
}