C# SqlQuery.ExecuteNonQuery方法代码示例

        public override int AddFriendGroups(int userID, List<KeyValuePair<int, string>> friendGroup)
        {
            using (SqlQuery query = new SqlQuery())
            {
                StringBuilder sbSql = new StringBuilder();
                int i=0;   
                string deleteExistGroupSql = "DELETE FROM bx_FriendGroups WHERE GroupID IN(";
                foreach (KeyValuePair<int, string> group in friendGroup)
                {
                    string groupName = "@GroupName_"+i;
                    string groupID = "@GroupID_"+i;
                 
                    sbSql.AppendFormat(" INSERT INTO bx_FriendGroups( GroupID, GroupName, UserID) VALUES({0},{1},@UserID);", groupID, groupName);
                    deleteExistGroupSql = string.Concat(deleteExistGroupSql,group.Key, ",");
                    query.CreateParameter<int>(groupID, group.Key, SqlDbType.Int);
                    query.CreateParameter<string>(groupName, group.Value, SqlDbType.NVarChar, 50);

                    i++;
                }

                deleteExistGroupSql = deleteExistGroupSql.TrimEnd(',') + ");";
                sbSql.Insert(0, deleteExistGroupSql); //删除已经存在的相同ID的好友分组--


                query.CommandText =sbSql.ToString();
                query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
                return query.ExecuteNonQuery();
            }
        }

        public override void BuildAuctionStage(IEnumerable<AuctionStage> newStages)
        {
            using( SqlQuery query = new SqlQuery() )
            {
                StringBuilder builer = new StringBuilder();
                int i = 0;
                foreach (AuctionStage s in newStages)
                {
                    string stageid = "@StageID_"+ i; 
                    string aucid = "@AuctionID_"+i;
                    string begintime = "@BeginTime_"+i;
                    string price = "@CurrentPrice_" + i;
                    string endTime = "@EndTime_"+i;
                    string usePoint = "@UsePoint_"+i;
                    builer.AppendFormat(@"IF NOT EXISTS( SELECT * FROM Chinaz_AuctionStages WHERE StageID = {0})
                                             INSERT INTO Chinaz_AuctionStages( StageID, AuctionID, BeginTime, EndTime, UsePoint,CurrentPrice) VALUES({0},{1},{2},{3},{4}, {5});"
                        , stageid, aucid, begintime, endTime, usePoint,price);

                    query.CreateParameter<int>(stageid, s.StageID, SqlDbType.Int);
                    query.CreateParameter<int>(aucid, s.AuctionID, SqlDbType.Int);
                    query.CreateParameter<DateTime>(begintime, s.BeginTime, SqlDbType.DateTime);
                    query.CreateParameter<DateTime>(endTime, s.EndTime, SqlDbType.DateTime);
                    query.CreateParameter<int>(usePoint, (int)s.UsePoint, SqlDbType.Int);
                    query.CreateParameter<int>(price, s.CurrentPrice, SqlDbType.Int);

                    i++;
                }

                query.CommandText = builer.ToString();
  
                query.ExecuteNonQuery();
            }
        }

        public override CreateClubResult CreateClub(int operatorID, int categoryID, string clubName, bool isApproved, string operatorIP, out int newClubID)
        {
            using (SqlQuery db = new SqlQuery())
            {
                db.CommandText = "bx_Club_CreateClub";
                db.CommandType = System.Data.CommandType.StoredProcedure;

                db.CreateParameter<int>("@UserID", operatorID, SqlDbType.Int);
                db.CreateParameter<int>("@CategoryID", categoryID, SqlDbType.Int);
                db.CreateParameter<bool>("@IsApproved", isApproved, SqlDbType.Bit);
                db.CreateParameter<string>("@CreateIP", operatorIP, SqlDbType.VarChar, 50);
                db.CreateParameter<string>("@Name", clubName, SqlDbType.NVarChar, 50);

                SqlParameter newID = db.CreateParameter<int>("@NewClubID", SqlDbType.Int, ParameterDirection.Output);

                SqlParameter result = db.CreateParameter<int>("@Result", SqlDbType.Int, ParameterDirection.ReturnValue);

                db.ExecuteNonQuery();

                switch ((int)result.Value)
                {
                    case 0:
                        newClubID = 0;
                        return CreateClubResult.HasSameNameClub;

                    default:
                        newClubID = (int)newID.Value;
                        return CreateClubResult.Succeed;
                }
            }
        }

        public override void CreateAution(int sortOrder
            , string title
            , string description
            , int lowestPrice
            , int increasePrice
            , int usePoint
            , bool isOpen
            ,int keepHours, string beginTime, int timeLength,string allowedRoles)
        {
            string sql;

            sql = @"INSERT INTO Chinaz_Auctions( SortOrder, Title,Description, LowestPrice, IncreasePrice, UsePoint, BeginTime, TimeLength, IsOpen, KeepHours, AllowedRoles) 
VALUES( @SortOrder, @Title, @Description, @LowestPrice, @IncreasePrice,  @UsePoint, @BeginTime, @TimeLength, @IsOpen, @KeepHours, @AllowedRoles)";

            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = sql;
                query.CreateParameter<int>("@SortOrder",sortOrder,SqlDbType.Int);
                query.CreateParameter<string>("@Title",title,SqlDbType.NVarChar,200);
                query.CreateParameter<string>("@Description",description,SqlDbType.NVarChar,1000);
                query.CreateParameter<int>("@LowestPrice",lowestPrice,SqlDbType.Int);
                query.CreateParameter<int>("@IncreasePrice", increasePrice, SqlDbType.Int);
                query.CreateParameter<int>("@TimeLength",timeLength,SqlDbType.Int);
                query.CreateParameter<bool>("@IsOpen",isOpen,SqlDbType.Bit);
                query.CreateParameter<int>("@KeepHours", keepHours, SqlDbType.Int);
                query.CreateParameter<string>("@AllowedRoles",allowedRoles,SqlDbType.VarChar,4000);
                query.CreateParameter<string>("@BeginTime", beginTime, SqlDbType.VarChar, 10);
                query.CreateParameter<int>("@UsePoint",usePoint,SqlDbType.Int);

                query.ExecuteNonQuery();
            }
        }

        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 int CreateTempUploadFile(int userID, string uploadAction, string searchInfo, StringList customParamList, string filename, string serverFileName, string md5, long fileSize, string fileID)
        {
            int tempUploadFileID;

            using (SqlQuery query = new SqlQuery())
            {
                query.CommandType = CommandType.StoredProcedure;
                query.CommandText = "bx_CreateTempUploadFile";

                query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
                query.CreateParameter<string>("@UploadAction", uploadAction, SqlDbType.VarChar, 100);
                query.CreateParameter<string>("@SearchInfo", searchInfo, SqlDbType.NVarChar, 100);
                query.CreateParameter<string>("@CustomParams", customParamList.ToString(), SqlDbType.NVarChar, 3000);
                query.CreateParameter<string>("@FileName", filename, SqlDbType.NVarChar, 256);
                query.CreateParameter<string>("@ServerFileName", serverFileName, SqlDbType.VarChar, 100);
                query.CreateParameter<string>("@MD5", md5, SqlDbType.Char, 32);
                query.CreateParameter<long>("@FileSize", fileSize, SqlDbType.Int);
                query.CreateParameter<string>("@FileID", fileID, SqlDbType.VarChar, 50);
                SqlParameter returnParam = query.CreateParameter<int>("@TempUploadFileID", SqlDbType.Int, ParameterDirection.Output);
                query.ExecuteNonQuery();

                tempUploadFileID = (int)returnParam.Value;
            }

            return tempUploadFileID;
        }

 public override void DeleteAnnouncement(int announcementID)
 {
     using (SqlQuery query = new SqlQuery())
     {
         query.CommandText = "DELETE FROM bx_Announcements WHERE [email protected]";
         query.CreateParameter<int>("@AnnouncementID", announcementID, SqlDbType.Int);
         query.ExecuteNonQuery();
     }
 }

 public override void DeleteRole(int roleid)
 {
     using (SqlQuery query = new SqlQuery())
     {
         query.CommandText = "DELETE FROM [bx_Roles] WHERE [email protected]";
         query.CreateParameter<int>("@RoleID", roleid, SqlDbType.Int);
         query.ExecuteNonQuery();
     }
 }

 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 DeleteByType(UserTempDataType dataType)
 {
     using (SqlQuery query = new SqlQuery())
     {
         query.CommandText = " DELETE FROM  bx_UserTempData WHERE DataType = @DataType";
         query.CreateParameter<short>("@DataType", (short)dataType, SqlDbType.SmallInt);
         query.CommandType = CommandType.Text;
         query.ExecuteNonQuery();
     }
 }

        /// <summary>
        /// Altera Session NLS_COMP e NLS_SORT, para iguinorar acentos na pesquisa
        /// </summary>
        /// <returns></returns>
        public static void AlterSessionBinary(ConnectionInfo pInfo)
        {
            SqlQuery lSqlQuery;
            int i;
            lSqlQuery = new SqlQuery(Instance.CreateDatabase(pInfo), "ALTER SESSION SET NLS_COMP=LINGUISTIC");
            i = lSqlQuery.ExecuteNonQuery();

            lSqlQuery = new SqlQuery(Instance.CreateDatabase(pInfo), "ALTER SESSION SET NLS_SORT=BINARY_AI");
            i = lSqlQuery.ExecuteNonQuery();
        }

 public override void DeleteUserDatas(int userID)
 {
     using (SqlQuery query = new SqlQuery())
     {
         query.CommandText = " DELETE FROM  bx_UserTempData WHERE UserID = @UserID";
         query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
         query.CommandType = CommandType.Text;
         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 bool UpdateClientInstructTypes( int clientID , IEnumerable<InstructType> instructTypes )
        {
            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = "UPDATE bx_PassportClients SET InstructTypes = @InstructTypes WHERE ClientID = @ClientID;";
                query.CommandType = CommandType.Text;
                query.CreateParameter<int>("@ClientID", clientID, SqlDbType.Int);
                query.CreateParameter<string>("@InstructTypes", StringUtil.Join(instructTypes), SqlDbType.Text);

                return query.ExecuteNonQuery() > 0;
            }
        }

        public override void SetJobLastExecuteTime(string type, DateTime executeTime)
        {
            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = "bx_SetJobExecuteTime";
                query.CommandType = CommandType.StoredProcedure;

                query.CreateParameter<string>("@Type", type, SqlDbType.VarChar, 200);
                query.CreateParameter<DateTime>("@ExecuteTime", executeTime, SqlDbType.DateTime);

                query.ExecuteNonQuery();
            }
        }