public async Task OrderBy()
{
var para = this.ParaForm();
KeyvalM data = KeyValBll.OrderBy(para.orderby);
await this.Json(new { errcode = data.ErrorCode, errmsg = data.ErrorMsg });
}
/// <summary>
/// 修改排序.
/// </summary>
/// <param name="orderString">id字符串,逗号隔开的.</param>
/// <returns></returns>
public static KeyvalM OrderBy(string orderString)
{
KeyvalM data = new KeyvalM();
if (string.IsNullOrWhiteSpace(orderString))
{
data.ErrorMsg = "排序参数不能为空!";
return(data);
}
string[] order = orderString.Split(',');
string sql = @"UPDATE Keyval SET orderby=@orderby WHERE id=@id";
SQLServer db = new SQLServer();
db.BeginTransaction();
for (int i = 0; i < order.Length; i++)
{
int re = db.ExecuteNoQuery(sql, i + 1, order[i]);
if (re == -999)
{
db.RollBackTransaction();
data.ErrorMsg = AlertMsg.数据库错误.ToString();
return(data);
}
}
if (db.CommitTransaction() == false)
{
data.ErrorMsg = AlertMsg.数据库错误.ToString();
return(data);
}
data.ErrorCode = 200;
// 更新缓存
CacheRefresh();
return(data);
}
/// <summary>
/// 数据:查找出符合条件的所有记录
/// </summary>
/// <param name="para">查询条件参数</param>
/// <returns></returns>
public static List <KeyvalM> All(KeyvalM para)
{
StringBuilder sb = new StringBuilder("1=1");
if (para.Category > 0)
{
sb.Append(" and category=@category");
}
string where = sb.ToString();
string sql = $@"
SELECT id,title,category,comment,ctime,CASE WHEN status=1 THEN 1 ELSE 0 END [enabled]
FROM Keyval
WHERE {where}
ORDER BY orderby , ctime DESC";
//
SQLServer db = new SQLServer();
// 数据列表
KeyvalM[] data = db.ExecuteQuery <KeyvalM, KeyvalM>(sql, para);
if (data == null)
{
return(new List <KeyvalM>());
}
para.ErrorCode = ErrCode.Success;
return(data.ToList());
}
/// <summary>
/// 修改,只修改 title,comment这两个字段
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public static void Update(KeyvalM para)
{
string update = "update KeyVal(title,comment) where id=@id";
SQLServer db = new SQLServer();
int re = db.Update <KeyvalM>(update, para);
if (re == 1)
{
// 更新缓存
CacheRefresh();
para.ErrorCode = 200;
return;
}
para.ErrorMsg = AlertMsg.数据库错误.ToString();
}
/// <summary>
/// 添加一个新的"K-V对",返回新KV对的id(8长度的字符串).如果添加失败返回错误信息
/// </summary>
/// <returns></returns>
public static void Add(KeyvalM para)
{
// id是PK,不能重复,尝试10次
string insert = "insert into KeyVal(id,title,category,comment,ctime,orderby,status)";
SQLServer db = new SQLServer();
for (int i = 0; i < 10; i++)
{
para.Id = NewCode();
para.Ctime = DateTimeOffset.Now;
para.Status = 1;
int re = db.Insert <KeyvalM>(insert, para);
if (re == 1)
{
// 更新缓存
CacheRefresh();
para.ErrorCode = 200;
return;
}
}
para.ErrorMsg = AlertMsg.数据库错误.ToString();
}
/// <summary>
/// 查询记录,并且按kvcategory分组,返回以组名为键的字典.
/// 查询出错,组名无效,返回null
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public static Dictionary <string, List <KeyvalM> > AllByCategory(KeyvalM para)
{
if (string.IsNullOrWhiteSpace(para.Categorys))
{
para.ErrorMsg = "无效的分组参数";
return(null);
}
Dictionary <string, List <KeyvalM> > datadict = new Dictionary <string, List <KeyvalM> >();
string[] categorys = para.Categorys.Split(',');
for (int i = 0; i < categorys.Length; i++)
{
if (int.TryParse(categorys[i], out para.Category) == false)
{
para.ErrorMsg = $"无效的分组: {categorys[i]}";
return(null);
}
var data = KeyValBll.All(para);
datadict.Add(categorys[i], data);
}
return(datadict);
}
public async Task Item()
{
var para = this.ParaForm <KeyvalM>();
KeyvalM data = KeyValBll.GetById(para.Id);
if (data == null)
{
await this.Json(new { msg = AlertMsg.没有数据.ToString() });
return;
}
// 返回字段按需设定
var redata = new
{
data.Id,
data.Title,
data.Category,
data.Comment,
data.Ctime
};
await this.Json(new { item = redata, errcode = 200 });
}
public async Task List()
{
// 支持传多个分组
KeyvalM para = this.ParaForm <KeyvalM>();
// get KVdata
Dictionary <string, List <KeyvalM> > datadict = KeyValBll.AllByCategory(para);
if (datadict == null)
{
await this.Json(new { errmsg = para.ErrorMsg });
return;
}
// 返回字段按需设定
Dictionary <string, IEnumerable <object> > redict = new Dictionary <string, IEnumerable <object> >();
string[] fields = null;
if (!string.IsNullOrWhiteSpace(para.Fields))
{
fields = para.Fields.Split(',');
}
foreach (var key in datadict.Keys)
{
// 按需字段列表
if (fields != null && fields.Length > 0)
{
redict[key] = SerializeHelp.ObjectsToDicts(datadict[key], fields);
}
else
{
redict[key] = AllFieldList(datadict[key]);
}
}
//
await this.Json(new { list = redict, errcode = 200 });
}
