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
}