public int SaveCusLink(CusLink cusLink)
{
int result = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
if (cusLink.Id > 0)
{
cusLink.Updatedate = DateTime.Now;
result = conn.Execute("update cuslink set " +
"title=@Title," +
"url=@Url," +
"catid=@Catid," +
"linktype=@LinkType," +
"updatedate=@Updatedate where id=@Id", cusLink);
}
else
{
cusLink.Adddate = DateTime.Now;
result = conn.Execute("INSERT INTO cuslink(title,url,status,catid,linktype,adddate)" +
"values(@Title,@Url,@Status,@Catid,@LinkType,@Adddate)", cusLink);
}
}
return(result);
}
public IEnumerable <Link> PagerLinkList(int size, int offset, string catid, string title, string url, out int total)
{
string sqlwhere = "where 1=1";
if (!string.IsNullOrWhiteSpace(catid))
{
sqlwhere += $" and catid = @catid";
}
if (!string.IsNullOrWhiteSpace(title))
{
sqlwhere += $" and title like '%{title}%'";
}
if (!string.IsNullOrWhiteSpace(url))
{
sqlwhere += $" and url like '%{url}%'";
}
IEnumerable <Link> list;
total = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT * FROM link {sqlwhere} ORDER BY createtime desc LIMIT @size OFFSET @offset");
list = conn.Query <Link>(sql, new { catid, size, offset });
total = conn.QueryFirstOrDefault <int>($"select count(*) from link {sqlwhere}", new { catid });
}
return(list);
}
public int SaveArticle(Article article)
{
int result = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
object exists = conn.ExecuteScalar("select id from article where Id = @Id", new { Id = article.Id });
if (exists == null && article.AddOrEdit)
{
result = conn.Execute("INSERT INTO article(id,origin,catalog,icon,title,brief,body)" +
"values(@Id,@Origin,@Catalog,@Icon,@Title,@Brief,@Body)", article);
}
else if (exists != null && !article.AddOrEdit)
{
result = conn.Execute("update article set " +
"origin=@Origin," +
"catalog=@Catalog," +
"icon=@Icon," +
"title=@Title," +
"brief=@Brief," +
"body=@Body where Id=@Id", article);
}
}
return(result);
}
public IEnumerable <Article> PagerArticleList(int size, int skip, string id, string title, string catalog, out int total, string fields = "*")
{
string sqlWhere = $"where 1=1";
if (!string.IsNullOrWhiteSpace(id))
{
sqlWhere = $"where id = @id";
}
else
{
if (!string.IsNullOrWhiteSpace(title))
{
sqlWhere += $" and title like '%{title}%'";
}
if (!string.IsNullOrWhiteSpace(catalog))
{
sqlWhere += " and catalog = @catalog";
}
}
IEnumerable <Article> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT {fields} FROM article {sqlWhere} ORDER BY createTime desc LIMIT @PageSize OFFSET @Offset");
list = conn.Query <Article>(sql, new { id, title, catalog, PageSize = size, Offset = skip });
total = conn.QueryFirstOrDefault <int>($"select count(*) FROM article {sqlWhere}", new { id, title, catalog });
}
return(list);
}
public int SaveLink(Link link)
{
int result = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
Guid guid = new Guid();
if (Guid.TryParse(link.Id, out guid))
{
result = conn.Execute("update link set " +
"title=@Title," +
"url=@Url," +
"icon=@Icon," +
"catid=@Catid," +
"linktype=@LinkType," +
"brief=@Brief where id=@Id", link);
}
else
{
link.Id = Guid.NewGuid().ToString();
result = conn.Execute("INSERT INTO link(id,icon,linktype,catid,title,url,brief)" +
"values(@Id,@Icon,@LinkType,@Catid,@Title,@Url,@Brief)", link);
}
}
return(result);
}
public int DeleteLink(string id)
{
int result = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
result = conn.Execute("delete from link where Id = @Id", new { Id = id });
}
return(result);
}
public int UpdatedArticleVisited(string id)
{
int result = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
result = conn.Execute("update article set visited=visited+1 where id=@id", new { id });
}
return(result);
}
public int UpdatedLinkVisited(string id)
{
int result = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
result = conn.Execute("update link set visited = visited + 1 where Id = @Id", new { Id = id });
}
return(result);
}
public int LinksVisitedCount()
{
int total = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
total = conn.QuerySingle <int>("select sum(visited) from link");
}
return(total);
}
public IEnumerable <Link> ListLinkByCat(string catid)
{
IEnumerable <Link> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT title,id,visited,brief FROM link where catid = @catid ORDER BY visited desc");
list = conn.Query <Link>(sql, new { catid });
}
return(list);
}
public IEnumerable <Link> GetAllLink()
{
IEnumerable <Link> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT icon,title,id,linkType FROM link ORDER BY linkType asc,visited desc");
list = conn.Query <Link>(sql);
}
return(list);
}
public LinkCat GetLinkCat(string id)
{
var linkCat = new LinkCat();
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format("SELECT * FROM linkcat WHERE Id = @Id");
linkCat = conn.QueryFirstOrDefault <LinkCat>(sql, new { Id = id });
}
return(linkCat);
}
public IEnumerable <LinkCat> LinkCatList()
{
IEnumerable <LinkCat> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT id,catname FROM linkcat ORDER BY id desc");
list = conn.Query <LinkCat>(sql);
}
return(list);
}
public IEnumerable <CusLink> ListCusLinkByPaging(int size, int skip, string fields = "*")
{
IEnumerable <CusLink> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT {fields} FROM cuslink ORDER BY id desc LIMIT @PageSize OFFSET @Offset");
list = conn.Query <CusLink>(sql, new { PageSize = size, Offset = skip });
}
return(list);
}
public IEnumerable <CusLink> GetTopCusLink(int size, string fields = "*")
{
IEnumerable <CusLink> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format("SELECT {0} FROM cuslink ORDER BY id desc LIMIT @size", fields);
list = conn.Query <CusLink>(sql, new { size });
}
return(list);
}
public IEnumerable <Article> ListArticleByPaging(int size, int skip, string fields = "*")
{
IEnumerable <Article> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT {fields} FROM article ORDER BY catalog asc,createTime desc LIMIT @PageSize OFFSET @Offset");
list = conn.Query <Article>(sql, new { PageSize = size, Offset = skip });
}
return(list);
}
public Article GetArticle(string id, string fields = "*")
{
var article = new Article();
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format("SELECT {0} FROM article WHERE Id = @Id", fields);
article = conn.QueryFirstOrDefault <Article>(sql, new { Id = id });
}
return(article);
}
public Account GetAccount(string id, string fields = "*")
{
var account = new Account();
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format("SELECT {0} FROM account WHERE Id = @Id", fields);
account = conn.QueryFirstOrDefault <Account>(sql, new { Id = id });
}
return(account);
}
public Link GetLink(string id, string fields = "*")
{
var link = new Link();
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format("SELECT {0} FROM link WHERE Id = @Id", fields);
link = conn.QueryFirstOrDefault <Link>(sql, new { Id = id });
}
return(link);
}
public Account GetLogin(string userid, string password, string fields = "*")
{
Account account;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT * FROM account where userid=@userid and password=@password");
account = conn.QueryFirstOrDefault <Account>(sql, new { userid, password });
}
return(account);
}
public IEnumerable <Account> PagerAccountList(int size, int skip, string username, out int total, string fields = "*")
{
IEnumerable <Account> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT {fields} FROM account ORDER BY id desc LIMIT @PageSize OFFSET @Offset");
list = conn.Query <Account>(sql, new { PageSize = size, Offset = skip });
total = conn.QueryFirstOrDefault <int>($"select count(*) FROM account");
}
return(list);
}