/// <summary> /// 导入文件 /// </summary> /// <param name="rstmsg"></param> /// <returns></returns> private int ImportData(out string rstmsg) { int rst = 0; rstmsg = ""; //获取文件数据 //rst = GetDataSet(out rstmsg); //if (rst != 1) //{ // MessageBox.Show(rstmsg); // return; //} btnRefresh_Click(null, null); //导入文件数据 if (ds == null) { MessageBox.Show("导入数据为空。"); } if (ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1) { MessageBox.Show("导入数据为空。"); } CurrencyDal.CodeMaker.DataBase dal = new CurrencyDal.CodeMaker.DataBase(); IDbLink dldal = new DbLink(); DbLinkInfo dlinfo = dldal.DbLinkGetInfo(dinfo.DbLinkID); string tableName = dinfo.Name; List <string> fieldlist = new List <string>(); string field = tb_field.Text.Trim(); field = field.Replace(" ", ""); int index = tabControl1.SelectedIndex; DataTable dt = ds.Tables[index]; DataBaseInfo dbinfo = GetDbInfo(dinfo.DbLinkID); string tablename = dinfo.Name; TableInfo tinfo = dbinfo.Tables[tablename]; rst = dal.ImportData(dlinfo, tinfo, field, dt, out rstmsg); return(rst); }
private DataBaseInfo GetDbInfo(int DbLinkId) { IDbLink dal = new DbLink(); DbLinkInfo dlinfo = dal.DbLinkGetInfo(DbLinkId); IDataBase dbDal = new CurrencyDal.CodeMaker.DataBase(); string rstmsg = ""; string tableName = ""; List <string> tableNameList = new List <string>(); DbDataTypeEnum dtype = DbDataType.GetDbDataType(dinfo.NameType); if (dtype == DbDataTypeEnum.表) { tableName = dinfo.Name; tableNameList.Add(tableName); } DataBaseInfo dbinfo = dbDal.DataBaseGetInfo(dlinfo, tableNameList, out rstmsg); return(dbinfo); }
/// <summary> /// /// </summary> /// <param name="type">操作类型:2 更新表字段,3 复制sql</param> /// <param name="rstmsg"></param> /// <returns></returns> private int UpdateFilde(int type, out string rstmsg) { int rst = 0; rstmsg = ""; btnRefresh_Click(null, null); //导入文件数据 if (ds == null) { MessageBox.Show("导入数据为空。"); } if (ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1) { MessageBox.Show("导入数据为空。"); } CurrencyDal.CodeMaker.DataBase dal = new CurrencyDal.CodeMaker.DataBase(); IDbLink dldal = new DbLink(); DbLinkInfo dlinfo = dldal.DbLinkGetInfo(dinfo.DbLinkID); string tableName = dinfo.Name; List <string> fieldlist = new List <string>(); string field = tb_field.Text.Trim(); field = field.Replace(" ", ""); int index = tabControl1.SelectedIndex; DataTable dt = ds.Tables[index]; DataBaseInfo dbinfo = GetDbInfo(dinfo.DbLinkID); string tablename = dinfo.Name; TableInfo tinfo = dbinfo.Tables[dinfo.Name]; if (!string.IsNullOrEmpty(txt_TargetTable.Text.Trim()) && type == 3) { tablename = txt_TargetTable.Text.Trim(); } string keyField = tb_Key.Text.Trim(); //主键 var listSql = dal.GetUpdateSql(dlinfo, tinfo, field, keyField, tb_Where.Text.Trim(), dt, out rstmsg); if (type == 2) { try { rst = dalRunSql.ExeSqlTran(dlinfo, listSql, out rstmsg); } catch (Exception ex) { rst = -1; rstmsg = "更新失败。" + ex.Message; return(rst); } if (rst >= 1) { rstmsg = "总共" + dt.Rows.Count + "行数据,更新成功" + rst + "行数据。"; } } 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行"; } } return(rst); }
/// <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); }