public OperationResult<string> Login(string userName, string password) { using (var conn = this.OpenConnection()) { password = Utility.MD5(password); string sql = @"SELECT TOP 1 ID FROM [User] WHERE UserName=@UserName AND Password=@Password"; var para = new { UserName = userName, Password = password }; var user = conn.Query(sql, para).FirstOrDefault(); if (user == null) { return OperationResult<string>.ErrorResult("用户名或密码错误"); } string token = Utility.MD5(Guid.NewGuid().ToString()); sql = @"UPDATE TOP(1) [User] SET Token=@Token WHERE ID =@ID"; conn.Execute(sql, new { ID = user.ID, Token = token }); return OperationResult<string>.SuccessResult(token); } }
public void TestStrings() { var values = new[] { new ConfigPathHelper{ Name="item1", Path="path1 path3 path4\tpath5\rpath6\n\rpath7"}, new ConfigPathHelper{ Name="item2", Path="path2"}, }; var config = CreateConfig(values); var lookup = config.ToLookup(); Assert.Equal(2, lookup.Count); Assert.Equal(new[] { "path1", "path3", "path4", "path5", "path6", "path7" }, lookup["item1"]); Assert.Equal(new[] { "path2" }, lookup["item2"]); }
public void ScriptStylesParse() { var values = new[] { new ConfigPathHelper{ Name="item1", Path="path1 path3 path4"}, new ConfigPathHelper{ Name="item2", Path="path2"}, }; var config = Substitute.For<IConfiguration>(); var scriptsConfig = CreateConfig(values); config.GetSubKey("Scripts").Returns(scriptsConfig); var cdnConfig = new ContentDeliveryNetworkConfiguration(config); Assert.Equal(2, cdnConfig.Scripts.Count); Assert.Equal(new[] { "path1", "path3", "path4" }, cdnConfig.Scripts["item1"]); Assert.Equal(new[] { "path2" }, cdnConfig.Scripts["item2"]); }
public OperationResult<int?> Add(AddCommentRequest model) { if (!this.ArticleService.IsArticleExisted(model.ArticleID)) { return OperationResult<int?>.ErrorResult("不存在的文章"); } if (model.ReplyTo.HasValue && !this.IsCommentExisted(model.ReplyTo.Value)) { return OperationResult<int?>.ErrorResult("不存在的评论"); } using (var conn = this.OpenConnection()) { string sql = @" INSERT INTO [Comment] (ArticleID,ReplyTo,Content,Email,Nickname,PostDate,PostIP,Status,NotifyOnReply) VALUES (@ArticleID,@ReplyTo,@Content,@Email,@Nickname,@PostDate,@PostIP,@Status,@NotifyOnReply); SELECT @@IDENTITY; "; var para = new { model.ArticleID, model.ReplyTo, model.Content, model.Email, model.Nickname, PostDate = DateTime.Now, model.PostIP, Status = 1, model.NotifyOnReply }; var id = conn.ExecuteScalar<int>(sql, para); string cacheKey = Helpers.CacheKeyHelper.GetArticleCommentsCacheKey(model.ArticleID); this.Cache.Remove(cacheKey); return OperationResult<int?>.SuccessResult(id); } }
public OperationResult<int?> Add(AddCategoryRequest model) { if (!this.CheckCategoryName(model.Name, null)) { return OperationResult<int?>.ErrorResult("分类名称重复"); } if (model.ParentID.HasValue && !this.CheckParentID(model.ParentID.Value)) { return OperationResult<int?>.ErrorResult("错误的上级分类"); } using (var conn = this.OpenConnection()) { string sql = @" INSERT INTO [Category] ( Name, ParentID ) VALUES ( @Name, @ParentID ); SELECT @@IDENTITY; "; var para = new { Name = model.Name, ParentID = model.ParentID }; var id = conn.ExecuteScalar<int>(sql, para); this.ClearCache(); return OperationResult<int?>.SuccessResult(id); } }
public OperationResult Delete(DeleteCategoryRequest model) { using (var conn = this.OpenConnection()) { string sql = @" UPDATE [Category] SET ParentID=NULL WHERE ParentID IN @IDs; DELETE FROM [Category] WHERE ID IN @IDs; DELETE FROM [CategoryArticle] WHERE CategoryID IN @IDs; "; var para = new { IDs = model.CategoryIDList }; var rows = conn.Execute(sql, para); if (rows == 0) { return OperationResult.ErrorResult("不存在的分类"); } this.ClearCache(); return OperationResult.SuccessResult(); } }
public OperationResult Edit(EditCategoryRequest model) { if (!this.CheckCategoryName(model.Name, model.ID)) { return OperationResult<int?>.ErrorResult("分类名称重复"); } if (model.ParentID.Value == model.ID.Value) { return OperationResult.ErrorResult("错误的上级分类"); } if (model.ParentID.HasValue && !this.CheckParentID(model.ParentID.Value)) { return OperationResult<int?>.ErrorResult("错误的上级分类"); } if (this.GetCategoryList().Any(t => t.ParentID == model.ID.Value)) { return OperationResult<int?>.ErrorResult("只允许二级分类"); } using (var conn = this.OpenConnection()) { string sql = @" UPDATE TOP(1) [Category] SET Name=@Name, ParentID=@ParentID WHERE ID=@ID; "; var para = new { model.ID, model.Name, model.ParentID }; var rows = conn.Execute(sql, para); if (rows == 0) { return OperationResult.ErrorResult("不存在的分类"); } else { this.ClearCache(); return OperationResult.SuccessResult(); } } }
private bool IsCommentExisted(int id) { using (var conn = this.OpenConnection()) { string sql = @"SELECT 1 FROM [Comment] WITH(NOLOCK) WHERE ID=@ID"; var para = new { ID = id }; return conn.Exist(sql, para); } }
public OperationResult ChangeStatus(ChangeStatusRequest model) { using (var conn = this.OpenConnection()) { string sql = @" UPDATE [Comment] SET [Status]=@Status WHERE ID IN @IDs; SELECT DISTINCT ArticleID FROM [Comment] WITH(NOLOCK) WHERE ID IN @IDs; "; var para = new { Status = model.Status, IDs = model.CommentIDList }; var articleIDList = conn.Query<int>(sql, para).ToList(); if (articleIDList.Count == 0) { return OperationResult.ErrorResult("不存在的评论"); } foreach (var articleID in articleIDList) { string cacheKey = Helpers.CacheKeyHelper.GetArticleCommentsCacheKey(articleID); this.Cache.Remove(cacheKey); } return OperationResult.SuccessResult(); } }
public OperationResult Delete(DeleteCommentRequest model) { using (var conn = this.OpenConnection()) { string sql = @" SELECT DISTINCT ArticleID FROM [Comment] WITH(NOLOCK) WHERE ID IN @IDs; UPDATE [Comment] SET ReplyTo=NULL WHERE ReplyTo IN @IDs; DELETE FROM [Comment] WHERE ID IN @IDs; "; var para = new { IDs = model.CommentIDList }; var articleIDList = conn.Query<int>(sql, para).ToList(); if (articleIDList.Count == 0) { return OperationResult.ErrorResult("不存在的评论"); } foreach (var articleID in articleIDList) { string cacheKey = Helpers.CacheKeyHelper.GetArticleCommentsCacheKey(articleID); this.Cache.Remove(cacheKey); } return OperationResult.SuccessResult(); } }
public List<ArticleModel> QueryByCategory(int categoryID, int pageIndex, int pageSize, out int total) { string where = "WHERE Status=2 AND ID IN (SELECT ArticleID FROM [CategoryArticle] WHERE CategoryID=@CategoryID)"; var para = new { CategoryID = categoryID }; var list = this.QueryByCondition(pageIndex, pageSize, out total, where, "ORDER BY PostDate DESC", para); return list; }
public List<CommentData> GetCommentList(int articleID) { string cacheKey = Helpers.CacheKeyHelper.GetArticleCommentsCacheKey(articleID); var list = this.Cache.Get<List<CommentData>>(cacheKey, ()=> { using (var conn = this.OpenConnection()) { string sql = @" SELECT ID,Content,ReplyTo,Email,Nickname,PostDate,PostIP,Status,NotifyOnReply FROM [Comment] WITH(NOLOCK) WHERE ArticleID=@ArticleID "; var para = new { ArticleID = articleID }; return conn.Query<CommentData>(sql, para).ToList(); } }); return list; }
private void SaveConfiguration(string server, string database, string userID, string password) { var data = new { ConnectionString = new { Server = server, Database = database, UserID = userID, Password = password } }; string json = Newtonsoft.Json.JsonConvert.SerializeObject(data); string path = Path.Combine(ApplicationEnvironment.ApplicationBasePath, "config/database.json"); string directoryName = Path.GetDirectoryName(path); Directory.CreateDirectory(directoryName); using (var stream = File.Create(path)) using (var writer = new StreamWriter(stream)) { writer.Write(json); } }
public List<ArticleModel> QueryByAuthor(int userID, int pageIndex, int pageSize, out int total) { string where = "WHERE Status=2 AND UserID=@UserID"; var para = new { UserID = userID }; var list = this.QueryByCondition(pageIndex, pageSize, out total, where, "ORDER BY PostDate DESC", para); return list; }
public bool IsArticleExisted(int id) { using (var conn = this.OpenConnection()) { string sql = @"SELECT 1 FROM [Article] WHERE ID=@ID"; var para = new { ID = id }; return conn.Exist(sql, para); } }
public OperationResult ChangeStatus(ChangeStatusRequest model) { using (var conn = this.OpenConnection()) { string sql = @" UPDATE [Article] SET [Status]=@Status WHERE ID IN @IDs; "; var para = new { Status = model.Status, IDs = model.ArticleIDList }; var rows = conn.Execute(sql, para); if (rows == 0) { return OperationResult.ErrorResult("不存在的文章"); } this.CategoryService.ClearCache(); return OperationResult.SuccessResult(); } }
public ArticleModel Get(int id) { using (var conn = this.OpenConnection()) { string sql = @" DECLARE @tbComments TABLE ( ID INT PRIMARY KEY, Total INT, Pass INT, Junk INT ); INSERT INTO @tbComments SELECT ArticleID AS ID, COUNT(1) AS Total, SUM(CASE Status WHEN 1 THEN 1 ELSE 0 END) AS Pass, SUM(CASE Status WHEN 2 THEN 1 ELSE 0 END) AS Junk FROM [Comment] WITH(NOLOCK) WHERE ArticleID = @ID GROUP BY ArticleID SELECT Article.ID, Article.Title, Article.Content, Article.Summary, Article.[From], Article.PostDate, Article.Status, Article.AllowComment, [User].ID, [User].UserName, [User].Nickname, A.ID, A.Total, A.Pass, A.Junk FROM Article WITH(NOLOCK) JOIN [User] WITH(NOLOCK) ON Article.UserID=[User].ID LEFT JOIN @tbComments A ON Article.ID = A.ID WHERE Article.ID = @ID; SELECT CategoryID, ArticleID FROM CategoryArticle WITH(NOLOCK) WHERE ArticleID = @ID; "; var para = new { ID = id }; using (var reader = conn.QueryMultiple(sql, para)) { var article = reader.Read<ArticleModel, UserModel, ArticleComments, ArticleModel>((a, u, ac) => { a.User = u; a.Comments = ac; return a; }).FirstOrDefault(); if (article == null) { return null; } var categoryArticleList = reader.Read<CategoryArticleData>().ToList(); var categoryDataList = this.CategoryService.GetCategoryList(); var query = from ca in categoryArticleList join c in categoryDataList on ca.CategoryID equals c.ID where ca.ArticleID == article.ID select new Models.Category.CategoryModel { ID = c.ID, Name = c.Name }; article.CategoryList = query.ToList(); return article; } } }