dataaccess.cs

抽象工厂的典型实现

using System;
using System.Data;
using System.Data.Common;
using System.Collections;

#region 版权声明
///
/// 版权所有(C)2005,2006  作者：漆巧林。保留所有权利, [e-mail], [e-mail]
/// 
/// 作者不对本代码提供任何保证，因此，对于使用该代码带来的损害或者潜在的损害，作者不承担责任。
/// 在满足如下的条件下，你可以自由使用该代码：
/// 1. 非商业应用
/// 2. 保留本版权声明
/// 要进行商业应用，必须得到作者的书面许可。
/// 你可以修改和自由发布本代码，条件是保留前述的版权声明。
/// 
#endregion

namespace DataAccess
{
	/// <summary>
	/// 数据访问接口
	/// </summary>
	public interface IDataAccess
	{
		int Count { get; }

		/// <summary>
		/// 插入表中的记录
		/// </summary>
		/// <param name="dt"></param>
		void Insert(DataTable dt);

		/// <summary>
		/// 根据数据集中提供的数据修改数据库
		/// </summary>
		/// <param name="ds"></param>
		void Insert(DataSet ds);

		/// <summary>
		/// 更新一个表
		/// </summary>
		/// <param name="dt"></param>
		void Update(DataTable dt);

		/// <summary>
		/// 更新数据集
		/// </summary>
		/// <param name="ds"></param>
		void Update(DataSet ds);

		/// <summary>
		/// 删除指定主键值的记录
		/// </summary>
		/// <param name="keyName"></param>
		/// <param name="keyValue"></param>
		void Delete(string keyName, object keyValue);

		/// <summary>
		/// 删除数据表中标记为删除的记录
		/// </summary>
		/// <param name="dt"></param>
		void Delete(DataTable dt);

		/// <summary>
		/// 删除数据集中标记为删除的记录
		/// </summary>
		/// <param name="ds"></param>
		void Delete(DataSet ds);

		/// <summary>
		/// 根据条件删除特定的记录
		/// </summary>
		/// <param name="condition"></param>
		void Delete(ConditionList condition);

		/// <summary>
		/// 返回所有记录
		/// </summary>
		DataTable AllRecords { get; }

		/// <summary>
		/// 返回指定键值的记录
		/// </summary>
		/// <param name="keyName"></param>
		/// <param name="keyValue"></param>
		/// <returns></returns>
		DataTable GetDataByKey(string keyName, object keyValue);

		/// <summary>
		/// 返回符合条件的数据
		/// </summary>
		/// <param name="condition"></param>
		/// <returns></returns>
		DataTable GetDataByCondition(ConditionList condition);

		void Fill(DataSet ds);
		void Fill(DataTable table);
		void FillByKey(DataSet ds, string keyName, object keyValue);
		void FillByKey(DataTable table, string keyName, object keyValue);
		void FillByCondition(DataSet ds, ConditionList condition);
		void FillByCondition(DataTable table, ConditionList condition);
	};

	/// <summary>
	/// DataAccessBase :数据访问基本类。
	/// </summary>
	public abstract class DataAccessBase : IDataAccess
	{
		protected IDbConnection connection;
		protected DbFactory dbFactory;

		protected DataAccessBase(String connString, DbFactory factory)
		{
			dbFactory = factory;
			this.connection = factory.CreateConnection(connString); //.DefaultConnection;
		}

		public abstract string TableName { get; set; }

		/// <summary>
		/// 清除数据库中所有记录
		/// </summary>
		public void Clear()
		{
			IDbCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = String.Format("DELETE FROM {0}", this.TableName);
			try
			{
				this.connection.Open();
				cmd.ExecuteNonQuery();
			}
			finally
			{
				this.connection.Close();
			}
		}

		/// <summary>
		/// 插入表中的记录
		/// </summary>
		/// <param name="dt"></param>
		public virtual void Insert(DataTable dt)
		{
			this.Update(dt);
		}

		public virtual void Insert(DataSet ds)
		{
			this.Update(ds);
		}

		public virtual void Update(DataTable dt)
		{
			DataSet ds = new DataSet();
			ds.Tables.Add(dt);
			this.Update(ds);
			ds.Tables.Remove(dt);
		}

		public int Count
		{
			get
			{
				IDbCommand cmd = this.connection.CreateCommand();
				cmd.CommandText = "SELECT COUNT(*) FROM " + this.TableName;
				int count = 0;
				try
				{
					this.connection.Open();
					count = Convert.ToInt32(cmd.ExecuteScalar());
				}
				catch(Exception)
				{
					count = 0;
				}
				this.connection.Close();
				return count;
			}
		}

		/// <summary>
		/// 统计符合条件的记录数目
		/// </summary>
		/// <param name="condition"></param>
		/// <returns></returns>
		public int CountByCondition(ConditionList condition)
		{
			IDbCommand cmd = this.dbFactory.CreateCommand(String.Format("SELECT COUNT(*) FROM {0}", this.TableName));
			cmd.Connection = this.connection;
			this.dbFactory.AppendCommandCondition(cmd, condition);
			int val = 0;
			try
			{
				this.connection.Open();
				val = Convert.ToInt32(cmd.ExecuteScalar());
			}
			catch(Exception)
			{
				val = 0;
			}
			this.connection.Close();
			return val;
		}

		public object GetLastValue(string fieldName)
		{
			IDbCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = String.Format("SELECT MAX({0}) FROM {1}", fieldName, this.TableName);
			object val = null;
			try
			{
				this.connection.Open();
				val = cmd.ExecuteScalar();
			}
			catch(Exception )
			{
				val = null;
			}
			this.connection.Close();
			return val;
		}

		public void Update(DataSet ds)
		{
			IDbCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = String.Format("SELECT * FROM {0}", this.TableName);

			IDbDataAdapter adapter = this.dbFactory.CreateAdapter(cmd);
			DbCommandBuilder cbDa = this.dbFactory.CreateCommandBuilder(adapter);

			adapter.TableMappings.Add("Table", this.TableName);
			adapter.Update(ds);
		}

		public virtual void Delete(DataTable dt)
		{
			this.Update(dt);
		}

		public virtual void Delete(DataSet ds)
		{
			this.Update(ds);
		}

		public void Delete(string keyName, object keyValue)
		{
			DataTable dt = this.GetDataByKey(keyName, keyValue);
			if(dt.Rows.Count > 0)
			{
				for(int i=0; i<dt.Rows.Count; i++)
				{
					dt.Rows[i].Delete();
				}
				this.Delete(dt);
			}
		}

		public void Delete(ConditionList condition)
		{
			DataTable dt = this.GetDataByCondition(condition);
			if(dt.Rows.Count > 0)
			{
				for(int i=0; i<dt.Rows.Count; i++)
				{
					dt.Rows[i].Delete();
				}
				this.Delete(dt);
			}
		}

		public DataTable AllRecords
		{
			get
			{
				return this.GetDataByCondition(new ConditionList());
			}
		}

		public int CurrentIdentity
		{
			get
			{
				IDbCommand cmd = this.dbFactory.CreateIdentityGetter(this.TableName);
				cmd.Connection = this.connection;
				int val = -1;
				try
				{
					this.connection.Open();
					val = Convert.ToInt32(cmd.ExecuteScalar());
				}
				finally
				{
					this.connection.Close();
				}
				return val;
			}
		}

		public DataTable GetDataByKey(string keyName, object keyValue)
		{
			ConditionList condition = new ConditionList();
			condition.Add(keyName, '=', keyValue);
			return this.GetDataByCondition(condition);
		}

		public DataTable GetDataByCondition(ConditionList condition)
		{
			DataTable dt = new DataTable();
			dt.TableName = this.TableName;
			this.FillByCondition(dt, condition);
			return dt;
		}

		public void Fill(DataSet ds)
		{
			IDbCommand cmd = this.connection.CreateCommand();
			cmd.CommandText = String.Format("SELECT * FROM {0}", this.TableName);
			IDbDataAdapter adapter = this.dbFactory.CreateAdapter(cmd);

			adapter.TableMappings.Add("Table", this.TableName);
			adapter.Fill(ds);
		}
		public void Fill(DataTable table)
		{
			DataSet ds = new DataSet();
			ds.Tables.Add(table);
			this.Fill(ds);
			ds.Tables.Remove(table);
		}
		public void FillByKey(DataSet ds, string keyName, object keyValue)
		{
			// adapter.TableMappings.Add("Table", this.TableName);
			ConditionList condition = new ConditionList();
			condition.Add(keyName, '=', keyValue);
			this.FillByCondition(ds, condition);
		}
		public void FillByKey(DataTable table, string keyName, object keyValue)
		{
			ConditionList condition = new ConditionList();
			condition.Add(keyName, '=', keyValue);
			table.TableName = this.TableName;
			this.FillByCondition(table, condition);
		}
		public void FillByCondition(DataSet ds, ConditionList condition)
		{
			IDbCommand cmd = this.dbFactory.CreateSelectCommand(this.TableName, condition);
			cmd.Connection = this.connection;
			IDbDataAdapter adapter = this.dbFactory.CreateAdapter(cmd);

			adapter.TableMappings.Add("Table", this.TableName);
			adapter.Fill(ds);
		}
		public void FillByCondition(DataTable table, ConditionList condition)
		{
		//	table.TableName = this.TableName;
			DataSet ds = new DataSet();
			ds.Tables.Add(table);
			this.FillByCondition(ds, condition);
			ds.Tables.Remove(table);
		}

		/// <summary>
		/// 根据唯一约束的字段值获取指定表的指定字段值
		/// </summary>
		/// <param name="tableName">表名称</param>
		/// <param name="fieldName">要获取的字段名</param>
		/// <param name="uniqueFiled">具有唯一性的字段名称</param>
		/// <param name="uniqueValue">具有唯一性字段的值</param>
		/// <returns>返回第一个符合条件的字段值</returns>
		public object GetFieldByUniqueField(string tableName, string fieldName, string uniqueField, object uniqueValue)
		{
			ConditionList list = new ConditionList();
			list.Add(uniqueField, '=', uniqueValue);
			return this.GetFirstValueByCondition(tableName, fieldName, list);
		}

		/// <summary>
		/// 查找第一个符合指定条件的记录的指定字段的值
		/// </summary>
		/// <param name="tableName"></param>
		/// <param name="fieldName"></param>
		/// <param name="condition"></param>
		/// <returns></returns>
		public object GetFirstValueByCondition(string tableName, string fieldName, ConditionList condition)
		{
			IDbCommand cmd = this.dbFactory.CreateSelectCommand(tableName, condition);
			cmd.Connection = this.connection;

			this.connection.Open();

			object val = DBNull.Value;
			System.Data.IDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				if(reader.Read())
					val = reader[fieldName];
			}
			catch(Exception)
			{
				val = DBNull.Value;
			}
			finally
			{
				if(reader != null)
					reader.Close();
				this.connection.Close();
			}

			return val;
		}
	}

	public class DataAccessClassic : DataAccessBase, IDataAccess
	{
		string tableName;

		public DataAccessClassic(string tableName, string connectionString, DbFactory factory)
			: base(connectionString, factory)
		{
			this.tableName = tableName;
		}

		public override string TableName
		{
			get
			{
				return this.tableName;
			}
			set { this.tableName = value; }
		}
	}
}