bookshelfdal.cs

会员管理系统

using System;
using System.Data;
using System.Text;
using System.Data.OleDb;
using com.sungoal.MemberManage.Common;

namespace com.sungoal.MemberManage.DataAccess
{
	/// <summary>
	/// 关于书架信息的数据库操作类。
	/// </summary>
	public class BookShelfDAL
	{

		private const string TABLE_BOOKSHELF="BookShelf";
		private const string TABLE_BOOKSHELFDETAIL="BookShelfDetail";
		private const string PK_ID="BookShelfID";
		private const string SELECT_BOOKSHELF_BY_USER="select * from BookShelf where UserID=" ;
		private const string SELECT_BOOKSHELF_BY_BOOKSHELF = "select * from BookShelf where BookShelfID=";
		private const string SELECT_BOOKSHELF_BY_PBOOKSHELF = "select * from BookShelf where PBookShelfID=";
		private const string SELECT_BOOKSHELFDETAIL_BY_USER="select b.*  from BookShelf a,BookShelfDetail b where a.BookShelfID=b.BookShelfID and a.UserID =" ;
		private const string SELECT_BOOKSHELFDETAIL_BY_BOOKSHELF=" select * from BookShelfDetail where BookShelfID = ";
		private int retVal;

		public BookShelfDAL()
		{
		}

		#region		书架分类信息数据库操作

		/// <summary>
		/// 生成新的书架分类编号
		/// </summary>
		/// <param name="userID">用户ID</param>
		/// <param name="number">生成个数</param>
		/// <returns>书架分类编号</returns>
		public string[] CreateNewID(string userID, int number)
		{
			// 1。根据用户ID从书架分类表中获取该用户书架的最大编号
			// 2。根据步骤 1 得到的最大编号计算新的书架分类编号
			if (number > 99999)
			{
				return null;
			}

			string identity = userID;
			string[] serial = new string[number];
			string[] newID = new string[number];
			int maxID = 0;

			OleDbConnection conn = new OleDbConnection(AppConfiguration.GetConnectionString());
			OleDBHelper helper = new OleDBHelper(conn);

			string sqlString = "select max(BookShelfID) from BookShelf where UserID='" + userID + "'";
			DataSet dataSet = new DataSet();
			helper.ExecuteSQL(sqlString, dataSet);
			if (dataSet.Tables[0].Rows[0][0] == DBNull.Value)
			{
				serial[0] = "00000";
				maxID = 0;
				newID[0] = identity + serial[0];
			}
			else
			{
				serial[0] = dataSet.Tables[0].Rows[0][0].ToString();
				serial[0] = serial[0].Substring(serial[0].Length - 5, 5);
				maxID = Int32.Parse(serial[0]);
				maxID = maxID + 1;
				if (maxID > 99999)
				{
					throw new Exception("Too many BookShelf for this user!");
				}
				serial[0] = maxID.ToString();
				int zeroCount = 5 - (int)Math.Log10(maxID) - 1;
				for (int j = 0; j < zeroCount; j ++)
				{
					serial[0] = "0" + serial[0];
				}
				newID[0] = identity + serial[0];
			}
			for (int i = 1; i < number; i ++)
			{
				maxID ++;
				if (maxID > 99999)
				{
					throw new Exception("Too many BookShelf for this user!");
				}
				serial[i] = maxID.ToString();
				int zeroCount = 5 - (int)Math.Log10(maxID) - 1;
				for (int j = 0; j < zeroCount; j ++)
				{
					serial[i] = "0" + serial[i];
				}
				newID[i] = identity + serial[i];
			}

			return newID;
		}

		/// <summary>
		/// 检查用户ID根书架分类编号是否匹配
		/// </summary>
		/// <param name="userID">用户ID</param>
		/// <param name="bookShelfID">书架分类编号</param>
		/// <returns>
		/// 1：存在书架分类，匹配
		/// 0：存在书架分类，不匹配
		/// -1：不存在该书架分类，不存在根书架
		/// -2：不存在该书架分类，存在根书架
		/// -3：普通用户附加信息表不存在
		/// -4：用户不存在
		/// </returns>
		public int CheckBookShelfOwner(string userID, string bookShelfID)
		{
			// 1。根据书架分类编号在书架分类表中查找记录
			// 2。记录存在，跳到步骤 3，记录不存在，跳到步骤 5
			// 3。根据步骤 1 得到的记录检查用户ID是否匹配
			// 4。匹配，返回 1，不匹配，返回 0
			// 5。检查用户ID是否存在
			// 6。不存在，返回 -4
			// 7。根据用户ID在普通用户附加信息表中查找记录
			// 8。记录不存在，返回 -3
			// 9。根据步骤 5 得到的记录检查个人书架编号是否为空
			// 10。空，返回 -1，非空，返回 -2
			int result = 0;

			BookShelfDAL bookShelfDAL = new BookShelfDAL();
			BookShelfData.BookShelfDataTable btable = (BookShelfData.BookShelfDataTable)bookShelfDAL.GetBookShelfByBookShelfID(bookShelfID);
			if (btable.Rows.Count != 0)
			{
				BookShelfData.BookShelfRow row = (BookShelfData.BookShelfRow)btable.Rows[0];
				if (row.UserID == userID)
				{
					result = 1;
				}
				else
				{
					result = 0;
				}
			}
			else
			{
				UserDAL userDAL = new UserDAL();
				CommonUserData.UserBaseInfoDataTable table = (CommonUserData.UserBaseInfoDataTable)userDAL.GetUserBaseInfoByUserID(userID);
				if (table.Rows.Count == 0)
				{
					result = -4;
				}
				else
				{
					CommonUserData.UserAddInfoDataTable utable = (CommonUserData.UserAddInfoDataTable)userDAL.GetUserAddInfoByUserID(userID);
					if (utable.Rows.Count != 0)
					{
						CommonUserData.UserAddInfoRow row = (CommonUserData.UserAddInfoRow)utable.Rows[0];
						if (row["BookShelfID"] == DBNull.Value)
						{
							result = -1;
						}
						else
						{
							result = -2;
						}
					}
					else
					{
						result = -3;
					}
				}
			}

			return result;
		}

		/// <summary>
		/// 根据用户ID得到用户书架分类及其详细信息。
		/// </summary>
		/// <param name="userId">用户ID</param>
		/// <returns>书架信息的DataSet</returns>
		public DataSet	GetBookShelfInfoByUserID(string userId)
		{
			DataSet ds=new BookShelfData();
			new OleDBHelper().ExecuteSQL(SELECT_BOOKSHELF_BY_USER+userId,ds,"BookShelf");
			new OleDBHelper().ExecuteSQL(SELECT_BOOKSHELFDETAIL_BY_USER+userId,ds,"BookShelfDetail");            
			return ds;
		}

		/// <summary>
		/// 根据用户ID得到用户书架分类。
		/// </summary>
		/// <param name="userId">用户ID</param>
		/// <returns>书架分类的DataTable</returns>
		public DataTable GetBookShelfByUserID(string userId)
		{
			DataTable data=new BookShelfData().BookShelf;
			new OleDBHelper().ExecuteSQL(SELECT_BOOKSHELF_BY_USER + "'" + userId + "'", data);
			return data;
		}

		/// <summary>
		/// 根据书架分类编号得到书架分类
		/// </summary>
		/// <param name="bookShelfID">书架分类</param>
		/// <returns>书架分类的DataTable</returns>
		public DataTable GetBookShelfByBookShelfID(string bookShelfID)
		{
			DataTable data = new BookShelfData().BookShelf;
			new OleDBHelper().ExecuteSQL(SELECT_BOOKSHELF_BY_BOOKSHELF + "'" + bookShelfID + "'", data);
			return data;
		}

		/// <summary>
		/// 根据上级书架编号得到书架分类
		/// </summary>
		/// <param name="pBookShelfID">上级书架编号</param>
		/// <returns>书架分类的DataTable</returns>
		public DataTable GetBookShelfByPBookShelfID(string pBookShelfID)
		{
			DataTable data = new BookShelfData().BookShelf;
			new OleDBHelper().ExecuteSQL(SELECT_BOOKSHELF_BY_PBOOKSHELF + "'" + pBookShelfID + "'", data);
			return data;
		}
	
		/// <summary>
		/// 根据用户ID得到用户书架分类详细信息。
		/// </summary>
		/// <param name="userId">用户ID</param>
		/// <returns>书架分类详细信息的DataTable</returns>
		public DataTable GetBookShelfDetailByUserID(string userId)
		{
			DataTable data=new BookShelfData().BookShelfDetail;
			new OleDBHelper().ExecuteSQL(SELECT_BOOKSHELFDETAIL_BY_USER+userId,data);
			return data;
		}


		/// <summary>
		/// 根据书架分类编号得到用户书架分类详细信息。
		/// </summary>
		/// <param name="bookShelfID">书架分类编号</param>
		/// <returns>书架分类详细信息的DataTable</returns>
		public DataTable GetBookShelfDetailByBookShelfID(string bookShelfID)
		{
			DataTable data=new BookShelfData().BookShelfDetail;
			new OleDBHelper().ExecuteSQL(SELECT_BOOKSHELFDETAIL_BY_BOOKSHELF + "'" + bookShelfID + "'", data);
			return data;
		}

		/// <summary>
		/// 用户第一次新建书架分类时候的操作
		/// </summary>
		/// <param name="userID">用户编号</param>
		/// <param name="dtBookShelf">书架分类的DataTable</param>
		/// <returns>返回－1：操作失败；0：没有操作；1操作成功。</returns>
		public int CreateBookShelf(string userID, DataTable dtBookShelf)
		{
			string[] sqlBookShelf;
			string sqlUserAddInfo = "";

			if (dtBookShelf == null)
			{
				return 0;
			}

			UserDAL userDAL = new UserDAL();
			CommonUserData.UserAddInfoDataTable utable = (CommonUserData.UserAddInfoDataTable)userDAL.GetUserAddInfoByUserID(userID);

			BookShelfData.BookShelfDataTable table = (BookShelfData.BookShelfDataTable)dtBookShelf;
			BookShelfData.BookShelfRow row = (BookShelfData.BookShelfRow)table.Rows[0];
			string BookShelfID = row.BookShelfID;

			if (utable.Rows.Count == 0)
			{
				sqlUserAddInfo = "insert into UserAddInfo(UserID, BookShelfID) values ('" + userID + "', '" + BookShelfID + "')";
			}
			else if (utable.Rows.Count == 1)
			{
				sqlUserAddInfo = "update UserAddInfo set BookShelfID='" + BookShelfID + "' where UserID='" + userID + "'";
			}

			sqlBookShelf = OleDBHelper.GetInsertSql(dtBookShelf,TABLE_BOOKSHELF);
			
			using (OleDbConnection conn = new OleDbConnection(AppConfiguration.GetConnectionString())) 
			{
				conn.Open();
				using (OleDbTransaction trans = conn.BeginTransaction()) 
				{
					try 
					{
						OleDBHelper.ExecuteSQL(sqlBookShelf, trans);
						OleDBHelper.ExecuteSQL(sqlUserAddInfo, trans);
						trans.Commit();
						retVal=1;
					}
					catch 
					{
						retVal=-1;
						throw;
					}
				}
			}

			return retVal;
		}
	
		/// <summary>
		/// 根据传入的参数添加书架分类信息记录（可能是多个），对在数据库中已经存在的记录的添加操作将导致意外，
		/// 并使整个事务回滚。
		/// </summary>
		/// <param name="dtBookShelf">需要添加的关于书架分类的内存表。</param>
		/// <param name="dtBookShelfDetail">书架分类对应的详细内容内存表。</param>
		/// <returns>返回－1：操作失败；0：没有操作；1操作成功。</returns>
		public int InsertBookShelf(DataTable dtBookShelf,DataTable dtBookShelfDetail)
		{
			if (dtBookShelf==null)
				return 0;

			string[] sqlBookShelf;
			string[] sqlBookShelfDetail=null;
				
			
			sqlBookShelf=OleDBHelper.GetInsertSql(dtBookShelf,TABLE_BOOKSHELF);

			if(dtBookShelfDetail!=null)
			{
				sqlBookShelfDetail=OleDBHelper.GetInsertSql(dtBookShelfDetail,TABLE_BOOKSHELFDETAIL);				
			}
			
			using (OleDbConnection conn = new OleDbConnection(AppConfiguration.GetConnectionString())) 
			{
				conn.Open();
				using (OleDbTransaction trans = conn.BeginTransaction()) 
				{
					try 
					{						
						OleDBHelper.ExecuteSQL(sqlBookShelf,trans);
						if (sqlBookShelfDetail.Length>0)
							OleDBHelper.ExecuteSQL(sqlBookShelfDetail,trans);
						trans.Commit();
						retVal=1;
					}
					catch 
					{
						retVal=-1;
						throw;
					}
				}
			}
			return retVal;
		}

		/// <summary>
		/// 插入书架分类记录
		/// </summary>
		/// <param name="row">书架分类记录</param>
		/// <returns>
		/// 1：操作成功
		/// -1：操作失败
		/// </returns>
		public int InsertBookShelf(DataRow row)
		{
			string insert = OleDBHelper.GetInsertSql(row, "BookShelf");
			retVal = new OleDBHelper().ExecuteSQL(insert);

			return retVal;
		}


		/// <summary>
		/// 根据用户更新书架信息，先删除该用户的书架及其明细信息，然后插入数据表中的记录到数据库中。
		/// </summary>
		/// <param name="dtBookShelf">书架分类信息。</param>
		/// <param name="dtBookShelfDetail">书架分类详细信息。</param>
		/// <param name="userId">用户ID</param>
		/// <returns>返回1：操作成功；0：没有操作；-1：操作失败；</returns>
		public int UpdateBookShelf(DataTable dtBookShelf,DataTable dtBookShelfDetail,string userId)
		{
			string[] sqlBookShelf;
			string[] sqlBookShelfDetail=null;
				
			if (dtBookShelf==null)
				return 0;

			sqlBookShelf=OleDBHelper.GetInsertSql(dtBookShelf,TABLE_BOOKSHELF);

			if(dtBookShelfDetail!=null)
			{
				sqlBookShelfDetail=OleDBHelper.GetInsertSql(dtBookShelfDetail,TABLE_BOOKSHELFDETAIL);				
			}
			
			using (OleDbConnection conn = new OleDbConnection(AppConfiguration.GetConnectionString())) 
			{
				conn.Open();
				using (OleDbTransaction trans = conn.BeginTransaction()) 
				{
					try 
					{
						OleDBHelper.ExecuteSQL(" delete from "+TABLE_BOOKSHELF+" , "+TABLE_BOOKSHELFDETAIL+ 
							" where UserID = "+userId+"  and "+TABLE_BOOKSHELF+"."+PK_ID+"="+TABLE_BOOKSHELFDETAIL+"."+PK_ID ,trans);				
						OleDBHelper.ExecuteSQL(sqlBookShelf,trans);
						if (sqlBookShelfDetail.Length>0)
							OleDBHelper.ExecuteSQL(sqlBookShelfDetail,trans);
						trans.Commit();
						retVal=1;
					}
					catch 
					{
						retVal=-1;
						throw;
					}
				}
			}
			return retVal;
		}

		/// <summary>
		/// 修改书架分类记录
		/// </summary>
		/// <param name="row">书架分类记录</param>
		/// <returns>
		/// 1：操作成功
		/// -1：操作失败
		/// </returns>
		public int UpdateBookShelf(DataRow row)
		{
			string update = OleDBHelper.GetUpdateSql(row, "BookShelf", "BookShelfID");
			retVal = new OleDBHelper().ExecuteSQL(update);

			return retVal;
		}
 

		/// <summary>
		/// 删除书架信息，根据书架分类编号删除书架分类及其对应的明细信息。
		/// </summary>
		/// <param name="bookShelfID">书架分类编号。</param>
		/// <returns></returns>
		public int DeleteBookShelf(DataTable dtBookShelf,DataTable dtBookShelfDetail)
		{
			if (dtBookShelf == null)
				return 0;

			string	delBookShelfSql = OleDBHelper.GetDeleteSql(dtBookShelf,TABLE_BOOKSHELF,PK_ID);
			string  delBookShelfDetailSql = OleDBHelper.GetDeleteSql(dtBookShelfDetail,TABLE_BOOKSHELFDETAIL,PK_ID);

			using (OleDbConnection conn = new OleDbConnection(AppConfiguration.GetConnectionString())) 
			{
				conn.Open();
				using (OleDbTransaction trans = conn.BeginTransaction()) 
				{
					try 
					{
						OleDBHelper.ExecuteSQL(delBookShelfDetailSql,trans);
						OleDBHelper.ExecuteSQL(delBookShelfSql,trans);
						trans.Commit();
						retVal=1;
					}
					catch
					{
						retVal=-1;
						throw;
					}
					finally
					{
						conn.Close();
					}
				}
			}
			return retVal;
		}


		

		#endregion

	
	}
}