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 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 tsbImport_Click(object sender, EventArgs e)
{
try
{
int iDbType = int.Parse(cbbDbType.SelectedValue.ToString());
_selectDBType = (DataBaseType)iDbType;
_dicQuery[_strTableName] = DBToolStaticString.DataGenerate_Table + " AND 数据库类型 = '" + _selectDBType.ToString() + "' order by [序号]";
_dicQuery[_strColName] = DBToolStaticString.DataGenerate_Column;
dsExcel = ExportHelper.GetExcelData(_dicQuery, out _DBConnString);
if (dsExcel != null)
{
bsTable.DataSource = dsExcel.Tables[_strTableName];
bsCos.DataSource = dsExcel.Tables[_strColName];
dgvTableList.DataSource = bsTable;
dgvColList.DataSource = bsCos;
//初始化变量
//MsgHelper.ShowInfo("导入成功！");
lblInfo.Text = _strImportSuccess;
tsbAutoSQL.Enabled = true;
_importDBType = _selectDBType;
}
}
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);
}
}
public bool checkTxt()
{
if ((((txtAccess.Text.Trim() == null) || (txtAccess.Text.Trim() == "")) || ((txtNameSpace.Text.Trim() == null) || (txtNameSpace.Text.Trim() == ""))) || (txtClassEndString.Text.Trim() == null))
{
MsgHelper.ShowErr("访问修饰符或命名空间不能为空！");
return(false);
}
return(true);
}
private void OpenMenu(DMenu dMenu, bool IsExpandTreeNode = true)
{
if (dMenu == null || dMenu.MenuType != MenuTypeEnum.Menu)
{
return;
}
if (IsExpandTreeNode)
{
OpenTreeNodeMenu(dMenu.Name);
}
//判断窗体是否已经打开
foreach (Form frm in this.MdiChildren)
{
DMenu dMenuFrm = frm.Tag as DMenu;
if (dMenuFrm.Guid.Equals(dMenu.Guid))
{
//选中页签
if (tcMenu.SelectedTab != tcMenu.TabPages[dMenu.Guid])
{
tcMenu.SelectedTab = tcMenu.TabPages[dMenu.Guid];
}
txbMenuPath.Text = dMenu.FullPath;
pnlDestop.Hide();
frm.Activate();
return;
}
}
//反射得到窗体
Assembly dll = Assembly.LoadFile(Path.Combine(_strAppPath, dMenu.DLLName));
object form = dll.CreateInstance(dMenu.FormName);
if (form is Form)
{
Form newForm = form as Form;
newForm.Tag = dMenu;
newForm.MdiParent = this;
newForm.WindowState = FormWindowState.Maximized;
newForm.Activated += ChildForm_Active;
newForm.FormClosed += MdiChild_Close;
//增加页签
tcMenu.TabPages.Add(dMenu.Guid, dMenu.Name);
tcMenu.TabPages[dMenu.Guid].Tag = dMenu;
tcMenu.SelectedTab = tcMenu.TabPages[dMenu.Guid];
txbMenuPath.Text = dMenu.FullPath;
newForm.Show();
}
else
{
MsgHelper.ShowErr("配置错误，【" + dMenu.Name + "】菜单不是窗体类型！");
}
}
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 tsbExport_Click(object sender, EventArgs e)
{
string strWhere = string.IsNullOrEmpty(rtbWhere.Text.Trim()) == true ? "" : " WHERE " + rtbWhere.Text.Trim();
string strDataTableName = cbbTableName.Text.Trim();
DataTable dtCount = _dataAccess.DataAccess.QueryHadParamSqlData("SELECT * FROM " + strDataTableName + strWhere, _dicQueryCondition);
if (dtCount.Rows[0][0].ToString() == "0")
{
MsgHelper.ShowErr("没有要生成的记录！", "提示");
return;
}
//导出Excel
ExportHelper.ExportExcel(dtCount, "数据_" + strDataTableName);
}
private void tsbAutoSQL_Click(object sender, EventArgs e)
{
string sPath = txbSelectPath.Text.Trim();
if (string.IsNullOrEmpty(sPath))
{
MsgHelper.ShowErr("请选择路径！");
}
rtbString.Clear();
StringBuilder sb = new StringBuilder();
DirectoryInfo rootDirectory = new DirectoryInfo(sPath);
GetDirectoryFile(sb, rootDirectory, cbbFileType.SelectedValue.ToString(), cbbPathType.SelectedValue.ToString(), !ckbIgnorChildDir.Checked);
rtbString.AppendText(sb.ToString());
}
private void tvDataBaseInfo_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
DataTable table = null;
try
{
if (e.Node.Level == 2)
{
_dbServer.Database = e.Node.Parent.Text;
table = _entiyBiz.GetMemorySTable(e.Node.Parent.Text, e.Node.Text, _dbServer);
}
else if ((e.Node.Level == 1) && (e.Node.Nodes.Count > 0))
{
_dbServer.Database = e.Node.Text;
DataTable schema = null;
if (_dbServer.DatabaseType == DataBaseType.Oracle)
{
schema = _entiyBiz.GetSchema("Tables", new string[] { _dbServer.Database });
}
else
{
schema = _entiyBiz.GetSchema("Tables", null);
}
dataTables = new DataSet();
for (int i = 0; i < schema.Rows.Count; i++)
{
string tblName = "";
if (_dbServer.DatabaseType == DataBaseType.Oracle)
{
tblName = schema.Rows[i][1].ToString();
}
else
{
tblName = schema.Rows[i][2].ToString();
}
dataTables.Tables.Add(_entiyBiz.GetMemorySTable(_dbServer.Database, tblName, _dbServer));
}
}
if (table != null)
{
dgvTableInfo.DataSource = table;
}
}
catch (Exception exception)
{
MsgHelper.ShowErr(exception.Message);
}
}
private void tsbConnect_Click(object sender, EventArgs e)
{
try
{
//非空判断
string strTableName = cbbTableName.Text.Trim();
string strWhere = rtbWhere.Text.Trim();
if (string.IsNullOrEmpty(strTableName) && string.IsNullOrEmpty(strWhere))
{
MsgHelper.ShowErr("表名和Where条件不能同时为空！");
return;
}
//得到服务器对象
_dbServer = uC_DbConnection1.GetDbServerInfo();
if (_dbServer == null)
{
return;
}
//得到数据库访问对象
ICustomDataAccess customDataAccess = new CustomDataAccess(_dbServer.DatabaseType, _dbServer);
//构造查询SQL
if (string.IsNullOrEmpty(strWhere)) //Where条件为空
{
_strMainSql = "SELECT * FROM " + strTableName;
}
else if (string.IsNullOrEmpty(strTableName))//表名为空，那么Where中为自定义SQL
{
_strMainSql = strWhere;
}
else //表名和Where条件都不为空，那么拼接语句
{
_strMainSql = "SELECT * FROM " + strTableName + " WHERE " + strWhere;
}
//查询数据
DataTable dtMain = customDataAccess.DataAccess.QueryHadParamSqlData(_strMainSql, _dicQuery);
dtMain.TableName = _strTableName;
bsTable.DataSource = dtMain;
//设置数据源
GlobalValue.Instance.SetPublicDataSource(new DataTable[] { dtMain });
dgvTableList.DataSource = bsTable;
}
catch (Exception ex)
{
MsgHelper.ShowErr(ex.Message);
}
}
private void tsbEdit_Click(object sender, EventArgs e)
{
DataRow drSelect = dgvQuery.GetCurrentRow();
if (drSelect == null)
{
MsgHelper.ShowErr("请选择一条数据！");
return;
}
FrmDBConfigSet_D frm = new FrmDBConfigSet_D(drSelect);
if (frm.ShowDialog() == DialogResult.OK)
{
tsbQuery.PerformClick();
}
}
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 btnLinkServer_Click(object sender, EventArgs e)
{
try
{
_dbServer = uC_DbConnection1.GetDbServerInfo();
if (_dbServer == null)
{
return;
}
_entiyBiz = new AutoEntityBiz(_dbServer.DatabaseType, _dbServer);
//加载数据库
LoadTreeView(_dbServer, _entiyBiz);
dgvEntityInfo.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
tabControl1.SelectedTab = tpgEntity;
}
catch (Exception exception)
{
MsgHelper.ShowErr(exception.Message);
}
}
private void tvDataBaseInfo_MouseUp(object sender, MouseEventArgs e)
{
try
{
if (treeNode != null)
{
if ((treeNode.Node.Level == 1) || (treeNode.Node.Level == 2))
{
if (treeNode.Node.IsSelected && (treeNode.Node.Level == 1))
{
if (treeNode.Node.Nodes.Count > 0)
{
tsmiStable.Visible = false;
tsmiFtable.Visible = true;
}
else
{
ctxmBuilt.Visible = false;
}
}
else
{
tsmiFtable.Visible = false;
tsmiStable.Visible = true;
}
}
else
{
ctxmBuilt.Visible = false;
}
}
}
catch (Exception exception)
{
MsgHelper.ShowErr(exception.Message);
}
}
private void tsbImport_Click(object sender, EventArgs e)
{
try
{
dsExcel = new DataSet();
#region 扩展说明变更
//表清单
_strMainSql = @"SELECT 表名,列名,扩展属性说明
FROM [表列扩展属性说明$] where 表名 is not null order by [表名]";
//错误提示信息
_strErr = @"请确定工作表包括名为“表列扩展属性说明”。其中“变更表清单”包括列“表名,列名,扩展说明”。";
#endregion
OpenFileDialog opd = new OpenFileDialog();
//opd.Filter = "Excel文件(*.xls,*.xlsx)|*.xls;*.xlsx"; //支持2003、2007以上格式的Excel
opd.Filter = "Excel文件(*.xlsx)|*.xlsx"; //只支持2007以上格式的Excel
opd.FilterIndex = 0;
opd.Title = "选择对应类型的导入模板Excel文件";
opd.RestoreDirectory = false;
if (DialogResult.Cancel == opd.ShowDialog())
{
return;
}
string sFilePath = opd.FileName;
string[] strFileNam = sFilePath.Split('.');
#region 导入模板后处理
string strFileFormart = strFileNam[strFileNam.Length - 1].ToString().ToLower();
if (strFileFormart == "xls")
{
_DBConnString = @"Provider=Microsoft.jet.OleDb.4.0;Data Source=" + sFilePath + ";Extended Properties='Excel 8.0;IMEX=1'";
}
else if (strFileFormart == "xlsx")
{
_DBConnString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + sFilePath + "; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
}
using (OleDbConnection con = new OleDbConnection(_DBConnString))
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
try
{
OleDbDataAdapter daTable = new OleDbDataAdapter(_strMainSql, con);
//打开连接并填充表
daTable.Fill(dsExcel, _strTableName);
bsTable.DataSource = dsExcel.Tables[_strTableName];
dgvTableList.DataSource = bsTable;
}
catch (Exception ex)
{
MsgHelper.ShowErr(ex.Message);
return;
}
}
#endregion
//导入成功后处理
tsbAutoSQL.Enabled = true;
tabControl1.SelectedTab = tpImport;
//导入成功提示
lblInfo.Text = _strImportSuccess;
}
catch (Exception ex)
{
MsgHelper.ShowErr(ex.Message);
}
}
public DbServerInfo GetDbServerInfo()
{
var DbServer = new DbServerInfo()
{
Database = txbDbName.Text.Trim(),
DatabaseType = (DataBaseType)int.Parse(cbbDatabaseType.SelectedValue.ToString()),
LoginMode = (cbbLoginType.SelectedValue == null || cbbLoginType.SelectedValue.ToString() == "1") ? LoginModeEnum.SQL : LoginModeEnum.Windows,
Password = txbPassword.Text.Trim(),
PortNo = txbPortNO.Text.Trim(),
SchemaName = txbSchemaName.Text.Trim(),
ServerName = txbServerIP.Text.Trim(),
UserName = txbUserName.Text.Trim(),
};
int iDbType = int.Parse(cbbDatabaseType.SelectedValue.ToString());
DataBaseType selectDBType = (DataBaseType)iDbType;
if (IsDbNameNotNull && string.IsNullOrEmpty(DbServer.Database))
{
MsgHelper.ShowErr("数据库名称不能为空！");
return(null);
}
switch (selectDBType)
{
case DataBaseType.SqlServer:
if (string.IsNullOrEmpty(DbServer.ServerName))
{
MsgHelper.ShowErr("服务器地址不能为空！");
return(null);
}
if (DbServer.LoginMode == LoginModeEnum.SQL)
{
if (string.IsNullOrEmpty(DbServer.UserName) || string.IsNullOrEmpty(DbServer.Password))
{
MsgHelper.ShowErr("用户名和密码都不能为空！");
return(null);
}
}
//显示登录类型
lblLoginType.Visible = true;
cbbLoginType.Visible = true;
break;
case DataBaseType.Oracle:
if (string.IsNullOrEmpty(DbServer.ServerName))
{
MsgHelper.ShowErr("TNS名称不能为空！");
return(null);
}
if (string.IsNullOrEmpty(DbServer.UserName) || string.IsNullOrEmpty(DbServer.Password))
{
MsgHelper.ShowErr("用户名和密码都不能为空！");
return(null);
}
break;
case DataBaseType.MySql:
if (string.IsNullOrEmpty(DbServer.ServerName))
{
MsgHelper.ShowErr("服务器地址不能为空！");
return(null);
}
if (string.IsNullOrEmpty(DbServer.UserName) || string.IsNullOrEmpty(DbServer.Password))
{
MsgHelper.ShowErr("用户名和密码都不能为空！");
return(null);
}
break;
case DataBaseType.SQLite:
if (string.IsNullOrEmpty(DbServer.ServerName))
{
MsgHelper.ShowErr("数据库文件路径不能为空！");
return(null);
}
break;
case DataBaseType.PostgreSql:
if (string.IsNullOrEmpty(DbServer.ServerName))
{
MsgHelper.ShowErr("服务器地址不能为空！");
return(null);
}
if (string.IsNullOrEmpty(DbServer.UserName) || string.IsNullOrEmpty(DbServer.Password))
{
MsgHelper.ShowErr("用户名和密码都不能为空！");
return(null);
}
break;
default:
throw new Exception("暂不支持该数据库类型！");
//break;
}
//得到数据库访问对象
CustomDac = new CustomDataAccess(selectDBType, DbServer);
UserTableList = CustomDac.DataAccess.GetUserTableList();//所有用户表
//返回
return(DbServer);
}
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 LoadTreeView(DbServerInfo server, AutoEntityBiz entiyBiz)
{
string collectionName = DbSchemaString.Databases;
string[] restrictionValues = null;
switch (server.DatabaseType)
{
case DataBaseType.SqlServer:
collectionName = DbSchemaString.Databases;
break;
case DataBaseType.Oracle:
collectionName = DbSchemaString.Users;
break;
case DataBaseType.MySql:
collectionName = DbSchemaString.Databases;
break;
case DataBaseType.SQLite:
collectionName = DbSchemaString.Tables; //对SQLite，只有唯一的Main数据库，没有数据库清单表
break;
case DataBaseType.PostgreSql:
collectionName = DbSchemaString.Databases;
break;
default:
break;
}
try
{
DataTable schema = entiyBiz.GetSchema(collectionName, null);
tvDataBaseInfo.Nodes.Clear();
TreeNode node = new TreeNode();
node.ToolTipText = "请您点选后，在按鼠标右键";
node.Text = server.ServerName;
for (int i = 0; i < schema.Rows.Count; i++)
{
TreeNode node2 = new TreeNode();
string str2 = schema.Rows[i][0].ToString();
if (server.DatabaseType == DataBaseType.MySql)
{
str2 = schema.Rows[i][1].ToString();
}
server.Database = str2;
if (server.DatabaseType == DataBaseType.Oracle)
{
restrictionValues = new string[] { str2 };
}
else if (server.DatabaseType == DataBaseType.MySql)
{
string[] strArray2 = new string[2];
strArray2[1] = str2;
restrictionValues = strArray2;
}
DataTable table2 = entiyBiz.GetSchema("Tables", restrictionValues);
DataView dv = table2.DefaultView;
table2.DefaultView.Sort = "TABLE_NAME ASC";
for (int j = 0; j < dv.Count; j++)
{
string str3 = "";
if (server.DatabaseType == DataBaseType.Oracle)
{
str3 = dv[j][1].ToString();
}
else
{
str3 = dv[j][2].ToString();
}
TreeNode node3 = new TreeNode();
node3.ToolTipText = "请您点选后，在按鼠标右键";
node3.Text = str3;
node2.Nodes.Add(node3);
}
node2.ToolTipText = "请您点选后，在按鼠标右键";
node2.Text = str2;
node.Nodes.Add(node2);
//对于SQLite，终止循环
if (server.DatabaseType == DataBaseType.SQLite)
{
break;
}
}
tvDataBaseInfo.Nodes.Add(node);
//展开默认的数据库表树
string strDefuatExpandDBName = server.Database;
foreach (TreeNode tnode in tvDataBaseInfo.Nodes)
{
ExpandDefaultDB(tnode, strDefuatExpandDBName);
}
}
catch (Exception exception)
{
MsgHelper.ShowErr(exception.Message);
}
}
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 btnImportAutoFile_Click(object sender, EventArgs e)
{
string strLayerNameSql = @"SELECT 分层类型,项目分层简称,项目分层全称
FROM [项目分层清单$] where [序号] is not null order by [序号]";
string strFileListSql = @"SELECT 分层类型,项目分层全称,文件目录相对路径,类名
FROM [目录及类名$] where [序号] is not null and 类名 is not null order by [序号]";
string _DBConnString = "";
#region 打开显示选择文件对话框，获取导入的文件名
OpenFileDialog opd = new OpenFileDialog();
opd.Filter = "Excel文件(*.xls,*.xlsx)|*.xls;*.xlsx"; //支持2003、2007以上格式的Excel
//opd.Filter = "Excel文件(*.xlsx)|*.xlsx"; //只支持2007以上格式的Excel
opd.FilterIndex = 0;
opd.Title = "请选择“生成IBD文件导入模板”";
opd.RestoreDirectory = false;
if (DialogResult.Cancel == opd.ShowDialog())
{
return;
}
string sFilePath = opd.FileName;
string[] strFileNam = sFilePath.Split('.');
#endregion
#region 导入模板后处理
string strFileFormart = strFileNam[strFileNam.Length - 1].ToString().ToLower();
if (strFileFormart == "xls")
{
_DBConnString = @"Provider=Microsoft.jet.OleDb.4.0;Data Source=" + sFilePath + ";Extended Properties='Excel 8.0;IMEX=1'";
}
else if (strFileFormart == "xlsx")
{
_DBConnString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + sFilePath + "; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";
}
using (OleDbConnection con = new OleDbConnection(_DBConnString))
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
try
{
dsExcel = new DataSet();
BindingSource bsLayerName = new BindingSource();
BindingSource bsFileList = new BindingSource();
OleDbDataAdapter daLayerName = new OleDbDataAdapter(strLayerNameSql, con);
OleDbDataAdapter daFileList = new OleDbDataAdapter(strFileListSql, con);
//打开连接并填充表
daLayerName.Fill(dsExcel, _strLayerList);
daFileList.Fill(dsExcel, _strFileList);
bsLayerName.DataSource = dsExcel.Tables[_strLayerList];
dgvLayerName.DataSource = bsLayerName;
bsFileList.DataSource = dsExcel.Tables[_strFileList];
dgvIBDFileList.DataSource = bsFileList;
}
catch (Exception ex)
{
MsgHelper.ShowErr(ex.Message);
}
}
#endregion
}
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 tsbImport_Click(object sender, EventArgs e)
{
try
{
dsExcel = new DataSet();
_dbServer = uC_DbConnection1.GetDbServerInfo();
DataTable dtMain;
DataTable dtSec;
if (_dbServer == null)
{
return;
}
string sTableName = cbbTableName.Text.Trim();
#region 读取数据库的表数据生成SQL处理
#region 确定SQL
if (_dbServer.DatabaseType == DataBaseType.SqlServer)
{
#region SqlServer读取数据库
if (!string.IsNullOrEmpty(_dbServer.SchemaName)) //有架构名时
{
//查询表
_strMainSql = string.Format(
@"SELECT B.NAME OWNER,A.NAME TABLE_NAME
FROM SYS.OBJECTS A
JOIN SYS.SCHEMAS B ON A.SCHEMA_ID=B.SCHEMA_ID
WHERE A.TYPE='U' AND A.NAME='{0}' AND B.NAME='{1}'"
, sTableName, _dbServer.SchemaName);
//查询表的所有列（不要的列在界面上删除）
_strSecondSql = string.Format(
@"SELECT A.COLID COLUMN_ID,A.NAME COLUMN_NAME,'' 固定值, '' 辅助查询值,
(SELECT TOP 1 NAME FROM SYS.TYPES WHERE USER_TYPE_ID = A.XUSERTYPE) DATA_TYPE,
A.LENGTH DATA_LENGTH,A.XPREC DATA_PRECISION,A.XSCALE DATA_SCALE,A.ISNULLABLE NULLABLE,
C.NAME AS OWNER,
B.NAME TABLE_NAME,A.COLSTAT
FROM SYSCOLUMNS A
JOIN (SELECT * FROM SYS.OBJECTS WHERE TYPE='U' AND NAME='{0}') B ON A.ID=B.OBJECT_ID
JOIN SYS.SCHEMAS C ON C.SCHEMA_ID=B.SCHEMA_ID
WHERE C.NAME='{1}'
ORDER BY A.COLID", sTableName, _dbServer.SchemaName);
}
else
{
//查询表
_strMainSql = string.Format(
@"SELECT B.NAME OWNER,A.NAME TABLE_NAME
FROM SYS.OBJECTS A
JOIN SYS.SCHEMAS B ON A.SCHEMA_ID=B.SCHEMA_ID WHERE A.TYPE='U' AND A.NAME='{0}'"
, sTableName);
//查询表的所有列（不要的列在界面上删除）
_strSecondSql = string.Format(
@"SELECT A.COLID COLUMN_ID,A.NAME COLUMN_NAME,'' 固定值, '' 辅助查询值,
(SELECT TOP 1 NAME FROM SYS.TYPES WHERE USER_TYPE_ID = A.XUSERTYPE) DATA_TYPE,
A.LENGTH DATA_LENGTH,A.XPREC DATA_PRECISION,A.XSCALE DATA_SCALE,A.ISNULLABLE NULLABLE,
(SELECT TOP 1 NAME FROM SYS.SCHEMAS WHERE SCHEMA_ID=B.SCHEMA_ID) OWNER,
B.NAME TABLE_NAME,A.COLSTAT
FROM SYSCOLUMNS A
JOIN (SELECT * FROM SYS.OBJECTS WHERE TYPE='U' AND NAME='{0}') B ON A.ID=B.OBJECT_ID
ORDER BY A.COLID",
sTableName);
}
#endregion
}
else if (_dbServer.DatabaseType == DataBaseType.Oracle)
{
#region Oracle读取数据库
if (!string.IsNullOrEmpty(_dbServer.SchemaName)) //当输入架构名称时
{
//查询表
_strMainSql = string.Format(
@"SELECT A.OWNER,A.TABLE_NAME
FROM ALL_TABLES A
WHERE UPPER(A.TABLE_NAME)=UPPER('{0}') AND UPPER(A.OWNER)=UPPER('{1}')",
sTableName, _dbServer.SchemaName);
//查询所有列
_strSecondSql = string.Format(
@"SELECT A.COLUMN_ID,A.COLUMN_NAME,'' 固定值,'' 辅助查询值,A.DATA_TYPE,A.DATA_LENGTH,
A.DATA_PRECISION,A.DATA_SCALE,A.NULLABLE,A.OWNER,A.TABLE_NAME
FROM ALL_TAB_COLS A
WHERE UPPER(A.TABLE_NAME)=UPPER('{0}') AND UPPER(A.OWNER)=UPPER('{1}')
ORDER BY A.COLUMN_ID", sTableName, _dbServer.SchemaName);
}
else
{
//查询表
_strMainSql = string.Format(
@"SELECT A.OWNER,A.TABLE_NAME
FROM ALL_TABLES A
WHERE UPPER(A.TABLE_NAME)=UPPER('{0}')",
sTableName);
//查询所有列
_strSecondSql = string.Format(
@"SELECT A.COLUMN_ID,A.COLUMN_NAME,'' 固定值,'' 辅助查询值,A.DATA_TYPE,A.DATA_LENGTH,
A.DATA_PRECISION,A.DATA_SCALE,A.NULLABLE,A.OWNER,A.TABLE_NAME
FROM ALL_TAB_COLS A WHERE UPPER(TABLE_NAME)=UPPER('{0}') ORDER BY A.COLUMN_ID",
sTableName);
}
#endregion
}
else if (_dbServer.DatabaseType == DataBaseType.MySql)
{
#region Mariadb读取数据库
if (string.IsNullOrEmpty(_dbServer.Database))
{
MsgHelper.ShowErr("数据库名不能为空！");
return;
}
if (string.IsNullOrEmpty(sTableName))
{
MsgHelper.ShowErr("表名不能为空！");
cbbTableName.Focus();
return;
}
//查询表
_strMainSql = string.Format(
@"SHOW TABLES
WHERE TABLES_IN_" + _dbServer.Database + " = LOWER('{0}')",
sTableName);
//查询所有列
_strSecondSql = string.Format(
@"SHOW COLUMNS FROM {0}",
sTableName);
#endregion
}
else if (_dbServer.DatabaseType == DataBaseType.PostgreSql)
{
#region PostgreSql读取数据库
if (!string.IsNullOrEmpty(_dbServer.SchemaName)) //当输入架构名称时
{
//查询表
_strMainSql = string.Format(
@"SELECT A.SCHEMANAME AS OWNER,A.TABLENAME AS TABLE_NAME
FROM PG_TABLES A
WHERE 1=1
AND UPPER(A.TABLENAME)=UPPER('{0}') AND UPPER(A.SCHEMANAME)=UPPER('{1}')",
sTableName, _dbServer.SchemaName);
//查询所有列
_strSecondSql = string.Format(
@" SELECT A.ORDINAL_POSITION as COLUMN_ID,a.COLUMN_NAME,'' 固定值,'' 辅助查询值,a.DATA_TYPE,
A.CHARACTER_MAXIMUM_LENGTH as DATA_LENGTH,A.NUMERIC_PRECISION as DATA_PRECISION,
A.NUMERIC_SCALE as DATA_SCALE,A.IS_NULLABLE as NULLABLE,A.TABLE_SCHEMA as owner,A.TABLE_NAME as TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS A
LEFT JOIN (SELECT C.ATTNAME AS COLUMN_NAME
FROM PG_CONSTRAINT A
JOIN PG_CLASS B
ON A.CONRELID = B.OID
JOIN PG_ATTRIBUTE C
ON C.ATTRELID = B.OID
AND C.ATTNUM = A.CONKEY[1]
JOIN PG_TYPE D
ON D.OID = C.ATTTYPID
WHERE UPPER(B.RELNAME) = UPPER('{0}')
AND A.CONTYPE = 'p') PK ON A.COLUMN_NAME = PK.COLUMN_NAME
WHERE UPPER(A.TABLE_SCHEMA)=UPPER('{1}')
AND UPPER(A.TABLE_NAME)=UPPER('{0}')
ORDER BY A.ORDINAL_POSITION",
sTableName, _dbServer.SchemaName);
}
else
{
//查询表
_strMainSql = string.Format(@"SELECT A.SCHEMANAME AS OWNER,A.TABLENAME AS TABLE_NAME
FROM PG_TABLES A
WHERE 1=1
AND UPPER(A.TABLENAME)=UPPER('{0}')",
sTableName);
//查询所有列
_strSecondSql = string.Format(
@" SELECT A.ORDINAL_POSITION AS COLUMN_ID,A.COLUMN_NAME,'' 固定值,'' 辅助查询值,A.DATA_TYPE,
A.CHARACTER_MAXIMUM_LENGTH AS DATA_LENGTH,A.NUMERIC_PRECISION AS DATA_PRECISION,
A.NUMERIC_SCALE AS DATA_SCALE,A.IS_NULLABLE AS NULLABLE,A.TABLE_SCHEMA AS OWNER,A.TABLE_NAME AS TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS A
LEFT JOIN (SELECT C.ATTNAME AS COLUMN_NAME
FROM PG_CONSTRAINT A
JOIN PG_CLASS B
ON A.CONRELID = B.OID
JOIN PG_ATTRIBUTE C
ON C.ATTRELID = B.OID
AND C.ATTNUM = A.CONKEY[1]
JOIN PG_TYPE D
ON D.OID = C.ATTTYPID
WHERE UPPER(B.RELNAME) = UPPER('{0}')
AND A.CONTYPE = 'P') PK ON A.COLUMN_NAME = PK.COLUMN_NAME
WHERE UPPER(A.TABLE_SCHEMA)=UPPER('PUBLIC')
AND UPPER(A.TABLE_NAME)=UPPER('{0}')
ORDER BY A.ORDINAL_POSITION",
sTableName);
}
#endregion
}
else if (_dbServer.DatabaseType == DataBaseType.SQLite)
{
#region SQLite读取数据库
//查询表:type,name,TBL_NAME,rootpage,sql
_strMainSql = string.Format(
@"SELECT TBL_NAME AS TABLE_NAME
FROM SQLITE_MASTER
WHERE UPPER(TYPE)= 'TABLE' AND UPPER(NAME)= ('{0}')",
sTableName);
//查询所有列:cid,name,type,notnull,dflt_value,pk
_strSecondSql = string.Format(
@"PRAGMA TABLE_INFO('{0}')",
sTableName);
#endregion
}
else
{
throw new Exception("暂不支持该数据库类型！");
}
#endregion
_dataAccess = new CustomDataAccess(_dbServer.DatabaseType, _dbServer);
_dicQueryCondition.Clear();
#region 查询或构造表
if (_dbServer.DatabaseType == DataBaseType.MySql || _dbServer.DatabaseType == DataBaseType.SQLite)
{
DataTable dtMainTemp = _dataAccess.DataAccess.QueryHadParamSqlData(_strMainSql, _dicQueryCondition);
DataTable dtSecTemp = _dataAccess.DataAccess.QueryHadParamSqlData(_strSecondSql, _dicQueryCondition);
if (dtMainTemp.Rows.Count == 0)
{
MsgHelper.ShowErr("表不存在！");
return;
}
//生成表和列
GenerateTableColumn(out dtMain, out dtSec);
dtMain.Rows[0]["TABLE_NAME"] = dtMainTemp.Rows[0][0];
int i = 1;
foreach (DataRow row in dtSecTemp.Rows)
{
DataRow drNew = dtSec.NewRow();
if (_dbServer.DatabaseType == DataBaseType.MySql)
{
drNew["COLUMN_ID"] = i;
drNew["COLUMN_NAME"] = row["Field"];
drNew["DATA_TYPE"] = row["Type"];
drNew["NULLABLE"] = row["Null"];
}
else
{
drNew["COLUMN_ID"] = row["cid"];
drNew["COLUMN_NAME"] = row["name"];
drNew["DATA_TYPE"] = row["type"];
drNew["NULLABLE"] = row["notnull"];
}
dtSec.Rows.Add(drNew);
i++;
}
}
else
{
dtMain = _dataAccess.DataAccess.QueryHadParamSqlData(_strMainSql, _dicQueryCondition);
dtSec = _dataAccess.DataAccess.QueryHadParamSqlData(_strSecondSql, _dicQueryCondition);
}
#endregion
dtMain.TableName = _strTableName;
bsTable.DataSource = dtMain;
dtSec.TableName = _strColName;
bsCos.DataSource = dtSec;
#region SqlServer自增长列处理
if (_dbServer.DatabaseType == DataBaseType.SqlServer)
{
if (dtSec.Select("COLSTAT>0").Length > 0)
{
ckbMainKeyInsert.Enabled = true;
}
else
{
ckbMainKeyInsert.Enabled = false;
}
}
else
{
ckbMainKeyInsert.Visible = false; //不可见
}
#endregion
//设置数据源
GlobalValue.Instance.SetPublicDataSource(new DataTable[] { dtMain, dtSec });
dgvTableList.DataSource = bsCos;
//dgvColList.DataSource = null;
//设置网格样式
bsCos.AllowNew = false;
foreach (DataGridViewColumn dgvc in dgvTableList.Columns)
{
dgvc.ReadOnly = true;
if (dgvc.Name == "固定值" || dgvc.Name == "辅助查询值")
{
dgvc.ReadOnly = false;
}
}
#endregion
//导入成功后处理
tsbAutoSQL.Enabled = true;
tsbExport.Enabled = true;
tabControl1.SelectedTab = tpImport;
//导入成功提示
lblInfo.Text = _strImportSuccess;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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 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();
}
