identitydataoperation.cs

一个通用的数据库访问层

using System;
using System.Data;
using System.Data.OleDb;
using System.Collections; 
using System.Windows.Forms; 

namespace YariSoft.DBUtil
{
	public class IdentityDataOperation :DBBaseOperation
	{
		#region Local variables
		private ArrayList		identityColumns		= null;
		private ArrayList		readOnlyColumns		= null;
		private OleDbCommand	identityCommand		= null;	
		#endregion

		public IdentityDataOperation ( OleDbConnection Connection, DataView Source, DataView Destination, System.Collections.ArrayList SelectedRows ):
			base ( Connection, Source, SelectedRows ) 
		{
			this.destination = Destination;
		}

		public bool Exec( OnDataChangedEventHandler OnDestinationDataChanged )
		{
			bool status = false;
			ConnectionState previousConnectionState = this.connection.State;
			
			try{
				if( previousConnectionState == ConnectionState.Closed ){
					this.connection.Open();
				}

				DataCopyOperation rowOperation = new DataCopyOperation( this.connection, (( DataView )this.source ), (( DataView )this.destination ), this.selectedRows );
				rowOperation.OnDestinationDataChanged += new YariSoft.DBUtil.DBBaseOperation.OnDataChangedEventHandler ( OnDestinationDataChanged );

				this.PrepareIdentityArrays ( this.connection, rowOperation, ( DataView )this.destination );

				if( rowOperation != null ){
					status = rowOperation.Exec();
					rowOperation.Dispose();
				}

			} finally {
				this.DisposeIdentityArrays ( this.connection, (( DataView ) this.destination ).Table.TableName );
			}

			if( previousConnectionState == ConnectionState.Closed ){
				this.connection.Close();
			}

			return status;
		}

		#region Private functions
		private void PrepareIdentityArrays( OleDbConnection DestConnection, DataCopyOperation RowOperation, DataView Destination )
		{
			if( this.identityColumns == null ){
				this.identityColumns = new ArrayList();
			}
			if( this.readOnlyColumns  == null ){
				this.readOnlyColumns = new ArrayList();
			}
			
			foreach( DataColumn column in Destination.Table.Columns ){
				if( column.AutoIncrement ){
					identityColumns.Add( column );
					column.AutoIncrement = false;
					if( column.ReadOnly ){
						column.ReadOnly = false;
						readOnlyColumns.Add( column );
					}
				}
			}
			if( this.ResetIdentityState( DestConnection, Destination.Table.TableName, true ) ){
				this.PrepareInsertCommand ( Destination.Table, DestConnection );
				if( identityColumns.Count > 0 ){
					RowOperation.OnNeedInsertIdentity += new DataCopyOperation.OnNeedInsertIdentityEventHandler( this.OnNeedInsertIdentity );
				}
			}
		}

		private void PrepareInsertCommand( DataTable Table, OleDbConnection DestConnection )
		{
			string insertSQL = "INSERT INTO [" + Table.TableName + "] (";
			string paramNames = "";
			
			foreach( DataColumn column in Table.Columns ){
				if( ! column.ReadOnly ){
					insertSQL += column.ColumnName + ",";
					paramNames += "?,";
				}
			}
			insertSQL  = insertSQL.Substring ( 0, insertSQL.Length - 1 );
			insertSQL += ") VALUES (";
			insertSQL += paramNames.Substring( 0, paramNames.Length - 1 ) + ")";

			this.identityCommand =new OleDbCommand( insertSQL, DestConnection );
		}

		private bool ResetIdentityState( OleDbConnection DestConnection, string TableName, bool Reset ) 
		{
			bool status = true;
			string SQL = "SET IDENTITY_INSERT [" + TableName + "] ";
			if( Reset ){
				SQL += "ON";
			} else {
				SQL += "OFF";
			}

			if( SQL != "" ){
				ConnectionState prevState = DestConnection.State;
				try{
					if( prevState == ConnectionState.Closed ){
						DestConnection.Open();
					}
					OleDbCommand command = new OleDbCommand ( SQL, DestConnection );
					command.ExecuteNonQuery();
					command.Dispose();
				}catch( Exception Exp ){
					YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
					status = false;
				}
				if( prevState == ConnectionState.Closed ){
					DestConnection.Close();
				}
			}

			return status;
		}

		private void DisposeIdentityArrays( OleDbConnection DestConnection, string TableName )
		{
			this.ResetIdentityState( DestConnection, TableName, false );

			foreach( DataColumn column in this.identityColumns ){
				column.AutoIncrement = true;
			}

			foreach( DataColumn column in this.readOnlyColumns ){
				column.ReadOnly = true;
			}
			this.identityColumns.Clear();
			this.readOnlyColumns.Clear();
			if( this.identityCommand != null ){
				this.identityCommand.Dispose();
			}
		}

		private bool OnNeedInsertIdentity( DataTable TableToUpdate, DataRow RowToUpdate, ref bool Updated )
		{
			Updated = false;
			try{
				this.identityCommand.Parameters.Clear();
				foreach( DataColumn column in TableToUpdate.Columns ){
					if( ! column.ReadOnly ){
						this.identityCommand.Parameters.Add( new OleDbParameter( column.ColumnName, RowToUpdate[ column ] ) );
					}
				}
				if( this.identityCommand.ExecuteNonQuery() == 1 ){
					Updated = true;
				}
			} catch ( Exception Exp ){
				YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
				return false;
			}
			
			return true;
		}
		#endregion

	}
}