public static void DownloadFile(string sSourceFilePath, string sSaveFileName, bool isOverWrite)
{
try
{
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件(*.xlsx)|*.xlsx";
saveDialog.FileName = sSaveFileName + "_" + DateTime.Now.ToyyyyMMddHHmmss();
saveDialog.FilterIndex = 1;
//saveDialog.OverwritePrompt = true;
if (saveDialog.ShowDialog() == DialogResult.OK)
{
string fileName = UIHelper.GetSystemFullPath(sSourceFilePath);
string destFileName = saveDialog.FileName;
if (System.IO.File.Exists(fileName))
{
System.IO.File.Copy(fileName, destFileName, isOverWrite);
MsgHelper.ShowInfo(string.Format("下载成功，文件保存在 {0}", destFileName));
}
else
{
MsgHelper.ShowErr("模板文件不存在，下载失败！");
}
}
}
catch (Exception ex)
{
MsgHelper.ShowErr(ex.Message);
}
}
private void llModelDown_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
try
{
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件(*.xlsx)|*.xlsx";
saveDialog.FilterIndex = 1;
if (saveDialog.ShowDialog(this) == DialogResult.OK)
{
string fileName = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase.ToString() + (@"\ModelFile\生成IBD文件导入模板.xlsx");
string destFileName = saveDialog.FileName;
if (System.IO.File.Exists(fileName))
{
System.IO.File.Copy(fileName, destFileName);
MsgHelper.ShowInfo(string.Format("下载成功，文件保存在 {0}", destFileName));
}
else
{
MsgHelper.ShowErr("模板文件不存在，下载失败！");
}
}
}
catch (Exception ex)
{
MsgHelper.ShowErr("下载失败，出现异常！" + ex.Message);
}
}
private void tsmiFtable_Click(object sender, EventArgs e)
{
try
{
if (txtPath.Text == "")
{
MsgHelper.ShowInfo("请您设置存放路径!");
}
else if (checkTxt() && (treeNode.Node.Level == 1))
{
string sEntityType = cbbEntityType.SelectedValue.ToString();
if (sEntityType == _sStringColumnList)
{
entityList = _entiyBiz.BuildCustomEntity(dataTables, txtNameSpace.Text.Trim(), txtAccess.Text.Trim(), txtClassPreString.Text.Trim(), txtClassEndString.Text.Trim(), txtPath.Text.Trim());
}
else if (sEntityType == _sStringColumn)
{
entityList = _entiyBiz.BuildTableColumnNameEntity(dataTables, txtNameSpace.Text.Trim(), txtAccess.Text.Trim(), txtClassPreString.Text.Trim(), txtClassEndString.Text.Trim(), txtPath.Text.Trim());
}
else
{
entityList = _entiyBiz.BuildEntity(dataTables, txtNameSpace.Text.Trim(), txtAccess.Text.Trim(), txtClassPreString.Text.Trim(), txtClassEndString.Text.Trim(), txtPath.Text.Trim());
}
if (entityList.Count > 0)
{
dgvEntityInfo.DataSource = entityList;
}
}
}
catch (Exception exception)
{
MsgHelper.ShowErr(exception.Message);
}
}
private void tsbSave_Click(object sender, EventArgs e)
{
DataTable dtSource = dgvQuery.GetBindingTable();
if (dtSource == null || dtSource.Rows.Count == 0)
{
MsgHelper.ShowErr("没有要保存的数据！");
return;
}
_dllSet.SaveXMLFile(dtSource, _dllSet.GetFileName());
MsgHelper.ShowInfo("保存成功！");
}
/// <summary>
/// 保存按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tsbSave_Click(object sender, EventArgs e)
{
groupBox1.Focus();
BindingSource bs = (BindingSource)dgvKey.DataSource;
bs.EndEdit();
DataTable dt = (DataTable)bs.DataSource;
DataTable dtSave = dt.GetChanges();
if (dtSave == null || dtSave.Rows.Count == 0)
{
MsgHelper.ShowInfo("没有修改数据，不用保存！");
return;
}
_kv.SaveXMLFile(dtSave, _FileName);
MsgHelper.ShowInfo("保存成功！");
}
private void tsbAutoSQL_Click(object sender, EventArgs e)
{
try
{
string sbAllSql = "";
DataTable dtMain = (DataTable)bsTable.DataSource;
if (dtMain.Rows.Count == 0)
{
MsgHelper.ShowInfo("没有可生成的数据！");
return;
}
for (int i = 0; i < dtMain.Rows.Count; i++)
{
//初始化单条数据为书写的文本
string strOneData = rtbConString.Text.Trim();
for (int j = 0; j < dtMain.Columns.Count; j++)
{
string strData = dtMain.Rows[i][j].ToString().Trim();
//将数据中的列名替换为单元格中的数据
strOneData = strOneData.Replace("#" + dtMain.Columns[j].ColumnName + "#", strData);
}
//所有SQL文本累加
sbAllSql += strOneData + "\n";
}
//保存属性
//PropSetting.Default.Save();
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);
}
}
private void tsbAutoSQL_Click(object sender, EventArgs e)
{
#region 表列扩展属性变更
int iDbType = int.Parse(cbbDbType.SelectedValue.ToString());
DataBaseType selectDBType = (DataBaseType)iDbType;
if (selectDBType == DataBaseType.SQLite)
{
MsgHelper.ShowInfo("SQLite不支持注释！");
return;
}
//取得数据源
DataTable dtMain = (DataTable)bsTable.DataSource;
#region 移除空行
string strColNameList = "";
for (int k = 0; k < dtMain.Columns.Count; k++)
{
string strOneDataCol = "([" + dtMain.Columns[k].ColumnName + "] is null or [" + dtMain.Columns[k].ColumnName + "]='') ";
if (k != 0)
{
strColNameList = strColNameList + "and " + strOneDataCol;
}
else
{
strColNameList = strColNameList + strOneDataCol;
}
}
foreach (DataRow dr in dtMain.Select(strColNameList))
{
dtMain.Rows.Remove(dr);
}
#endregion
//得到变更后数据
dtMain.AcceptChanges();
if (dtMain.Rows.Count == 0)
{
MsgHelper.ShowInfo("没有可生成的数据！");
return;
}
string strExtendList = "";
int iTableExtend = 1;
string sbAllSql = "";
if (selectDBType == DataBaseType.SqlServer)
{
#region SqlServer
foreach (DataRow drCol in dtMain.Rows)
{
string strExtendTableName = drCol["表名"].ToString();
string strExtendColName = drCol["列名"].ToString();
string strExtendText = drCol["扩展属性说明"].ToString();
string strExtend = " SELECT " + StringHelper.ChangeIntoSqlString(strExtendTableName) + " TABLE_NAME,"
+ StringHelper.ChangeIntoSqlString(strExtendColName) + " COLUMNS_NAME,"
+ StringHelper.ChangeIntoSqlString(strExtendText) + " EXTEND_TEXT"
+ " \n";
if (iTableExtend != 1)
{
strExtend = " UNION " + strExtend;
}
strExtendList = strExtendList + strExtend;
iTableExtend++;
}
//读取并替换
sbAllSql = File.ReadAllText(UIHelper.GetSystemFullPath(@"SqlScript/1.001_修改表列说明.sql"), Encoding.Default).Replace("#EXTEND_LIST#", strExtendList);
#endregion
}
else
{
foreach (DataRow drCol in dtMain.Rows)
{
string strExtendTableName = drCol["表名"].ToString();
string strExtendColName = drCol["列名"].ToString();
string strExtendText = drCol["扩展属性说明"].ToString().Trim();
if (string.IsNullOrEmpty(strExtendTableName))
{
iTableExtend++;
continue;
}
if (string.IsNullOrEmpty(strExtendColName))
{
if (selectDBType == DataBaseType.Oracle)
{
strExtendList += string.Format(@"COMMENT ON TABLE {0} IS '{1}';" + "\n", strExtendTableName, strExtendText);
}
else if (selectDBType == DataBaseType.MySql)
{
strExtendList += string.Format(@"ALTER TABLE {0} COMMENT = '{1}';" + "\n", strExtendTableName, strExtendText);
}
else if (selectDBType == DataBaseType.PostgreSql)
{
//
strExtendList += string.Format(@"COMMENT ON TABLE {0} IS '{1}';" + "\n", strExtendTableName, strExtendText);
}
else
{
throw new Exception("暂不支持该数据库类型");
}
}
else
{
if (selectDBType == DataBaseType.Oracle)
{
strExtendList += string.Format(@"COMMENT ON COLUMN {0}.{1} IS '{2}';" + "\n", strExtendTableName, strExtendColName, strExtendText);
}
else if (selectDBType == DataBaseType.MySql)
{
//需要加上原字段信息，所以建议利用修改表列的SQL来修改
//strExtendList += string.Format(@"ALTER TABLE {0} MODIFY COLUMN {1} COMMENT '{2}';" + "\n", strExtendTableName, strExtendColName, strExtendText);
}
else if (selectDBType == DataBaseType.PostgreSql)
{
//
strExtendList += string.Format(@"COMMENT ON COLUMN {0}.{1} IS '{2}';" + "\n", strExtendTableName, strExtendColName, strExtendText);
}
else
{
throw new Exception("暂不支持该数据库类型");
}
}
iTableExtend++;
}
sbAllSql = strExtendList;
}
//生成SQL并提示
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); //返回到第一
return;
#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();
}
private void tsbSave_Click(object sender, EventArgs e)
{
_saveMenu.Name = txbMenuName.Text.Trim();
_saveMenu.Code = txbMenuCode.Text.Trim();
_saveMenu.DLLName = cbbDLL.Text;
_saveMenu.FormName = txbClassFullPath.Text.Trim();
_saveMenu.ShortCutKey = txbShortCutKey.Text.Trim();
if (string.IsNullOrEmpty(_saveMenu.Name))
{
MsgHelper.ShowErr("【菜单名称】不能为空！");
return;
}
bool isAdd = false;
XmlDocument xmlMenu = DMenu.ReadMenuXmlFile(AppType.WinForm);
if (string.IsNullOrEmpty(_saveMenu.Guid))
{
isAdd = true;
}
#region 新增
XmlElement xnNew;
switch (_saveMenu.MenuType)
{
case MenuTypeEnum.Modul:
xnNew = xmlMenu.CreateElement("Model");
if (isAdd)
{
xnNew.SetAttribute(MemuAttrString.Guid, StringHelper.GetGUID());
xmlMenu.DocumentElement.AppendChild(xnNew);
}
else
{
xnNew = xmlMenu.SelectSingleNode("xml/Model[@Guid='" + _saveMenu.Guid + "']") as XmlElement;
}
break;
case MenuTypeEnum.Class:
xnNew = xmlMenu.CreateElement("Class");
if (isAdd)
{
xnNew.SetAttribute(MemuAttrString.Guid, StringHelper.GetGUID());
//先找模块
XmlNode xnParent = xmlMenu.SelectSingleNode("xml/Model[@Guid='" + _saveMenu.ParentGuid + "']");
if (xnParent != null)
{
xnParent.AppendChild(xnNew);
}
}
if (xnNew.ParentNode == null)
{
//第一个分类
GetClassNode(isAdd, xmlMenu, "xml/Model/Class", ref xnNew);
}
if (xnNew.ParentNode == null)
{
//第二个菜单分类
GetClassNode(isAdd, xmlMenu, "xml/Model/Class/Class", ref xnNew);
}
if (xnNew.ParentNode == null)
{
//最多支持第三个菜单分类
GetClassNode(isAdd, xmlMenu, "xml/Model/Class/Class/Class", ref xnNew);
}
break;
case MenuTypeEnum.Menu:
default:
if (string.IsNullOrEmpty(_saveMenu.DLLName))
{
MsgHelper.ShowErr("【所在DLL文件】不能为空！");
return;
}
if (string.IsNullOrEmpty(_saveMenu.FormName))
{
MsgHelper.ShowErr("【全路径类名】不能为空！");
return;
}
xnNew = xmlMenu.CreateElement("Menu");
if (isAdd)
{
xnNew.SetAttribute(MemuAttrString.Guid, StringHelper.GetGUID());
}
//第一个分类
GetMenuNode(isAdd, xmlMenu, "xml/Model/Class", "xml /Model/Class/Menu", ref xnNew);
if (xnNew.ParentNode == null)
{
//第二个菜单分类
GetMenuNode(isAdd, xmlMenu, "xml/Model/Class/Class", "xml/Model/Class/Class/Menu", ref xnNew);
}
if (xnNew.ParentNode == null)
{
//最多支持第三个菜单分类
GetMenuNode(isAdd, xmlMenu, "xml/Model/Class/Class/Class", "xml/Model/Class/Class/Class/Menu", ref xnNew);
}
break;
}
#endregion
xnNew.SetAttribute(MemuAttrString.Name, _saveMenu.Name);
xnNew.SetAttribute(MemuAttrString.Code, _saveMenu.Code);
xnNew.SetAttribute(MemuAttrString.DLLName, _saveMenu.DLLName);
xnNew.SetAttribute(MemuAttrString.FormName, _saveMenu.FormName);
xnNew.SetAttribute(MemuAttrString.ShortCutKey, _saveMenu.ShortCutKey);
//保存
xmlMenu.Save(DMenu.MenuXmlFilePath);
MsgHelper.ShowInfo("保存成功！");
UIHelper.ResetControl(grpEdit);
//重新加载菜单
LoadMenu();
}
private void tsbGenIBDFile_Click(object sender, EventArgs e)
{
try
{
if (dsExcel == null)
{
MsgHelper.ShowInfo("请先导入！");
return;
}
DataTable dtMain = (DataTable)(dgvIBDFileList.DataSource as BindingSource).DataSource;
DataTable dtList = dsExcel.Tables[_strLayerList];
string strMainPath = txbIBDMainPath.Text.Trim();
if (string.IsNullOrEmpty(strMainPath))
{
MsgHelper.ShowInfo("请选择“生成的根目录”！");
return;
}
string strClassPre = txbIBDClassPre.Text.Trim().ToUpper();
list = new List <EntityInfo>();
strMainPath = strMainPath + @"\IBD自动生成文件夹\";
for (int i = 0; i < dtMain.Rows.Count; i++)
{
string strPath = dtMain.Rows[i]["文件目录相对路径"].ToString().Replace("/", "\\");
string strFromLayerName = dtMain.Rows[i]["项目分层全称"].ToString().Trim();
string strLayerType = dtMain.Rows[i]["分层类型"].ToString().Trim();
DataRow[] drFrom = dtList.Select("项目分层全称 = '" + strFromLayerName + "' AND 分层类型='" + strLayerType + "'");
if (drFrom.Length == 0)
{
MsgHelper.ShowInfo("“" + strPath + "”的项目分层全称未定义，请修改或在[项目分层清单]里增加！");
return;
}
string strSpaceName = strPath.Replace("\\", ".");//空间名
string strClassName = strClassPre + dtMain.Rows[i]["类名"].ToString();
if (!strPath.EndsWith("\\"))
{
strPath += "\\";
}
//当前使用的项目层集合
DataTable dtUseLaye = dtList.Clone();
foreach (DataRow dr in dtList.Select("分层类型='" + drFrom[0]["分层类型"] + "'"))
{
dtUseLaye.ImportRow(dr);
}
string Ipath = strMainPath + ReplaceString(strPath, "0", "I", strFromLayerName, dtUseLaye);
string Bpath = strMainPath + ReplaceString(strPath, "0", "B", strFromLayerName, dtUseLaye);
string Dpath = strMainPath + ReplaceString(strPath, "0", "D", strFromLayerName, dtUseLaye);
string IDpath = strMainPath + ReplaceString(strPath, "0", "ID", strFromLayerName, dtUseLaye);
string UIpath = strMainPath + ReplaceString(strPath, "0", "U", strFromLayerName, dtUseLaye);
string ISpaceName = ReplaceString(strSpaceName + ".", "1", "I", strFromLayerName, dtUseLaye);
string BSpaceName = ReplaceString(strSpaceName + ".", "1", "B", strFromLayerName, dtUseLaye);
string DSpaceName = ReplaceString(strSpaceName + ".", "1", "D", strFromLayerName, dtUseLaye);
string IDSpaceName = ReplaceString(strSpaceName + ".", "1", "ID", strFromLayerName, dtUseLaye);
string UISpaceName = ReplaceString(strSpaceName + ".", "1", "U", strFromLayerName, dtUseLaye);
if (!Directory.Exists(Ipath))
{
Directory.CreateDirectory(Ipath);
}
if (!Directory.Exists(Bpath))
{
Directory.CreateDirectory(Bpath);
}
if (!Directory.Exists(IDpath))
{
Directory.CreateDirectory(IDpath);
}
if (!Directory.Exists(Dpath))
{
Directory.CreateDirectory(Dpath);
}
if (!Directory.Exists(UIpath))
{
Directory.CreateDirectory(UIpath);
}
//生成接口文件
GenerateIFile(Ipath, ISpaceName, strClassName); //I
GenerateBFile(Bpath, BSpaceName, strClassName, strFromLayerName, dtUseLaye); //B
GenerateIDFile(IDpath, IDSpaceName, strClassName); //ID
GenerateDFile(Dpath, DSpaceName, strClassName); //D
GenerateUIFile(UIpath, UISpaceName, strClassName, strFromLayerName, dtUseLaye); //UI
}
//提示成功
MsgHelper.ShowInfo("生成成功！");
}
catch (Exception ex)
{
MsgHelper.ShowErr(ex.Message);
}
}
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
}
