dbcreatefkconstraintoperation.cs

一个通用的数据库访问层

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

namespace YariSoft.DBUtil
{
	public class DBCreateFKConstraintOperation : DBSchemaOperation
	{
		#region Local variables
		private ForeignKeyConstraint constraint;
		#endregion

		#region Constructor/Destructor
		public DBCreateFKConstraintOperation( OleDbConnection Connection, ForeignKeyConstraint Constraint )
		{
			this.connection = Connection;
			this.constraint = Constraint;
		}
		#endregion

		#region Public functions
		public override bool PrepareSQL()
		{
			bool status = false;
			if( this.connection.Provider.ToUpper().Substring( 0, 8 ) == "SQLOLEDB" ){
				status = this.PrepareMSSQLString();
			} else {
				YariSoft.Utils.YMessages.Show( "Unsupported OLE DB data provider!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
			}
			return status;
		}
		#endregion

		#region Private functions
		private bool PrepareMSSQLString()
		{
			bool status = true;
			bool tablesFound = false;
			ConnectionState previousConnectionState = this.connection.State;
			try{
				if( previousConnectionState == ConnectionState.Closed ){
					this.connection.Open();
				}
				if(		Util.IsTableExists( this.connection, this.constraint.Table.TableName, this._transaction ) 
					&&	Util.IsTableExists( this.connection, this.constraint.RelatedTable.TableName, this._transaction ) ){
					tablesFound	 = true;
				}
				
			} catch( Exception Exp ){
				YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
				status = false;
			}

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

			if( ! status ){
				return status;
			}

			if( tablesFound ){
				string tempSQL ="ALTER TABLE [" + this.constraint.Table.TableName  + "] ";
				tempSQL += "ADD CONSTRAINT " + this.constraint.ConstraintName + " ";
				tempSQL += "FOREIGN KEY (";
				foreach ( DataColumn column in this.constraint.Columns ){
					tempSQL	+= column.ColumnName + ", ";
				}
				tempSQL = tempSQL.Substring ( 0, tempSQL.Length - 2 ) + ") ";
				tempSQL	+= "REFERENCES " + constraint.RelatedTable.TableName + "(";
				foreach ( DataColumn column in this.constraint.RelatedColumns ){
					tempSQL	+= column.ColumnName + ", ";
				}
				tempSQL = tempSQL.Substring ( 0, tempSQL.Length - 2 ) + ") ";
				/*
				if( this.constraint.UpdateRule == Rule.Cascade ){
					tempSQL += " ON UPDATE CASCADE ";
				}
				if( this.constraint.DeleteRule == Rule.Cascade ){
					tempSQL += " ON DELETE CASCADE ";
				}
				*/
				this.strSQL += "\r\n" + tempSQL;
			}

			return status;
		}
		#endregion
	}
}