C# SqlQuery.CreateParameter方法代码示例

        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 ChatMessageCollection GetLastChatMessages(int userID, int targetUserID, int lastMessageID, int MessageCount)
        {
            ChatMessageCollection result;
            using (SqlQuery query = new SqlQuery())
            {
                query.CommandText = "bx_Chat_GetLastMessages";
                query.CommandType = CommandType.StoredProcedure;

                query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
                query.CreateParameter<int>("@TargetUserID", targetUserID, SqlDbType.Int);
                query.CreateParameter<int>("@LastMessageID", lastMessageID, SqlDbType.Int);

                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    result = new ChatMessageCollection();

                    while (reader.Read())
                    {
                        result.Insert(0, new ChatMessage(reader));
                    }
                }

                return result;
            }
        }

        public override MaxSerial CreateSerial(int ownerUserId, DateTime expriseDate, SerialType type, string data,out bool success)
        {
            using (SqlQuery query = new SqlQuery())
            {
                Guid serial = Guid.NewGuid();

                DateTime createDate = DateTimeUtil.Now;

                query.CommandText = "bx_CreateSerial";
                query.CommandType = System.Data.CommandType.StoredProcedure;
                query.CreateParameter<int>("@UserID", ownerUserId, SqlDbType.Int);
                query.CreateParameter<DateTime>("@ExpiresDate", expriseDate, SqlDbType.DateTime);
                query.CreateParameter<byte>("@Type", (byte)type, SqlDbType.TinyInt);
                query.CreateParameter<string>("@Data", data, SqlDbType.NVarChar, 1000);

                SqlParameter outputParam = query.CreateParameter<bool>("@Success", SqlDbType.Bit, ParameterDirection.Output);

                MaxSerial newSerial = null;
                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    while (reader.Next)
                    {
                        newSerial = new MaxSerial(reader);
                    }
                }

                success = (bool)outputParam.Value;
                return newSerial;
            }
        }

        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 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 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 GetPointStatInfo(PointLogFilter filter, int pointIndex, out int count, out int produceCount, out int consumeCount)
        {
            using (SqlQuery query = new SqlQuery())
            {
                StringBuilder sb = new StringBuilder();
                string sql1 = string.Format("SELECT SUM(Point{0}) FROM bx_PointLogs WHERE  Point{0} > 0 ", pointIndex);
                string sql2 = string.Format("SELECT SUM(Point{0}) FROM bx_PointLogs WHERE  Point{0} < 0 ", pointIndex);
                string sql0 = string.Format("SELECT SUM(Point_{0}) FROM bx_Users WHERE Point_{0}>0", pointIndex + 1);

                if (!string.IsNullOrEmpty(filter.Username))
                {
                    sb.Append(" AND UserID IN( SELECT UserID FROM bx_Users WHERE Username = @Username )");
                    query.CreateParameter<string>("@Username", filter.Username, SqlDbType.NVarChar, 50);
                    sql2 += " AND Username = @Username;";
                }

                if (filter.OperateID != null)
                {
                    sb.Append(" AND OperateID = @OperateID");
                    query.CreateParameter<int>("@OperateID", filter.OperateID.Value, SqlDbType.Int);
                }

                if (filter.BeginDate != null)
                {
                    sb.Append(" AND CreateTime >= @BeginDate");
                    query.CreateParameter<DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
                }

                if (filter.EndDate != null)
                {
                    sb.Append(" AND CreateTime <= @EndDate");
                    query.CreateParameter<DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
                }

                sql1 += sb;
                sql2 += sb;
                string sql = string.Format("{0};{1};{2};", sql0, sql1, sql2);

                query.CommandText = sql;
                count = 0;
                produceCount = 0;
                consumeCount = 0;

                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    while (reader.Next)
                        count = reader.Get<int>(0);
                    if (reader.NextResult())
                    {
                        while (reader.Next)
                            produceCount = reader.Get<int>(0);

                        if (reader.NextResult())
                            while (reader.Next)
                                consumeCount = reader.Get<int>(0);
                    }
                }
            }
        }

 public override void Delete(int userID, UserTempDataType dataType)
 {
     using (SqlQuery query = new SqlQuery())
     {
         query.CommandText = "DELETE FROM  bx_UserTempData WHERE UserID = @UserID AND DataType = @DataType";
         query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
         query.CreateParameter<short>("@DataType", (short)dataType, SqlDbType.SmallInt);
         query.CommandType = CommandType.Text;
         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();
            }
        }

        public override int GetTodayPostDoingCount(int userID, DateTime today)
        {
            using (SqlQuery db = new SqlQuery())
            {
                db.CommandText = "bx_Doing_GetTodayPostDoingCount";
                db.CommandType = CommandType.StoredProcedure;

                db.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
                db.CreateParameter<DateTime>("@Today", today, SqlDbType.DateTime);

                return db.ExecuteScalar<int>();
            }
        }

        public override UserPayCollection GetUserPays(int userID, PaylogFilter filter, int pageSize, int pageNumber)
        {
            using (SqlQuery query = new SqlQuery())
            {
                StringBuilder condition = new StringBuilder(" AND UserID = @UserID");
                query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
                
                if (filter != null)
                {
                    if (filter.State < 2)
                    {
                        condition.Append(" AND [email protected]");
                        query.CreateParameter<bool>("@State", (filter.State == 1), SqlDbType.Bit);
                    }
                    if (filter.BeginDate != null)
                    {
                        condition.Append(" AND PayDate>= @BeginDate");
                        query.CreateParameter<DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
                    }

                    if (filter.EndDate != null)
                    {
                        condition.Append(" AND PayDate<= @EndDate");
                        query.CreateParameter<DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
                    }

                }
                if (condition.Length > 5)
                    condition.Remove(0, 5);

                query.Pager.TableName = "bx_Pay";
                query.Pager.PageSize = pageSize;
                query.Pager.PageNumber = pageNumber;
                query.Pager.PrimaryKey = "PayID";
                query.Pager.SelectCount = true;
                query.Pager.SortField = "PayID";
                query.Pager.Condition = condition.ToString();
                query.Pager.IsDesc = true;
                using (XSqlDataReader reader = query.ExecuteReader())
                {
                    UserPayCollection result = new UserPayCollection(reader);
                    if (reader.NextResult())
                    {
                        while (reader.Next)
                            result.TotalRecords = reader.Get<int>(0);
                    }

                    return result;
                }
            }
        }

        public override void AddPropLog(int userID, PropLogType logType, string log)
        {
            using(SqlQuery query = new SqlQuery())
            {
                query.CommandText = "bx_Prop_AddPropLog";
                query.CommandType = CommandType.StoredProcedure;

                query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
                query.CreateParameter<PropLogType>("@Type", logType, SqlDbType.TinyInt);
                query.CreateParameter<string>("@Log", log, SqlDbType.NText);

                query.ExecuteNonQuery();
            }
        }

        public override int GetPostBlogArticleCount(int userID, DateTime beginDate, DateTime endDate)
        {
            using (SqlQuery db = new SqlQuery())
            {
                db.CommandText = "bx_Blog_GetPostBlogArticleCount";
                db.CommandType = CommandType.StoredProcedure;

                db.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
                db.CreateParameter<DateTime>("@BeginDate", beginDate, SqlDbType.DateTime);
                db.CreateParameter<DateTime>("@EndDate", endDate, SqlDbType.DateTime);

                return db.ExecuteScalar<int>();
            }
        }

        public override bool DeleteWebsite(int websiteID)
        {
            using (SqlQuery query = new SqlQuery())
            {

                query.CommandText = "bx_Chinaz_DeleteWebsite";
                query.CommandType = CommandType.StoredProcedure;
                query.CreateParameter<int>("@WebsiteID", websiteID, SqlDbType.Int);
                query.CreateParameter<int>("@Result", SqlDbType.Int, ParameterDirection.ReturnValue);
                query.ExecuteNonQuery();

                return (int)query.Parameters[1].Value > 0;

            }
        }