/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(tb_city model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder setSql = new StringBuilder();
strSql.Append("update tb_city set ");
if (!String.IsNullOrEmpty(model.cityname))
{
setSql.Append("cityname=@cityname,");
}
strSql.Append(setSql.ToString().TrimEnd(','));
strSql.Append(" where id=@id ");
using (IDbConnection conn = DapperHelper.OpenConnection())
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 市 列表
/// </summary>
public ActionResult cityList(tb_city model)
{
int count = 0;
ViewBag.cityList = dcity.GetList(model, ref count);
ViewBag.page = Utils.ShowPage(count, model.PageSize, model.PageIndex, 5);
return(View());
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public tb_city GetInfo(tb_city model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from tb_city");
strSql.Append(" where id=@id ");
using (IDbConnection conn = DapperHelper.OpenConnection())
{
model = conn.Query <tb_city>(strSql.ToString(), model)?.FirstOrDefault();
}
return(model);
}
public void InsertProv()
{
var txt = LoadFile.ReadFile(@"F:\1.json");
if (!string.IsNullOrEmpty(txt))
{
var txtnew = txt.Replace("\r", "").Replace("\n", "").Replace("\t", "");
List <info> prov = JsonConvert.DeserializeObject <List <info> >(txtnew);
for (int i = 0; i < prov.Count; i++)
{
info provmode = prov[i];//省
//插入省份
var model = new tb_province();
model.provid = provmode.code;
model.provname = provmode.name;
model.state = 1;
DbContext.Instance.Insert <tb_province>(model);
for (int j = 0; j < provmode.sub.Count; j++)
{
var citym = new tb_city();
citym.cityid = provmode.sub[j].code;
citym.cityname = provmode.sub[j].name;
citym.provid = provmode.code;
citym.provname = provmode.name;
citym.state = 1;
DbContext.Instance.Insert <tb_city>(citym);
//List<model> countrymodel = JsonConvert.DeserializeObject<List<model>>(provmode.sub[j].sub);
if (provmode.sub[j].sub != null)
{
for (int c = 0; c < provmode.sub[j].sub.Count; c++)
{
var country = provmode.sub[j].sub[c]; //县
var cmodel = new tb_county();
cmodel.countyid = country.code;
cmodel.countyname = country.name;
cmodel.cityid = provmode.sub[j].code;
cmodel.cityname = provmode.sub[j].name;
cmodel.state = 1;
DbContext.Instance.Insert <tb_county>(cmodel);
}
}
}
}
}
}
/// <summary>
/// 市 保存
/// </summary>
public JsonResult citySave(tb_city model)
{
if (model == null)
{
return(ResultTool.jsonResult(false, "参数错误！"));
}
if (!String.IsNullOrEmpty(model.id))
{
bool boolResult = dcity.Update(model);
return(ResultTool.jsonResult(boolResult, boolResult ? "成功！" : "更新失败！"));
}
else
{
model.id = Guid.NewGuid().ToString("N");
bool boolResult = dcity.Add(model);
return(ResultTool.jsonResult(boolResult, boolResult ? "成功！" : "添加失败！"));
}
}
/// <summary>
/// 是否存在该记录
/// </summary>
public bool Exists(tb_city model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from tb_city");
strSql.Append(" where id=@id ");
using (IDbConnection conn = DapperHelper.OpenConnection())
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(tb_city model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into tb_city(");
strSql.Append("cityname,id)");
strSql.Append(" values (");
strSql.Append("@cityname,@id)");
using (IDbConnection conn = DapperHelper.OpenConnection())
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public List <tb_city> GetList(tb_city model, ref int total)
{
List <tb_city> list;
StringBuilder strSql = new StringBuilder();
StringBuilder whereSql = new StringBuilder(" where 1 = 1 ");
strSql.Append(" select ROW_NUMBER() OVER(ORDER BY id desc) AS RID, * from tb_city ");
if (!String.IsNullOrEmpty(model.cityname))
{
whereSql.Append(" and cityname=@cityname");
}
strSql.Append(whereSql);
string CountSql = "SELECT COUNT(1) as RowsCount FROM (" + strSql.ToString() + ") AS CountList";
string pageSqlStr = "select * from ( " + strSql.ToString() + " ) as Temp_PageData where Temp_PageData.RID BETWEEN {0} AND {1}";
pageSqlStr = string.Format(pageSqlStr, (model.PageSize * (model.PageIndex - 1) + 1).ToString(), (model.PageSize * model.PageIndex).ToString());
using (IDbConnection conn = DapperHelper.OpenConnection())
{
list = conn.Query <tb_city>(pageSqlStr, model)?.ToList();
total = conn.ExecuteScalar <int>(CountSql, model);
}
return(list);
}
/// <summary>
/// 市 详情
/// </summary>
public ActionResult cityInfo(tb_city model)
{
model = dcity.GetInfo(model);
return(View(model ?? new tb_city()));
}
/// <summary>
/// 市 删除
/// </summary>
public JsonResult cityDelete(tb_city model)
{
bool boolResult = dcity.Delete(model);
return(ResultTool.jsonResult(boolResult, boolResult ? "成功！" : "删除失败！"));
}