125 lines
4.5 KiB
C#
125 lines
4.5 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using Dapper;
|
|
using dccdc.Models;
|
|
|
|
namespace dccdc.DAL
|
|
{
|
|
public class townDal
|
|
{
|
|
public Models.Sz_town getM(string id)
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select townCode,townName,countyCode,x ,y from Sz_town where townCode=@townCode";
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.Sz_town>(sql, new { townCode = id }).FirstOrDefault();
|
|
}
|
|
}
|
|
|
|
public object svae(Sz_town m)
|
|
{
|
|
//throw new NotImplementedException();
|
|
try
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select * from Sz_town where townCode=@townCode";
|
|
m.countyCode = "3714020000";
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
var m1 = conn.Query<Models.Sz_town>(sql, new { townCode = m.townCode }).FirstOrDefault();
|
|
if (m1 == null)
|
|
{
|
|
sql = "insert into Sz_town(townCode,townName,countyCode,x,y) values(@townCode,@townName,@countyCode,@x,@y)";
|
|
}
|
|
else
|
|
{
|
|
sql = "update Sz_town set townName=@townName,countyCode=@countyCode,x=@x,y=@y where townCode=@townCode";
|
|
}
|
|
conn.Execute(sql, m);
|
|
return new { State = 1, Message = "保存成功！" };
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
|
|
public int getCount(string townname)
|
|
{
|
|
string sql = "select count(1) from Sz_town where 1=1 and countyCode='3714020000'";
|
|
if(!string.IsNullOrEmpty(townname))
|
|
{
|
|
sql += " townName like @townName";
|
|
}
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.ExecuteScalar<int>(sql, new { townName = "%" + townname + "%" });
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据街道、乡镇名称获取辖区街道乡镇列表
|
|
/// </summary>
|
|
/// <param name="page"></param>
|
|
/// <param name="pagesize"></param>
|
|
/// <param name="townname"></param>
|
|
/// <returns></returns>
|
|
public List<Sz_town> getList(int page, int pagesize, string townname)
|
|
{
|
|
string sql = "select *,row_number() over(order by townCode desc) as rownum from Sz_town where 1=1 and countyCode='3714020000'";
|
|
if (!string.IsNullOrEmpty(townname))
|
|
{
|
|
sql += " and townName like @townName";
|
|
}
|
|
sql = "select * from ( " + sql + ") t where t.rownum>(" + page + "-1)*" + pagesize + " and rownum<=" + page + "*" + pagesize;
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
return conn.Query<Models.Sz_town>(sql, new { townName = "%" + townname + "%" }).ToList();
|
|
}
|
|
}
|
|
|
|
public object del(string id)
|
|
{
|
|
//throw new NotImplementedException();
|
|
|
|
string sql = "delete from Sz_town where townCode=@townCode";
|
|
try
|
|
{
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
conn.Execute(sql, new { townCode = id });
|
|
return new { State = 1, Message = "删除成功！" }; }
|
|
}
|
|
catch(Exception ex)
|
|
{
|
|
return new { State = 0, Message = ex.Message };
|
|
}
|
|
}
|
|
|
|
public object getgeo()
|
|
{
|
|
//throw new NotImplementedException();
|
|
string sql = "select * from Sz_town where countyCode='3714020000'";
|
|
Dictionary<string, decimal[]> ts = new Dictionary<string, decimal[]>();
|
|
using (var conn = CommHelper.GetSqlConnection())
|
|
{
|
|
var towns = conn.Query<Models.Sz_town>(sql).ToList();
|
|
towns.ForEach(t => {
|
|
if(!string.IsNullOrEmpty(t.x)&&!string.IsNullOrEmpty(t.y))
|
|
{
|
|
ts.Add(t.townName, new decimal[] { decimal.Parse(t.x), decimal.Parse(t.y) });
|
|
}
|
|
});
|
|
}
|
|
return ts;
|
|
}
|
|
}
|
|
}
|