readcarddal.cs

会员管理系统

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

namespace com.sungoal.MemberManage.DataAccess
{
	/// <summary>
	/// 关于阅读卡的数据库操作类.
	/// </summary>
	public class ReadCardDAL
	{

		private const string TABLE_NAME="ReadCardInfo";
		private const string PK_ID="CardID";
		private const string SELECT_READCARD_LIST = "select * from ReadCardInfo";
		private const string SELECT_READCARD_BY_CARDID = "select * from ReadCardInfo where CardID=";
		private int retVal;

		public ReadCardDAL()
		{
			
		}

		/// <summary>
		/// 生成新的阅读卡ID
		/// </summary>
		/// <param name="number">生成个数</param>
		/// <returns>阅读卡ID</returns>
		public string[] CreateNewID(int number)
		{
			// 1。获取当天生成的阅读卡的最大ID
			// 2。根据步骤 1 得到的最大编号计算新的阅读卡ID
			if (number > 9999)
			{
				return null;
			}
			string identity = "";
			string strToday = DateTime.Today.ToString("yyyyMMdd");
			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(CardID) from ReadCardInfo";
			DataSet dataSet = new DataSet();
			helper.ExecuteSQL(sqlString, dataSet);
			if (dataSet.Tables[0].Rows[0][0] == DBNull.Value)
			{
				serial[0] = "0000";
				maxID = 0;
				newID[0] = identity + strToday + serial[0];
			}
			else
			{
				serial[0] = dataSet.Tables[0].Rows[0][0].ToString();
				string strDateTime = serial[0].Substring(serial[0].Length - 12, 8);
				if (strDateTime != strToday)
				{
					serial[0] = "0000";
				}
				else
				{
					serial[0] = serial[0].Substring(serial[0].Length - 4, 4);
					maxID = Int32.Parse(serial[0]);
					maxID = maxID + 1;
					if (maxID > 9999)
					{
						throw new Exception("Too many ReadCard for today!");
					}
					serial[0] = maxID.ToString();
					int zeroCount = 4 - (int)Math.Log10(maxID) - 1;
					for (int j = 0; j < zeroCount; j ++)
					{
						serial[0] = "0" + serial[0];
					}
				}
				newID[0] = identity + strToday + serial[0];
			}
			for (int i = 1; i < number; i ++)
			{
				maxID ++;
				if (maxID > 9999)
				{
					throw new Exception("Too many ReadCard for today!");
				}
				serial[i] = maxID.ToString();
				int zeroCount = 4 - (int)Math.Log10(maxID) - 1;
				for (int j = 0; j < zeroCount; j ++)
				{
					serial[i] = "0" + serial[i];
				}
				newID[i] = identity + strToday + serial[i];
			}

			return newID;
		}

		/// <summary>
		/// 生成新的阅读卡密码
		/// </summary>
		/// <param name="number">生成个数</param>
		/// <returns>阅读卡密码</returns>
		public string[] CreateNewPassword(int number)
		{
			string[] password = new string[number];
			Random r = new Random();

			for (int i = 0; i < number; i ++)
			{
				password[i] = "";
				for (int j = 0; j < 10; j ++)
				{
					password[i] = password[i] + r.Next(10);
				}
			}

			return password;
		}

		/// <summary>
		/// 获取阅读卡列表
		/// </summary>
		/// <returns>阅读卡DataTable</returns>
		public DataTable GetReadCardList()
		{
			ReadCardData.ReadCardInfoDataTable data = new ReadCardData().ReadCardInfo;
			string sqlString = SELECT_READCARD_LIST;
			new OleDBHelper().ExecuteSQL(sqlString, data);
			return data;
		}

		/// <summary>
		/// 根据阅读卡ID获取阅读卡信息
		/// </summary>
		/// <param name="cardID">阅读卡ID</param>
		/// <returns>阅读卡DataTable</returns>
		public DataTable GetReadCardListByCardID(string cardID)
		{
			ReadCardData.ReadCardInfoDataTable data = new ReadCardData().ReadCardInfo;
			string sqlString = SELECT_READCARD_BY_CARDID + "'" + cardID + "'";
			new OleDBHelper().ExecuteSQL(sqlString, data);
			return data;
		}

		/// <summary>
		/// 根据用户输入的参数得到阅读卡信息。
		/// </summary>
		/// <param name="param">关于参数的哈希表，格式为数据库字段名、参数值，参数值的书写跟数据类型有关，比如字符型应该加引号''。</param>
		/// <returns></returns>
		public DataTable GetReadCardByParam(Hashtable param)
		{	
			DataTable data=new ReadCardData().ReadCardInfo;
			string sqlString = OleDBHelper.GetSelectSql(TABLE_NAME,param);
			new OleDBHelper().ExecuteSQL(sqlString,data);
			return data;
		}
 
		
		/// <summary>
		///  根据传入的参数添加阅读卡信息记录（可能是多个），对在数据库中已经存在的记录的添加操作将导致意外，并使整个事务回滚。
		/// </summary>
		/// <param name="dtReadCard">需要添加的关于阅读卡信息的内存表。</param>
		/// <returns>返回－1：操作失败；0：没有操作；1操作成功。</returns>
		public int InsertReadCard(DataTable dtReadCard)
		{
			if (dtReadCard==null)
				return 0;

			string[] sqlString=OleDBHelper.GetInsertSql(dtReadCard,TABLE_NAME);
			return new OleDBHelper().ExecuteSQL(sqlString);
		}


		/// <summary>
		/// 根据传入的参数更新阅读卡信息，对在数据库中不存在的记录的更新操作将被忽略。
		/// </summary>
		/// <param name="dtReadCard">需要更新的关于阅读卡信息的内存表。</param>
		/// <returns>返回－1：操作失败；0：没有操作；1操作成功。</returns>
		public int UpdateReadCard(DataTable dtReadCard)
		{
			if (dtReadCard==null)
				return 0;

			string[] sqlString=OleDBHelper.GetUpdateSql(dtReadCard,TABLE_NAME,PK_ID);
			return new OleDBHelper().ExecuteSQL(sqlString);
		}

		
		/// <summary>
		/// 根据传入的参数删除相应的阅读卡信息记录，对在数据库中不存在的记录的删除操作将被忽略。
		/// </summary>
		/// <param name="dtReadCard">需要删除的关于阅读卡信息的内存表。</param>
		/// <returns>返回－1：操作失败；0：没有操作；1操作成功。</returns>
		public int DeleteReadCard(DataTable dtReadCard)
		{
			if (dtReadCard==null)
				return 0;

			string sqlString=OleDBHelper.GetDeleteSql(dtReadCard,TABLE_NAME,PK_ID);
			return new OleDBHelper().ExecuteSQL(sqlString);
		}

	}
}