private void FrmMainMDI_FormClosing(object sender, FormClosingEventArgs e)
{
if (MsgHelper.ShowOkCancel("确定要关闭所有窗体退出吗？") == DialogResult.Cancel)
{
e.Cancel = true;
IsReLoad = false;
}
else
{
e.Cancel = false;
IsReLoad = true;
if (FormClosed != null)
{
FormClosed(this, e);
}
}
}
private void tsbAutoSQL_Click(object sender, EventArgs e)
{
#region 数据库表数据导入处理
string strWhere = string.IsNullOrEmpty(rtbWhere.Text.Trim()) == true ? "" : " WHERE " + rtbWhere.Text.Trim();
//取得数据源
DataTable dtMain = (DataTable)GlobalValue.Instance.dicBindingSource[_strTableName].DataSource;
DataTable dtSec = (DataTable)GlobalValue.Instance.dicBindingSource[_strColName].DataSource;
//移除空行
dtMain.DeleteNullRow();
//得到变更后数据
dtMain.AcceptChanges();
dtSec.AcceptChanges();
//目标数据库类型
int iDbType = int.Parse(cbbTargetDbType.SelectedValue.ToString());
DataBaseType selectDBType = (DataBaseType)iDbType;
#region 提交字符处理
string strDataStyle = cbbCommitType.SelectedValue.ToString(); //提交方式
string strCommit = "\n"; //提交字符
if (selectDBType == DataBaseType.SqlServer)
{
strCommit = "GO\n";//提交字符
}
else if (selectDBType == DataBaseType.Oracle)
{
strCommit = "commit;\n";
}
#endregion
StringBuilder sbAllSql = new StringBuilder();
int iTable = 1;
foreach (DataRow drTable in dtMain.Rows)//针对表清单循环
{
string strDataTableName = drTable["TABLE_NAME"].ToString().Trim();
string strDataDealType = "新增";
string strColQueryAllSql = ""; //所有列清单
string strColQuerySql = ""; //插入列查询SQL，有固定值的不包括
string strColInsertSql = ""; //新增数据列清单
sbAllSql.Append("/**" + iTable.ToString() + " " + strDataTableName + strDataDealType + "新增数据*/\n");
if (_dbServer.DatabaseType == DataBaseType.SqlServer && selectDBType == DataBaseType.SqlServer && ckbMainKeyInsert.Checked)
{
sbAllSql.Append("Set IDENTITY_INSERT " + strDataTableName + " ON\n");
}
#region 确定查询列SQL、新增SQL，将SQL参数化（格式：#+列名+#）
foreach (DataRow drCol in dtSec.Rows)//针对列清单循环
{
string strColCode = drCol["COLUMN_NAME"].ToString().Trim().ToUpper();
string strColFixedValue = drCol["固定值"].ToString().Trim(); //固定值
string strColHelpQueryValue = drCol["辅助查询值"].ToString().Trim(); //辅助查询值
//查询所有列
strColQueryAllSql += strColCode + ",";
if (string.IsNullOrEmpty(strColFixedValue) && drCol["DATA_TYPE"].ToString() == "TIMESTAMP(6)")
{
strColQuerySql += "to_char(" + strColCode + ") as " + strColCode + ",";//将oracle的时间类型转换为文本
}
//查询数据列清单
else if (string.IsNullOrEmpty(strColFixedValue))
{
strColQuerySql += strColCode + ",";
}
else if (!string.IsNullOrEmpty(strColFixedValue) && !string.IsNullOrEmpty(strColHelpQueryValue))
{
strColQuerySql += strColCode + ",";
}
//新增SQL列清单
if (!string.IsNullOrEmpty(strColFixedValue))//固定值不为空时
{
strColInsertSql += strColFixedValue + ",";
}
else
{
strColInsertSql += "'#" + strColCode + "#',";//将SQL参数化
}
}
#endregion
strColQuerySql = strColQuerySql.Substring(0, strColQuerySql.Length - 1);
strColQueryAllSql = strColQueryAllSql.Substring(0, strColQueryAllSql.Length - 1);
//操作记录数
DataTable dtCount = _dataAccess.DataAccess.QueryHadParamSqlData("SELECT COUNT(*) FROM " + strDataTableName + strWhere, _dicQueryCondition);
if (dtCount.Rows[0][0].ToString() == "0")
{
MsgHelper.ShowErr("没有要生成的记录！", "提示");
return;
}
if (int.Parse(dtCount.Rows[0][0].ToString()) > 1000)//超过1000条才提示是否继续
{
if (MsgHelper.ShowOkCancel("本次操作记录数：" + dtCount.Rows[0][0].ToString() + "，是否继续？") == DialogResult.Cancel)
{
return;
}
}
//获取数据信息
string strColDataList = "SELECT " + strColQuerySql + " FROM " + strDataTableName + strWhere;
DataTable dtData = _dataAccess.DataAccess.QueryHadParamSqlData(strColDataList, _dicQueryCondition);
//生成SQL的前缀和后缀
string strInsertPre = "insert into " + strDataTableName + "(" + strColQueryAllSql + ") \n values (";//Oracler的Value方式支持子查询和时间类型，Select方式不支持
string strInsertOracleEnd = " );";
if (selectDBType == DataBaseType.SqlServer)
{
strInsertPre = "insert into " + strDataTableName + "(" + strColQueryAllSql + ") \nselect "; //Sql Server的Select支持子查询，Value方式不支持
strInsertOracleEnd = ";"; //因为使用select方式，所以最后只加分号即可。
}
#region 新增数据
int iDataNum = 1;//数据计数器
int iCommitCount = Convert.ToInt32(strDataStyle);
int iDataRowCount = dtData.Rows.Count;
foreach (DataRow drColData in dtData.Rows)//导入数据表内循环
{
string strColInsertSql_Replace = strColInsertSql;
foreach (DataRow drCol in dtSec.Rows)//针对列清单循环来替换参数
{
string strColCode = drCol["COLUMN_NAME"].ToString().Trim().ToUpper();
string strColHelpQueryValue = drCol["辅助查询值"].ToString().Trim(); //辅助查询值
if (!string.IsNullOrEmpty(strColHelpQueryValue)) //辅助查询值
{
strColHelpQueryValue = strColHelpQueryValue.Replace("#" + strColCode + "#", drColData[strColCode].ToString().Trim().Replace("'", ""));
DataTable dtHelpData = _dataAccess.DataAccess.QueryHadParamSqlData(strColHelpQueryValue, _dicQueryCondition);
if (dtHelpData.Rows.Count > 0)
{
strColInsertSql_Replace = strColInsertSql_Replace.Replace("#辅助查询值#", dtHelpData.Rows[0][0].ToString());
}
}
else if (dtData.Columns.Contains(strColCode)) //非辅助查询值
{
if (drColData[strColCode] == DBNull.Value) //null
{
strColInsertSql_Replace = strColInsertSql_Replace.Replace("'#" + strColCode + "#'", "null");
}
else
{
if (dtData.Columns[strColCode].DataType == typeof(DateTime) && selectDBType == DataBaseType.Oracle)
{
//oracle要将时间字符转为日期
strColInsertSql_Replace = strColInsertSql_Replace.Replace("'#" + strColCode + "#'", "TO_DATE('" + drColData[strColCode].ToString() + "','yyyy-MM-dd HH24:mi:ss')");
}
else if (drColData[strColCode].ToString().Trim().Contains("&") && selectDBType == DataBaseType.Oracle)
{
//oralce的&为输入参数转义字符，需要替换为chr(38)
strColInsertSql_Replace = strColInsertSql_Replace.Replace("#" + strColCode + "#", drColData[strColCode].ToString().Trim().Replace("&", "' || chr(38) || '"));
}
else
{
//将参数化的字段以实际数据代替
strColInsertSql_Replace = strColInsertSql_Replace.Replace("#" + strColCode + "#", drColData[strColCode].ToString().Trim().Replace("'", ""));
}
}
}
}
string strSQLOneData = strInsertPre + strColInsertSql_Replace.Substring(0, strColInsertSql_Replace.Length - 1);
//生成单个数据SQL
strSQLOneData = DataBaseCommon.GenOneDataSql(selectDBType, strDataStyle, strCommit, strInsertOracleEnd, strSQLOneData, iDataNum, iCommitCount, iDataRowCount);
sbAllSql.Append(strSQLOneData);
iDataNum++;
}
#endregion
iTable++;
if (_dbServer.DatabaseType == DataBaseType.SqlServer && selectDBType == DataBaseType.SqlServer && ckbMainKeyInsert.Checked)
{
sbAllSql.Append("SET IDENTITY_INSERT " + strDataTableName + " OFF\n");
}
}
//保存属性
//PropSetting.Default.Save();
//返回值和显示处理
if (strDataStyle == "0")//全部
{
sbAllSql.Append("\n" + strCommit);
}
else
{
sbAllSql.Append("\n");
}
rtbResult.Clear();
rtbResult.AppendText(sbAllSql.ToString() + "\n");
Clipboard.SetData(DataFormats.UnicodeText, sbAllSql.ToString());
tabControl1.SelectedTab = tpAutoSQL;
//生成SQL成功后提示
//MessageBox.Show(strInfo, "生成成功", MessageBoxButtons.OK);
lblInfo.Text = _strAutoSqlSuccess;
rtbResult.Select(0, 0); //返回到第一行
#endregion
}
private void tsmiDelete_Click(object sender, EventArgs e)
{
//设置上级菜单
TreeNode trSelect = tvLeftMenu.SelectedNode;
if (trSelect == null)
{
return;
}
if (trSelect.GetNodeCount(true) > 0)
{
MsgHelper.ShowErr("请先删除子节点！");
return;
}
if (MsgHelper.ShowOkCancel("确定要删除该菜单？") == DialogResult.Cancel)
{
return;
}
DMenu selectMenu = trSelect.Tag as DMenu;
XmlDocument xmlMenu = DMenu.ReadMenuXmlFile(AppType.WinForm);
#region 除
XmlNode xnNew;
switch (_saveMenu.MenuType)
{
case MenuTypeEnum.Modul:
xnNew = xmlMenu.SelectSingleNode("xml/Model[@Guid='" + selectMenu.Guid + "']");
xnNew.ParentNode.RemoveChild(xnNew);
break;
case MenuTypeEnum.Class:
//第一个分类
xnNew = xmlMenu.SelectSingleNode("xml/Model/Class[@Guid='" + selectMenu.Guid + "']");
if (xnNew == null)
{
//第二个菜单分类
xnNew = xmlMenu.SelectSingleNode("xml/Model/Class/Class[@Guid='" + selectMenu.Guid + "']");
}
if (xnNew == null)
{
//最多支持第三个菜单分类
xnNew = xmlMenu.SelectSingleNode("xml/Model/Class/Class/Class[@Guid='" + selectMenu.Guid + "']");
}
xnNew.ParentNode.RemoveChild(xnNew);
break;
case MenuTypeEnum.Menu:
default:
//第一个分类
xnNew = xmlMenu.SelectSingleNode("xml/Model/Class/Menu[@Guid='" + selectMenu.Guid + "']");
if (xnNew == null)
{
//第二个菜单分类
xnNew = xmlMenu.SelectSingleNode("xml/Model/Class/Class/Menu[@Guid='" + selectMenu.Guid + "']");
}
if (xnNew == null)
{
//最多支持第三个菜单分类
xnNew = xmlMenu.SelectSingleNode("xml/Model/Class/Class/Class/Menu[@Guid='" + selectMenu.Guid + "']");
}
xnNew.ParentNode.RemoveChild(xnNew);
break;
}
#endregion
//保存
xmlMenu.Save(DMenu.MenuXmlFilePath);
MsgHelper.ShowInfo("删除成功！");
UIHelper.ResetControl(grpEdit);
//重新加载菜单
LoadMenu();
}
