Esempio n. 1
0
        public override DeleteResult DeleteSearchShares(ShareFilter filter, IEnumerable<Guid> excludeRoleIDs, bool getDeleteResult)
        {

            using (SqlQuery query = new SqlQuery())
            {

                //string condition = @" 1 = 1 "
                //        + (filter.UserID == null ? "" : ("AND [UserID] = @UserID "))
                //        + (filter.ShareID == null ? "" : ("AND [ShareID] = @ShareID "))
                //        + (filter.ShareCatagory == ShareCatagory.All ? "" : ("AND [Type] = @Type "))
                //        + (filter.PrivacyType == null ? "" : ("AND [PrivacyType] = @PrivacyType "))
                //        + (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "))
                //        + (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "))
                //        + "AND " + DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query);

                SqlConditionBuilder condition = new SqlConditionBuilder(SqlConditionStart.Where);
                condition += (filter.UserID == null ? "" : ("AND [UserID] = @UserID "));
                condition += (filter.ShareID == null ? "" : ("AND [ShareID] = @ShareID "));
                condition += (filter.ShareType == ShareType.All ? "" : ("AND [Type] = @Type "));
                condition += (filter.PrivacyType == null ? "" : ("AND [PrivacyType] = @PrivacyType "));
                condition += (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "));
                condition += (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "));
                condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query));

                if (getDeleteResult)
                {
                    query.CommandText = @"
SELECT [UserID],COUNT(*) AS [Count] FROM [bx_SharesView] " + condition.ToString() + " GROUP BY [UserID];";

                }
                else
                    query.CommandText = string.Empty;


                query.CommandText = query.CommandText + @"
DELETE [bx_SharesView] " + condition.ToString();


                query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
                query.CreateParameter<int?>("@ShareID", filter.ShareID, SqlDbType.Int);
                query.CreateParameter<int>("@Type", (int)filter.ShareType, SqlDbType.Int);
                query.CreateParameter<int?>("@PrivacyType", filter.PrivacyType == null ? 0 : (int)filter.PrivacyType, SqlDbType.Int);
                query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
                query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);



                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    DeleteResult deleteResult = new DeleteResult();

                    while (reader.Read())
                    {
                        deleteResult.Add(reader.Get<int>("UserID"), reader.Get<int>("Count"));
                    }
                    return deleteResult;
                }
            }
        }
Esempio n. 2
0
        public override ShareCollection SearchShares(int pageNumber, ShareFilter filter, IEnumerable<Guid>  excludeRoleIDs, ref int totalCount)
        {

            using (SqlQuery query = new SqlQuery())
            {
                query.Pager.IsDesc = filter.IsDesc;

                if (filter.Order == ShareFilter.OrderBy.ShareID)
                    query.Pager.SortField = filter.Order.ToString();

                query.Pager.PageNumber = pageNumber;
                query.Pager.PageSize = filter.PageSize;
                query.Pager.TotalRecords = totalCount;
                query.Pager.SelectCount = true;
                query.Pager.TableName = "[bx_SharesView]";

                SqlConditionBuilder condition = new SqlConditionBuilder(SqlConditionStart.None);
                condition += (filter.UserID == null ? "" : ("AND [UserID] = @UserID "));
                condition += (filter.ShareID == null ? "" : ("AND [ShareID] = @ShareID "));
                condition += (filter.ShareType == ShareType.All ? "" : ("AND [Type] = @Type "));
                condition += (filter.PrivacyType == null ? "" : ("AND [PrivacyType] = @PrivacyType "));
                condition += (filter.BeginDate == null ? "" : ("AND [CreateDate] > @BeginDate "));
                condition += (filter.EndDate == null ? "" : ("AND [CreateDate] < @EndDate "));
                condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query));

                query.Pager.Condition = condition.ToString();



                query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
                query.CreateParameter<int?>("@ShareID", filter.ShareID, SqlDbType.Int);
                query.CreateParameter<int>("@Type", (int)filter.ShareType, SqlDbType.Int);
                query.CreateParameter<int?>("@PrivacyType", filter.PrivacyType == null ? 0 : (int)filter.PrivacyType, SqlDbType.Int);
                query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
                query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);



                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    ShareCollection shares = new ShareCollection(reader);

                    if (reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            totalCount = reader.Get<int>(0);
                        }
                    }
                    return shares;
                }
            }
        }
Esempio n. 3
0
		public override DeleteResult DeleteSharesBySearch(ShareFilter filter, IEnumerable<Guid> excludeRoleIDs, int topCount, out int deletedCount)
		{
			deletedCount = 0;

			using (SqlQuery query = new SqlQuery())
			{
				string conditions = BuildConditionsByFilter(query, filter, excludeRoleIDs, true);

				StringBuffer sql = new StringBuffer();

				sql += @"
DECLARE @DeleteData table (UserID int, ShareID int);

INSERT INTO @DeleteData SELECT TOP (@TopCount) [UserID],[ShareID] FROM [bx_SharesView] " + conditions + @";

DELETE [bx_Shares] WHERE [ShareID] IN (SELECT [ShareID] FROM @DeleteData);

SELECT @@ROWCOUNT;

SELECT [UserID],COUNT(*) AS [Count] FROM @DeleteData GROUP BY [UserID];";

				query.CreateTopParameter("@TopCount", topCount);

				query.CommandText = sql.ToString();

				using (XSqlDataReader reader = query.ExecuteReader())
				{
					DeleteResult deleteResult = new DeleteResult();

                    if (reader.Read())
                        deletedCount = reader.Get<int>(0);

                    if (reader.NextResult())
					{
                        while (reader.Read())
						{
                            deleteResult.Add(reader.Get<int>("UserID"), reader.Get<int>("Count"));
						}
					}

					return deleteResult;
				}
			}

		}
Esempio n. 4
0
		private string BuildConditionsByFilter(SqlQuery query, ShareFilter filter, IEnumerable<Guid> excludeRoleIDs, bool startWithWhere)
		{
			SqlConditionBuilder condition = new SqlConditionBuilder(startWithWhere ? SqlConditionStart.Where : SqlConditionStart.None);

			if (filter.UserID != null)
			{
				condition.AppendAnd("[UserID] = @UserID");

				query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
			}

			if (filter.ShareID != null)
			{
				condition.AppendAnd("[ShareID] = @ShareID");

				query.CreateParameter<int?>("@ShareID", filter.ShareID, SqlDbType.Int);
			}

			if (filter.ShareType != null)
			{
				condition.AppendAnd("[Type] = @Type");

				query.CreateParameter<ShareType?>("@Type", filter.ShareType, SqlDbType.Int);
			}

			if (filter.PrivacyType != null)
			{
				condition.AppendAnd("[PrivacyType] = @PrivacyType");

				query.CreateParameter<PrivacyType?>("@PrivacyType", filter.PrivacyType, SqlDbType.Int);
			}

			if (filter.BeginDate != null)
			{
				condition.AppendAnd("[CreateDate] > @BeginDate");

				query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
			}

			if (filter.EndDate != null)
			{
				condition.AppendAnd("[CreateDate] < @EndDate");

				query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);
			}

			condition.AppendAnd(DaoUtil.GetExcludeRoleSQL("[UserID]", excludeRoleIDs, query));

			return condition.ToString();
		}
Esempio n. 5
0
		public override ShareCollection GetSharesBySearch(Guid[] excludeRoleIDs, ShareFilter filter, int pageNumber)
		{
			using (SqlQuery query = new SqlQuery())
			{
				string sqlCondition = BuildConditionsByFilter(query, filter, excludeRoleIDs, false);

                query.Pager.TableName = "[bx_SharesView]";
				query.Pager.SortField = filter.Order.ToString();

				if (filter.Order != ShareFilter.OrderBy.ShareID)
				{
					query.Pager.PrimaryKey = "ShareID";
				}

				query.Pager.PageNumber = pageNumber;
				query.Pager.PageSize = filter.PageSize;
				query.Pager.SelectCount = true;
				query.Pager.IsDesc = filter.IsDesc;

				query.Pager.Condition = sqlCondition;


				using (XSqlDataReader reader = query.ExecuteReader())
				{
					ShareCollection shares = new ShareCollection(reader);

					if (reader.NextResult())
					{
						if (reader.Read())
						{
							shares.TotalRecords = reader.Get<int>(0);
						}
					}

					return shares;
				}
			}
		}