dbaccess.cs

分布式图书管理系统

using System;
using System.Data;
using System.Data.SqlClient;

namespace lib.factory
{
	/// <summary>
	/// DbAccess类,即进行数据库访问时需要调用的类
	/// </summary>
	public class DbAccess
	{
		/// <summary>
		/// DbAccess构造函数
		/// </summary>
		public DbAccess()
		{
		}

		/// <summary>
		/// 无条件查询操作,即查询表中所有记录
		/// </summary>
		/// <param name="strTableName">表名</param>
		/// <param name="strColumn">列名组</param>
		/// <returns>无条件查询结果</returns>
		public static DataSet SelectAll(string strTableName, string[] strColumn)
		{
			DataSet ds = new DataSet();
			Factory factory = Factory.GetInstance();
			AbstractDbFactory abstractDbFactory = factory.CreateInstance();
			IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
			concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
			concreteDbConn.Open();
			IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
			IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
			concreteDbCommand.Connection = concreteDbConn;
			concreteDbCommand.Transaction = concreteDbTrans;
			IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter();
			try
			{
				string strSql = "SELECT ";
				for(int i = 0; i < strColumn.Length - 1; i++)
				{
					strSql += (strColumn[i] + ", ");
				}
				strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName);
				concreteDbCommand.CommandText = strSql;
				concreteDbAdapter.SelectCommand = concreteDbCommand;    
				int k=concreteDbAdapter.Fill(ds);
				concreteDbTrans.Commit();
			}
			catch
			{
				concreteDbTrans.Rollback();
				ds.Clear();
				throw;
			}
			finally
			{
				concreteDbConn.Close();
			}
			return ds;

		}

		/// <summary>
		/// 条件查询操作
		/// </summary>
		/// <param name="strTableName">表名</param>
		/// <param name="strColumn">列名组</param>
		/// <param name="strCondition">条件"where ..."</param>
		/// <returns>条件查询结果</returns>
		public static DataSet Select(string strTableName, string[] strColumn, string strCondition)
		{
			DataSet ds = new DataSet();
			Factory factory = Factory.GetInstance();
			AbstractDbFactory abstractDbFactory = factory.CreateInstance();
			IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
			concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
			concreteDbConn.Open();
			IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
			IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
			concreteDbCommand.Connection = concreteDbConn;
			concreteDbCommand.Transaction = concreteDbTrans;
			IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter();
			try
			{
				string strSql = "SELECT ";
				for(int i = 0; i < strColumn.Length - 1; i++)
				{
					strSql += (strColumn[i] + ", ");
				}
				strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName  + strCondition);
				concreteDbCommand.CommandText = strSql;
				concreteDbAdapter.SelectCommand = concreteDbCommand;    
				concreteDbAdapter.Fill(ds);
				concreteDbTrans.Commit();
			}
			catch
			{
				concreteDbTrans.Rollback();
				ds.Clear();
				throw;
			}
			finally
			{
				concreteDbConn.Close();
			}
			return ds;
		}

		/// <summary>
		/// 单条记录的插入操作
		/// </summary>
		/// <param name="strTableName">表名</param>
		/// <param name="strColumn">列名组</param>
		/// <param name="strValue">值组</param>
		public static void Insert(string strTableName, string[] strColumn, object[] strValue)
		{
			Factory factory = Factory.GetInstance();
			AbstractDbFactory abstractDbFactory = factory.CreateInstance();
			IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
			concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();   
			concreteDbConn.Open();
			IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
			IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
			concreteDbCommand.Connection = concreteDbConn;
			concreteDbCommand.Transaction = concreteDbTrans;
			try
			{
				string strSql = "INSERT INTO " + strTableName + " (";
				for(int i = 0; i < strColumn.Length - 1; i++)
				{
					strSql += (strColumn[i] + ", ");
				}
				strSql += (strColumn[strColumn.Length - 1] + ") VALUES ('");
				for(int i = 0; i < strValue.Length - 1; i++)
				{
					strSql += (strValue[i] + "', '");
				}
				strSql += (strValue[strValue.Length - 1] + "')");
				concreteDbCommand.CommandText = strSql;
				concreteDbCommand.ExecuteNonQuery();
				concreteDbTrans.Commit();
			}
			catch
			{
				concreteDbTrans.Rollback();
				throw;
			}
			finally
			{
				concreteDbConn.Close();
			}   
		}

		/// <summary>
		/// 批量记录的插入操作,即可一次向多张表中插入不同的批量记录
		/// </summary>
		/// <param name="ds">批量记录组成的DataSet,DataSet中的各个DataTable名为表名,各DataTable中的DataColumn名为列名</param>
		public static void InsertSet(ref DataSet ds)
		{
			Factory factory = Factory.GetInstance();
			AbstractDbFactory abstractDbFactory = factory.CreateInstance();
			IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
			concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();   
			concreteDbConn.Open();
			IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
			IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
			concreteDbCommand.Connection = concreteDbConn;
			concreteDbCommand.Transaction = concreteDbTrans;
			try
			{
				foreach(DataTable dt in ds.Tables)
				{
					foreach(DataRow dr in dt.Rows)
					{
						string strSql = "INSERT INTO " + dt.TableName + " (";
						for(int i = 0; i < dt.Columns.Count - 1; i++)
						{
							strSql += (dt.Columns[i].Caption + ", ");
						}
						strSql += (dt.Columns[dt.Columns.Count - 1].Caption + ") VALUES ('");
						for(int i = 0; i < dt.Columns.Count - 1; i++)
						{
							strSql += (dr[i] + "', '");
						}
						strSql += (dr[dt.Columns.Count - 1] + "')");
						concreteDbCommand.CommandText = strSql;
						concreteDbCommand.ExecuteNonQuery();
					}
				}
				concreteDbTrans.Commit();
			}
			catch
			{
				concreteDbTrans.Rollback();
				throw;
			}

			finally
			{
				concreteDbConn.Close();
			}   
		}

		/// <summary>
		/// 无条件删除操作,即删除表中所有记录
		/// </summary>
		/// <param name="strTableName">表名</param>
		public static void DeleteAll(string strTableName)
		{
			Factory factory = Factory.GetInstance();
			AbstractDbFactory abstractDbFactory = factory.CreateInstance();
			IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
			concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
			concreteDbConn.Open();
			IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
			IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
			concreteDbCommand.Connection = concreteDbConn;
			concreteDbCommand.Transaction = concreteDbTrans;
			try
			{
				string strSql = "DELETE FROM " + strTableName;
				concreteDbCommand.CommandText = strSql;
				concreteDbCommand.ExecuteNonQuery();
				concreteDbTrans.Commit();
			}
			catch
			{
				concreteDbTrans.Rollback();
				throw;
			}
			finally
			{
				concreteDbConn.Close();
			}   
		}

		/// <summary>
		/// 条件删除操作
		/// </summary>
		/// <param name="strTableName">表名</param>
		/// <param name="strCondition">条件</param>
		public static void Delete(string strTableName, string strCondition)
		{
			Factory factory = Factory.GetInstance();
			AbstractDbFactory abstractDbFactory = factory.CreateInstance();
			IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
			concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
			concreteDbConn.Open();
			IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
			IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
			concreteDbCommand.Connection = concreteDbConn;
			concreteDbCommand.Transaction = concreteDbTrans;
			try
			{
				string strSql = "DELETE FROM " + strTableName + " WHERE " + strCondition;
				concreteDbCommand.CommandText = strSql;
				concreteDbCommand.ExecuteNonQuery();
				concreteDbTrans.Commit();
			}
			catch
			{
				concreteDbTrans.Rollback();
				throw;
			}
			finally
			{
				concreteDbConn.Close();
			}   
		}

		/// <summary>
		/// 无条件更新操作,即更新表中所有记录
		/// </summary>
		/// <param name="strTableName">表名</param>
		/// <param name="strColumn">列名组</param>
		/// <param name="strValue">值组</param>
		public static void UpdateAll(string strTableName, string[] strColumn, object[] strValue)
		{
			Factory factory = Factory.GetInstance();
			AbstractDbFactory abstractDbFactory = factory.CreateInstance();   
			IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
			concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
			concreteDbConn.Open();
			IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
			IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
			concreteDbCommand.Connection = concreteDbConn;
			concreteDbCommand.Transaction = concreteDbTrans;
			try
			{
				string strSql = "UPDATE " + strTableName + " SET ";
				for(int i = 0; i < strColumn.Length - 1; i++)
				{
					strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
				}
				strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' ");
				concreteDbCommand.CommandText = strSql;
				concreteDbCommand.ExecuteNonQuery();
				concreteDbTrans.Commit();
			}
			catch
			{
				concreteDbTrans.Rollback();
				throw;
			}
			finally
			{
				concreteDbConn.Close();
			}
		}

		/// <summary>
		/// 条件更新操作
		/// </summary>
		/// <param name="strTableName">表名</param>
		/// <param name="strColumn">列名组</param>
		/// <param name="strValue">值组</param>
		/// <param name="strCondition">条件</param>
		public static void Update(string strTableName, string[] strColumn, object[] strValue, string strCondition)
		{
			Factory factory = Factory.GetInstance();
			AbstractDbFactory abstractDbFactory = factory.CreateInstance();   
			IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
			concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();
			concreteDbConn.Open();
			IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
			IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
			concreteDbCommand.Connection = concreteDbConn;
			concreteDbCommand.Transaction = concreteDbTrans;
			try
			{
				string strSql = "UPDATE " + strTableName + " SET ";
				for(int i = 0; i < strColumn.Length - 1; i++)
				{
					strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
				}
				strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' " + " WHERE " + strCondition);
				concreteDbCommand.CommandText = strSql;
				concreteDbCommand.ExecuteNonQuery();
				concreteDbTrans.Commit();
			}
			catch
			{
				concreteDbTrans.Rollback();
				throw;
			}
			finally
			{
				concreteDbConn.Close();
			}
		}
		
		/// <summary>
		/// 执行全是输入参数的存储过程不返回值.
		/// 调用行为存储过程
		/// </summary>
		/// <param name="spname">存储过程名称</param>
		/// <param name="paramnames">参数名称数组</param>
		/// <param name="values">参数值数组</param>
		public static DataSet ExecProcedure(string spname,string[] paramnames,object[] values,SqlDbType[] paramtypes ,int [] large)
		{
			Factory factory = Factory.GetInstance();
			AbstractDbFactory abstractDbFactory = factory.CreateInstance();
			IDbConnection concreteDbConn = abstractDbFactory.CreateConnection();
			concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString();   
			concreteDbConn.Open();

			IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter();
			IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand();
			IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn);
			concreteDbCommand.Connection = concreteDbConn;
			concreteDbCommand.Transaction = concreteDbTrans;
			DataSet backDataSet=new DataSet();
			try
			{
				concreteDbCommand.Connection=concreteDbConn;
				concreteDbCommand.CommandText=spname;
				concreteDbCommand.CommandType=CommandType.StoredProcedure;

				for(int i = 0; i < paramnames.Length ; i++)
				{
					SqlParameter work;
					if(large[i]>0)
					{	
						work=new SqlParameter(paramnames[i],paramtypes[i],large[i]);
					}
					else
					{
						work=new SqlParameter(paramnames[i],paramtypes[i]);
					}
					work.Value=Convert.ToString(values[i]);
					concreteDbCommand.Parameters.Add(work);
				}
				concreteDbAdapter.SelectCommand=concreteDbCommand;
				concreteDbAdapter.Fill(backDataSet);
				concreteDbTrans.Commit();
			}
			catch
			{
				concreteDbTrans.Rollback();
				throw;
			}
			finally
			{
				concreteDbConn.Close();
			}   
			return 	backDataSet;
		}
	}
}