private void RunToolScriptBtn_Click(object sender, EventArgs e)
{
string sqlmsg = "";
string rstmsg = "";
string sql = textEditorControl.ActiveTextAreaControl.TextArea.SelectionManager.SelectedText.Trim();
sql = GetSqlZC(sql);
if (sql == "")
{
sql = textEditorControl.Text.Trim();
}
if (sql == "")
{
return;
}
sql = sql.Replace("\r\n", " ");
ListItem item = DBToolStripComboBox.SelectedItem as ListItem;
int DbLinkID = int.Parse(item.ID.ToString());
if (DbLinkID == 0)
{
MessageBox.Show("请选择数据库");
}
List <DataTable> listDt = new List <DataTable>();
IDbLink ldal = new DbLink();
DbLinkInfo dlinfo = ldal.DbLinkGetInfo(DbLinkID);
if (dlinfo.DbType == (int)DataBaseTypeEnum.Oracle)
{
string[] arr = sql.Split(';');
foreach (var sqlitem in arr)
{
string sqlorc = sqlitem.Replace("\r\n", "");
DataSet ds = dal.Run(dlinfo, sqlorc, out sqlmsg, out rstmsg);
if (ds != null)
{
listDt.Add(ds.Tables[0]);
}
}
}
else
{
DataSet ds = dal.Run(dlinfo, sql, out sqlmsg, out rstmsg);
rtb_Message.Text = sqlmsg;
if (ds == null)
{
MessageBox.Show(rstmsg);
return;
}
for (int i = 0; i < ds.Tables.Count; i++)
{
listDt.Add(ds.Tables[i]);
}
}
this.tabControl1.SelectTab("tabPage1");
DGV1.DataSource = null;
DGV2.DataSource = null;
DGV3.DataSource = null;
DGV4.DataSource = null;
DGV5.DataSource = null;
for (int i = 0; i < listDt.Count; i++)
{
switch (i)
{
case 0:
DGV1.DataSource = listDt[i];
break;
case 1:
DGV2.DataSource = listDt[i];
break;
case 2:
DGV3.DataSource = listDt[i];
break;
case 3:
DGV4.DataSource = listDt[i];
break;
case 4:
DGV5.DataSource = listDt[i];
break;
}
}
}
/// <summary>
/// 转换坐标/更新数据
/// </summary>
/// <param name="type">操作类型：1 查询，2 更新表字段，3 复制sql</param>
/// <param name="rstmsg"></param>
/// <returns></returns>
private int Coordinate(int type, out string rstmsg, out DataSet rtds)
{
int rst = 0;
rstmsg = "";
string sqlmsg = "";
rtds = new DataSet();
try
{
string keyField = tb_Key.Text.Trim(); //主键
string mapXField = ""; //经度
string mapYField = ""; //纬度
string mapXYField = ""; //坐标集合
CurrencyDal.CodeMaker.DataBase dal = new CurrencyDal.CodeMaker.DataBase();
IDbLink dldal = new DbLink();
DbLinkInfo dlinfo = dldal.DbLinkGetInfo(dinfo.DbLinkID);
string tableName = dinfo.Name;
string targetTableName = "[]";
List <string> fieldlist = new List <string>();
string field = tb_field.Text.Trim();
field = field.Replace(" ", "");
string[] fieldItem = field.Split(',');
for (int i = 0; i < fieldItem.Length; i++)
{
if (!string.IsNullOrEmpty(fieldItem[i]))
{
string strfield = fieldItem[i];
string strFieldLast = "";
if (!string.IsNullOrEmpty(strfield))
{
strFieldLast = strfield.Substring(strfield.Length - 1, 1);
}
if (strFieldLast.ToLower().IndexOf("x") >= 0 && strfield.ToLower().IndexOf("xy") < 0)
{
mapXField = strfield;
}
else if (strFieldLast.ToLower().IndexOf("y") >= 0 && strfield.ToLower().IndexOf("xy") < 0)
{
mapYField = strfield;
}
else if (strfield.ToLower() == txt_Coordinate.Text.Trim().ToLower() && !string.IsNullOrEmpty(txt_Coordinate.Text.Trim()))
{
mapXYField = txt_Coordinate.Text.Trim();
}
fieldlist.Add(strfield);
}
}
DataBaseInfo dbinfo = GetDbInfo(dinfo.DbLinkID);
string tablename = dinfo.Name;
TableInfo tinfo = dbinfo.Tables[dinfo.Name];
if (!string.IsNullOrEmpty(txt_TargetTable.Text.Trim()) && type == 3)
{
targetTableName = txt_TargetTable.Text.Trim();
}
//查询数据
string sqlQuery = tb_Sql.Text.Trim();
if (string.IsNullOrEmpty(sqlQuery))
{
sqlQuery = "Select ";
if (!string.IsNullOrEmpty(tb_field.Text.Trim()))
{
sqlQuery += tb_field.Text.Trim() + " ";
}
else
{
rst = -1;
rstmsg = "请输入sql语句";
return(rst);
}
sqlQuery += " From " + tablename + " ";
if (!string.IsNullOrEmpty(tb_Where.Text.Trim()))
{
sqlQuery += " Where " + tb_Where.Text.Trim();
}
}
DataSet dsr = dalRunSql.Run(dlinfo, sqlQuery, out sqlmsg, out rstmsg);
rtds = dsr;
if (type == 1)
{
return(1); //查询数据
}
List <string> listSql = new List <string>();
int coordinateType = 0; //1 百度地图转天地图,2天地图转百度地图
char sourceDelimiter = new char(); //源分割符 “_”、“|”都转为“|”
string targetDelimiter = ""; //目标分割符 “|”天地图
if (cb_Coordinate1.Checked)
{
sourceDelimiter = '|';
targetDelimiter = "|";
coordinateType = 1;
}
else if (cb_Coordinate.Checked)
{
sourceDelimiter = '|';
targetDelimiter = "|";
coordinateType = 2;
}
string MapX = "";
string MapY = "";
if (dsr == null && dsr.Tables[0] == null && dsr.Tables[0].Rows.Count == 0)
{
rstmsg = "未查询到数据";
return(1);
}
foreach (DataRow dr in dsr.Tables[0].Rows)
{
string MapListOneLevel = "";
string MapList = "";
if (!string.IsNullOrEmpty(mapXField) && !string.IsNullOrEmpty(mapYField))
{
MapX = dr[mapXField].ToString().Trim();
MapY = dr[mapYField].ToString().Trim();
GetMapXAndMapY(coordinateType, MapX, MapY, out MapX, out MapY);
#region 坐标转换 已不用
//if (!string.IsNullOrEmpty(MapX) && !string.IsNullOrEmpty(MapY))
//{
// double mapx = double.Parse(MapX); double mapy = double.Parse(MapY);
// if (mapx > 0 && mapy > 0)
// {
// if (coordinateType == 1)
// {
// //百度地图转天地图
// double[] gcjarr = MapTransform.BD09ToGCJ02(mapx, mapy);
// double[] wgsarr = MapTransform.GCJ02ToWGS84(gcjarr[0], gcjarr[1]);
// MapX = Math.Round(wgsarr[0], 6).ToString();
// MapY = Math.Round(wgsarr[1], 6).ToString();
// }
// else if (coordinateType == 2)
// {
// //天地图转百度地图
// double[] GCJ02 = MapTransform.WGS84ToGCJ02(mapx, mapy);
// double[] BD09 = MapTransform.GCJ02ToBD09(GCJ02[0], GCJ02[1]);
// MapX = Math.Round(BD09[0], 6).ToString();
// MapY = Math.Round(BD09[1], 6).ToString();
// }
// }
//}
#endregion
}
if (!string.IsNullOrEmpty(mapXYField))
{
#region 坐标集合转换
string mapxy = dr[mapXYField].ToString();
mapxy = mapxy.Replace("_", "|"); //源分割符 “_”、“|”都转为“|”
List <string> mapListOneLevel = new List <string>();
List <string> mapList = new List <string>();
string[] mapOneLevel = new string[0];
if (!string.IsNullOrEmpty(mapxy.Trim()))
{
mapOneLevel = mapxy.Split(sourceDelimiter);
}
foreach (var item in mapOneLevel)
{
string[] maps = new string[0];
if (!string.IsNullOrEmpty(item.Trim()))
{
maps = item.Split(';');
}
foreach (string map in maps)
{
if (string.IsNullOrEmpty(map.Trim()))
{
continue;
}
string s_mapx = "", s_mapy = "";
try
{
s_mapx = map.Split(',')[0];
s_mapy = map.Split(',')[1];
}
catch (Exception ex) { continue; }
GetMapXAndMapY(coordinateType, s_mapx, s_mapy, out s_mapx, out s_mapy);
#region 坐标转换 已不用
//double smapx = 0, smapy = 0;
//double.TryParse(s_mapx, out smapx); double.TryParse(s_mapy, out smapy);
//if (smapx > 0 && smapy > 0)
//{
// if (coordinateType == 1)
// {
// //百度地图转天地图
// double[] gcjarr = MapTransform.BD09ToGCJ02(smapx, smapy);
// double[] wgsarr = MapTransform.GCJ02ToWGS84(gcjarr[0], gcjarr[1]);
// s_mapx = Math.Round(wgsarr[0], 6).ToString();
// s_mapy = Math.Round(wgsarr[1], 6).ToString();
// }
// else if (coordinateType == 2)
// {
// //天地图转百度地图
// double[] GCJ02 = MapTransform.WGS84ToGCJ02(smapx, smapy);
// double[] BD09 = MapTransform.GCJ02ToBD09(GCJ02[0], GCJ02[1]);
// s_mapx = Math.Round(BD09[0], 6).ToString();
// s_mapy = Math.Round(BD09[1], 6).ToString();
// }
//}
#endregion
mapList.Add(s_mapx + "," + s_mapy);
}
//MapList = string.Join(";", mapList);
foreach (var mapItem in mapList)
{
if (!string.IsNullOrEmpty(MapList))
{
MapList = MapList + ";";
}
MapList = MapList + mapItem;
}
if (mapListOneLevel.Count > 0 && !string.IsNullOrEmpty(MapList))
{
mapListOneLevel.Add(targetDelimiter);
}
if (!string.IsNullOrEmpty(MapList))
{
mapListOneLevel.Add(MapList);
}
MapList = "";
mapList.Clear();
}
foreach (var mapItemOneLevel in mapListOneLevel)
{
MapListOneLevel = MapListOneLevel + mapItemOneLevel;
}
#endregion
}
//需要更新的字段
string filedData = "";
if (!string.IsNullOrEmpty(mapXField) && !string.IsNullOrEmpty(mapYField) && !string.IsNullOrEmpty(MapX) && !string.IsNullOrEmpty(MapY))
{
if (filedData != "")
{
filedData = ",";
}
filedData = mapXField + "=" + MapX + " ," + mapYField + "=" + MapY;
}
if (!string.IsNullOrEmpty(mapXYField) && !string.IsNullOrEmpty(MapListOneLevel))
{
if (filedData != "")
{
filedData = filedData + ",";
}
filedData = filedData + mapXYField + "='" + MapListOneLevel + "'";
}
if (!string.IsNullOrEmpty(filedData))
{
StringBuilder strBuilder = new StringBuilder();
if (type == 3)
{
strBuilder.Append(" Update " + targetTableName);
}
else
{
strBuilder.Append(" Update " + tablename);
}
strBuilder.Append(" Set ");
strBuilder.Append(filedData);
strBuilder.Append(" Where " + keyField + "='" + dr[keyField].ToString().Trim() + "'");
listSql.Add(strBuilder.ToString());
}
MapListOneLevel = "";
}
if (type == 2)
{
rst = dalRunSql.ExeSqlTran(dlinfo, listSql, out rstmsg); // 更新地图坐标
}
else if (type == 3)
{
//导出查询语句
string strData = "";
if (listSql != null && listSql.Count > 0)
{
foreach (var item in listSql)
{
if (!string.IsNullOrEmpty(strData))
{
strData = strData + ";" + Environment.NewLine;
}
strData = strData + item;
}
Clipboard.SetDataObject(strData);
rstmsg = string.Format("复制Sql更新语句{0}行", listSql.Count);
}
else
{
rstmsg = "复制Sql更新语句0行";
}
}
}
catch (Exception ex)
{
rst = -1;
rstmsg = ex.Message;
}
return(rst);
}
