util.cs

一个通用的数据库访问层

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

namespace YariSoft.DBUtil
{
	public class Util
	{
		#region Constructor/Destructor
		public Util()
		{
		}
		#endregion

		#region Public functions
		static public bool PrepareConnectionString ( IDbConnection Connection ) 
		{
			try {
				MSDASC.DataLinksClass dlc = new MSDASC.DataLinksClass();
				ADODB.Connection conn;
				if( Connection.ConnectionString != "" ){
					conn = new ADODB.Connection();
					conn.ConnectionString = Connection.ConnectionString;
					object obj = ( object )conn;
					if( dlc.PromptEdit ( ref obj ) ){
						Connection.ConnectionString = conn.ConnectionString;
					}
				} else {
					conn = ( ADODB.Connection )dlc.PromptNew();  
					if( conn != null ){
						Connection.ConnectionString = conn.ConnectionString;
					}
				}
			} catch(Exception Exp) {
				YariSoft.Utils.YMessages.Show( Exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error );
				return false;
			}
			return true;
		}

		static public bool TryToSaveConcurrencyData ( DBConcurrencyException Exp, OleDbDataAdapter DataAdapter ) 
		{
			if( Exp == null || 
				Exp.Row == null || 
				DataAdapter == null || 
				DataAdapter.SelectCommand == null || 
				DataAdapter.SelectCommand.Connection == null ){
				return false;
			}
			
			if( Exp.Row.RowState != DataRowState.Modified ){
				return false;
			}

			bool result = true;
			ConnectionState prevState = DataAdapter.SelectCommand.Connection.State;
			OleDbCommand checkCommand = new OleDbCommand();
			OleDbCommand updateCommand = new OleDbCommand();
			try{
				checkCommand.Connection = DataAdapter.SelectCommand.Connection;
				checkCommand.CommandText = "SELECT COUNT(*) FROM [" + Exp.Row.Table.TableName + "]" ;
				
				updateCommand.Connection = DataAdapter.SelectCommand.Connection;
				updateCommand.CommandText = "UPDATE ["+ Exp.Row.Table.TableName +"] SET ";
				
				string conditionString = " WHERE ";

				foreach( DataColumn column in Exp.Row.Table.Columns ){
					if( ! column.AutoIncrement ){
						string curString = column.ColumnName + " = ?";
						updateCommand.CommandText	+= curString +",";

						object val = Exp.Row[column, DataRowVersion.Current];
						if( Exp.Row.HasVersion( DataRowVersion.Proposed ) ){
							val = Exp.Row[column, DataRowVersion.Proposed];
						}

						updateCommand.Parameters.Add( new OleDbParameter( ( column.ColumnName ), val ) );

						conditionString += "(" + curString;

						if( column.AllowDBNull ){
							conditionString += " OR ( ? IS NULL AND " + column.ColumnName + " IS NULL )";
						}
						conditionString += ") AND";

						if( Exp.Row.HasVersion( DataRowVersion.Original ) ){
							val = Exp.Row[column, DataRowVersion.Original];
						}

						checkCommand.Parameters.Add( new OleDbParameter( ( "Original_" + column.ColumnName ), val ) );

						if( column.AllowDBNull ){
							checkCommand.Parameters.Add( new OleDbParameter( ( "Original_" + column.ColumnName + "1" ), val ) );
						}
					}
				}

				conditionString			 = conditionString.Substring( 0, conditionString.Length - 4 );
				checkCommand.CommandText += conditionString;
				
				if ( prevState == ConnectionState.Closed ){
					DataAdapter.SelectCommand.Connection.Open();
				}

				if( (int)checkCommand.ExecuteScalar()== 1 ){
					updateCommand.CommandText  = updateCommand.CommandText.Substring ( 0, updateCommand.CommandText.Length - 1 );
					updateCommand.CommandText += conditionString;
				
					OleDbParameter[] paramsArray = new OleDbParameter[checkCommand.Parameters.Count];
					checkCommand.Parameters.CopyTo(paramsArray, 0);
					checkCommand.Parameters.Clear();

					foreach( OleDbParameter param in paramsArray ){
						updateCommand.Parameters.Add( param );
					}

					if( updateCommand.ExecuteNonQuery() != 1 ){
						result = false;
					}
				} else {
					result = false;
				}

				if ( prevState == ConnectionState.Closed ){
					DataAdapter.SelectCommand.Connection.Close();
				}
				//Exp.Row.ClearErrors();

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

			if ( prevState == ConnectionState.Closed ){
				DataAdapter.SelectCommand.Connection.Close();
			}

			checkCommand.Dispose();
			updateCommand.Dispose();

			return result;
		}

		static public bool IsTableExists ( OleDbConnection Connection, string TableName, OleDbTransaction Transaction ) 
		{
			bool status = true;
			OleDbCommand command = null;
			try{
				command = new OleDbCommand("Select count(*)From ["+ TableName +"] Where 2<1", Connection );
				if( Transaction != null ){
					command.Transaction = Transaction;
				}
				command.ExecuteNonQuery();
			}catch{
				status = false;
			}
			if( command != null ){
				command.Dispose();
			}
			return status;
		}

		static public DataTable GetTableFromDB ( OleDbConnection Connection, string SQL, string TableName  ) 
		{
			OleDbCommand command		= new OleDbCommand( SQL, Connection );
			OleDbDataAdapter adapter	= new OleDbDataAdapter();
			adapter.SelectCommand		= command;
			DataSet dataset				= new DataSet();
			adapter.Fill( dataset, TableName );
			DataTable result = dataset.Tables[ TableName ].Copy();
			dataset.Dispose();
			adapter.Dispose();
			command.Dispose();
			return result;
		}

		static public DataTable GetTableSchemaFromDB ( OleDbConnection Connection, string TableName ) 
		{
			string SQL = "Select * From ["+ TableName +"]";
			OleDbCommand command		= new OleDbCommand( SQL, Connection );
			OleDbDataAdapter adapter	= new OleDbDataAdapter();
			adapter.SelectCommand		= command;
			DataTable table = new DataTable( TableName );
			DataTable result = adapter.FillSchema ( table, System.Data.SchemaType.Source );
			table.Dispose();
			adapter.Dispose();
			command.Dispose();
			return result;
		}


		public static bool CompareColumns( DataColumn Source, DataColumn Destination )
		{
			if( Source.AllowDBNull != Destination.AllowDBNull ){
				return false;
			}
			if( Source.AutoIncrement != Destination.AutoIncrement ){
				return false;
			}
			if( Source.AutoIncrementStep != Destination.AutoIncrementStep ){
				return false;
			}
			if( Source.DataType != Destination.DataType ){
				return false;
			}
			if( Source.DefaultValue != Destination.DefaultValue ){
				return false;
			}
			if( Source.MaxLength != Destination.MaxLength ){
				return false;
			}
			if( Source.ReadOnly != Destination.ReadOnly ){
				return false;
			}
			if( Source.Unique != Destination.Unique ){
				return false;
			}

			return true;
		}

		public static string GetTableName( string TableName )
		{
			string result = TableName;
			result = "[" + result + "]";
			return result;
		}
		
		public static string GetTableNameByFK( string FKName, DataSet Schema )
		{
			int dotPosition = FKName.LastIndexOf( "." ) ;
			if( dotPosition > -1 ){
				FKName = FKName.Substring( ( dotPosition + 1 ), ( FKName.Length - dotPosition - 1 ) );
			}
			
			string childTableName = "";
			DataRelation relation = Schema.Relations[ FKName ];
			if( relation == null ){
				return "";
			} else {
				childTableName = relation.ChildTable.TableName;
			}
			return childTableName;
		}

		#endregion
	}
}