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

using System.Data.SqlClient;
using System.Data.OleDb;

namespace WindowsApplication4
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class DataFrm : System.Windows.Forms.Form
	{
		private System.Windows.Forms.TabControl tabCtrl;
		private System.Windows.Forms.TabPage accessTb;
		private System.Windows.Forms.TabPage sqlTb;
		private System.Windows.Forms.TabPage myTb;
		private System.Windows.Forms.DataGrid accessGrid;
		private System.Windows.Forms.DataGrid sqlGrid;
		private System.Windows.Forms.DataGrid mydataGrid;
		private System.Windows.Forms.ListBox leftListBx;
		private System.Windows.Forms.ListBox rightListBx;
		private System.Windows.Forms.Button leftToRightBtn;
		private System.Windows.Forms.Button rightToLeftBtn;
		private System.Windows.Forms.Button viewBtn;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.GroupBox groupBox2;

		private SqlConnection sqlConn;
		private SqlDataAdapter sqlAdp;

		private OleDbConnection oleConn;
		private OleDbDataAdapter oleAdp;
		
		private DataSet ds = new DataSet();

		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

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

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
		}

		/// <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.tabCtrl = new System.Windows.Forms.TabControl();
			this.accessTb = new System.Windows.Forms.TabPage();
			this.accessGrid = new System.Windows.Forms.DataGrid();
			this.sqlTb = new System.Windows.Forms.TabPage();
			this.sqlGrid = new System.Windows.Forms.DataGrid();
			this.myTb = new System.Windows.Forms.TabPage();
			this.mydataGrid = new System.Windows.Forms.DataGrid();
			this.leftListBx = new System.Windows.Forms.ListBox();
			this.rightListBx = new System.Windows.Forms.ListBox();
			this.leftToRightBtn = new System.Windows.Forms.Button();
			this.rightToLeftBtn = new System.Windows.Forms.Button();
			this.viewBtn = new System.Windows.Forms.Button();
			this.groupBox1 = new System.Windows.Forms.GroupBox();
			this.groupBox2 = new System.Windows.Forms.GroupBox();
			this.tabCtrl.SuspendLayout();
			this.accessTb.SuspendLayout();
			((System.ComponentModel.ISupportInitialize)(this.accessGrid)).BeginInit();
			this.sqlTb.SuspendLayout();
			((System.ComponentModel.ISupportInitialize)(this.sqlGrid)).BeginInit();
			this.myTb.SuspendLayout();
			((System.ComponentModel.ISupportInitialize)(this.mydataGrid)).BeginInit();
			this.SuspendLayout();
			// 
			// tabCtrl
			// 
			this.tabCtrl.Controls.AddRange(new System.Windows.Forms.Control[] {
								
								
				  this.accessTb,
								
								
				  this.sqlTb,
								
								
				  this.myTb});
			this.tabCtrl.Location = new System.Drawing.Point(8, 8);
			this.tabCtrl.Name = "tabCtrl";
			this.tabCtrl.SelectedIndex = 0;
			this.tabCtrl.Size = new System.Drawing.Size(592, 296);
			this.tabCtrl.TabIndex = 0;
			this.tabCtrl.SelectedIndexChanged += new System.EventHandler(this.tabCtrl_Changed);
			// 
			// accessTb
			// 
			this.accessTb.Controls.AddRange(new System.Windows.Forms.Control[] {
								
								
				   this.accessGrid});
			this.accessTb.Location = new System.Drawing.Point(4, 22);
			this.accessTb.Name = "accessTb";
			this.accessTb.Size = new System.Drawing.Size(584, 270);
			this.accessTb.TabIndex = 0;
			this.accessTb.Text = "ACCESS";
			// 
			// accessGrid
			// 
			this.accessGrid.DataMember = "";
			this.accessGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.accessGrid.Name = "accessGrid";
			this.accessGrid.Size = new System.Drawing.Size(584, 272);
			this.accessGrid.TabIndex = 0;
			// 
			// sqlTb
			// 
			this.sqlTb.Controls.AddRange(new System.Windows.Forms.Control[] {
								
								
				this.sqlGrid});
			this.sqlTb.Location = new System.Drawing.Point(4, 22);
			this.sqlTb.Name = "sqlTb";
			this.sqlTb.Size = new System.Drawing.Size(584, 270);
			this.sqlTb.TabIndex = 1;
			this.sqlTb.Text = "SQL";
			// 
			// sqlGrid
			// 
			this.sqlGrid.DataMember = "";
			this.sqlGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.sqlGrid.Location = new System.Drawing.Point(0, -1);
			this.sqlGrid.Name = "sqlGrid";
			this.sqlGrid.Size = new System.Drawing.Size(584, 272);
			this.sqlGrid.TabIndex = 1;
			// 
			// myTb
			// 
			this.myTb.Controls.AddRange(new System.Windows.Forms.Control[] {
								
								
			   this.mydataGrid});
			this.myTb.Location = new System.Drawing.Point(4, 22);
			this.myTb.Name = "myTb";
			this.myTb.Size = new System.Drawing.Size(584, 270);
			this.myTb.TabIndex = 2;
			this.myTb.Text = "My Data";
			// 
			// mydataGrid
			// 
			this.mydataGrid.DataMember = "";
			this.mydataGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.mydataGrid.Location = new System.Drawing.Point(0, -1);
			this.mydataGrid.Name = "mydataGrid";
			this.mydataGrid.Size = new System.Drawing.Size(584, 272);
			this.mydataGrid.TabIndex = 1;
			// 
			// leftListBx
			// 
			this.leftListBx.Location = new System.Drawing.Point(40, 352);
			this.leftListBx.Name = "leftListBx";
			this.leftListBx.Size = new System.Drawing.Size(112, 173);
			this.leftListBx.TabIndex = 1;
			// 
			// rightListBx
			// 
			this.rightListBx.Location = new System.Drawing.Point(248, 352);
			this.rightListBx.Name = "rightListBx";
			this.rightListBx.Size = new System.Drawing.Size(112, 173);
			this.rightListBx.TabIndex = 1;
			// 
			// leftToRightBtn
			// 
			this.leftToRightBtn.Location = new System.Drawing.Point(168, 392);
			this.leftToRightBtn.Name = "leftToRightBtn";
			this.leftToRightBtn.Size = new System.Drawing.Size(64, 32);
			this.leftToRightBtn.TabIndex = 2;
			this.leftToRightBtn.Text = ">>";
			this.leftToRightBtn.Click += new System.EventHandler(this.leftToRightBtn_Click);
			// 
			// rightToLeftBtn
			// 
			this.rightToLeftBtn.Location = new System.Drawing.Point(168, 448);
			this.rightToLeftBtn.Name = "rightToLeftBtn";
			this.rightToLeftBtn.Size = new System.Drawing.Size(64, 32);
			this.rightToLeftBtn.TabIndex = 2;
			this.rightToLeftBtn.Text = "<<";
			this.rightToLeftBtn.Click += new System.EventHandler(this.rightToLeftBtn_Click);
			// 
			// viewBtn
			// 
			this.viewBtn.Location = new System.Drawing.Point(440, 384);
			this.viewBtn.Name = "viewBtn";
			this.viewBtn.Size = new System.Drawing.Size(104, 88);
			this.viewBtn.TabIndex = 3;
			this.viewBtn.Text = "보기";
			this.viewBtn.Click += new System.EventHandler(this.viewBtn_Click);
			// 
			// groupBox1
			// 
			this.groupBox1.Location = new System.Drawing.Point(16, 320);
			this.groupBox1.Name = "groupBox1";
			this.groupBox1.Size = new System.Drawing.Size(368, 224);
			this.groupBox1.TabIndex = 4;
			this.groupBox1.TabStop = false;
			this.groupBox1.Text = "컬럼 편집";
			// 
			// groupBox2
			// 
			this.groupBox2.Location = new System.Drawing.Point(400, 320);
			this.groupBox2.Name = "groupBox2";
			this.groupBox2.Size = new System.Drawing.Size(192, 224);
			this.groupBox2.TabIndex = 5;
			this.groupBox2.TabStop = false;
			this.groupBox2.Text = "결과보기";
			// 
			// DataFrm
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(608, 557);
			this.Controls.AddRange(new System.Windows.Forms.Control[] {
								
								
		  this.viewBtn,
								
								
		  this.leftToRightBtn,
								
								
		  this.leftListBx,
								
								
		  this.tabCtrl,
								
								
		  this.rightListBx,
								
								
		  this.rightToLeftBtn,
								
								
		  this.groupBox1,
								
								
		  this.groupBox2});
			this.Name = "DataFrm";
			this.Text = "Data Integration";
			this.Load += new System.EventHandler(this.DataFrm_Load);
			this.tabCtrl.ResumeLayout(false);
			this.accessTb.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.accessGrid)).EndInit();
			this.sqlTb.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.sqlGrid)).EndInit();
			this.myTb.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.mydataGrid)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

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

		private void tabCtrl_Changed(object sender, System.EventArgs e)
		{
			switch(tabCtrl.SelectedIndex)
			{
				case 0 :
					leftListBx.Items.Clear();
					for(int i = 0; i < ds.Tables["mytable"].Columns.Count; i++)
					{
						leftListBx.Items.Add(ds.Tables["mytable"].Columns[i].Caption);
					}
					break;
				case 1 :
					leftListBx.Items.Clear();
					for(int i = 0; i < ds.Tables["neo_members"].Columns.Count; i++)
					{
						leftListBx.Items.Add(ds.Tables["neo_members"].Columns[i].Caption);
					}
					break;
				default : break;
			}
		}
        
		private void leftToRightBtn_Click(object sender, System.EventArgs e)
		{
			if(rightListBx.Items.Count == 0)
			{
				rightListBx.Items.Add(leftListBx.SelectedItem);
			}
			else
			{
				if(Duplicate_Func(leftListBx.SelectedItem.ToString()))
				{}
				else
				{
					rightListBx.Items.Add(leftListBx.SelectedItem);
				}
			}
		}

		private bool Duplicate_Func(string listItem)
		{
			for(int i = 0; i < rightListBx.Items.Count; i++)
			{
				if(listItem.Equals(rightListBx.Items[i].ToString()))
				{
					MessageBox.Show(rightListBx.SelectedItem + "이미 선택한 컬럼입니다.");
					return true;
				}
			}
			return false;
		}

		private void rightToLeftBtn_Click(object sender, System.EventArgs e)
		{
			rightListBx.Items.Remove(rightListBx.SelectedItem);	
	
		}

		private void viewBtn_Click(object sender, System.EventArgs e)
		{
			DataTable dt = new DataTable("NewTable");

			try
			{
				for(int i = 0; i < rightListBx.Items.Count; i++)
				{
					DataColumn dc = new DataColumn();
					dc.DataType = System.Type.GetType("System.String");
					dc.DefaultValue="";
					dc.ColumnName = rightListBx.Items[i].ToString();
					dt.Columns.Add(dc);
				}

				for(int i = 0; i < rightListBx.Items.Count; i++)
				{
					for(int j = 0; j < ds.Tables.Count; j++)
					{
						for(int k = 0; k < ds.Tables[j].Columns.Count; k++)
						{
							if((rightListBx.Items[i].ToString()).Equals(ds.Tables[j].Columns[k].Caption))
							{
								foreach(DataRow mydr in ds.Tables[j].Rows)
								{
									DataRow dr = dt.NewRow();
									dr[dt.Columns[i].ColumnName] = mydr[ds.Tables[j].Columns[k].Caption];
									dt.Rows.Add(dr);
								}
							}
						}
					}
					mydataGrid.DataSource = dt.DefaultView;
				}
			}
			catch(Exception E)
			{
				MessageBox.Show(E.Message);
			}
			finally
			{

			}
		}

		private void DataFrm_Load(object sender, System.EventArgs e)
		{
			AccessView();
			SqlView();

			for(int i=0; i < ds.Tables[0].Columns.Count; i++)
			{
				leftListBx.Items.Add(ds.Tables[0].Columns[i].Caption);
			}
		}

		private void AccessView()
		{
			try
			{
				oleConn = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0; password=; user id=; data source=d:\ado.mdb");
				oleAdp = new OleDbDataAdapter("select * from mytable", oleConn);

				oleAdp.Fill(ds, "mytable");
				accessGrid.DataSource = ds.Tables["mytable"].DefaultView;
			}
			catch(Exception E)
			{
				MessageBox.Show(E.Message);
			}
			finally
			{
				oleConn.Close();
			}
		}

		private void SqlView()
		{
			try
			{
				sqlConn = new SqlConnection("server=ns; database=csharp; uid=csharp; pwd=csharp;");
				sqlAdp = new SqlDataAdapter("select * from neo_members", sqlConn);

				sqlAdp.Fill(ds, "neo_members");
				sqlGrid.DataSource = ds.Tables["neo_members"].DefaultView;
			}
			catch(Exception E)
			{
				MessageBox.Show(E.Message);
			}
			finally
			{
				sqlConn.Close();
			}
		}
	}
}