public int DbLink_Add(DbLinkInfo info)
{
int rst = 0;
try
{
OleDbParameter[] param = new OleDbParameter[7];
param[0] = new OleDbParameter("@DbName", OleDbType.VarWChar, 50);
param[0].Value = info.DbName;
param[1] = new OleDbParameter("@UserName", OleDbType.VarWChar, 50);
param[1].Value = info.UserName;
param[2] = new OleDbParameter("@PassWord", OleDbType.VarWChar, 50);
param[2].Value = info.PassWord;
param[3] = new OleDbParameter("@DbType", OleDbType.Integer);
param[3].Value = info.DbType;
param[4] = new OleDbParameter("@DataSource", OleDbType.VarWChar, 255);
param[4].Value = info.DataSource;
param[5] = new OleDbParameter("@Port", OleDbType.VarWChar, 10);
param[5].Value = info.Port;
param[6] = new OleDbParameter("@DbAbbreviation", OleDbType.VarWChar, 50);
param[6].Value = info.DbAbbreviation;
string sql = "insert into Cm_DbLink (DbName,UserName,[PassWord],DbType,DataSource,CreateTime,Port,DbAbbreviation) values (@DbName,@UserName,@PassWord,@DbType,@DataSource,now(),@Port,@DbAbbreviation)";
rst = DbHelper.ExecuteNonQuery(constring, CommandType.Text, sql, param);
}
catch (Exception ex)
{
rst = -1;
}
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;
//oracle数据库表名大写
if (dlinfo.DbType == 2)
{
tableName = tableName.ToUpper();
}
tableNameList.Add(tableName);
}
DataBaseInfo dbinfo = dbDal.DataBaseGetInfo(dlinfo, tableNameList, out rstmsg);
return(dbinfo);
}
public DbLinkInfo DbLinkGetInfo(int ID)
{
DbLinkInfo info = new DbLinkInfo();
DataTable dt = new DataTable();
OleDbParameter[] param = new OleDbParameter[5];
param[0] = new OleDbParameter("@ID", OleDbType.VarWChar, 20);
param[0].Value = ID;
string sql = "select * from Cm_DbLink where ID=@ID";
dt = DbHelper.ExecuteTable(constring, CommandType.Text, sql, param);
if (dt.Rows.Count > 0)
{
info.ID = Convert.ToInt32(dt.Rows[0]["ID"].ToString());
info.DbName = dt.Rows[0]["DbName"].ToString();
info.UserName = dt.Rows[0]["UserName"].ToString();
info.PassWord = dt.Rows[0]["PassWord"].ToString();
info.DataSource = dt.Rows[0]["DataSource"].ToString();
info.DbType = int.Parse(dt.Rows[0]["DbType"].ToString());
info.CreateTime = Convert.ToDateTime(dt.Rows[0]["CreateTime"].ToString());
info.Port = dt.Rows[0]["Port"].ToString();
info.DbAbbreviation = dt.Rows[0]["DbAbbreviation"].ToString();
}
return(info);
}
private void btn_Save_Click(object sender, EventArgs e)
{
DbLinkInfo info = GetDbLinkInfo();
string DbTime = "";
string rstmsg = "";
if (GetDbLink(out DbTime, out rstmsg) == -1)
{
MessageBox.Show("保存失败。" + rstmsg);
return;
}
int rst = 0;
if (info.ID == 0)
{
rst = dal.DbLink_Add(info);
}
else if (info.ID > 0)
{
rst = dal.DbLink_Edit(info);
}
if (rst > 0)
{
Initdgv_DbLink();
MessageBox.Show("保存成功。");
}
else
{
MessageBox.Show("保存失败。");
}
}
private void comboBoxDB_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
string rstmsg = "";
IDbLink dal = new DbLink();
int id = 0;
var lisitem = comboBoxDB.SelectedItem as ListItem;
id = string.IsNullOrEmpty(lisitem.ID) ? 0 : int.Parse(lisitem.ID);
//id = comboBoxDB.SelectedValue.ToString() == "" ? 0 : int.Parse(comboBoxDB.SelectedValue.ToString());
DbLinkInfo dlinfo = dal.DbLinkGetInfo(id);
IDataBase dbDal = new CurrencyDal.CodeMaker.DataBase();
DataBaseInfo dbinfo = dbDal.GetTableInfo(dlinfo, out rstmsg);
comboBoxTable.DataSource = null;
List <ListItem> list = new List <ListItem>();
TableInfo model = new TableInfo();
list.Add(new ListItem("请选择表", "请选择表"));
foreach (TableInfo item in dbinfo.Tables)
{
TableInfo modeltable = new TableInfo();
list.Add(new ListItem(item.xType, item.Name));
}
comboBoxTable.DataSource = list;
}
catch (Exception ex)
{
string msgerror = ex.Message;
}
}
public IList <DbLinkInfo> DbLinkGetList()
{
IList <DbLinkInfo> ilist = new List <DbLinkInfo>();
DataTable dt = new DataTable();
string sql = "select *,iif(DbType=1, 'SQLServer', iif(DbType=2, 'Oracle', iif(DbType=3, 'MySql', iif(DbType=4, 'Access', iif(DbType=5, 'SqLite', iif(DbType=6, 'PDM', iif(DbType=7, 'MongoDB', iif(DbType=8, 'Redis', '')))))))) as DbTypeName from Cm_DbLink";
dt = DbHelper.ExecuteTable(constring, CommandType.Text, sql, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
DbLinkInfo info = new DbLinkInfo();
info.ID = Convert.ToInt32(dt.Rows[i]["ID"].ToString());
info.DbName = dt.Rows[i]["DbName"].ToString();
info.UserName = dt.Rows[i]["UserName"].ToString();
info.PassWord = dt.Rows[i]["PassWord"].ToString();
info.DataSource = dt.Rows[i]["DataSource"].ToString();
info.DbType = int.Parse(dt.Rows[i]["DbType"].ToString());
info.CreateTime = Convert.ToDateTime(dt.Rows[i]["CreateTime"].ToString());
info.Port = dt.Rows[i]["Port"].ToString();
info.DbAbbreviation = dt.Rows[i]["DbAbbreviation"].ToString();
info.DbTypeName = dt.Rows[i]["DbTypeName"].ToString();
ilist.Add(info);
}
return(ilist);
}
public int DbLink_Edit(DbLinkInfo info)
{
int rst = 0;
try
{
OleDbParameter[] param = new OleDbParameter[8];
param[0] = new OleDbParameter("@DbName", OleDbType.VarWChar, 50);
param[0].Value = info.DbName;
param[1] = new OleDbParameter("@UserName", OleDbType.VarWChar, 50);
param[1].Value = info.UserName;
param[2] = new OleDbParameter("@PassWord", OleDbType.VarWChar, 50);
param[2].Value = info.PassWord;
param[3] = new OleDbParameter("@DbType", OleDbType.Integer);
param[3].Value = info.DbType;
param[4] = new OleDbParameter("@DataSource", OleDbType.VarWChar, 255);
param[4].Value = info.DataSource;
param[5] = new OleDbParameter("@Port", OleDbType.VarWChar, 10);
param[5].Value = info.Port;
param[6] = new OleDbParameter("@DbAbbreviation", OleDbType.VarWChar, 50);
param[6].Value = info.DbAbbreviation;
param[7] = new OleDbParameter("@ID", OleDbType.VarWChar, 50);
param[7].Value = info.ID;
string sql = "update Cm_DbLink set DbName=@DbName,UserName=@UserName,[PassWord]=@PassWord,DbType=@DbType,DataSource=@DataSource,CreateTime=now(),Port=@Port,DbAbbreviation=@DbAbbreviation where ID=@ID";
rst = DbHelper.ExecuteNonQuery(constring, CommandType.Text, sql, param);
}
catch
{
rst = -1;
}
return(rst);
}
private DataBaseInfo GetDbInfo()
{
IDbLink dal = new DbLink();
DbLinkInfo dlinfo = dal.DbLinkGetInfo(dinfo.DbLinkID);
IDataBase dbDal = new CurrencyDal.CodeMaker.DataBase();
string rstmsg = "";
List <string> tableNameList = new List <string>();
tableNameList.Add(dinfo.Name);
DataBaseInfo dbinfo = dbDal.DataBaseGetInfo(dlinfo, tableNameList, out rstmsg);
return(dbinfo);
}
private DataBaseInfo GetDbInfo(int ID)
{
IDbLink dal = new DbLink();
DbLinkInfo dlinfo = dal.DbLinkGetInfo(ID);
IDataBase dbDal = new CurrencyDal.CodeMaker.DataBase();
string rstmsg = "";
List <string> tableNameList = new List <string>();
string tableName = "";
DbDataTypeEnum dtype = DbDataType.GetDbDataType(dinfo.NameType);
if (dtype == DbDataTypeEnum.表)
{
tableName = dinfo.Name;
tableNameList.Add(tableName);
}
DataBaseInfo dbinfo = dbDal.DataBaseGetInfo(dlinfo, tableNameList, out rstmsg);
//DataBaseInfo2 info = new DataBaseInfo2();
//try
//{
//switch (dlinfo.DbType)
//{
// case 1:
// System.Data.Common.DbConnection connSql = new System.Data.SqlClient.SqlConnection();
// connSql.ConnectionString = "Data Source=" + dlinfo.DataSource + ";Initial Catalog=" + dlinfo.DbName + ";User ID=" + dlinfo.UserName + ";Password="******"Data Source=" + dlinfo.DbName + ";User ID=" + dlinfo.UserName + ";Password="******"";
// info.GetOracleDb(connOracle);
// break;
// case 3:
// string ConnString = dlinfo.DataSource;
// info.LoadFromAccess2000(ConnString);
// break;
//}
//}
//catch (Exception ex)
//{
// MessageBox.Show("操作失败。" + ex.Message);
//}
return(dbinfo);
}
private void ExportInsert()
{
string sqlmsg = "";
string rstmsg = "";
string sql = textEditorControl.ActiveTextAreaControl.TextArea.SelectionManager.SelectedText.Trim();
if (sql == "")
{
sql = textEditorControl.Text.Trim();
}
if (sql == "")
{
return;
}
sql = GetSqlZC(sql);
sql = sql.Replace("\r\n", " ");
ListItem item = DBToolStripComboBox.SelectedItem as ListItem;
int DbLinkID = int.Parse(item.ID.ToString());
if (DbLinkID == 0)
{
MessageBox.Show("请选择数据库");
}
IDbLink ldal = new DbLink();
DbLinkInfo dlinfo = ldal.DbLinkGetInfo(DbLinkID);
DataSet ds = dal.Run(dlinfo, sql, out sqlmsg, out rstmsg);
if (ds != null && ds.Tables != null && ds.Tables[0].Rows.Count > 0)
{
DataBaseTypeEnum dbtype = DataBaseTypeEnum.SQLServer;
string TargetDbType = toolStripComboBox_TargetDbType.Text;
if (TargetDbType == "SQLServer")
{
dbtype = DataBaseTypeEnum.SQLServer;
}
else if (TargetDbType == "Oracle")
{
dbtype = DataBaseTypeEnum.Oracle;
}
else if (TargetDbType == "MySql")
{
dbtype = DataBaseTypeEnum.MySql;
}
string strSql = GetInserSql(ds.Tables[0], dbtype, out rstmsg);
Clipboard.SetDataObject(strSql);
}
}
/// <summary>
/// 执行事务
/// </summary>
/// <param name="sqllist">sql语句列表</param>
/// <returns></returns>
public int ExeSqlTran(DbLinkInfo info, List <string> sqllist, out string rstmsg)
{
int rst = 0;
rstmsg = "";
try
{
System.Data.Common.DbConnection conn = DBConfig.GetDbConnection(info);
rst = DbHelper.ExecuteNonQueryCurrencyTransactions(conn, CommandType.Text, sqllist, null);
rstmsg = "共" + sqllist.Count + "行数据，成功修改" + rst + "行数据,有" + (sqllist.Count - rst).ToString() + "行未修改。";
}
catch (Exception ex)
{
rstmsg = ex.Message;
}
return(rst);
}
/// <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 DbLinkInfo GetDbLinkInfo()
{
DbLinkInfo info = new DbLinkInfo();
info.DbName = tb_DbName.Text;
info.UserName = tb_UserName.Text;
info.PassWord = tb_Password.Text;
info.DataSource = tb_DataSource.Text;
info.Port = tb_Port.Text;
info.DbAbbreviation = tb_DbAbbreviation.Text;
info.ID = tb_ID.Text == "" || tb_ID.Text == "0" ? 0 : int.Parse(tb_ID.Text);
info.DbType = int.Parse(cob_DbType.SelectedValue.ToString());
info.Charset = txt_Charset.Text;
//switch (cob_DbType.Text)
//{
// case "SQLServer":
// info.DbType = (int)DataBaseTypeEnum.SQLServer;
// break;
// case "Oracle":
// info.DbType = (int)DataBaseTypeEnum.Oracle;
// break;
// case "Access":
// info.DbType = (int)DataBaseTypeEnum.Access;
// break;
// case "SQLite":
// info.DbType = (int)DataBaseTypeEnum.SqLite;
// break;
// case "MySql":
// info.DbType = (int)DataBaseTypeEnum.MySql;
// break;
// case "MongoDB":
// info.DbType = (int)DataBaseTypeEnum.MongoDB;
// break;
// case "Redis":
// info.DbType = (int)DataBaseTypeEnum.Redis;
// break;
//}
return(info);
}
/// <summary>
/// 导出文件
/// </summary>
private void ExportData()
{
SaveFileDialog SaveFile = new SaveFileDialog();
SaveFile.FileName = "temp.xls";
//SaveFile.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Miscrosoft Office Excel 97-2003 工作表|*.xls|所有文件(*.*)|*.*";
SaveFile.Filter = "Miscrosoft Office Excel 97-2003 工作表|*.xls|excel07文件(*.xlsx)|*.xlsx|txt 文件 (*.txt)|*.txt|所有文件 (*.*)|*.*";
SaveFile.RestoreDirectory = true;
string filePath = "";
string extend = "";
if (SaveFile.ShowDialog() != DialogResult.OK)
{
return;
}
else
{
filePath = SaveFile.FileName;
extend = System.IO.Path.GetExtension(SaveFile.FileName);
extend = extend.Replace(".", "");
}
string sqlmsg = "";
string rstmsg = "";
string sql = textEditorControl.ActiveTextAreaControl.TextArea.SelectionManager.SelectedText.Trim();
if (sql == "")
{
sql = textEditorControl.Text.Trim();
}
if (sql == "")
{
return;
}
sql = GetSqlZC(sql);
sql = sql.Replace("\r\n", " ");
ListItem item = DBToolStripComboBox.SelectedItem as ListItem;
int DbLinkID = int.Parse(item.ID.ToString());
if (DbLinkID == 0)
{
MessageBox.Show("请选择数据库");
}
IDbLink ldal = new DbLink();
DbLinkInfo dlinfo = ldal.DbLinkGetInfo(DbLinkID);
DataSet ds = dal.Run(dlinfo, sql, out sqlmsg, out rstmsg);
rtb_Message.Text = sqlmsg;
if (ds == null)
{
MessageBox.Show(rstmsg);
return;
}
if (extend == "xls")
{
Common.Excel.DataSetToExcel(ds, filePath, out rstmsg);
}
else if (extend == "xlsx")
{
}
else if (extend == "txt")
{
Common.FileHandle.DataSetToTxt(ds, filePath, out rstmsg);
}
MessageBox.Show(rstmsg);
}
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">操作类型：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);
}
public DataSet Run(DbLinkInfo info, string sql, out string sqlmsg, out string rstmsg)
{
sqlmsg = "";
rstmsg = "";
DataSet ds = null;
try
{
DataBaseTypeEnum fc = DataBaseType.GetDataBaseType(info.DbType);
System.Data.Common.DbConnection conn = DBConfig.GetDbConnection(info);
DbHelper.SqlErrorList = null;
switch (fc)
{
case DataBaseTypeEnum.SQLServer:
sql = sql + " print '('+convert(varchar(10), @@rowcount)+'行受影响)'";
ds = DbHelper.ExecuteDataSetCurrency(conn, CommandType.Text, sql, null);
break;
case DataBaseTypeEnum.Oracle:
//sql = @"declare n number; begin "+sql+" n:=sql%rowcount; dbms_output.put_line(n); end; ";
ds = DbHelper.ExecuteDataSetCurrency(conn, CommandType.Text, sql, null);
break;
case DataBaseTypeEnum.Access:
ds = DbHelper.ExecuteDataSetCurrency(conn, CommandType.Text, sql, null);
break;
case DataBaseTypeEnum.SQLite:
ds = DbHelper.ExecuteDataSetCurrency(conn, CommandType.Text, sql, null);
break;
case DataBaseTypeEnum.MySql:
ds = DbHelper.ExecuteDataSetCurrency(conn, CommandType.Text, sql, null);
break;
}
StringBuilder ser = new StringBuilder();
switch (fc)
{
case DataBaseTypeEnum.SQLServer:
if (DbHelper.SqlErrorList != null)
{
for (int i = 0; i < DbHelper.SqlErrorList.Count; i++)
{
System.Data.SqlClient.SqlError r = DbHelper.SqlErrorList[i];
//ser.Append("从 SQL Server 中获取一个数值错误代码，它表示错误、警告或“未找到数据”消息。" + r.State.ToString());
//ser.Append("获取生成错误的提供程序的名称。" + r.Source.ToString());
//ser.Append("获取生成错误的 SQL Server 实例的名称。" + r.Server.ToString());
//ser.Append("获取生成错误的存储过程或远程过程调用 (RPC) 的名称。" + r.Procedure.ToString());
//ser.Append("获取一个标识错误类型的数字。" + r.Number.ToString());
//ser.Append("获取对错误进行描述的文本。" + r.Message.ToString());
//ser.Append("从包含错误的 Transact-SQL 批命令或存储过程中获取行号。" + r.LineNumber.ToString());
//ser.Append("获取从 SQL Server 返回的错误的严重程度。" + r.Class.ToString());
if (i > 0)
{
ser.Append("\r\n");
}
ser.Append(r.Message);
}
}
break;
case DataBaseTypeEnum.Oracle:
if (DbHelper.OracleErrorList != null)
{
for (int i = 0; i < DbHelper.OracleErrorList.Count; i++)
{
Devart.Data.Oracle.OracleError r = DbHelper.OracleErrorList[i];
if (i > 0)
{
ser.Append("\r\n");
}
ser.Append(r.Message);
}
}
break;
case DataBaseTypeEnum.Access:
if (DbHelper.OleDbErrorList != null)
{
for (int i = 0; i < DbHelper.OleDbErrorList.Count; i++)
{
System.Data.OleDb.OleDbError r = DbHelper.OleDbErrorList[i];
if (i > 0)
{
ser.Append("\r\n");
}
ser.Append(r.Message);
}
}
break;
}
sqlmsg = ser.ToString();
}
catch (Exception ex)
{
rstmsg = "操作失败。" + ex.Message;
}
return(ds);
}
private void btn_import_Click(object sender, EventArgs e)
{
//导入Excel表格时将列展开，如果列显示不全可能缺少某列
int rst = 0;
string rstmsg = "";
//获取文件数据
//rst = GetDataSet(out rstmsg);
//if (rst != 1)
//{
// MessageBox.Show(rstmsg);
// return;
//}
btnRefresh_Click(null, null);
//导入文件数据
if (ds == null)
{
MessageBox.Show("导入数据为空。");
return;
}
if (ds == null || ds.Tables == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1)
{
MessageBox.Show("导入数据为空。");
return;
}
CurrencyDal.CodeMaker.DataBase dal = new CurrencyDal.CodeMaker.DataBase();
IDbLink dldal = new DbLink();
DbLinkInfo dlinfo = new DbLinkInfo();
string tableName = "";
if (ckb_IsAddTable.Checked)
{
var lisitem = comboBoxDB.SelectedItem as ListItem;
int DbLinkID = string.IsNullOrEmpty(lisitem.ID) ? 0 : int.Parse(lisitem.ID);
dlinfo = dldal.DbLinkGetInfo(DbLinkID);
tableName = tb_AddTableName.Text.Trim();
dinfo = new DbDataInfo();
dinfo.DbLinkID = DbLinkID;
dinfo.DbName = dlinfo.DbName;
dinfo.Name = tableName;
dinfo.NameType = (int)DbDataTypeEnum.表;
}
else
{
dlinfo = dldal.DbLinkGetInfo(dinfo.DbLinkID);
tableName = dinfo.Name;
}
List <string> fieldlist = new List <string>();
string field = tb_field.Text.Trim();
field = field.Replace(" ", "");
DataBaseInfo dbinfo = new DataBaseInfo();
int index = tabControl1.SelectedIndex;
DataTable dt = ds.Tables[index];
#region 新建表
if (ckb_IsAddTable.Checked && dt != null && dt.Rows.Count > 0)
{
string sqlmsg = "";
CurrencyDal.CodeMaker.RunSql rdal = new CurrencyDal.CodeMaker.RunSql();
field = "";
StringBuilder sql = new StringBuilder();
tableName = tb_AddTableName.Text.Trim();
string sqljc = "select 1 As Num from " + tableName;
DataSet dsjs = rdal.Run(dlinfo, sql.ToString(), out sqlmsg, out rstmsg);
try
{
if (dsjs != null && dsjs.Tables[0] != null)
{
MessageBox.Show("表" + tableName + "已存在。");
return;
}
}
catch { }
sql.Append("Create Table ");
sql.Append(tableName);
sql.Append(" ( ");
string createField = "";
string fieldType = "";
if (dlinfo.DbType == 1)
{
fieldType = "nvarchar";
}
else if (dlinfo.DbType == 2)
{
fieldType = "nvarchar2";
}
else if (dlinfo.DbType == 3)
{
fieldType = "nvarchar";
}
else
{
fieldType = "nvarchar";
}
for (int i = 0; i < dt.Columns.Count; i++)
{
string cname = dt.Columns[i].ColumnName;
//sql.Append("[" + cname + "] [nvarchar](255) NULL,");
if (createField == "")
{
createField = cname + " " + fieldType + "(255) NULL";
}
else
{
createField += "," + cname + " " + fieldType + "(255) NULL";
}
if (field == "")
{
field = cname;
}
else
{
field += "," + cname;
}
}
sql.Append(createField);
sql.Append(")");
DataSet dset = rdal.Run(dlinfo, sql.ToString(), out sqlmsg, out rstmsg);
}
#endregion
dbinfo = GetDbInfo(dinfo.DbLinkID);
TableInfo tinfo = dbinfo.Tables[tableName];
rst = dal.ImportData(dlinfo, tinfo, field, dt, out rstmsg);
MessageBox.Show(rstmsg);
}
