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 tsbAutoSQL_Click(object sender, EventArgs e) { int iDbType = int.Parse(cbbDbType.SelectedValue.ToString()); _selectDBType = (DataBaseType)iDbType; using (OleDbConnection con = new OleDbConnection(_DBConnString)) { if (con.State != ConnectionState.Open) { con.Open(); } try { #region 提交字符处理 string strDataStyle = cbbThree.SelectedValue.ToString(); string strCommit = "";//提交字符 //string strCommitLast = ""; strCommit = "\n"; if (_selectDBType == DataBaseType.SqlServer) { strCommit = "GO\n"; } else if (_selectDBType == DataBaseType.Oracle) { strCommit = "commit;\n"; } else { strCommit = "\n"; } #endregion //sbAllSql为本次生成的SQL存储对象 StringBuilder sbAllSql = new StringBuilder(); sbAllSql.Append("/***********************************************************************************\n"); sbAllSql.Append("* 脚本描述: 初始化数据\n"); sbAllSql.Append("* 创建作者: \n"); sbAllSql.Append("* 创建日期: " + DateTime.Now.ToShortDateString() + " \n"); sbAllSql.Append("* 使用模块:\n"); sbAllSql.Append("* 使用版本: \n"); sbAllSql.Append("* 说 明:\n"); int iTable = 1; foreach (DataRow dr in dsExcel.Tables[_strTableName].Rows) { sbAllSql.Append("**\t" + iTable + " " + dr["表名"].ToString() + dr["类型"].ToString() + "数据\n"); iTable++; } sbAllSql.Append("***********************************************************************************/\n"); iTable = 1; foreach (DataRow drTable in dsExcel.Tables[_strTableName].Rows)//针对表清单循环 { string strSheet = drTable["数据Sheet名"].ToString().Trim(); string strDataTableName = drTable["表名"].ToString().Trim(); string strDataDealType = drTable["类型"].ToString().Trim(); string strColQuerySql = ""; //插入列查询SQL,有固定值的不包括 string strColQueryAllSql = ""; //查询插入所有列清单 string strColInsertSql = ""; //新增数据列清单 string strColUpdateSql = ""; //修改的条件 string strColUpdateConditionSql = ""; //修改的条件 string strDataDealType_Modify = "修改"; string strColModifyCondition_Yes = "是"; int iModifyCondition = 0; sbAllSql.Append("/**" + iTable.ToString() + " " + strDataTableName + strDataDealType + "数据*/\n"); #region 数据变更表必须有列配置判断 //筛选出表的列清单 DataRow[] dtColRowListAll = dsExcel.Tables[_strColName].Select(" 表名='" + strDataTableName + "'"); if (dtColRowListAll.Length == 0) { MessageBox.Show("生成失败," + strDataTableName + "表没有列!", "提示", MessageBoxButtons.OK); return; } DataRow[] dtColRowList = dsExcel.Tables[_strColName].Select(" 表名='" + strDataTableName + "' and (固定值 is null or 是否修改条件='是')"); #endregion #region 修改表记录必须有条件判断 DataRow[] dtTableModifyList = dsExcel.Tables[_strTableName].Select(" 类型='" + strDataDealType_Modify + "'"); foreach (DataRow dr in dtTableModifyList) { DataRow[] dtColModifyConditionList = dsExcel.Tables[_strColName].Select(" 表名='" + dr["表名"].ToString().Trim() + "' and 是否修改条件='" + strColModifyCondition_Yes + "'"); if (dtColModifyConditionList.Length == 0) { MessageBox.Show("生成失败,修改的表" + dr["表名"].ToString().Trim() + "中“是否修改条件”为至少有一个字段为“是”!", "提示", MessageBoxButtons.OK); return; } } #endregion #region 确定查询列SQL、更新SQL和条件、新增SQL,将SQL参数化(格式:#+列名+#) foreach (DataRow drCol in dtColRowListAll)//针对列清单循环 { string strColCode = drCol["列名"].ToString().Trim().ToUpper(); string strColFixedValue = drCol["固定值"].ToString().Trim().ToUpper(); string strColModifyCondition = drCol["是否修改条件"].ToString().Trim(); string strColIsRemoveYH = drCol["是否不加引号"].ToString().Trim(); string strColIsHelp = drCol["是否辅助列"].ToString().Trim(); //查询所有列:除了辅助列 if (strColIsHelp != strColModifyCondition_Yes) { strColQueryAllSql += strColCode + ","; } //查询数据列清单:辅助列、没有固定值、修改条件 if (strColIsHelp == strColModifyCondition_Yes || string.IsNullOrEmpty(strColFixedValue) || strColModifyCondition == strColModifyCondition_Yes) { strColQuerySql += "[" + strColCode + "],";//插入列查询SQL,有固定值的不包括。这里加上[]是为了避免跟Excel数据源的关键字一致而报错 if (strColIsHelp == strColModifyCondition_Yes) { continue;//辅助列只查询 } } if (strDataDealType == strDataDealType_Modify) //修改数据处理 { if (strColModifyCondition == strColModifyCondition_Yes) //修改主键处理 { //修改条件SQL的确定 if (iModifyCondition == 0) { strColUpdateConditionSql += strColCode + "='#" + strColCode + "#'";//将SQL参数化 } else { strColUpdateConditionSql += " and " + strColCode + "='#" + strColCode + "#'";//将SQL参数化 } iModifyCondition++; } else if (!string.IsNullOrEmpty(strColFixedValue))//有固定值 { strColUpdateSql += strColCode + "=" + strColFixedValue + ","; } else if (strColIsRemoveYH == strColModifyCondition_Yes) { strColUpdateSql += strColCode + "=#" + strColCode + "#,";//将SQL参数化:SQL时把单引号去掉 } else { strColUpdateSql += strColCode + "='#" + strColCode + "#',";//将SQL参数化 } } else//新增数据处理 { if (!string.IsNullOrEmpty(strColFixedValue)) { strColInsertSql += strColFixedValue + ","; } else if (strColIsRemoveYH == strColModifyCondition_Yes) { strColInsertSql += "#" + strColCode + "#,";//将SQL参数化:SQL时把单引号去掉 } else { strColInsertSql += "'#" + strColCode + "#',";//将SQL参数化 } } } #endregion strColQuerySql = strColQuerySql.Substring(0, strColQuerySql.Length - 1); strColQueryAllSql = strColQueryAllSql.Substring(0, strColQueryAllSql.Length - 1); //获取数据信息 string strColDataList = "SELECT " + strColQuerySql + " FROM [" + drTable["数据Sheet名"].ToString().Trim() + "$] "; //“IErrorInfo.GetDescription 因 E_FAIL(0x80004005) 而失败。”这个错误,查了半天才知道是因为使用了Access的关键字 //在Access中和在MS SQL中一样,对关键字的转义是使用“[”和“]”的,比如你的数据库中某个表中有以上述关键字作为字段名的,记得在该字段前后分别加上“[”和“]”,如[user],[password],[yes],不过建议还是尽量避免使用关键字。 OleDbDataAdapter daOneTable = new OleDbDataAdapter(strColDataList, con); daOneTable.Fill(dsExcel, strDataTableName); //移除空行 dsExcel.Tables[strDataTableName].DeleteNullRow(); //生成SQL的前缀和后缀 string strInsertPre = "insert into " + strDataTableName + "(" + strColQueryAllSql + ") \nselect "; string strInsertOracleEnd = " from dual;"; string strUpdatePre = "update " + strDataTableName + " set "; string strUpdateOracleEnd = ";"; //用于判断增加提交字符的变量 int iDataNum = 1;//数据计数器 int iCommitCount = Convert.ToInt32(strDataStyle); int iDataRowCount = dsExcel.Tables[strDataTableName].Rows.Count; if (strDataDealType == strDataDealType_Modify)//修改数据 { #region 修改数据 //更新条件的最终确定 strColUpdateConditionSql = " WHERE " + strColUpdateConditionSql; foreach (DataRow drColData in dsExcel.Tables[strDataTableName].Rows)//导入数据表内循环 { string strColUpdateSql_Replace = strColUpdateSql; string strColUpdateConditionSql_Replace = strColUpdateConditionSql; foreach (DataRow drCol in dtColRowList)//针对列清单循环来替换参数 { string strColCode = drCol["列名"].ToString().Trim().ToUpper(); string strColIsRemoveYH2 = drCol["是否不加引号"].ToString().Trim(); string strDataValue = drColData[strColCode].ToString().Trim(); if (string.IsNullOrEmpty(strColIsRemoveYH2) || strColIsRemoveYH2 != strColModifyCondition_Yes) { //去掉单引号 strDataValue = drColData[strColCode].ToString().Trim().Replace("'", ""); } if (string.IsNullOrEmpty(drColData[strColCode].ToString().Trim())) { //空白时赋值null strColUpdateSql_Replace = strColUpdateSql_Replace.Replace("'#" + strColCode + "#'", "null"); } else { //将参数化的字段以实际数据代替 strColUpdateSql_Replace = strColUpdateSql_Replace.Replace("#" + strColCode + "#", strDataValue); strColUpdateConditionSql_Replace = strColUpdateConditionSql_Replace.Replace("#" + strColCode + "#", strDataValue); } } string strSQLOneData = strUpdatePre + strColUpdateSql_Replace.Substring(0, strColUpdateSql_Replace.Length - 1) + strColUpdateConditionSql_Replace; //生成单个数据SQL strSQLOneData = DataBaseCommon.GenOneDataSql(_selectDBType, strDataStyle, strCommit, strUpdateOracleEnd, strSQLOneData, iDataNum, iCommitCount, iDataRowCount); sbAllSql.Append(strSQLOneData); iDataNum++; } #endregion } else//新增数据 { #region 新增数据 foreach (DataRow drColData in dsExcel.Tables[strDataTableName].Rows)//导入数据表内循环 { string strColInsertSql_Replace = strColInsertSql; foreach (DataRow drCol in dtColRowList)//针对列清单循环来替换参数 { string strColCode = drCol["列名"].ToString().Trim().ToUpper(); string strColIsSQL2 = drCol["是否不加引号"].ToString().Trim(); string strDataValue = drColData[strColCode].ToString().Trim(); if (string.IsNullOrEmpty(strColIsSQL2) || strColIsSQL2 != strColModifyCondition_Yes) { //非SQL时要去掉单引号,SQL不用 strDataValue = drColData[strColCode].ToString().Trim().Replace("'", ""); } if (dsExcel.Tables[strDataTableName].Columns.Contains(strColCode)) { if (string.IsNullOrEmpty(drColData[strColCode].ToString().Trim())) { //空白时赋值null strColInsertSql_Replace = strColInsertSql_Replace.Replace("'#" + strColCode + "#'", "null"); } else { //将参数化的字段以实际数据代替 strColInsertSql_Replace = strColInsertSql_Replace.Replace("#" + strColCode + "#", strDataValue); } } } 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++; }//表循环结束 rtbResult.Clear(); 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); //返回到第一 } catch (Exception ex) { MsgHelper.ShowErr(ex.Message); return; } }//连接结束 }
private void tsbAutoSQL_Click(object sender, EventArgs e) { #region 生成增删改查SQL #region 变量 StringBuilder sbAllSql = new StringBuilder(); string strEndDicSetValue = ""; //字典参数赋值,在SQL末尾增加。目前MDS中用到 string strEndDicInRemark = ""; //注释中的传入字典参数说明。目前MDS中用到 string strEndColumnsRemark = ""; //注释中的列参数说明。目前MDS中用到 string strEndTableColumnsRemark = "/// <param type=\"DataTable\" name=\"#PART#\">出库备件清单\n"; //注释中的表列参数说明。目前MDS中用到 string strFileDictionaryRemark = ""; //开发指导书字典注释。目前MDS中用到 string strFileTableColumnRemark = ""; //开发指导书表的列注释。目前MDS中用到 string strWhereFirst = "WHERE 1=1 \r"; string strWhereNoFirst = "WHERE "; string strConditionPre = "WHERE 1=1 \r"; //条件前缀 string strAnd = " AND "; string strWhere = ""; string strMDSQueryParm = ""; //MDS查询的参数 string strMDSInsertUpdateParm = ""; //新增修改的参数 //已增加的列键 IDictionary <string, string> dicHaveAddColumnKey = new Dictionary <string, string>(); strClassCode = txbClassCode.Text.Trim(); strClassCn = txbClassNameCn.Text.Trim(); bool _isQueryParm = cbbParaType.SelectedValue.ToString() == "2" ? true : false;//是否SQL参数化 #endregion #region 取得数据 string strTwoType = cmbType.SelectedValue.ToString(); SqlType sqlTypeNow = SqlType.Query;; switch (strTwoType) { case "1": sqlTypeNow = SqlType.Insert; break; case "2": sqlTypeNow = SqlType.Update; break; case "3": sqlTypeNow = SqlType.Query; break; case "4": sqlTypeNow = SqlType.Delete; break; default: sqlTypeNow = SqlType.Query; break; } //取得数据源 DataTable dtMain = (DataTable)GlobalValue.Instance.dicBindingSource[_strTableName].DataSource; DataTable dtSec = (DataTable)GlobalValue.Instance.dicBindingSource[_strColName].DataSource; //移除空行 dtMain.DeleteNullRow(); //得到变更后数据 dtMain.AcceptChanges(); dtSec.AcceptChanges(); #endregion #region 获取默认值、排除列配置信息 //获取默认值、排除列配置信息 DataTable dtDefault = null; DataTable dtExclude = null; string strTSName = ""; if (_dbServer.DatabaseType == DataBaseType.SqlServer) { #region Sql Server _kv = new KeyValue(DataBaseType.SqlServer, AutoSqlColumnSetType.Default); dtDefault = _kv.LoadXMLFile(); _kv = new KeyValue(DataBaseType.SqlServer, AutoSqlColumnSetType.Exclude); dtExclude = _kv.LoadXMLFile(); strTSName = txbOrcTableShortName.Text.Trim().Replace(".", "").Replace("'", ""); strTableAlias = " " + strTSName;//只有查询用别名,其他语句不能使用 strTableAliasAndDot = strTableAlias + "."; if (_isQueryParm) { strEndTableColumnsRemark = "/// <param type=\"DataTable\" name=\"PART\">出库备件清单\n";//注释中的表列参数说明。目前MDS中用到 } #endregion } else { #region Oracle _kv = new KeyValue(DataBaseType.Oracle, AutoSqlColumnSetType.Default); dtDefault = _kv.LoadXMLFile(); _kv = new KeyValue(DataBaseType.Oracle, AutoSqlColumnSetType.Exclude); dtExclude = _kv.LoadXMLFile(); strTSName = txbOrcTableShortName.Text.Trim().Replace(".", "").Replace("'", ""); strTableAlias = string.IsNullOrEmpty(strTSName) ? " A" : " " + strTSName;//查询和修改中的别名:注前面的空格为必须 strTableAliasAndDot = strTableAlias + "."; if (_isQueryParm) { strEndTableColumnsRemark = "/// <param type=\"DataTable\" name=\"PART\">出库备件清单\n";//注释中的表列参数说明。目前MDS中用到 } #endregion } if (sqlTypeNow == SqlType.Insert && string.IsNullOrEmpty(strTSName)) { strTableAliasAndDot = ""; } #endregion #region 得到有效的数据 DataTable dtSecCopy = dtSec.Clone(); DataTable dtSecCondition = dtSec.Clone(); //得到并发ID行 DataRow[] drUpdateControlColumn = dtSec.Select("COLUMN_NAME='" + _strUpdateCtrolColumnCode + "'"); //得到查询条件集合 foreach (DataRow dr in dtSec.Select("条件='True' and COLUMN_NAME<>'" + _strUpdateCtrolColumnCode + "'")) { dtSecCondition.ImportRow(dr); } //得到有效的列 foreach (DataRow dr in dtSec.Select("选择='True' and COLUMN_NAME<>'" + _strUpdateCtrolColumnCode + "'")) { string strColCodeCopy = dr["COLUMN_NAME"].ToString(); if (dtExclude.Select("TYPE='" + strTwoType + "' AND KEY='" + strColCodeCopy + "'").Length == 0) { if (sqlTypeNow == SqlType.Update) //修改 { if (dtSecCondition.Select("COLUMN_NAME='" + strColCodeCopy + "'").Length == 0) { dtSecCopy.ImportRow(dr); //当条件不存在时才导入 } } else { dtSecCopy.ImportRow(dr); //对非修改,不是排除列就导入 } } } //并发ID字段处理 if (drUpdateControlColumn.Length > 0) { if (sqlTypeNow == SqlType.Update) { //对于修改的并发ID字段,修改列和条件都增加 dtSecCopy.ImportRow(drUpdateControlColumn[0]); dtSecCondition.ImportRow(drUpdateControlColumn[0]); } else { if (drUpdateControlColumn[0]["选择"].ToString() == "True") { if (dtExclude.Select("TYPE='" + strTwoType + "' AND KEY='" + _strUpdateCtrolColumnCode + "'").Length == 0) { dtSecCopy.ImportRow(drUpdateControlColumn[0]); } } if (drUpdateControlColumn[0]["条件"].ToString() == "True") { dtSecCondition.ImportRow(drUpdateControlColumn[0]); } } } #endregion #region 得到条件 //返回按列ID排序的行数组 DataRow[] drConditionArr = dtSecCondition.Select("1=1", "COLUMN_ID"); for (int i = 0; i < drConditionArr.Length; i++) { //变量声明 string strColCode = drConditionArr[i]["COLUMN_NAME"].ToString().Trim().ToUpper(); string strColType = drConditionArr[i]["DATA_TYPE"].ToString().Trim().ToUpper(); string strColFixedValue = drConditionArr[i]["固定值"].ToString().Trim(); //固定值 string strColComments = drConditionArr[i]["COMMENTS"].ToString().Trim(); //列说明 string strColCodeParm = "#" + strColCode + "#"; //加上#号的列编码 string strNowAnd = strAnd; //查询的时间范围 string strBeginDateParm = "#BEGIN_" + strColCode + "#"; string strEndDateParm = "#END_" + strColCode + "#"; string strQueryWhereDateRange = ""; if (_isQueryParm) { strColCodeParm = "@" + strColCode; strBeginDateParm = "@BEGIN_" + strColCode; strEndDateParm = "@END_" + strColCode; } //列空注释的处理 strColComments = DataBaseCommon.GetColumnComment(strColCode, strColComments); //对修改和删除,WHERE后面不加1=1 if (i == 0) { strConditionPre = strWhereFirst; if (sqlTypeNow == SqlType.Delete || sqlTypeNow == SqlType.Update) { strNowAnd = ""; strConditionPre = strWhereNoFirst; } } else { strConditionPre = ""; } if (_dbServer.DatabaseType == DataBaseType.SqlServer) { //SQL Server的时间范围 strQueryWhereDateRange = strNowAnd + strTableAliasAndDot + strColCode + " >='" + strBeginDateParm + "' \n" + strAnd + strTableAliasAndDot + strColCode + " < '" + strBeginDateParm + "' \r"; //结束日期:注要传入界面结束时间的+1天。目前MDS中用到 if (_columnNameCodeCheck && sqlTypeNow == SqlType.Query) //增加IF { strQueryWhereDateRange = _strPreIf + strNowAnd + strTableAliasAndDot + strColCode + " >='" + strBeginDateParm + "' \n" + _strEndIf + _strPreIf + strAnd + strTableAliasAndDot + strColCode + " < '" + strBeginDateParm + "' \r" + _strEndIf; //结束日期:注要传入界面结束时间的+1天。目前MDS中用到 } if (_isQueryParm) { strQueryWhereDateRange = strNowAnd + strTableAliasAndDot + strColCode + " >=" + strBeginDateParm + " \n" + strAnd + strTableAliasAndDot + strColCode + " < " + strBeginDateParm + " \r"; //结束日期:注要传入界面结束时间的+1天。目前MDS中用到 } } else { #region Oracle的时间范围 strQueryWhereDateRange = strNowAnd + strTableAliasAndDot + strColCode + " >= TO_DATE('" + strBeginDateParm + "','YYYY-MM-DD') \n" + strAnd + strTableAliasAndDot + strColCode + " < TO_DATE('" + strEndDateParm + "','YYYY-MM-DD') + 1 \r"; //目前MDS中用到 if (_columnNameCodeCheck && sqlTypeNow == SqlType.Query) //增加IF { strQueryWhereDateRange = _strPreIf + strNowAnd + strTableAliasAndDot + strColCode + " >= TO_DATE('" + strBeginDateParm + "','YYYY-MM-DD') \n" + _strEndIf + _strPreIf + strAnd + strTableAliasAndDot + strColCode + " < TO_DATE('" + strEndDateParm + "','YYYY-MM-DD') + 1 \r" + _strEndIf; //目前MDS中用到 } if (_isQueryParm) { strQueryWhereDateRange = strNowAnd + strTableAliasAndDot + strColCode + " >= " + strBeginDateParm + " \n" + strAnd + strTableAliasAndDot + strColCode + " < " + strEndDateParm + " \r"; //目前MDS中用到 } #endregion } if (sqlTypeNow == SqlType.Query && (strColType == "DATE" || strColType == "DATETIME" || strColType.Contains("TIMESTAMP"))) //列为日期时间类型 { #region 查询的日期时间段处理 strWhere += strConditionPre + strQueryWhereDateRange; //使用范围查询条件 if (!dicHaveAddColumnKey.ContainsKey(strBeginDateParm)) { dicHaveAddColumnKey[strBeginDateParm] = strBeginDateParm; dicHaveAddColumnKey[strEndDateParm] = strEndDateParm; //字典赋值 strEndDicSetValue += DataBaseCommon.GetDicSetValueString(strBeginDateParm, _isQueryParm); strEndDicInRemark += DataBaseCommon.GetFuncParamComment(strBeginDateParm, strColComments, _isQueryParm); strFileDictionaryRemark += DataBaseCommon.GetFileDictionaryParamComment(strBeginDateParm, strColComments, _isQueryParm); //字典赋值 strEndDicSetValue += DataBaseCommon.GetDicSetValueString(strEndDateParm, _isQueryParm); strEndDicInRemark += DataBaseCommon.GetFuncParamComment(strEndDateParm, strColComments, _isQueryParm); strFileDictionaryRemark += DataBaseCommon.GetFileDictionaryParamComment(strEndDateParm, strColComments, _isQueryParm); strMDSQueryParm += "parser.Sql(\"BEGIN_" + strColCode + "\", \"AND " + strTableAliasAndDot + strColCode + " >= @BEGIN_" + strColCode + "\",DbDataType.DateTime);\n"; strMDSQueryParm += "parser.Sql(\"END_" + strColCode + "\", \"AND " + strTableAliasAndDot + strColCode + " < @END_" + strColCode + " + 1\",DbDataType.DateTime);\n"; } #endregion } else { if (_isQueryParm) { strWhere += strConditionPre + strNowAnd + DataBaseCommon.MakeConditionColumnComment(strColCode, strColCodeParm, "", _isQueryParm); } else { //MDS中条件不能加注释,所以列注释传空。固定值传:'#列编码#' if (_columnNameCodeCheck && sqlTypeNow == SqlType.Query) { strWhere += strConditionPre + _strPreIf + strNowAnd + DataBaseCommon.MakeConditionColumnComment(strColCode, "'" + strColCodeParm + "'", "", _isQueryParm) + _strEndIf; } else { strWhere += strConditionPre + strNowAnd + DataBaseCommon.MakeConditionColumnComment(strColCode, "'" + strColCodeParm + "'", "", _isQueryParm); } } if (sqlTypeNow != SqlType.Insert) //新增没有条件 { if (!dicHaveAddColumnKey.ContainsKey(strColCodeParm)) //不存在才增加 { strEndDicSetValue += DataBaseCommon.GetDicSetValueString(strColCode, _isQueryParm); if (strColCode == _strUpdateCtrolColumnCode) { strEndDicInRemark += DataBaseCommon.GetFuncParamComment(strColCode, strColComments, true); //并发控制ID } else { strEndDicInRemark += DataBaseCommon.GetFuncParamComment(strColCode, strColComments, _isQueryParm); } strFileDictionaryRemark += DataBaseCommon.GetFileDictionaryParamComment(strColCode, strColComments, _isQueryParm); dicHaveAddColumnKey[strColCodeParm] = strColCodeParm; if (_isQueryParm) { if (sqlTypeNow == SqlType.Query) { strMDSQueryParm += "parser.Sql(\"" + strColCode + "\", \"AND " + strTableAliasAndDot + strColCode + " = @" + strColCode + "\");\n"; } else { strMDSInsertUpdateParm += "\"" + strColCode + "\"" + ","; } } } } } } #endregion int iTable = 1; //表计数器 foreach (DataRow drTable in dtMain.Rows) //针对表清单循环 { #region 变量声明 string strDataTableName = drTable["TABLE_NAME"].ToString().Trim(); string strDataTableComment = drTable["COMMENTS"].ToString().Trim(); string strSelect = ""; string strInsertColums = ""; string strInsertVale = ""; string strUpdate = ""; string strDelete = ""; string strOneSql = ""; int iColumnCount = dtSecCopy.Rows.Count; //选择的总行数 int j = 0; #endregion #region 生成SQL DataRow[] drArr = dtSecCopy.Select("1=1", "COLUMN_ID"); for (int i = 0; i < drArr.Length; i++)//针对列清单循环:因为只有一个表,所以第二个网格是该表的全部列 { #region 变量 j = i + 1; string strColCode = drArr[i]["COLUMN_NAME"].ToString().Trim().ToUpper(); string strColCodeOld = drArr[i]["COLUMN_NAME"].ToString().Trim().ToUpper(); string strColType = drArr[i]["DATA_TYPE"].ToString().Trim().ToUpper(); string strColFixedValue = drArr[i]["固定值"].ToString().Trim(); //固定值 string strColComments = drArr[i]["COMMENTS"].ToString().Trim(); //列说明 string strColValue = ""; //列值 string strColCodeParm = "#" + strColCode + "#"; //加上#号的列编码 string strNowComma = ","; //当前使用的逗号,最行一列的新增和修改是不用加逗号的 #endregion if (_isQueryParm) { strColCodeParm = "@" + strColCode; } #region 默认字段注释处理(针对MDS项目) strColComments = DataBaseCommon.GetColumnComment(strColCode, strColComments); #endregion //表列说明的备注构造 strEndTableColumnsRemark += DataBaseCommon.GetDicTableColumnRemarkString(strColCode, strColComments); //优先使用本次写的固定值,其次才是维护中的键值。对修改,非条件列,或虽然是条件,但为UPDATE_CONTROL_ID字段,都查找固定设置值 if (sqlTypeNow == SqlType.Insert || sqlTypeNow == SqlType.Update) { #region 新增修改处理 if (string.IsNullOrEmpty(strColFixedValue)) { #region 没有输入固定值 //查询全局默认值配置 DataRow[] drKeyArr = dtDefault.Select("TYPE='" + strTwoType + "' AND KEY='" + strColCode + "'"); if (dtDefault.Rows.Count > 0 && drKeyArr.Length > 0 && !string.IsNullOrEmpty(drKeyArr[0]["VALUE"].ToString())) { #region 全局有该类型设置,且非空 strColValue = drKeyArr[0]["VALUE"].ToString().Trim(); string strColValuePara = strColValue.Replace("'", ""); //移除引号 if (strColValuePara.StartsWith("#") && strColValuePara.EndsWith("#")) { #region 固定值为参数时 //查找参数设置 DataRow[] drKeyRemark = dtDefault.Select("TYPE='" + ((int)SqlType.Parameter).ToString() + "' AND KEY='" + strColValuePara + "'"); if (drKeyRemark.Length > 0 && !dicHaveAddColumnKey.ContainsKey(strColValuePara)) { #region 注释,且字典中没有该键时 //注:strColValuePara已包括了#号 strEndDicSetValue += DataBaseCommon.GetDicSetValueString(strColValuePara, _isQueryParm); strEndDicInRemark += DataBaseCommon.GetFuncParamComment(strColValuePara, drKeyRemark[0]["VALUE"].ToString(), _isQueryParm); strFileDictionaryRemark += DataBaseCommon.GetFileDictionaryParamComment(strColValuePara, drKeyRemark[0]["VALUE"].ToString(), _isQueryParm); //列名不用加#号 strEndColumnsRemark += DataBaseCommon.GetTableColumnComment(strColCode, strColComments); strFileTableColumnRemark += DataBaseCommon.GetFileColumnComment(strColCode, strColComments); dicHaveAddColumnKey[strColValuePara] = strColValuePara; if (_isQueryParm) { strMDSInsertUpdateParm += "\"" + strColValuePara.Replace("#", "") + "\"" + ","; } #endregion } else { #region 没有注释,或已增加了该键 //列名不用加#号 strEndColumnsRemark += DataBaseCommon.GetTableColumnComment(strColCode, strColComments); strFileTableColumnRemark += DataBaseCommon.GetFileColumnComment(strColCode, strColComments); #endregion } #endregion //参数加上引号 strColValue = "'" + strColValuePara + "'"; if (_isQueryParm) { strColValue = "@" + strColValuePara.Replace("#", ""); } } else { //非参数不加上引号 strColValue = strColValuePara; } #endregion } else //全局默认值设置为空时 { #region 全局默认值设置为空时 if (!dicHaveAddColumnKey.ContainsKey(strColCodeParm)) //不存在才增加 { strEndDicSetValue += DataBaseCommon.GetDicSetValueString(strColCode, _isQueryParm); //列名不用加#号 strEndColumnsRemark += DataBaseCommon.GetTableColumnComment(strColCode, strColComments); if (_dbServer.DatabaseType == DataBaseType.Oracle && drArr[i]["IS_PK"].ToString().Trim() == "1") { strEndDicInRemark += DataBaseCommon.GetFuncParamComment(strColCode, strColComments, true); } else { strEndDicInRemark += DataBaseCommon.GetFuncParamComment(strColCode, strColComments, _isQueryParm); } strFileDictionaryRemark += DataBaseCommon.GetFileDictionaryParamComment(strColCode, strColComments, _isQueryParm); strFileTableColumnRemark += DataBaseCommon.GetFileColumnComment(strColCode, strColComments); dicHaveAddColumnKey[strColCodeParm] = strColCodeParm; strMDSInsertUpdateParm += "\"" + strColCode + "\"" + ","; } #endregion } #endregion } else //网格输入了固定值 { strColValue = strColFixedValue; } #endregion } else if (sqlTypeNow == SqlType.Query) { //增加程序参数中的列说明、开发指导书的表列说明 strEndColumnsRemark += DataBaseCommon.GetTableColumnComment(strColCode, strColComments); strFileTableColumnRemark += DataBaseCommon.GetFileColumnComment(strColCode, strColComments); } //生成SQL if (sqlTypeNow == SqlType.Insert) { #region 新增 strWhere = ""; if (j == 1) //第一行 { if (j == iColumnCount) { strNowComma = ""; strInsertColums += "INSERT INTO " + DataBaseCommon.MakeTableComment(strDataTableName + DataBaseCommon.AddRightBand(strTableAlias), strDataTableComment) + "(\n" + "\t" + strTableAliasAndDot + strColCode + strNowComma + "\n)\n"; strInsertVale += "VALUES\n(\n" + DataBaseCommon.MakeAddValueColumnComment(strNowComma, strColCode, strColValue, strColComments, strColType, _isQueryParm) + "\n)\n";; } else { strInsertColums += "INSERT INTO " + DataBaseCommon.MakeTableComment(strDataTableName + DataBaseCommon.AddRightBand(strTableAlias), strDataTableComment) + "(\n" + "\t" + strTableAliasAndDot + strColCode + strNowComma + "\n"; strInsertVale += "VALUES\n(\n" + DataBaseCommon.MakeAddValueColumnComment(strNowComma, strColCode, strColValue, strColComments, strColType, _isQueryParm); } } else if (j != iColumnCount) //非首行,并且非尾行 { strInsertColums += "\t" + strTableAliasAndDot + strColCode + _strComma + "\n";; strInsertVale += DataBaseCommon.MakeAddValueColumnComment(strNowComma, strColCode, strColValue, strColComments, strColType, _isQueryParm); } else //最后一行 { strNowComma = ""; strInsertColums += "\t" + strTableAliasAndDot + strColCode + "\n)\n"; //最后一行不用加逗号 strInsertVale += DataBaseCommon.MakeAddValueColumnComment(strNowComma, strColCode, strColValue, strColComments, strColType, _isQueryParm) + ")\n"; } strOneSql = strInsertColums + strInsertVale; #endregion } else if (sqlTypeNow == SqlType.Update) { #region 修改 if (j == 1) //第一行 { if (j == iColumnCount) { strNowComma = ""; } strUpdate += "UPDATE " + DataBaseCommon.MakeTableComment(strDataTableName + DataBaseCommon.AddRightBand(strTableAlias), strDataTableComment) + "SET " + DataBaseCommon.MakeUpdateColumnComment(strNowComma, strColCode, strColValue, strColComments, strColType, _isQueryParm); } else if (j != iColumnCount) //中间行 { strUpdate += DataBaseCommon.MakeUpdateColumnComment(strNowComma, strColCode, strColValue, strColComments, strColType, _isQueryParm); } else //最后一行 { strNowComma = ""; strUpdate += DataBaseCommon.MakeUpdateColumnComment(strNowComma, strColCode, strColValue, strColComments, strColType, _isQueryParm); } strOneSql = strUpdate; #endregion } } else if (sqlTypeNow == SqlType.Query) { #region 查询 if (j == 1) //第一行 { if (j == iColumnCount) { strNowComma = ""; strSelect += "SELECT " + DataBaseCommon.MakeQueryColumnComment(strNowComma, strTableAliasAndDot + strColCode, strColComments) + "FROM " + DataBaseCommon.MakeTableComment(strDataTableName + DataBaseCommon.AddRightBand(strTableAlias), strDataTableComment); } else { strSelect += "SELECT " + DataBaseCommon.MakeQueryColumnComment(strNowComma, strTableAliasAndDot + strColCode, strColComments); } } else if (j != iColumnCount) //中间行 { strSelect += DataBaseCommon.MakeQueryColumnComment(strNowComma, strTableAliasAndDot + strColCode, strColComments); } else //最后一行 { strNowComma = ""; strSelect += DataBaseCommon.MakeQueryColumnComment(strNowComma, strTableAliasAndDot + strColCode, strColComments) + "FROM " + DataBaseCommon.MakeTableComment(strDataTableName + DataBaseCommon.AddRightBand(strTableAlias), strDataTableComment); } strOneSql = strSelect; #endregion } } if (sqlTypeNow == SqlType.Delete) { #region 除 strDelete += "DELETE FROM " + strDataTableName + DataBaseCommon.AddRightBand(strTableAlias) + "\n"; strOneSql = strDelete; #endregion } #endregion if (_isQueryParm) { if (!string.IsNullOrEmpty(strMDSInsertUpdateParm)) { if (sqlTypeNow == SqlType.Update) { } strMDSInsertUpdateParm = strMDSInsertUpdateParm.Substring(0, strMDSInsertUpdateParm.Length - 1); } if (sqlTypeNow == SqlType.Query) { strOneSql = "SqlQueryParser parser = new SqlQueryParser();\nparser.Sql(@\"" + strOneSql + " WHERE 1=1 \");\n" + strMDSQueryParm + "DataTable dtReturn = parser.Query(dicParam);\nreturn dtReturn;\n"; } else { strOneSql = "int iEff = SqlQueryParser.Update(\n@\"" + strOneSql + strWhere + "\",dicParam,tran," + strMDSInsertUpdateParm + ");\n" + "if (iEff == 0)\n" + "{\n" + " throw new ConcurrencyException();\n" + "}\n" + "return ExecutionResult.Success();\n"; } } else { strOneSql += strWhere; } string strFuncRemark = ""; if (sqlTypeNow == SqlType.Query) { strFuncRemark = "\r/*DataAcce方法*/\r" + DataBaseCommon.GetFuncParmQueryPreString(true, _isQueryParm) + strEndDicInRemark + DataBaseCommon.GetFuncParmQueryMiddleString(_isQueryParm) + strEndColumnsRemark + DataBaseCommon.GetFuncParmQueryEndString(true, _isQueryParm, strOneSql) + "\r/*Bussiness方法*/\r" + DataBaseCommon.GetFuncParmQueryPreString(false, _isQueryParm) + strEndDicInRemark + DataBaseCommon.GetFuncParmQueryMiddleString(_isQueryParm) + strEndColumnsRemark + DataBaseCommon.GetFuncParmQueryEndString(false, _isQueryParm, strOneSql); } else { strFuncRemark = "\r/*DataAcce方法*/\r" + DataBaseCommon.GetFuncParmSavePreString(true, _isQueryParm) + strEndDicInRemark + DataBaseCommon.GetFuncParmSaveEndString(true, _isQueryParm, strOneSql) + "\r/*Bussiness方法*/\r" + DataBaseCommon.GetFuncParmSavePreString(false, _isQueryParm) + strEndDicInRemark + DataBaseCommon.GetFuncParmSaveEndString(false, _isQueryParm, strOneSql); } strEndTableColumnsRemark += "/// </param>"; sbAllSql.Append(strOneSql + strFuncRemark + "\r/*开发指导书字典注释*/\r" + strFileDictionaryRemark + "\r/*开发指导书表的列注释*/\r" + strFileTableColumnRemark + "\r/*参数赋值*/\r" + strEndDicSetValue + "\r/*表列注释*/\r" + strEndTableColumnsRemark); iTable++; } rtbResult.Clear(); rtbResult.AppendText(sbAllSql.ToString() + "\n"); Clipboard.SetData(DataFormats.UnicodeText, sbAllSql.ToString()); tabControl1.SelectedTab = tpAutoSQL; //生成SQL成功后提示 MsgHelper.ShowInfo(_strAutoSqlSuccess); return; #endregion }