form1.cs

C# 使用OLEDB

			this.label4.Text = "Database File";
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(8, 8);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(64, 16);
			this.label1.TabIndex = 0;
			this.label1.Text = "Driver";
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(128, 8);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(64, 16);
			this.label2.TabIndex = 0;
			this.label2.Text = "User ID";
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(592, 341);
			this.Controls.AddRange(new System.Windows.Forms.Control[] {
																		  this.Select_button,
																		  this.Delete,
																		  this.Insert,
																		  this.Edit,
																		  this.button3,
																		  this.listView1,
																		  this.button2,
																		  this.button1,
																		  this.textBox4,
																		  this.textBox3,
																		  this.textBox2,
																		  this.textBox1,
																		  this.label4,
																		  this.label3,
																		  this.label2,
																		  this.label1});
			this.Name = "Form1";
			this.Text = "CSharpOLEDB3";
			this.ResumeLayout(false);

		}
		#endregion

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

		/// <summary>
		/// Open a file dialog and browse for an access database
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void OnBrowse(object sender, System.EventArgs e)
		{
			openFileDialog1.InitialDirectory = Directory.GetCurrentDirectory();
			openFileDialog1.Filter = "Access Files ( *.mdb )| *.mdb";
			openFileDialog1.FilterIndex = 1;
			
			if( openFileDialog1.ShowDialog() == DialogResult.OK )
			{
				DatabaseName = openFileDialog1.FileName;
				textBox4.Text = DatabaseName;
			}

		}

		/// <summary>
		/// Quit the application
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void OnOK(object sender, System.EventArgs e)
		{
			Application.Exit();
		}

		/// <summary>
		/// Display the columns in the list
		/// </summary>
		/// <param name="reader"></param>
		public void DisplayList( OleDbDataReader reader )
		{
			listView1.Clear();

			int nCount = reader.FieldCount;

			/// build the headers first
			
			for( int i=0; i<nCount; i++ )
			{
				ColumnHeader header = new ColumnHeader();
				header.Width = 100;
				header.Text = reader.GetName( i );
				listView1.Columns.Add( header );
				/// Store the header names in a collection
				stringCol.Add( reader.GetName( i ) );
			}

			/// now add the data
			ListViewItem lvItem = new ListViewItem();

			while( reader.Read() == true )
			{
				lvItem = listView1.Items.Add( reader.GetValue( 0 ).ToString() );
				for( int i=1; i<nCount; i++ )
				{
					lvItem.SubItems.Add( reader.GetValue( i ).ToString() );
				}
			}
		}


		/// <summary>
		/// Open the database
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void OnOpenDatabase(object sender, System.EventArgs e)
		{
			if( textBox1.Text == null  )
			{
				MessageBox.Show( "You need to enter a database driver" );
				return;
			}
			else
				Provider = textBox1.Text;

			if( textBox2.Text == null )
			{
				MessageBox.Show( "You need to enter a user id" );
				return;
			}
			else
				UserID = textBox2.Text;

			Password = textBox3.Text;

			/// the password can be blank so skip it
			if( textBox4.Text == null )
			{
				MessageBox.Show( "You need to enter a database file" ); 
				return;
			}
			else
				DatabaseName = textBox4.Text;


			if( dbAccess != null )
			{
				if( dbAccess.IsOpen == true )
				{
					dbAccess.Close();
				}
			}

			dbAccess = new GenericOLEDBClass();
			dbAccess.Open( Provider, UserID, Password, DatabaseName, Mode );

			/// set the insert command and run it
			if( SelectCommand == null )
			{
				MessageBox.Show( "you need to generate a select command first" );
				return;
			}

			dbAccess.SelectCommand = SelectCommand;

			if( dbAccess.ExecuteCommand() == true )
			{
				DisplayList( dbAccess.GetReader );
			}
			else
			{
				MessageBox.Show( dbAccess.ErrorMessage );
				return;
			}

		}

		/// <summary>
		/// Edit the selected record
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void OnEdit(object sender, System.EventArgs e)
		{
			ListView.SelectedListViewItemCollection col = listView1.SelectedItems;

			IEnumerator colEnum = col.GetEnumerator();
			
			/// move to the first and only
			colEnum.MoveNext();

			/// get the list view item
			ListViewItem item = ( ListViewItem )colEnum.Current;

			/// get the collection of subitems
			ListViewItem.ListViewSubItemCollection subItemsCol = item.SubItems;

			dbAccess.SelectCommand = SelectCommand;
			if( dbAccess.ExecuteCommand() == false )
			{
				MessageBox.Show( dbAccess.ErrorMessage );
				return;
			}

			try
			{

				OleDbDataReader reader = dbAccess.GetReader;

				ListViewItem.ListViewSubItem subItem = null;
				bool bFound = false;
				bool bChecked = true;

				while( bFound == false && reader.Read() == true )
				{
					IEnumerator subEnum = subItemsCol.GetEnumerator();

					subEnum.MoveNext();

					/// check each field is identical

					bChecked = true;

					for( int i=0; i<reader.FieldCount; i++ )
					{
						if( bChecked == true )
						{
							subItem = ( ListViewItem.ListViewSubItem )subEnum.Current;
							if( subItem.Text != reader.GetValue( i ).ToString() )
							{
								bChecked = false;		
							}

							subEnum.MoveNext();
						}
					}

					if( bChecked == true )
						bFound = true;
				}

				

				EditBox dlg = new EditBox( this, reader, Table, false );
				dlg.ShowDialog();

				UpdateCommand = dlg.SQL;
				dbAccess.UpdateCommand = UpdateCommand;

				if( dbAccess.ExecuteCommand() == true )
				{
					dbAccess.SelectCommand = SelectCommand;
					if( dbAccess.ExecuteCommand() == true )
					{
						DisplayList( dbAccess.GetReader );
					}
					else
					{
						MessageBox.Show( dbAccess.ErrorMessage );
					}
				}
				else
				{
					MessageBox.Show( dbAccess.ErrorMessage );
				}
			}
			catch( NullReferenceException nullRef )
			{
				StringBuilder builder = new StringBuilder();
				builder.Append( "Error Reader Equals null " + nullRef.Message );
				MessageBox.Show( builder.ToString() );
			}
		}

		/// <summary>
		/// Delete the selected record
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void OnDelete( object sender, System.EventArgs e )
		{
			ListView.SelectedListViewItemCollection col = listView1.SelectedItems;

			IEnumerator colEnum = col.GetEnumerator();

			/// move to the first and only
			colEnum.MoveNext();

			/// get the list view item
			ListViewItem item = ( ListViewItem )colEnum.Current;

			/// get the collection of subitems
			ListViewItem.ListViewSubItemCollection subItemsCol = item.SubItems;

			IEnumerator subEnum = subItemsCol.GetEnumerator();
			StringEnumerator stringEnum = stringCol.GetEnumerator();

			StringBuilder builder = new StringBuilder();

			builder.Append( "DELETE FROM " );
			builder.Append( Table );
			builder.Append( " WHERE " );

			bool bFirst = true;

			OleDbDataReader reader = dbAccess.GetReader;
			reader.Read();
			int nColumn = 0;
			string strType;

			while( subEnum.MoveNext() == true && stringEnum.MoveNext() == true )
			{
				
				ListViewItem.ListViewSubItem subItem = ( ListViewItem.ListViewSubItem )subEnum.Current;
				strType = reader.GetDataTypeName( nColumn++ );

				if( subItem.Text != "" )
				{
					if( bFirst == false )
					{
						builder.Append( " AND" );
					}
					else
						bFirst = false;

					builder.Append( " [" );
					builder.Append( stringEnum.Current );
					if( strType == "DBTYPE_I4" )
						builder.Append( "] = " );
					else
						builder.Append( "] = '" );
					builder.Append( subItem.Text );
					if( strType != "DBTYPE_I4" )
						builder.Append( "'" );
				}
				
			}

			DeleteCommand = builder.ToString();

			dbAccess.DeleteCommand = DeleteCommand;

			if( dbAccess.ExecuteCommand() == true )
			{
				OleDbDataReader dbReader = dbAccess.GetReader;
				DisplayList( dbReader );
			}
			else
			{
				MessageBox.Show( dbAccess.ErrorMessage );
				return;
			}
		}


		/// <summary>
		/// Insert a new record
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void OnInsert(object sender, System.EventArgs e)
		{
			OleDbDataReader reader = null;

			dbAccess.SelectCommand = SelectCommand;
			if( dbAccess.ExecuteCommand() == false )
			{
				MessageBox.Show( dbAccess.ErrorMessage );
				return;
			}

			try
			{
				EditBox dlg = new EditBox( this, dbAccess.GetReader, Table, true );
				dlg.ShowDialog();

				InsertCommand = dlg.SQL;

				dbAccess.InsertCommand = InsertCommand;

				if( dbAccess.ExecuteCommand() == true )
				{
					dbAccess.SelectCommand = SelectCommand;
					if( dbAccess.ExecuteCommand() == true )
					{
						DisplayList( dbAccess.GetReader );
					}
					else
					{
						MessageBox.Show( dbAccess.ErrorMessage );
					}
				}
				else
				{
					MessageBox.Show( dbAccess.ErrorMessage );
				}
			}
			catch( NullReferenceException nullRef )
			{
				StringBuilder builder = new StringBuilder();
				builder.Append( "Error Reader Equals null " + nullRef.Message );
				MessageBox.Show( builder.ToString() );
			}
		}

		/// <summary>
		/// Select rows from the table
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void OnSelectCommand(object sender, System.EventArgs e)
		{
			SelectBox dlg = new SelectBox();
			dlg.ShowDialog();

			StringBuilder builder = new StringBuilder();
			builder.Append( "SELECT " );
			builder.Append( dlg.Selection );
			builder.Append( " FROM " );
			builder.Append( dlg.Table );

			Table = dlg.Table;

			SelectCommand = builder.ToString();

		}

	}
}