sqlbookshopprofileprovider.cs

一个非常好的网上书店系统

				new SqlParameter("@ApplicationName", SqlDbType.VarChar, 256)};
			parms[0].Value = activityDate;
			parms[1].Value = activityDate;
			parms[2].Value = userName;
			parms[3].Value = appName;
		}
        SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlUpdate, parms);
	}

    /// <summary>
    /// 获取当前用户的配置ID
    /// </summary>
    /// <param name="userName">用户名</param>
    /// <param name="isAuthenticated">验证标志</param>
    /// <param name="ignoreAuthenticationType">忽略验证标志</param>
    /// <param name="appName">应用程序名</param>
    /// <returns>配置ID</returns>
	public int GetUniqueID(string userName, bool isAuthenticated, bool ignoreAuthenticationType, string appName) {
		string sqlSelect = "SELECT ProfileID FROM Profiles WHERE Username = @Username AND ApplicationName = @ApplicationName";

		SqlParameter[] parms = {
			new SqlParameter("@Username", SqlDbType.VarChar, 256),
			new SqlParameter("@ApplicationName", SqlDbType.VarChar, 256)};
		parms[0].Value = userName;
		parms[1].Value = appName;
		if(!ignoreAuthenticationType) {
			Array.Resize<SqlParameter>(ref parms, parms.Length + 1);
			parms[2] = new SqlParameter("@IsAnonymous", SqlDbType.Bit);
			parms[2].Value = !isAuthenticated;
		}
		int uniqueID = 0;
		object retVal = null;
        retVal = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlSelect, parms);
		if(retVal == null)
			uniqueID = CreateProfileForUser(userName, isAuthenticated, appName);
		else
			uniqueID = Convert.ToInt32(retVal);
		return uniqueID;
	}

    /// <summary>
    /// 创建当前用户的配置记录
    /// </summary>
    /// <param name="userName">用户名</param>
    /// <param name="isAuthenticated">验证标志</param>
    /// <param name="appName">应用程序名</param>
    /// <returns>记录条数</returns>
	public int CreateProfileForUser(string userName, bool isAuthenticated, string appName) {

		string sqlInsert = "INSERT INTO Profiles (Username,  LastActivityDate, LastUpdateDate, ApplicationName) Values(@Username, @LastActivityDate, @LastUpdatedDate, @ApplicationName); SELECT @@IDENTITY;";

		SqlParameter[] parms = {
			new SqlParameter("@Username", SqlDbType.VarChar, 256),
			new SqlParameter("@ApplicationName", SqlDbType.VarChar, 256),
			new SqlParameter("@LastActivityDate", SqlDbType.DateTime),
			new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)};

		parms[0].Value = userName;
		parms[1].Value = appName;
		parms[2].Value = DateTime.Now;
		parms[3].Value = DateTime.Now;
		int uniqueID = 0;
        int.TryParse(SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlInsert, parms).ToString(), out uniqueID);
		return uniqueID;
	}

    /// <summary>
    /// 获取非活动用户集合
    /// </summary>
    /// <param name="authenticationOption">验证设置</param>
    /// <param name="userInactiveSinceDate">搜索日期</param>
    /// <param name="appName">应用程序名</param>
    /// <returns>非活动的用户集合</returns>
	public IList<string> GetInactiveProfiles(int authenticationOption, DateTime userInactiveSinceDate, string appName) {

		StringBuilder sqlSelect = new StringBuilder("SELECT Username FROM Profiles WHERE ApplicationName = @ApplicationName AND LastActivityDate <= @LastActivityDate");

		SqlParameter[] parms = {
			new SqlParameter("@ApplicationName", SqlDbType.VarChar, 256),
			new SqlParameter("@LastActivityDate", SqlDbType.DateTime)};
		parms[0].Value = appName;
		parms[1].Value = userInactiveSinceDate;

        //判断验证设置
		switch(authenticationOption) {
			case AUTH_ANONYMOUS:
				sqlSelect.Append(" AND IsAnonymous = @IsAnonymous");
				Array.Resize<SqlParameter>(ref parms, parms.Length + 1);
				parms[2] = new SqlParameter("@IsAnonymous", SqlDbType.Bit);
				parms[2].Value = true;
				break;
			case AUTH_AUTHENTICATED:
				sqlSelect.Append(" AND IsAnonymous = @IsAnonymous");
				Array.Resize<SqlParameter>(ref parms, parms.Length + 1);
				parms[2] = new SqlParameter("@IsAnonymous", SqlDbType.Bit);
				parms[2].Value = false;
				break;
			default:
				break;
		}

		IList<string> usernames = new List<string>();
        //获取用户数据集
        SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlSelect.ToString(), parms);
		while(dr.Read()) {
			usernames.Add(dr.GetString(0));
		}
		dr.Close();
		return usernames;
	}

    /// <summary>
    /// 删除用户的配置
    /// </summary>
    /// <param name="userName">用户名</param>
    /// <param name="appName">应用程序名</param>
    /// <returns>是否成功</returns>
	public bool DeleteProfile(string userName, string appName) 
    {
        //获取用户的配置ID
        int uniqueID = GetUniqueID(userName, false, true, appName);
        //创建参数并赋值
		string sqlDelete = "DELETE FROM Profiles WHERE UniqueID = @UniqueID;";
		SqlParameter param = new SqlParameter("@UniqueId", SqlDbType.Int, 4);
		param.Value = uniqueID;
        //执行删除语句
        int numDeleted = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlDelete, param);
		if(numDeleted <= 0)
			return false;
		else
			return true;
	}
    /// <summary>
    /// 找回配置信息
    /// </summary>
    /// <param name="authenticationOption">验证设置</param>
    /// <param name="usernameToMatch">用户名</param>
    /// <param name="userInactiveSinceDate">搜索日期</param>
    /// <param name="appName">应用程序名</param>
    /// <param name="totalRecords">返回条数</param>
    /// <returns>配置集合</returns>
	public IList<CustomProfileInfo> GetProfileInfo(int authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate, string appName, out int totalRecords) {
        
		// 返回总条数
		StringBuilder sqlSelect1 = new StringBuilder("SELECT COUNT(*) FROM Profiles WHERE ApplicationName = @ApplicationName");
		SqlParameter[] parms1 = {
			new SqlParameter("@ApplicationName", SqlDbType.VarChar, 256)};
		parms1[0].Value = appName;

		// 找回配置信息
		StringBuilder sqlSelect2 = new StringBuilder("SELECT Username, LastActivityDate, LastUpdateDate, IsAnonymous FROM Profiles WHERE ApplicationName = @ApplicationName");
		SqlParameter[] parms2 = { new SqlParameter("@ApplicationName", SqlDbType.VarChar, 256) };
		parms2[0].Value = appName;
		int arraySize;
		// 通过用户名搜索.
		if(usernameToMatch != null) {
			arraySize = parms1.Length;
			sqlSelect1.Append(" AND Username LIKE @Username ");
			Array.Resize<SqlParameter>(ref parms1, arraySize + 1);
			parms1[arraySize] = new SqlParameter("@Username", SqlDbType.VarChar, 256);
			parms1[arraySize].Value = usernameToMatch;
			sqlSelect2.Append(" AND Username LIKE @Username ");
			Array.Resize<SqlParameter>(ref parms2, arraySize + 1);
			parms2[arraySize] = new SqlParameter("@Username", SqlDbType.VarChar, 256);
			parms2[arraySize].Value = usernameToMatch;
		}
		// 通过激活日期
		if(userInactiveSinceDate != null) {
			arraySize = parms1.Length;
			sqlSelect1.Append(" AND LastActivityDate >= @LastActivityDate ");
			Array.Resize<SqlParameter>(ref parms1, arraySize + 1);
			parms1[arraySize] = new SqlParameter("@LastActivityDate", SqlDbType.DateTime);
			parms1[arraySize].Value = (DateTime)userInactiveSinceDate;

			sqlSelect2.Append(" AND LastActivityDate >= @LastActivityDate ");
			Array.Resize<SqlParameter>(ref parms2, arraySize + 1);
			parms2[arraySize] = new SqlParameter("@LastActivityDate", SqlDbType.DateTime);
			parms2[arraySize].Value = (DateTime)userInactiveSinceDate;
		}
		//通过验证设置
		if(authenticationOption != AUTH_ALL) {
			arraySize = parms1.Length;
			Array.Resize<SqlParameter>(ref parms1, arraySize + 1);
			sqlSelect1.Append(" AND IsAnonymous = @IsAnonymous");
			parms1[arraySize] = new SqlParameter("@IsAnonymous", SqlDbType.Bit);  

			Array.Resize<SqlParameter>(ref parms2, arraySize + 1);
			sqlSelect2.Append(" AND IsAnonymous = @IsAnonymous");
			parms2[arraySize] = new SqlParameter("@IsAnonymous", SqlDbType.Bit);
            //判断是否是匿名
			switch(authenticationOption) {
				case AUTH_ANONYMOUS:   						
					parms1[arraySize].Value = true;
					parms2[arraySize].Value = true;
					break;
				case AUTH_AUTHENTICATED:
					parms1[arraySize].Value = false;
					parms2[arraySize].Value = false;
					break;
				default:
					break;
			}
		}
		IList<CustomProfileInfo> profiles = new List<CustomProfileInfo>();
		// 获取配置条数
        totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlSelect1.ToString(), parms1);

		// 没有发现配置信息
		if(totalRecords <= 0)
			return profiles;
		SqlDataReader dr;
        dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlSelect2.ToString(), parms2);
		while(dr.Read()) {
			CustomProfileInfo profile = new CustomProfileInfo(dr.GetString(0), dr.GetDateTime(1), dr.GetDateTime(2), dr.GetBoolean(3));
			profiles.Add(profile);	
		}	
		dr.Close();
		return profiles;
	}
}