C# SqlQuery.CreateTopParameter方法代码示例

        public override void DeletePropLogs(JobDataClearMode clearMode, DateTime dateTime, int saveRows)
        {
            using (SqlQuery query = new SqlQuery())
            {
                switch(clearMode)
                {
                    case JobDataClearMode.ClearByDay:
                        query.CommandText = "DELETE FROM bx_PropLogs WHERE CreateDate <= @Time;";

                        query.CreateParameter<DateTime>("@Time", dateTime, SqlDbType.DateTime);
                        break;

                    case JobDataClearMode.ClearByRows:
                        query.CommandText = "DELETE FROM bx_PropLogs WHERE PropLogID < (SELECT MIN(O.PropLogID) FROM (SELECT TOP(@TopCount) PropLogID FROM bx_PropLogs ORDER BY PropLogID DESC) AS O)";
                        query.CreateTopParameter("@TopCount", saveRows);
                        break;

                    case JobDataClearMode.CombinMode:
                        query.CommandText = "DELETE FROM bx_PropLogs WHERE PropLogID < (SELECT MIN(O.PropLogID) FROM (SELECT TOP(@TopCount) PropLogID FROM bx_PropLogs ORDER BY PropLogID DESC) AS O) AND CreateDate >= @Time";
                        query.CreateTopParameter("@TopCount", saveRows);

                        query.CreateParameter<DateTime>("@Time", dateTime, SqlDbType.DateTime);
                        break;
                }

                query.ExecuteNonQuery();
            }
        }

        public override List<Instruct> LoadClientInstruct(int clientID, int loadCount, out int laveCount)
        {
            List<Instruct> results = new List<Instruct>();
            laveCount = 0;
            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = @"SELECT TOP (@LoadCount) * FROM bx_Instructs WHERE ClientID = @ClientID;
                SELECT Count(*)  FROM bx_Instructs WHERE ClientID = @ClientID;";
                query.CreateParameter<int>("@ClientID", clientID, SqlDbType.Int);
                query.CreateTopParameter("@LoadCount", loadCount);

                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    while (reader.Next)
                    {
                        results.Add(new Instruct(reader));
                    }

                    reader.NextResult();
                    if (reader.Next)
                        laveCount = reader.Get<int>(0);

                    laveCount-=loadCount;

                    if(laveCount<=0)
                        laveCount=0;
                }

                return results;
            }
        }

        public override CommentCollection GetLastestCommentsForSomeone(int targetUserID, CommentType type, int top)
        {
            using (SqlQuery query = new SqlQuery())
            {
                string getTargetNameSql = null;

                switch (type)
                {
                    case CommentType.Blog:
                        getTargetNameSql = "(SELECT [Subject] FROM [bx_BlogArticles] WHERE [ArticleID]=TargetID) AS [TargetName] ";
                        break;

                    case CommentType.Photo:
                        getTargetNameSql = "(SELECT [Name] FROM [bx_Photos] WHERE [PhotoID]=TargetID) AS [TargetName] ";
                        break;

                    default:
                        getTargetNameSql = string.Empty;
                        break;
                }

                query.CommandText = "SELECT TOP (@TopCount) *, " + getTargetNameSql + " FROM bx_Comments WHERE [TargetUserID][email protected] AND [Type][email protected] ORDER BY [CommentID] DESC";
                query.CommandType = CommandType.Text;

                query.CreateParameter<int>("@TargetUserID", targetUserID, SqlDbType.Int);
                query.CreateParameter<CommentType>("@Type", type, SqlDbType.TinyInt);

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

                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    return new CommentCollection(reader);
                }
            }
        }

        public override ImpressionTypeCollection GetImpressionTypesForUse(int targetUserID, int usrCount, int sysCount)
        {
            using (SqlQuery db = new SqlQuery())
            {
                db.CommandText = @"
DECLARE @TempTable table(TypeID int);

INSERT INTO @TempTable
SELECT TOP (@UsrCount1) TypeID
FROM bx_Impressions WHERE UserID = @TargetUserID ORDER BY UpdateDate DESC;

SELECT * FROM bx_ImpressionTypes C RIGHT JOIN @TempTable A ON C.TypeID = A.TypeID; 

DECLARE @Count2 AS int;
DECLARE @Count3 AS int;

SET @Count2 = @UsrCount - (SELECT COUNT(*) FROM @TempTable) + @SysCount;

IF @Count2 > 0 BEGIN

    EXEC(
    'SELECT TOP ' + @Count2 + ' *, (SELECT TOP 1 C.UpdateDate FROM bx_Impressions C WHERE C.TypeID = A.TypeID ORDER BY C.UpdateDate DESC) AS UpdateDate FROM bx_ImpressionTypes A WHERE A.TypeID IN(SELECT B.TypeID FROM bx_Impressions B WHERE B.UserID != ' + @TargetUserID + ') ORDER BY UpdateDate'
    );

END";

                db.CreateTopParameter("@UsrCount1", usrCount);
                db.CreateParameter<int>("@UsrCount", usrCount, SqlDbType.Int);
                db.CreateParameter<int>("@SysCount", sysCount, SqlDbType.Int);

                db.CreateParameter<int>("@TargetUserID", targetUserID, SqlDbType.Int);

                using (XSqlDataReader reader = db.ExecuteReader())
                {
                    ImpressionTypeCollection result = new ImpressionTypeCollection(reader);

                    if (reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            result.Add(new ImpressionType(reader));
                        }
                    }

                    return result;
                }
            }
        }

        public override bool DeleteImpressionRecordsForAdmin(AdminImpressionRecordFilter filter, int topCount, out int deletedCount)
        {
            deletedCount = 0;

            using (SqlQuery query = new SqlQuery())
            {
                string conditions = BuildCondition(query, filter);

                StringBuffer sql = new StringBuffer();

                sql += @"
DECLARE @DeleteData table (RecordID int);

INSERT INTO @DeleteData SELECT TOP (@TopCount) [RecordID] FROM [bx_ImpressionRecords] WHERE " + conditions + @";

DELETE [bx_ImpressionRecords] WHERE TypeID IN (SELECT [RecordID] FROM @DeleteData);

SELECT @@ROWCOUNT;";

                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);

                    return true;
                }
            }
        }

 public override void ClearPointLogs(int days, int rows, JobDataClearMode mode)
 {
     using (SqlQuery query = new SqlQuery())
     {
         if (mode == JobDataClearMode.ClearByDay)
         {
             query.CommandText = "DELETE FROM bx_PointLogs WHERE CreateTime < DATEADD(day, 0 - @Days, GETDATE())";
             query.CreateParameter<int>("@Days", days, SqlDbType.Int);
         }
         else if (mode == JobDataClearMode.ClearByRows)
         {
             query.CommandText = "DELETE FROM bx_PointLogs WHERE  LogID < ( SELECT MIN(LogID) FROM(SELECT TOP (@Rows) LogID FROM bx_PointLogs ORDER BY LogID DESC) t)";
             query.CreateTopParameter("@Rows", rows);
         }
         else if (mode == JobDataClearMode.CombinMode)
         {
             query.CommandText = "DELETE FROM bx_PointLogs WHERE  LogID < ( SELECT MIN(LogID) FROM(SELECT TOP (@Rows) LogID FROM bx_PointLogs ORDER BY LogID DESC) t) AND CreateTime < DATEADD(day, @Days, GETDATE())";
             query.CreateTopParameter("@Rows", rows);
             query.CreateParameter<int>("@Days", days, SqlDbType.Int);
         }
         query.ExecuteNonQuery();
     }
 }

        public override DeleteResult DeleteBlogArticlesBySearch(AdminBlogArticleFilter filter, int operatorUserID, IEnumerable<Guid> excludeRoleIDs, int topCount, out int deletedCount)
        {

            deletedCount = 0;

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

                StringBuffer sql = new StringBuffer();

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

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

DELETE [bx_BlogArticles] WHERE ArticleID IN (SELECT [ArticleID] 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;
                }
            }

        }

        public override DeleteResult DeleteSearchPosts(PostFilter filter, IEnumerable<Guid> excludeRoleIDs, bool getDeleteResult, int topCount, out int deletedCount, out List<int> threadIDs)
        {

            using (SqlQuery query = new SqlQuery())
            {

                string conditon = BuilderSearchPostCondition(filter, excludeRoleIDs, query, false);

                query.CommandText = @"
DECLARE @Table table(TempPostID int,TempTopicID int,TempUserID int,TempForumID int,TempThreadStatus tinyint);
INSERT INTO @Table SELECT TOP (@TopCount) [PostID],[ThreadID],[UserID],[ForumID],[ThreadStatus] FROM [bx_Posts] WHERE " + conditon + @";
SELECT DISTINCT TempTopicID FROM @Table;
";

                if (getDeleteResult)
                {
                    query.CommandText += @"
SELECT [TempUserID],[TempForumID],COUNT(*) AS [Count] FROM @Table WHERE TempThreadStatus<4 GROUP BY [TempUserID],[TempForumID];";

                }


                query.CommandText = query.CommandText + @"
DELETE [bx_Posts] WHERE PostID IN(SELECT TempPostID FROM @Table);
SELECT @@ROWCOUNT;
";
                query.CreateTopParameter("@TopCount", topCount);

                threadIDs = new List<int>();
                deletedCount = 0;
                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    DeleteResult deleteResult = new DeleteResult();

                    while (reader.Read())
                    {
                        int threadID = reader.Get<int>("TempTopicID");
                        if (threadIDs.Contains(threadID) == false)
                            threadIDs.Add(threadID);
                    }

                    if (getDeleteResult)
                    {
                        if (reader.NextResult())
                        {
                            while (reader.Read())
                            {
                                deleteResult.Add(reader.Get<int>("TempUserID"), reader.Get<int>("Count"), reader.Get<int>("TempForumID"));
                            }
                        }
                    }
                    if (reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            deletedCount = reader.Get<int>(0);
                        }
                    }
                    return deleteResult;
                }
            }
        }

        public override DeleteResult DeleteAlbumsBySearch(AdminAlbumFilter filter, int operatorID, IEnumerable<Guid> excludeRoleIDs, int topCount, out int deletedCount, out int[] deletedPhotoIDs)
        {
            deletedCount = 0;

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

                StringBuffer sql = new StringBuffer();

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

INSERT INTO @DeleteData SELECT TOP (@TopCount) [UserID],[A].[AlbumID],(SELECT COUNT(*) FROM [bx_Photos] AS [B] WHERE [B].[AlbumID]=[A].[AlbumID]) FROM [bx_Albums] AS [A] " + conditions + @";

DECLARE @DeletePhotos table (PhotoID int);

INSERT INTO @DeletePhotos SELECT PhotoID from bx_Photos WHERE AlbumID IN (SELECT [AlbumID] FROM @DeleteData);

DELETE [bx_Albums] WHERE [AlbumID] IN (SELECT [AlbumID] FROM @DeleteData);

SELECT @@ROWCOUNT;

SELECT [UserID],SUM([PhotoCount]) AS [Count] FROM @DeleteData GROUP BY [UserID];

SELECT [PhotoID] FROM @DeletePhotos;";

                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);

                    reader.NextResult();

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

                    reader.NextResult();

                    List<int> deletePhotos = new List<int>();

                    while (reader.Read())
                    {
                        deletePhotos.Add(reader.Get<int>(0));
                    }

                    deletedPhotoIDs = deletePhotos.ToArray();

                    return deleteResult;
                }
            }
        }

//.........这里部分代码省略.........
            if (mode == SearchMode.UserThread)
            {
                sql = sql + @"
    SELECT TOP(@TopCount) ThreadID FROM bx_Threads WITH(NOLOCK) WHERE PostUserID = @UserID AND " + condition + @" ThreadStatus<4 " + orderby + @";
";
                query.CreateParameter<int>("@UserID", targetUserID, SqlDbType.Int);
            }
            else if (mode == SearchMode.UserPost)
            {
                sql = sql + @"
    SELECT TOP(@TopCount) PostID FROM bx_Posts WITH(NOLOCK) WHERE UserID = @UserID AND " + condition + @" SortOrder<4000000000000000 " + orderby + @";
";
                query.CreateParameter<int>("@UserID", targetUserID, SqlDbType.Int);
            }
            else
            {
                #region keyword

                keyword = string.Empty;
                string keywordCondition = null;

                int i = 0;
                foreach (string word in keywords)
                {
                    string tempWord = DaoUtil.GetSafeString(word).Trim();
                    if (tempWord == string.Empty)
                        continue;

                    keyword += " " + word;

                    if (mode == SearchMode.Subject)
                    {
                        if (searchType == SearchType.LikeStatement)
                        {
                            condition += " Subject LIKE'%'+ @Word_" + i + " +'%' AND ";
                            //condition += " Subject LIKE'%" + tempWord + "%' AND ";
                            query.CreateParameter<string>("@Word_" + i, tempWord, SqlDbType.NVarChar, 20);
                        }
                        else
                            keywordCondition += "\"*" + tempWord + "*\" AND ";
                    }
                    else if (mode == SearchMode.Content || mode == SearchMode.TopicContent)
                    {
                        if (searchType == SearchType.LikeStatement)
                        {
                            condition += " [Content] LIKE'%'+ @Word_" + i + " +'%' AND ";
                            //condition += " [Content] LIKE'%" + tempWord + "%' AND ";
                            query.CreateParameter<string>("@Word_" + i, tempWord, SqlDbType.NVarChar, 20);
                        }
                        else
                            keywordCondition += "\"*" + tempWord + "*\" AND ";
                    }

                    i++;
                }

                keyword = keyword.Trim();

                if (keywordCondition != null)
                {
                    keywordCondition = keywordCondition.Substring(0, keywordCondition.Length - 4);

                    if (mode == SearchMode.Subject)
                        condition += " Contains([Subject], @keywordCondition) AND ";
                    //condition += " Contains([Subject],'" + keywordCondition + "') AND ";
                    else
                        condition += " Contains([Content], @keywordCondition) AND ";
                    //condition += " Contains([Content],'" + keywordCondition + "') AND ";

                    query.CreateParameter<string>("@keywordCondition", keywordCondition, SqlDbType.NVarChar, 100);
                }
                #endregion

                if (mode == SearchMode.Subject)
                {
                    sql = sql + @"
SELECT TOP(@TopCount) ThreadID FROM [bx_Threads] WITH(NOLOCK) WHERE " + condition + @" ThreadStatus<4 " + orderby + @";
";
                }
                else if (mode == SearchMode.TopicContent)
                {
                    sql = sql + @"
SELECT TOP(@TopCount) PostID FROM [bx_Posts] WITH(NOLOCK) WHERE PostType=1 AND " + condition + @" SortOrder<4000000000000000 " + orderby + @";
";
                }
                else if (mode == SearchMode.Content)
                {
                    sql = sql + @"
SELECT TOP(@TopCount) PostID FROM [bx_Posts] WITH(NOLOCK) WHERE " + condition + @" SortOrder<4000000000000000 " + orderby + @";
";
                }
            }


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


            return sql;

        }

        public override void ClearMessage(int days, int rows, bool ClearNoRead, JobDataClearMode mode)
        {
            using (SqlQuery query = new SqlQuery())
            {
                if (mode == JobDataClearMode.ClearByDay)
                {
                    query.CommandText = "DELETE FROM bx_ChatMessages WHERE CreateDate < DATEADD(day, 0 - @Days, GETDATE())" + (ClearNoRead ? "" : " AND IsRead = 1;");
                    query.CreateParameter<int>("@Days", days, SqlDbType.Int);
                }
                else if (mode == JobDataClearMode.ClearByRows)
                {
                    query.CommandText = "DELETE FROM bx_ChatMessages WHERE  MessageID < ( SELECT MIN(MessageID) FROM(SELECT TOP (@Rows) MessageID FROM bx_ChatMessages ORDER BY MessageID DESC) t)" + (ClearNoRead ? "" : " AND IsRead = 1;");
                    query.CreateTopParameter("@Rows", rows);
                }
                else if (mode == JobDataClearMode.CombinMode)
                {
                    query.CommandText = "DELETE FROM bx_ChatMessages WHERE  MessageID < ( SELECT MIN(MessageID) FROM(SELECT TOP (@Rows) MessageID FROM bx_ChatMessages ORDER BY MessageID DESC) t) AND CreateDate < DATEADD(day, @Days, GETDATE())" + (ClearNoRead ? "" : " AND IsRead = 1;");
                    query.CreateTopParameter("@Rows", rows);
                    query.CreateParameter<int>("@Days", days, SqlDbType.Int);
                }

                query.CreateParameter<bool>("@ClearNoRead", ClearNoRead, SqlDbType.Bit);
                query.ExecuteNonQuery();

            }
        }

        public override void DeleteSearchAttachments(AttachmentFilter filter, IEnumerable<Guid> excludeRoleIDs, int topCount, out int deletedCount, out List<int> threadIDs)
        {

            using (SqlQuery query = new SqlQuery())
            {

                string conditon = BuilderSearchAttachmentCondition(filter, excludeRoleIDs, query, false);

                query.CommandText = @"
DECLARE @Table table(AttachmentID int,FileID varchar(50),ThreadID int);
INSERT INTO @Table SELECT TOP (@TopCount) [AttachmentID],[FileID],[ThreadID] FROM [bx_AttacmentsWithForumID] WHERE " + conditon + @";
DELETE [bx_Attachments] WHERE AttachmentID IN(SELECT AttachmentID FROM @Table);
SELECT @@ROWCOUNT;
SELECT DISTINCT ThreadID FROM @Table;
";


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

                threadIDs = new List<int>();
                //fileIDs = new List<string>();
                deletedCount = 0;
                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        deletedCount = reader.Get<int>(0);
                    }
                    if (reader.NextResult())
                    {
                        while (reader.Read())
                        {
                            threadIDs.Add(reader.Get<int>(0));
                        }
                    }
                }
            }
        }

        public override ThreadCollectionV5 GetThreads(ThreadSortField sortType, int count, IEnumerable<int> forumIDs)
        {
            using (SqlQuery query = new SqlQuery())
            {
                if (forumIDs != null && ValidateUtil.HasItems<int>(forumIDs))
                {
                    query.CommandText = @"
SELECT TOP(@TopCount) " + ThreadFields + @" FROM bx_Threads WITH(NOLOCK) WHERE ForumID in(@ForumIDs) AND ThreadStatus = 1 ORDER BY " + GetSortField(sortType) + @" DESC;
";
                    query.CreateInParameter<int>("@ForumIDs", forumIDs);
                }
                else
                {
                    query.CommandText = @"
SELECT TOP(@TopCount) " + ThreadFields + @" FROM bx_Threads WITH(NOLOCK) WHERE ThreadStatus = 1 ORDER BY " + GetSortField(sortType) + @" DESC;
";
                }

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

                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    return new ThreadCollectionV5(reader);
                }
            }
        }

        public override ThreadCollectionV5 GetUserQuestionThreads(int userID, int count, int exceptThreadID)
        {
            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = string.Concat(@"
    IF @ExceptThreadID>0
		SELECT TOP(@TopCount) ", ThreadFields, @" FROM bx_Threads WITH (NOLOCK) WHERE ThreadType = 2 AND [email protected] AND ThreadID<>@ExceptThreadID AND [ThreadStatus] < 4 ORDER BY ThreadID DESC;
	ELSE
		SELECT TOP(@TopCount) ", ThreadFields, @" FROM bx_Threads WITH (NOLOCK) WHERE ThreadType = 2 AND [email protected] AND [ThreadStatus] < 4 ORDER BY ThreadID DESC;
");
                query.CommandType = CommandType.Text;

                query.CreateTopParameter("@TopCount", count);
                query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
                query.CreateParameter<int>("@ExceptThreadID", exceptThreadID, SqlDbType.Int);

                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    return new ThreadCollectionV5(reader);
                }
            }
        }

        public override ThreadCollectionV5 GetTopViewThreads(IEnumerable<int> forumIDs, int count, DateTime? beginDate, DateTime? endDate)
        {
            using (SqlQuery query = new SqlQuery())
            {
                DeclareVariableCollection beforeSqlDeclare;
                string beforeSql, condition;
                ProcessThreadDateScope(beginDate, endDate, query, out beforeSqlDeclare, out beforeSql, out condition);

                if (forumIDs != null && ValidateUtil.HasItems<int>(forumIDs))
                {
                    query.CommandText = string.Concat(@"
 SELECT TOP (@TopCount) ", ThreadFields, @" FROM bx_Threads WHERE ForumID in(@ForumIDs) AND ThreadStatus = 1 ", condition, @" ORDER BY TotalViews DESC;
");
                    query.CreateInParameter<int>("@ForumIDs", forumIDs);
                }
                else
                {
                    query.CommandText = string.Concat(@"
 SELECT TOP (@TopCount) ", ThreadFields, @" FROM bx_Threads WHERE ThreadStatus = 1 ", condition, @" ORDER BY TotalViews DESC;
");
                }

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


                query.CommandText = beforeSqlDeclare.GetDeclareVariableSql() + beforeSql + query.CommandText;

                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    return new ThreadCollectionV5(reader);
                }
            }
        }