C# SqlQuery.CreateInParameter方法代码示例

 public override void DeleteAnnouncements(IEnumerable<int> announcementIds)
 {
     using (SqlQuery query = new SqlQuery())
     {
         query.CommandText = "DELETE FROM bx_Announcements WHERE AnnouncementID IN (@AnnouncementIDs)";
         query.CreateInParameter<int>("@AnnouncementIDs", announcementIds);
         query.ExecuteNonQuery();
     }
 }

 public override void Delete(IEnumerable<int> userIds, UserTempDataType dataType)
 {
     using (SqlQuery query = new SqlQuery())
     {
         query.CommandText = "DELETE FROM bx_UserTempData WHERE UserID IN (@UserIds) AND DataType = @DataType";
         query.CommandType = CommandType.Text;
         query.CreateParameter<short>("@DataType" ,(short)dataType , SqlDbType.SmallInt );
         query.CreateInParameter<int>("@UserIds" ,userIds);
         query.ExecuteNonQuery();
     }
 }

        public override void AdminDeleteSessions(IEnumerable<int> sessionIds, IEnumerable<Guid> exludeRoles)
        {
            using (SqlQuery query = new SqlQuery())
            {
                string excludeRoleCondition = DaoUtil.GetExcludeRoleSQL("UserID", exludeRoles, query);

                query.CommandText = "DELETE FROM bx_ChatSessions WHERE ChatSessionID IN( @IDs) ";

                if (!string.IsNullOrEmpty(excludeRoleCondition))
                {
                    query.CommandText += " AND " + excludeRoleCondition;
                }

                query.CreateInParameter<int>("@IDs", sessionIds);
                query.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 获取几张相片
        /// </summary>
        /// <param name="photoIDs">相片ID集</param>
        public override PhotoCollection GetPhotos(IEnumerable<int> photoIDs)
        {
            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = "SELECT * FROM [bx_Photos] WHERE [PhotoID] IN (@PhotoIDs);";

                query.CreateInParameter<int>("@PhotoIDs", photoIDs);

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

        /*

        /// <summary>
        /// 相册搜索
        /// </summary>
        public override AlbumCollection GetAlbumsByFilter(AlbumFilter filter, bool isGetPrivacyType, int pageNumber, int pageSize, ref int? count)
        {
            #region 数据条件

            string qUsernames = filter.Username;
            string searchKeys = filter.SearchKey;

            DateTime now = DateTimeUtil.Now;
            DateTime? endDate = filter.EndDate;
            DateTime? beginDate = filter.BeginDate;

            IEnumerable<string> usernames = null;
            if (!string.IsNullOrEmpty(qUsernames))
            {
                usernames = StringUtil.Split(qUsernames);
            }

            #endregion

            #region 组合SQL语句

            StringBuffer condition = new StringBuffer();
            condition += "([Name] LIKE '%' + @Key + '%') ";
            if (beginDate != null)
            {
                condition += " AND [CreateDate] >= @BeginDate ";
            }
            if (endDate != null)
            {
                condition += " AND [CreateDate] <= @EndDate ";
            }
            if (usernames != null)
            {
                condition += " AND [UserID] IN (SELECT [UserID] FROM [bx_Users] WHERE [Username] IN (@Usernames)) ";
            }
            if (!isGetPrivacyType)
            {
                condition += " AND ([PrivacyType] IN (0,3))";
            }

            #endregion

            using (SqlQuery query = new SqlQuery())
            {
                query.Pager.TableName = "bx_Albums";
                query.Pager.SortField = "[AlbumID]";
                query.Pager.PageNumber = pageNumber;
                query.Pager.PageSize = pageSize;
                query.Pager.TotalRecords = count;
                query.Pager.SelectCount = true;
                query.Pager.IsDesc = true;

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

                query.CreateParameter<string>("@Key", searchKeys, SqlDbType.NVarChar, 50);
                query.CreateParameter<DateTime?>("@BeginDate", beginDate, SqlDbType.DateTime);
				query.CreateParameter<DateTime?>("@EndDate", endDate, SqlDbType.DateTime);


				query.CreateInParameter("@Usernames", usernames);

                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    SqlDataReaderWrap readerWrap = new SqlDataReaderWrap(reader);
                    AlbumCollection albums = new AlbumCollection(readerWrap);
                    if (count == null && readerWrap.NextResult())
                    {
                        if (readerWrap.Next)
                        {
                            count = readerWrap.Get<int>(0);
                        }
                    }
                    return albums;
                }
            }
        }

        */

        /// <summary>
        /// 高级相册搜索
        /// </summary>


        /// <summary>
        /// 批量移动相片到新相册
        /// </summary>
        /// <param name="photoIDs">要移动的相片ID集</param>
        /// <param name="targetAlbumID">新相册ID</param>
        public override bool MovePhotos(IEnumerable<int> photoIds, int targetAlbumID)
        {
            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = @"UPDATE [bx_Photos] SET [AlbumID] = @AlbumID WHERE [PhotoID] IN (@PhotoIds)";

//.........这里部分代码省略.........

        //public override void ModifyFriendGroupName(int userID, string friendGroups)
        //{
        //    using (SqlQuery query = new SqlQuery())
        //    {
        //        query.CommandText = "UPDATE [bx_Users] SET FriendGroups = @FriendGroups WHERE UserID = @UserID";

        //        query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
        //        query.CreateParameter<string>("@FriendGroups", friendGroups, SqlDbType.NVarChar, 500);

        //        query.ExecuteNonQuery();
        //    }
        //}

        public override void MoveFriends(int userID, IEnumerable<int> friendUserIds, int groupID)
        {
            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = "UPDATE [bx_Friends] SET GroupID = @FriendGroupID WHERE UserID = @UserID AND FriendUserID IN (@FriendUserIds)";

                query.CreateParameter<int>("@FriendGroupID", groupID, SqlDbType.Int);
                query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);

                query.CreateInParameter<int>("@FriendUserIds", friendUserIds);

                query.ExecuteNonQuery();
            }
        }

        /**************************************
         *      Delete开头的函数删除数据      *
         **************************************/

        public override DeleteResult DeleteBlogArticles(IEnumerable<int> articleIDs, int operatorID, IEnumerable<Guid> excludeRoleIDs)
        {
            using (SqlQuery query = new SqlQuery())
            {
                string excludeRolesSql = DaoUtil.GetExcludeRoleSQL("[UserID]", "[LastEditUserID]", operatorID, excludeRoleIDs, query);

                if (string.IsNullOrEmpty(excludeRolesSql) == false)
                    excludeRolesSql = " AND ([UserID] = @UserID OR " + excludeRolesSql + ")";

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

INSERT INTO @DeleteData SELECT [UserID],[ArticleID] FROM [bx_BlogArticles] WHERE [ArticleID] IN (@ArticleIDs)" + excludeRolesSql + @";

DELETE [bx_BlogArticles] WHERE ArticleID IN (SELECT [ArticleID] FROM @DeleteData);

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

                query.CommandText = sql;

                query.CreateInParameter<int>("@ArticleIDs", articleIDs);
                query.CreateParameter<int>("@UserID", operatorID, SqlDbType.Int);

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

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

                    return deleteResult;
                }
            }
        }

        public override void DeleteComments(IEnumerable<int> commentIds)
        {
            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = "DELETE FROM bx_Comments WHERE CommentID IN (@CommentIds)";

                query.CreateInParameter<int>("@CommentIds", commentIds);

                query.ExecuteNonQuery();
            }
        }

        public override CommentCollection GetComments(IEnumerable<int> commentIds)
        {
            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = "SELECT * FROM [bx_Comments] WHERE CommentID IN (@CommentIds);";

                query.CreateInParameter<int>("@CommentIds", commentIds);

                CommentCollection comments = new CommentCollection();

                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        comments.Add(new Comment(reader));
                    }
                    return comments;
                }
            }
        }

        public override RevertableCollection<ImpressionRecord> GetImpressionRecordsWithReverters(int[] impressionTypeIDs)
        {
            if (ValidateUtil.HasItems(impressionTypeIDs) == false)
                return null;

            RevertableCollection<ImpressionRecord> records = new RevertableCollection<ImpressionRecord>();

            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = @"
SELECT 
    B.*,
	A.Text,
    A.KeywordVersion,
	TextReverter = ISNULL(R.TextReverter, '')
FROM 
    bx_ImpressionRecords AS B
LEFT JOIN
	bx_ImpressionTypes AS A WITH(NOLOCK) ON A.TypeID = B.TypeID
LEFT JOIN 
	bx_ImpressionTypeReverters AS R WITH(NOLOCK) ON R.TypeID = A.TypeID
WHERE 
	B.TypeID IN (@TypeIDs)";

                query.CreateInParameter<int>("@TypeIDs", impressionTypeIDs);

                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string textReverter = reader.Get<string>("TextReverter");

                        ImpressionRecord record = new ImpressionRecord(reader);

                        records.Add(record, textReverter);
                    }
                }
            }

            return records;
        }

		public override void IgnoreDenouncings(int[] denouncingIDs)
		{
			using (SqlQuery query = new SqlQuery())
			{
				query.CommandText = "UPDATE bx_Denouncings SET IsIgnore = 1 WHERE DenouncingID IN (@DenouncingIDs)";
				
				query.CreateInParameter<int>("@DenouncingIDs", denouncingIDs);

				query.ExecuteNonQuery();
			}
		}

        public override void UpdateClubMemberStatus(int clubID, int[] userIDs, ClubMemberStatus status)
        {
            using (SqlQuery db = new SqlQuery())
            {
                db.CommandText = "UPDATE bx_ClubMembers SET Status = @Status WHERE ClubID = @ClubID AND UserID IN (@UserIDs);";

                db.CreateParameter<int>("@ClubID", clubID, SqlDbType.Int);
                db.CreateParameter<ClubMemberStatus>("@Status", status, SqlDbType.TinyInt);
                db.CreateInParameter<int>("@UserIDs", userIDs);

                db.ExecuteNonQuery();
            }
        }

        public override bool DeleteImpressionRecords(int[] recordIDs)
        {
            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = "DELETE FROM bx_ImpressionRecords WHERE TypeID IN (@TypeIDs)";

                query.CreateInParameter<int>("@TypeIDs", recordIDs);

                query.ExecuteNonQuery();

                return true;
            }
        }

        public override void RemoveClubMembers(int clubID, int[] userIDs)
        {
            using (SqlQuery db = new SqlQuery())
            {
                db.CommandText = "DELETE FROM bx_ClubMembers WHERE ClubID = @ClubID AND UserID IN (@UserIDs);";

                db.CreateParameter<int>("@ClubID", clubID, SqlDbType.Int);
                db.CreateInParameter<int>("@UserIDs", userIDs);

                db.ExecuteNonQuery();
            }
        }

 /// <summary>
 /// 删除执行过的指令
 /// </summary>
 /// <param name="instructID"></param>
 public override void DeleteInstruct(IEnumerable<long> instructID)
 {
     using (SqlQuery query = new SqlQuery())
     {
         query.CommandText = "DELETE FROM bx_Instructs WHERE InstructID IN( @InstructIDs );";
         query.CreateInParameter<long>("@InstructIDs", instructID);
         query.ExecuteNonQuery();
     }
 }