editbox.cs

C# 使用OLEDB

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

namespace CSHARPOLEDB3
{
	/// <summary>
	/// Summary description for EditBox.
	/// </summary>
	public class EditBox : System.Windows.Forms.Form
	{
		/// <summary>
		/// create a grid to display the data
		/// </summary>
		private System.Windows.Forms.DataGrid dataGrid1;
		
		/// <summary>
		/// DataTable object for storing the data
		/// </summary>
		private DataTable dataTable;

		/// <summary>
		/// DataSet for storing the DataTable
		/// </summary>
		private DataSet dataSet;

		/// <summary>
		/// string holder for the sql statement
		/// </summary>
		private string strSQL;

		/// <summary>
		/// Get and Set the SQL statement
		/// </summary>
		public string SQL
		{
			get
			{
				return strSQL;
			}
			set
			{
				strSQL = value;
			}
		}

		/// <summary>
		/// private variable for inserting checking if it is an insert command
		/// </summary>
		private bool bInsertCommand;

		/// <summary>
		/// Get and set for the private insert command
		/// </summary>
		private bool InsertCommand
		{
			get
			{
				return bInsertCommand;
			}
			set 
			{
				bInsertCommand = value;
			}
		}

		/// <summary>
		///  Private variable for keeping track of the field count
		/// </summary>
		private int nCount;

		/// <summary>
		/// Get and Set for the private field count variable
		/// </summary>
		private int Count
		{
			get
			{
				return nCount;
			}
			set
			{
				nCount = value;
			}
		}

		/// <summary>
		/// Private string for keeping track of the tablename
		/// </summary>
		private string strTableName;

		/// <summary>
		/// Get and Set for the private tablename variable
		/// </summary>
		private string TableName
		{
			get
			{
				return strTableName;
			}
			set
			{
				strTableName = value;
			}
		}


		/// <summary>
		/// private variable for keeping track of disabled fields
		/// </summary>
		private bool[] bDisableArray;


		/// <summary>
		/// Close the dialog
		/// </summary>
		private System.Windows.Forms.Button OK;

		/// <summary>
		/// Disable the selected column
		/// </summary>
		private System.Windows.Forms.Button DisableColumn;
		private System.Windows.Forms.Button Cancel;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		/// <summary>
		/// Hide the standard constructor
		/// </summary>
		private EditBox()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

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

		/// <summary>
		/// Custom constructor is the only entry point to the class
		/// </summary>
		/// <param name="win"></param>
		/// <param name="reader"></param>
		public EditBox( IWin32Window win, OleDbDataReader reader, string TableName, bool bInsert )
		{
			InitializeComponent();

			/// set the insert command
			InsertCommand = bInsert;

			/// Create the DataSet
			dataSet = new DataSet( "SmashingPumkins" );
			/// Create the DataTable
			dataTable = new DataTable( "MelancholyAndTheInfiniteSadness" );
			

			int nCount = reader.FieldCount;

			this.TableName = TableName;

			Count = nCount;

			/// allocate the disbaled field array
			bDisableArray = new Boolean[ nCount ];
			for( int nDisable=0; nDisable<nCount; nDisable++ )
				bDisableArray[ nDisable ] = false;

			/// Loop through the fields and create columns for each field
			for( int i=0; i<nCount; i++ )
			{
				try
				{
					DataColumn dataCol = dataTable.Columns.Add( reader.GetName( i ), reader.GetFieldType( i ) );
					if( reader.GetSchemaTable() != null )
					{
						DataTable tempTable = reader.GetSchemaTable();
				
						DataColumnCollection colCollect = tempTable.Columns;

						IEnumerator colEnum = colCollect.GetEnumerator();

						colEnum.MoveNext();
						for( int count=0; count<i; count++ )
						{
							colEnum.MoveNext();
						}

						DataColumn tempCol = ( DataColumn )colEnum.Current;

						if( tempCol.Unique == true )
						{
							dataCol.Unique = true;
						}
						/// this is technically incorrect AutoIncrement fields
						/// are being returned with this as false
						if( tempCol.AutoIncrement == true )
						{
							dataCol.ReadOnly = true;
							dataCol.AutoIncrement = true;
						}
					

						dataCol.MaxLength = tempCol.MaxLength;

					}
				}
				catch( ArgumentException argExp )
				{
					MessageBox.Show( "ArgumentException thrown " + argExp.Message + "\nCaused by " + argExp.ParamName ); 
				}
			}

			/// add only one row to the table
			DataRow dataRow = dataTable.NewRow();

			if( bInsert == false )
			{
				object[] itemArray = dataRow.ItemArray;

				for( int i=0; i<nCount; i++ )
				{
					itemArray[ i ] = reader.GetValue( i ).ToString();
				}

				dataRow.ItemArray = itemArray;
			}

			dataTable.Rows.Add( dataRow );
			dataTable.AcceptChanges();

			/// add the DataTable to the DataSet
			dataSet.Tables.Add( dataTable );

			try
			{
				reader.NextResult();
				/// set the grid binding
				dataGrid1.SetDataBinding( dataSet, dataTable.TableName );

			}
			catch( ArgumentException argExp )
			{
				MessageBox.Show( "SetDataBinding threw an exception " + argExp.Message + "\nCaused by " + argExp.ParamName );
			}
			catch( NullReferenceException nullExp )
			{
				MessageBox.Show( "SetDataBinding threw an exception " + nullExp.Message + "\nCaused by " + nullExp.StackTrace );
			}
			

		}


		/// <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.DisableColumn = new System.Windows.Forms.Button();
			this.dataGrid1 = new System.Windows.Forms.DataGrid();
			this.Cancel = new System.Windows.Forms.Button();
			this.OK = new System.Windows.Forms.Button();
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
			this.SuspendLayout();
			// 
			// DisableColumn
			// 
			this.DisableColumn.Location = new System.Drawing.Point(16, 16);
			this.DisableColumn.Name = "DisableColumn";
			this.DisableColumn.Size = new System.Drawing.Size(168, 23);
			this.DisableColumn.TabIndex = 1;
			this.DisableColumn.Text = "Don\'t Include Selected Column";
			this.DisableColumn.Click += new System.EventHandler(this.OnDisableColumn);
			// 
			// dataGrid1
			// 
			this.dataGrid1.BackgroundColor = System.Drawing.SystemColors.Window;
			this.dataGrid1.CaptionVisible = false;
			this.dataGrid1.DataMember = "";
			this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dataGrid1.Location = new System.Drawing.Point(8, 64);
			this.dataGrid1.Name = "dataGrid1";
			this.dataGrid1.Size = new System.Drawing.Size(432, 104);
			this.dataGrid1.TabIndex = 0;
			// 
			// Cancel
			// 
			this.Cancel.Location = new System.Drawing.Point(280, 16);
			this.Cancel.Name = "Cancel";
			this.Cancel.TabIndex = 3;
			this.Cancel.Text = "Cancel";
			this.Cancel.Click += new System.EventHandler(this.OnCancel);
			// 
			// OK
			// 
			this.OK.Location = new System.Drawing.Point(368, 16);
			this.OK.Name = "OK";
			this.OK.TabIndex = 2;
			this.OK.Text = "OK";
			this.OK.Click += new System.EventHandler(this.OnOK);
			// 
			// EditBox
			// 
			this.AutoScale = false;
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(448, 189);
			this.Controls.AddRange(new System.Windows.Forms.Control[] {
																		  this.Cancel,
																		  this.OK,
																		  this.DisableColumn,
																		  this.dataGrid1});
			this.MaximizeBox = false;
			this.MinimizeBox = false;
			this.Name = "EditBox";
			this.SizeGripStyle = System.Windows.Forms.SizeGripStyle.Hide;
			this.Text = "EditBox";
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		///  OnOK Will generate the SQL statement and will return it to the calling code
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void OnOK(object sender, System.EventArgs e)
		{
			if( InsertCommand == true )
			{
				StringBuilder builder = new StringBuilder();
				StringBuilder variables = new StringBuilder();

				builder.Append( "INSERT INTO " );
				builder.Append( TableName );
				builder.Append( "( " );

				variables.Append( " VALUES(" );

				DataColumnCollection dataColumnCol = dataTable.Columns;
				DataRowCollection dataRowCol = dataTable.Rows;

				IEnumerator enumCols = dataColumnCol.GetEnumerator();
				IEnumerator enumRows = dataRowCol.GetEnumerator();

				enumCols.MoveNext();
				enumRows.MoveNext();
				/// only one row so get it
				
				DataColumn dataColumn = ( DataColumn )enumCols.Current;
				DataRow dataRow = ( DataRow )enumRows.Current;

				object[] cellArray = dataRow.ItemArray;
				
				bool bMove = true;
				bool bFirst = true;
				string strTest;
				for( int i=0; i<Count; i++ )
				{
					
					if( bDisableArray[ i ] == false )
					{
						variables.Append( " " );
						if( bFirst == true )
						{
							builder.Append( " [" );
							bFirst = false;
						}
						else
						{
							builder.Append( " [" );
						}
						builder.Append( dataColumn.ColumnName );
						builder.Append( "] " );
						strTest = dataColumn.DataType.ToString();
						if( strTest != "System.Int32" )
							variables.Append( "'" );
						variables.Append( cellArray[ i ] );
						variables.Append( " " );
						strTest = dataColumn.DataType.ToString();
						if( strTest != "System.Int32" )
							variables.Append( "'" );
						if( i < nCount-1 )
						{
							builder.Append( "," );
							variables.Append( "," );
						}
					}

					if( bMove == true )
					{
						bMove = enumCols.MoveNext();
						if( bMove == true )
							dataColumn = ( DataColumn )enumCols.Current;
					}
					
				}
				builder.Append( " )" );

				builder.Append( variables.ToString() + " )" );

				SQL = builder.ToString();

			}
			else /// Generate the update sql
			{
				StringBuilder builder = new StringBuilder();
				StringBuilder whereBuilder = new StringBuilder();

				builder.Append( "UPDATE " );
				builder.Append( TableName );
				builder.Append( " SET " );

				whereBuilder.Append( "	WHERE " );

				DataColumnCollection dataColumnCol = dataTable.Columns;
				DataRowCollection dataRowCol = dataTable.Rows;

				IEnumerator enumCols = dataColumnCol.GetEnumerator();
				IEnumerator enumRows = dataRowCol.GetEnumerator();

				enumCols.MoveNext();
				enumRows.MoveNext();
				
				DataColumn dataColumn = ( DataColumn )enumCols.Current;
				DataRow dataRow = ( DataRow )enumRows.Current;

				object[] cellArray = dataRow.ItemArray;
				
				bool bMove = true;
				bool bFirst = true;
				int nWhereCount = 0;
				string strTest;
				for( int i=0; i<Count; i++ )
				{
					
					if( bDisableArray[ i ] == false )
					{
						builder.Append( " " );
						if( bFirst == true )
						{
							builder.Append( " [" );
							bFirst = false;
						}
						else
						{
							builder.Append( " [" );
						}
						builder.Append( dataColumn.ColumnName );
						builder.Append( "] = " );
						strTest = dataColumn.DataType.ToString();
						if( strTest != "System.Int32" )
							builder.Append( "'" );
						builder.Append( cellArray[ i ] );
						builder.Append( " " );
						strTest = dataColumn.DataType.ToString();
						if( strTest != "System.Int32" )
							builder.Append( "'" );
						if( i < nCount-1 )
						{
							builder.Append( "," );
						}
					}
					else
					{
						if( bFirst == true )
						{
							whereBuilder.Append( " [" );
							bFirst = false;
						}
						else
						{
							whereBuilder.Append( " [" );
						}
						whereBuilder.Append( dataColumn.ColumnName );
						whereBuilder.Append( "] = " );
						strTest = dataColumn.DataType.ToString();
						if( strTest != "System.Int32" )
							whereBuilder.Append( "'" );
						whereBuilder.Append( cellArray[ i ] );
						whereBuilder.Append( " " );
						strTest = dataColumn.DataType.ToString();
						if( strTest != "System.Int32" )
							whereBuilder.Append( "'" );
						if( nWhereCount > 1 )
						{
							whereBuilder.Append( "," );
						}
						else
							nWhereCount++;
	
						whereBuilder.Append( " " );
					}

					if( bMove == true )
					{
						bMove = enumCols.MoveNext();
						if( bMove == true )
							dataColumn = ( DataColumn )enumCols.Current;
					}
					
				}
				builder.Append( whereBuilder.ToString() );

				SQL = builder.ToString();
			}

			this.Close();
		}

		/// <summary>
		/// Records that a column is to be disabled and not used in the insert command
		/// or is used in the where section of the update command
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void OnDisableColumn(object sender, System.EventArgs e)
		{
			DataGridCell dataGridCell = dataGrid1.CurrentCell;

			bDisableArray[ dataGridCell.ColumnNumber ] = true;
		}

		/// <summary>
		/// Quit the edit dialog without saving changes
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void OnCancel(object sender, System.EventArgs e)
		{
			SQL = "";
			this.Close();
		}

	}
}