dbschema.cs

一个通用的数据库访问层

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

namespace YariSoft.DBUtil
{
	public class SchemaGenerator
	{
		#region Local variables
		private DataSet _schema = null; 
		private DataTable _tables = null;
		private Hashtable _adapters = null;
		#endregion

		#region Properties
		public DataSet Schema 
		{
			get{ return this._schema; }
		}

		public DataTable Tables 
		{
			get{ return this._tables ; }
		}

		public Hashtable Adapters 
		{
			get{ return this._adapters ; }
		}


		#endregion

		#region Constructor/Destructor
		public SchemaGenerator()
		{
			this._schema = new DataSet();
			this._adapters = new Hashtable();
		}

		~SchemaGenerator()
		{
			this.Dispose();
		}

		public void Dispose()
		{
			if( this._schema != null ){
				this._schema.Dispose();
			}
			if( this._tables != null ){
				this._tables.Dispose();
			}
			if( this._adapters != null ){
				foreach ( YOleDbAdapter adapter in this._adapters.Values ){
					adapter.Dispose();
				}
			}
		}
		#endregion

		#region Public functions
		public bool BuildSchema( OleDbConnection Connection )
		{
			bool status = true;

			ConnectionState previousConnectionState = Connection.State;
			try{
				if( previousConnectionState == ConnectionState.Closed ){
					Connection.Open();
				}
				this._schema.Relations.Clear();
				foreach( DataTable table in this._schema.Tables ){
					for( int i = table.Constraints.Count - 1; i >= 0; i-- ){
						if( table.Constraints[i] is ForeignKeyConstraint ){
							table.Constraints.Remove(table.Constraints[i]);
						}
					}
				}
				this._schema.Tables.Clear();

				this._tables = Connection.GetOleDbSchemaTable( OleDbSchemaGuid.Tables,
																		new object[] {null, null, null, "TABLE"});
				this._tables.TableName = "__YS_Tables_Internal";
				foreach ( DataRow Row in this._tables.Rows ){
					if( ! this.AddTable( Connection, ( string )Row["TABLE_NAME"]) ){
						status = false;
						break;
					}
				}
				this._schema.EnforceConstraints = false;
				if( status ){
					status = this.AddConstraints( Connection );
				}

			} catch( Exception Exp ) {
				YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
				status = false;
			}

			if( previousConnectionState == ConnectionState.Closed ){
				Connection.Close();
			}
			return status;
		}
		#endregion

		#region Private functions
		private bool AddTable( OleDbConnection Connection, string TableName )
		{
			try {
				System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter( "SELECT * FROM ["+ TableName +"] Where 2 < 1", Connection );
				DataTable[] tables = dataAdapter.FillSchema( this._schema, System.Data.SchemaType.Source );
				foreach ( DataTable table in tables ){
					table.TableName = TableName;
				}
				dataAdapter.Dispose();
			} catch(Exception Exp ) {
				YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
				return false;
			}

			return true;
		}

		private bool AddConstraints( OleDbConnection Connection )
		{
			bool status = true;
			DataTable constraints = null;
			try {
				constraints = Connection.GetOleDbSchemaTable( OleDbSchemaGuid.Foreign_Keys, new object[] {});
				foreach ( DataRow row in constraints.Rows ){
					DataColumn pCol = this._schema.Tables[row["PK_TABLE_NAME"].ToString()].Columns[row["PK_COLUMN_NAME"].ToString()];
					DataColumn fCol = this._schema.Tables[row["FK_TABLE_NAME"].ToString()].Columns[row["FK_COLUMN_NAME"].ToString()];
					this._schema.Relations.Add( row["FK_NAME"].ToString(), pCol, fCol );
				}
			} catch(Exception Exp ) {
				YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
				status = false;
			}

			if( constraints != null ){
				constraints.Dispose();
			}
			return status;
		}
		#endregion
	}
}