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;
}
}
}
