/// <summary>
/// 获取数据库中已有信息以List返回
/// </summary>
/// <param name="querystring">查询字符串</param>
/// <param name="colname">列名</param>
/// <returns></returns>
List <String> GetList(String querystring, String colname)
{
//读取数据库中已有POI分类信息存储于List中
DataTable existdt = mysqlHelper.ExecuteDataTable(querystring);
List <String> aList = new List <string>();
foreach (DataRow row in existdt.Rows)
{
aList.Add(row[colname].ToString());
}
return(aList);
}
///// <summary>
///// 导入 城市-ID对
///// </summary>
///// <param name="request"></param>
///// <returns></returns>
//public WebApiResult<string> Any(ImportCityIdRequest request)
//{
// if (request.City == null || request.Id == null)
// return null;
// Angels.Application.Data.MySqlHelper mysqlHelper = new Angels.Application.Data.MySqlHelper(Config.GetValue("DataMySql"));
// //String ConnectionString = String.Format("server={0};user id={1};password={2};database={3};CharSet=utf8", "localhost", "root", "123456", "webmap");
// //mysqlHelper = new Angels.Application.Data.MySqlHelper(ConnectionString);
// try
// {
// List<MySqlParameter> listParam = new List<MySqlParameter>();
// listParam.Add(new MySqlParameter(@"Id", request.Id));
// listParam.Add(new MySqlParameter(@"Name", request.City));
// listParam.Add(new MySqlParameter(@"pId", request.pId));
// if (request.maptype == 0)//百度
// {
// String sqlcommand = "insert into baidu_cityid(baiduId, Name,baidupId) values(@Id, @Name,@pId)"; ;
// var a = mysqlHelper.ExecuteDataTable("SELECT * FROM baidu_cityid WHERE Name = " + "'" + request.City + "'");
// if (a.Rows.Count != 0)
// {
// sqlcommand = "update baidu_cityid SET baiduId=@Id,baidupId=@pId WHERE Name=@Name";
// }
// mysqlHelper.ExecuteNonQuery(sqlcommand, listParam.ToArray());
// return new WebApiResult<string>() { success = 1, msg = "更新成功" };
// }
// else if (request.maptype == 1)//高德
// {
// string sqlcommand = "insert into gaode_cityid(gaodeId, Name,gaodepId) values(@Id, @Name,@pId)";
// var a = mysqlHelper.ExecuteDataTable("SELECT * FROM gaode_cityid WHERE Name = " + "'" + request.City + "'");
// if (a.Rows.Count != 0)
// {
// sqlcommand = "update gaode_cityid SET gaodeId=@Id,gaodepId=@pId WHERE Name=@Name";
// }
// mysqlHelper.ExecuteNonQuery(sqlcommand, listParam.ToArray());
// return new WebApiResult<string>() { success = 1, msg = "更新成功" };
// }
// else
// return new WebApiResult<string>() { success = 0, msg = "输入参数错误"};
// }
// catch (Exception ex)
// {
// return new WebApiResult<string>() { success = 0, msg = "错误： " + ex.ToString() };
// }
//}
///// <summary>
///// 导入 城市-拼音对
///// </summary>
///// <param name="request"></param>
///// <returns></returns>
//public WebApiResult<string> Any(ImportCityNameRequest request)
//{
// if (request.City == null || request.Name == null)
// return null;
// Angels.Application.Data.MySqlHelper mysqlHelper = new Angels.Application.Data.MySqlHelper(Config.GetValue("DataMySql"));
// //String ConnectionString = String.Format("server={0};user id={1};password={2};database={3};CharSet=utf8", "localhost", "root", "123456", "webmap");
// //mysqlHelper = new Angels.Application.Data.MySqlHelper(ConnectionString);
// try
// {
// var a = mysqlHelper.ExecuteDataTable("SELECT * FROM mapbar_cityname WHERE City = " + "'" + request.City + "'");
// List<MySqlParameter> listParam = new List<MySqlParameter>();
// listParam.Add(new MySqlParameter(@"City", request.City));
// listParam.Add(new MySqlParameter(@"Name", request.Name));
// if (a.Rows.Count == 0)
// {
// //插入数据库
// String sqlcommand = "insert into mapbar_cityname(City, Name) values(@City, @Name)";
// mysqlHelper.ExecuteNonQuery(sqlcommand, listParam.ToArray());
// }
// else
// {
// //更新数据库
// String sqlcommand = "update mapbar_cityname SET Name=@Name WHERE City=@City";
// mysqlHelper.ExecuteNonQuery(sqlcommand, listParam.ToArray());
// }
// return new WebApiResult<string>() { success = 1, msg = "更新成功" };
// }
// catch (Exception ex)
// {
// return new WebApiResult<string>() { success = 0, msg = "错误： " + ex.ToString() };
// }
//}
/// <summary>
/// 导入城市ID、拼音信息
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public WebApiResult <string> Any(ImportCityInformRequest request)
{
if (request.Name == null || request.baiduId == null || request.baidupId == null || request.gaodeId == null || request.pinyin == null)
{
return new WebApiResult <string>()
{
success = 0, msg = "参数不全"
}
}
;
Angels.Application.Data.MySqlHelper mysqlHelper = new Angels.Application.Data.MySqlHelper(Config.GetValue("DataMySql"));
//String ConnectionString = String.Format("server={0};user id={1};password={2};database={3};CharSet=utf8", "localhost", "root", "123456", "webmap");
//mysqlHelper = new Angels.Application.Data.MySqlHelper(ConnectionString);
try
{
DataTable dt = mysqlHelper.ExecuteDataTable("SELECT * FROM cityinform WHERE Name = " + "'" + request.Name + "'");
List <MySqlParameter> listParam = new List <MySqlParameter>();
listParam.Add(new MySqlParameter(@"Name", request.Name));
listParam.Add(new MySqlParameter(@"baiduId", request.baiduId));
listParam.Add(new MySqlParameter(@"baidupId", request.baidupId));
listParam.Add(new MySqlParameter(@"gaodeId", request.gaodeId));
listParam.Add(new MySqlParameter(@"pinyin", request.pinyin));
if (dt.Rows.Count == 0)
{
//插入数据库
String sqlcommand = "insert into cityinform(Name, baiduId, baidupId, gaodeId, pinyin) values(@Name, @baiduId, @baidupId, @gaodeId, @pinyin)";
mysqlHelper.ExecuteNonQuery(sqlcommand, listParam.ToArray());
}
else
{
//更新数据库
String sqlcommand = "update cityinform SET baiduId=@baiduId,baidupId=@baidupId,gaodeId=@gaodeId,pinyin=@pinyin WHERE Name=@Name";
mysqlHelper.ExecuteNonQuery(sqlcommand, listParam.ToArray());
}
return(new WebApiResult <string>()
{
success = 1, msg = "更新成功"
});
}
catch (Exception ex)
{
return(new WebApiResult <string>()
{
success = 0, msg = "错误： " + ex.ToString()
});
}
}
/// <summary>
/// 查询城市ID、拼音信息
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public WebApiResult <string> Any(GetCityInformRequest request)
{
Angels.Application.Data.MySqlHelper mysqlHelper = new Angels.Application.Data.MySqlHelper(Config.GetValue("DataMySql"));
//String ConnectionString = String.Format("server={0};user id={1};password={2};database={3};CharSet=utf8", "localhost", "root", "123456", "webmap");
//mysqlHelper = new Angels.Application.Data.MySqlHelper(ConnectionString);
try
{
DataTable dt = mysqlHelper.ExecuteDataTable("SELECT * FROM cityinform");
//DataTable dt1 = mysqlHelper.ExecuteDataTable("SELECT * FROM baidu_cityid LEFT JOIN gaode_cityid ON baidu_cityid.Name=gaode_cityid.Name");
//DataTable dt2 = mysqlHelper.ExecuteDataTable("SELECT * FROM baidu_cityid RIGHT JOIN gaode_cityid ON baidu_cityid.Name=gaode_cityid.Name");
//if(dt1.Rows.Count==0&&dt2.Rows.Count==0) return new WebApiResult<string>() { success = 1, msg = "[]" };
////获取两个数据源的并集
//IEnumerable<DataRow> query2 = dt1.AsEnumerable().Union(dt2.AsEnumerable(), DataRowComparer.Default);
////两个数据源的并集集合
//DataTable dt = query2.CopyToDataTable();
//dt.Columns.Add("pinyin");
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// //var a = mysqlHelper.ExecuteDataTable("SELECT * FROM gaode_cityid WHERE Name = " + "'" + dt.Rows[i]["Name"] + "'");
// //if (a.Rows.Count != 0)
// //{
// // dt.Rows[i]["gaodeId"] = a.Rows[0]["gaodeId"];
// // dt.Rows[i]["gaodepId"] = a.Rows[0]["gaodepId"];
// //}
// //else
// // dt.Rows[i]["gaodeId"] = "";
// var a = mysqlHelper.ExecuteDataTable("SELECT * FROM mapbar_cityname WHERE City = " + "'" + dt.Rows[i]["Name"] + "'");
// if (a.Rows.Count != 0)
// dt.Rows[i]["pinyin"] =a.Rows[0]["Name"];
//}
return(new WebApiResult <string>()
{
success = 1, msg = dt.ToJson()
});
}
catch (Exception ex)
{
return(new WebApiResult <string>()
{
success = 0, msg = "错误： " + ex.ToString()
});
}
}
/// <summary>
/// 查询城市-拼音信息
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public WebApiResult <string> Any(GetCityNameRequest request)
{
Angels.Application.Data.MySqlHelper mysqlHelper = new Angels.Application.Data.MySqlHelper(Config.GetValue("DataMySql"));
//String ConnectionString = String.Format("server={0};user id={1};password={2};database={3};CharSet=utf8", "localhost", "root", "123456", "webmap");
//mysqlHelper = new Angels.Application.Data.MySqlHelper(ConnectionString);
try
{
DataTable dt = mysqlHelper.ExecuteDataTable("SELECT Name,pinyin FROM cityinform");
return(new WebApiResult <string>()
{
success = 1, msg = dt.ToJson()
});
}
catch (Exception ex)
{
return(new WebApiResult <string>()
{
success = 0, msg = "错误： " + ex.ToString()
});
}
}
