DACBuilder – Data Access objects generation tool based on XML and XSL templates transformation

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using DACBuilder.CustomFields;
using DataGridCustomColumns;
using System.Text.RegularExpressions;
using Framework.DACCreator;


namespace DACBuilder
{
	/// <summary>
	/// Summary description for frmCustomFieldsManagement.
	/// </summary>
	public class frmCustomFieldsManagement : System.Windows.Forms.Form
	{
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;
		private System.Windows.Forms.Button btnSave;
		private System.Windows.Forms.Button btnCancel;
		private System.Windows.Forms.DataGrid CustomFieldsGrid;

		private string tableName = string.Empty;
		private string ConnectionString;
		private DataTable dtProperties;
		int[] IDs;
		private System.Windows.Forms.DataGridTableStyle dataGridTableStyle1;
		private System.Windows.Forms.DataGridTextBoxColumn dataGridTextBoxColumn1;
		private System.Windows.Forms.DataGridTextBoxColumn dataGridTextBoxColumn2;
		DACCustomFieldsTable tblCFT;
		DACCustomFieldsProperty tblCFP;
		DACCustomFieldsTableColumn tblCFTC;

		int Counter = 0;

		public frmCustomFieldsManagement(string tableName, string ConnectionString)
		{
			InitializeComponent();
			this.tableName = tableName;
			this.ConnectionString = ConnectionString;
			this.Text += " for table " + this.tableName;
			ReadFields();
		}

		/// <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.CustomFieldsGrid = new System.Windows.Forms.DataGrid();
			this.dataGridTableStyle1 = new System.Windows.Forms.DataGridTableStyle();
			this.dataGridTextBoxColumn1 = new System.Windows.Forms.DataGridTextBoxColumn();
			this.dataGridTextBoxColumn2 = new System.Windows.Forms.DataGridTextBoxColumn();
			this.btnSave = new System.Windows.Forms.Button();
			this.btnCancel = new System.Windows.Forms.Button();
			((System.ComponentModel.ISupportInitialize)(this.CustomFieldsGrid)).BeginInit();
			this.SuspendLayout();
			// 
			// CustomFieldsGrid
			// 
			this.CustomFieldsGrid.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) 
				| System.Windows.Forms.AnchorStyles.Left) 
				| System.Windows.Forms.AnchorStyles.Right)));
			this.CustomFieldsGrid.BackgroundColor = System.Drawing.Color.LightGray;
			this.CustomFieldsGrid.DataMember = "";
			this.CustomFieldsGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.CustomFieldsGrid.Location = new System.Drawing.Point(8, 16);
			this.CustomFieldsGrid.Name = "CustomFieldsGrid";
			this.CustomFieldsGrid.Size = new System.Drawing.Size(448, 160);
			this.CustomFieldsGrid.TabIndex = 0;
			this.CustomFieldsGrid.TableStyles.AddRange(new System.Windows.Forms.DataGridTableStyle[] {
																										 this.dataGridTableStyle1});
			// 
			// dataGridTableStyle1
			// 
			this.dataGridTableStyle1.DataGrid = this.CustomFieldsGrid;
			this.dataGridTableStyle1.GridColumnStyles.AddRange(new System.Windows.Forms.DataGridColumnStyle[] {
																												  this.dataGridTextBoxColumn1,
																												  this.dataGridTextBoxColumn2});
			this.dataGridTableStyle1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dataGridTableStyle1.MappingName = "";
			// 
			// dataGridTextBoxColumn1
			// 
			this.dataGridTextBoxColumn1.Format = "";
			this.dataGridTextBoxColumn1.FormatInfo = null;
			this.dataGridTextBoxColumn1.HeaderText = "Name";
			this.dataGridTextBoxColumn1.MappingName = "PropertyName";
			this.dataGridTextBoxColumn1.Width = 75;
			// 
			// dataGridTextBoxColumn2
			// 
			this.dataGridTextBoxColumn2.Format = "";
			this.dataGridTextBoxColumn2.FormatInfo = null;
			this.dataGridTextBoxColumn2.HeaderText = "Type";
			this.dataGridTextBoxColumn2.MappingName = "TypeID";
			this.dataGridTextBoxColumn2.Width = 75;
			// 
			// btnSave
			// 
			this.btnSave.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
			this.btnSave.Location = new System.Drawing.Point(296, 200);
			this.btnSave.Name = "btnSave";
			this.btnSave.TabIndex = 1;
			this.btnSave.Text = "Save";
			this.btnSave.Click += new System.EventHandler(this.btnSave_Click);
			// 
			// btnCancel
			// 
			this.btnCancel.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
			this.btnCancel.Location = new System.Drawing.Point(384, 200);
			this.btnCancel.Name = "btnCancel";
			this.btnCancel.TabIndex = 2;
			this.btnCancel.Text = "Cancel";
			this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
			// 
			// frmCustomFieldsManagement
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(472, 238);
			this.Controls.Add(this.btnCancel);
			this.Controls.Add(this.btnSave);
			this.Controls.Add(this.CustomFieldsGrid);
			this.Name = "frmCustomFieldsManagement";
			this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
			this.Text = "Custom Fields Management";
			((System.ComponentModel.ISupportInitialize)(this.CustomFieldsGrid)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion


		private void ReadFields()
		{
			string SelectQuery;
			SqlCommand Command;
			SqlConnection Connection;
//			SqlDataReader Reader;
//			DataRow Row; 

			tblCFT = new DACCustomFieldsTable();
			tblCFT.ConnectionString = this.ConnectionString;
			tblCFTC =  new DACCustomFieldsTableColumn();
			tblCFTC.ConnectionString = this.ConnectionString;
			tblCFP = new DACCustomFieldsProperty();
			tblCFP.ConnectionString = this.ConnectionString;


			tblCFT.TableName = this.tableName;


			SelectQuery = "SELECT PKProperty as PropertyID, PropertyName AS PropertyName, FKPropertyType AS TypeID " +
						  "FROM dbo.CustomFieldsProperty " +
						  "INNER JOIN dbo.CustomFieldsTableColumn ON dbo.CustomFieldsProperty.FKColumn = dbo.CustomFieldsTableColumn.PKColumn " +
						  "INNER JOIN dbo.CustomFieldsType ON dbo.CustomFieldsType.PKType = dbo.CustomFieldsProperty.FKPropertyType "+
				          "INNER JOIN dbo.CustomFieldsTable ON dbo.CustomFieldsTable.PKTable = dbo.CustomFieldsTableColumn.FKTable " +
						  "WHERE dbo.CustomFieldsTable.TableName = '" + this.tableName + "'";
			
			Connection = new SqlConnection(ConnectionString);
			Command = new SqlCommand(SelectQuery,Connection);

			try
			{

				DataTable dt = new DataTable();
				SqlDataAdapter da = new SqlDataAdapter("SELECT PKType AS TypeID, TypeName FROM CustomFieldsType", ConnectionString);
				da.Fill(dt);
				UiUtils.ReplaceTypeWithCombo(CustomFieldsGrid, dt, "TypeName", "TypeID");

				dtProperties = new DataTable();
				da = new SqlDataAdapter(SelectQuery, ConnectionString);
				da.Fill(dtProperties);
				/*
				Connection.Open();
				Reader = Command.ExecuteReader();
				dtProperties = new DataTable("gridTable");
				DataColumn Column = new DataColumn(); 
				Column.DataType = System.Type.GetType("System.Int32"); 
				Column.AllowDBNull = true; 
				Column.Caption = "PropertyID"; 
				Column.ColumnName = "PropertyID"; 
				dtProperties.Columns.Add(Column);
				Column = new DataColumn(); 
				Column.DataType = System.Type.GetType("System.String"); 
				Column.AllowDBNull = true; 
				Column.Caption = "PropertyName"; 
				Column.ColumnName = "PropertyName"; 
				dtProperties.Columns.Add(Column); 
				Column = new DataColumn(); 
				Column.DataType = System.Type.GetType("System.Int32"); 
				Column.AllowDBNull = true; 
				Column.Caption = "Type"; 
				Column.ColumnName = "TypeID"; 
				dtProperties.Columns.Add(Column); 

				while (Reader.Read()) 
				{
					Row = dtProperties.NewRow();
					Row["PropertyID"] = Reader.GetInt32(0); 
					Row["PropertyName"] = Reader.GetString(1); 
					Row["TypeID"] = Reader.GetInt32(2); 
					dtProperties.Rows.Add(Row); 
				}
				Reader.Close();

				dtProperties.AcceptChanges();
				*/
				dtProperties.RowDeleting +=new DataRowChangeEventHandler(dtProperties_RowDeleting);
				dtProperties.RowChanged += new DataRowChangeEventHandler(dtProperties_RowChanged);
				dtProperties.RowDeleted +=new DataRowChangeEventHandler(dtProperties_RowDeleted);

				CustomFieldsGrid.DataSource = dtProperties;

				DataColumn col = dtProperties.Columns["TypeID"];
				col.DefaultValue = 1;

			}
			catch (Exception e)
			{
			
				MessageBox.Show(e.Message);
			}			
			finally 
			{
				Connection.Close();
			}

			IDs = new int[dtProperties.Rows.Count];





		}

		private void btnSave_Click(object sender, System.EventArgs e)
		{
			string ColumnName;
			SqlCommand Command;
			SqlConnection Connection;
			SqlParameter param;
			SqlDataReader Reader;
			DataRow dr;
			bool chk;

			try
			{
				if(!tblCFT.ReadByTableName())
				{
					tblCFT.Add();
					tblCFT.Read();
					tblCFTC.FKTable =  tblCFT.PKTable;
					tblCFTC.Add();
					tblCFTC.Read();
					tblCFP.FKColumn = tblCFTC.PKColumn;
					//tblCFP.Add();

				}


				Connection = new SqlConnection(ConnectionString);
				Command = new SqlCommand("GetPrimaryKeyColumnName", Connection);
				Command.CommandType = CommandType.StoredProcedure;

				Connection.Open();
					
				param = new SqlParameter("@TableName", SqlDbType.VarChar, 255);
				param.Value = this.tableName;
				Command.Parameters.Add(param);

				Reader = Command.ExecuteReader();
				Reader.Read();
				ColumnName = Reader.GetString(0);


				tblCFTC.FKTable = tblCFT.PKTable;
				if (!tblCFTC.ReadByFKTable())
				{
					tblCFTC.ColumnName = ColumnName;
					tblCFTC.TypeId = (int)CustomFieldsType.Integer;
					tblCFTC.TypeIdColumnName = CustomFieldsType.Integer.ToString();
					tblCFTC.Add(); 
					tblCFTC.Read();
				}
				else 
				{ 
					tblCFTC.Read();
				}
				Reader.Close();
				Connection.Close();

				if(dtProperties.Rows.Count > 0)
				{
					for (int i = 0; i < dtProperties.Rows.Count; i++)
					{


						dr = dtProperties.Rows[i];
						if(chk = REChecker.IsIdentifier(dr["PropertyName"].ToString()))
						{

							if(dr.RowState == DataRowState.Added || dr.RowState == DataRowState.Modified)
							{

								switch (dr.RowState)
								{
									case DataRowState.Added:
										tblCFP = new DACCustomFieldsProperty();
										tblCFP.ConnectionString = this.ConnectionString;
										tblCFP.FKColumn = tblCFTC.PKColumn;
										tblCFP.PKProperty = -1;
										tblCFP.FKPropertyType = (int)Enum.Parse(typeof(CustomFieldsType),dr["TypeID"].ToString());
										tblCFP.DefaultValue = string.Empty;
										tblCFP.PropertyName = dr["PropertyName"].ToString();
										tblCFP.FKPropertyList = -1;
										tblCFP.Add();
										break;
									case DataRowState.Modified:
										tblCFP = new DACCustomFieldsProperty();
										tblCFP.ConnectionString = this.ConnectionString;
										tblCFP.FKColumn = tblCFTC.PKColumn;
										tblCFP.PKProperty = int.Parse(dr["PropertyID"].ToString());
										tblCFP.FKPropertyType = (int)Enum.Parse(typeof(CustomFieldsType),dr["TypeID"].ToString());
										tblCFP.DefaultValue = string.Empty;
										tblCFP.PropertyName = dr["PropertyName"].ToString();
										tblCFP.FKPropertyList = -1;
										tblCFP.Edit();
										break;
								}
							}
						}
						else
						{
							MessageBox.Show("Insert a valid Property Name! Only letters and digits are accepted!");
							CustomFieldsGrid.CurrentRowIndex = i;
							return;

						}
					}
				}

				for (int i = 0; i < IDs.Length; i++)
				{
					if(IDs[i] > 0)
					{
						tblCFP.PKProperty = IDs[i];
						tblCFP.Delete();
					}					
				}

			}
			catch(Exception ex)
			{
				MessageBox.Show(ex.Message);
			}

			this.Close();

			

		}

		private void btnCancel_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}


		private void dtProperties_RowDeleting(object sender, DataRowChangeEventArgs e)
		{
			try
			{
				IDs[Counter] = int.Parse(e.Row["PropertyID"].ToString());
				Counter++;
			}
			catch{}
//			try
//			{
//				e.Row.Delete();
//			}
//			catch(Exception ex)
//			{
//				MessageBox.Show(ex.Message);
//			}
		}

		private void dtProperties_RowChanged(object sender, DataRowChangeEventArgs e)
		{
			//MessageBox.Show(e.Row["TypeID"].ToString());
		}

		private void dtProperties_RowDeleted(object sender, DataRowChangeEventArgs e)
		{
		}
	}
}