本文整理汇总了C#中SqlQuery.CreateTopParameter方法的典型用法代码示例。如果您正苦于以下问题:C# SqlQuery.CreateTopParameter方法的具体用法?C# SqlQuery.CreateTopParameter怎么用?C# SqlQuery.CreateTopParameter使用的例子?那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类SqlQuery
的用法示例。
在下文中一共展示了SqlQuery.CreateTopParameter方法的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: DeletePropLogs
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();
}
}
示例2: LoadClientInstruct
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;
}
}
示例3: GetLastestCommentsForSomeone
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);
}
}
}
示例4: GetImpressionTypesForUse
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;
}
}
}
示例5: DeleteImpressionRecordsForAdmin
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;
}
}
}
示例6: ClearPointLogs
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();
}
}
示例7: DeleteBlogArticlesBySearch
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;
}
}
}
示例8: DeleteSearchPosts
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;
}
}
}
示例9: DeleteAlbumsBySearch
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;
}
}
}
示例10: GetSearchTopicsSql
//.........这里部分代码省略.........
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;
}
示例11: ClearMessage
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();
}
}
示例12: DeleteSearchAttachments
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));
}
}
}
}
}
示例13: GetThreads
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);
}
}
}
示例14: GetUserQuestionThreads
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);
}
}
}
示例15: GetTopViewThreads
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);
}
}
}