/// <summary>执行
/// 执行
/// </summary>
/// <param name="sender">sender</param>
/// <param name="e">e</param>
private void btnRun_Click(object sender, EventArgs e)
{
if (CheckInput())
{
return;
}
if (CheckDesignTable() == false)
{
return;
}
DataTable dtSource = (grdTable.DataSource as DataTable);
if (dtSource == null)
{
return;
}
DataTable dtChanges = dtSource.GetChanges();
if (dtChanges == null)
{
return;
}
try
{
if (IsNew)
{
//第一次执行生成表名
string strSqlAddTable = AddTableScript(m_databaseSchema).Replace("GO", "");
m_dbHelper.CreateCommand(strSqlAddTable + " ; SELECT 'OK'");
DataTable dtResult = m_dbHelper.ExecuteQuery();
if (dtResult != null && dtResult.Rows.Count != 1)
{
MessageBox.Show("新建失败");
return;
}
else
{
m_sda.Update(dtChanges);
dtSource.AcceptChanges();
m_strTableName = txtTableName.Text.Trim();
//切换成编辑模式
IsNew = false;
DatabaseTable databaseTable = m_databaseSchema.Tables.Where(t => t.Name == m_strTableName).FirstOrDefault();
TreeNode tableRoot = TreeViewHelper.FindTableNode(GlobalHelp.TreeView.Nodes, m_strServer, m_strDbName, "表");
if (tableRoot != null)
{
//及时刷新TreeView
SchemaToTreeview.FillOneTable(databaseTable, tableRoot, 2);
}
MessageBox.Show("新建成功！");
}
}
}
catch (Exception)
{
throw;
}
}
public static DataSet GetAllViews(SqlType dbType, string strConn, DataTable dtDbNames)
{
IDBHelper dbHelper = GetDbHelper(dbType, strConn);
string strSql = string.Empty;
switch (dbType)
{
case SqlType.SqlServer:
foreach (DataRow dr in dtDbNames.Rows)
{
string strDbName = dr[0].ToString();
//strSql += " USE [" + strDBName + "] SELECT '" + strDBName + "' as DBName, name FROM dbo.sysobjects where OBJECTPROPERTY(id, N'IsView') = 1 ";
strSql += " USE [" + strDbName + "] SELECT '" + strDbName + "' as DBName, Name ,object_definition(OBJECT_ID) AS ObjectContent ,'VIEW' AS Type FROM sys.views ORDER BY Name ";
}
break;
case SqlType.MySql:
break;
case SqlType.Oracle:
break;
}
dbHelper.CreateCommand(strSql);
return(dbHelper.ExecuteQueryDataSet());
}
/// <summary>判断数据库对象是否存在
/// 判断数据库对象是否存在
/// </summary>
/// <param name="dbType">dbType</param>
/// <param name="strConn">strConn</param>
/// <param name="strObjectName">strObjectName</param>
/// <returns>true:存在，false:不存在</returns>
public static bool DbObjectExists(SqlType dbType, string strConn, string strObjectName)
{
bool flag = false;
string strSql = string.Empty;
IDBHelper dbHelper = GetDbHelper(dbType, strConn);
switch (dbType)
{
case SqlType.SqlServer:
strSql = string.Format("SELECT 1 FROM sys.objects WHERE object_id=OBJECT_ID('{0}')", strObjectName);
break;
case SqlType.MySql:
break;
case SqlType.Oracle:
break;
}
if (strSql != string.Empty)
{
dbHelper.CreateCommand(strSql);
flag = !string.IsNullOrEmpty(dbHelper.ExecuteScalar());
}
return(flag);
}
private void FrmTableRelationView_Load(object sender, EventArgs e)
{
IDBHelper helper = DataBaseManager.GetDbHelper(SqlType.SqlServer, GlobalHelp.ConfigConn);
helper.CreateCommand("Select * from TableView order by ParentID");
DataTable dt = helper.ExecuteQuery();
dataGridView1.DataSource = dt;
if (dt.Rows.Count == null || dt.Rows.Count == 0)
{
return;
}
cTreeView1.DrawStyle = CTreeViewDrawStyle.VerticalDiagram;
cTreeView1.ShowRootLines = false;
DataTable dtParent = dt.Select("ParentId=0").CopyToDataTable();
for (int i = 0; i < dtParent.Rows.Count; i++)
{
CTreeNode treeNode = new CTreeNode(new Label());
((Label)treeNode.Control).Text = dtParent.Rows[i]["TableName"].ToString();
((Label)treeNode.Control).Width = 100;
((Label)treeNode.Control).AutoSize = true;
((Label)treeNode.Control).TextAlign = ContentAlignment.MiddleCenter;
cTreeView1.Nodes.Add(treeNode);
AddNode(dt, int.Parse(dtParent.Rows[i]["Id"].ToString()), treeNode);
}
//cTreeView1.Nodes.Add(new CTreeNode(new Label()));
//((Label)cTreeView1.Nodes[0].Control).Text = "附属设施关系";
//((Label)cTreeView1.Nodes[0].Control).Width = 100;
//((Label)cTreeView1.Nodes[0].Control).AutoSize = true;
//((Label)cTreeView1.Nodes[0].Control).TextAlign = ContentAlignment.MiddleCenter;
//cTreeView1.Nodes[0].Nodes.Add(new CTreeNode(new Label()));
//((Label)cTreeView1.Nodes[0].Nodes[0].Control).Text = "RuleId_DetailId";
//((Label)cTreeView1.Nodes[0].Nodes[0].Control).Width = 100;
//((Label)cTreeView1.Nodes[0].Nodes[0].Control).AutoSize = true;
//((Label)cTreeView1.Nodes[0].Nodes[0].Control).TextAlign = ContentAlignment.MiddleCenter;
////下面添加两个GridView
//cTreeView1.Nodes[0].Nodes[0].Nodes.Add(new CTreeNode(new DataGridView()));
//((DataGridView)cTreeView1.Nodes[0].Nodes[0].Nodes[0].Control).Columns.Add("1", "1");
//((DataGridView)cTreeView1.Nodes[0].Nodes[0].Nodes[0].Control).Columns.Add("2", "2");
//((DataGridView)cTreeView1.Nodes[0].Nodes[0].Nodes[0].Control).Rows.Add(new string[] { "a", "b" });
//cTreeView1.Nodes[0].Nodes[0].Nodes.Add(new CTreeNode(new DataGridView()));
//((DataGridView)cTreeView1.Nodes[0].Nodes[0].Nodes[1].Control).Columns.Add("1", "1");
//((DataGridView)cTreeView1.Nodes[0].Nodes[0].Nodes[1].Control).Columns.Add("2", "2");
//((DataGridView)cTreeView1.Nodes[0].Nodes[0].Nodes[1].Control).Rows.Add(new string[] { "a", "b" });
//cTreeView1.Nodes[0].Nodes[0].Nodes[1].Nodes.Add(new CTreeNode(new Label()));
//((Label)cTreeView1.Nodes[0].Nodes[0].Nodes[1].Nodes[0].Control).Text = "RuleId_DetailId";
//((Label)cTreeView1.Nodes[0].Nodes[0].Nodes[1].Nodes[0].Control).Width = 100;
//((Label)cTreeView1.Nodes[0].Nodes[0].Nodes[1].Nodes[0].Control).AutoSize = true;
//((Label)cTreeView1.Nodes[0].Nodes[0].Nodes[1].Nodes[0].Control).TextAlign = ContentAlignment.MiddleCenter;
}
private DataSet Run(DatabaseSchema dbSchema, string strSql)
{
string strConn = dbSchema.ConnectionString;
m_dbHelper = DataBaseManager.GetDbHelper(m_serverTag.DBType, strConn);
m_dbHelper.CreateCommand(strSql);
DataSet ds = m_ds = m_dbHelper.ExecuteQueryDataSet();
return(ds);
}
public static DataTable GetDataDictionary(SqlType dbType, string strConn)
{
IDBHelper dbHelper = GetDbHelper(dbType, strConn);
string strSql = string.Empty;
switch (dbType)
{
case SqlType.SqlServer:
strSql = "SELECT 表名= case when a.colorder=1 then d.name else '' end, 表说明= case when a.colorder=1 then isnull(f.value,'') else '' end, 字段序号= a.colorder, 字段名 = a.name, 标识= case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, 主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end, 类型 = b.name, 占用字节数 = a.length, 长度= COLUMNPROPERTY(a.id,a.name,'PRECISION'), 小数位数= isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 允许空= case when a.isnullable=1 then '√'else '' end, 默认值= isnull(e.text,''), 字段说明= isnull(g.[value],'') FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' and d.name<>'sysdiagrams' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 order by a.id,a.colorder ";
break;
}
dbHelper.CreateCommand(strSql);
return(dbHelper.ExecuteQuery());
}
private void btnRun_Click(object sender, EventArgs e)
{
if (cboServer.Text == string.Empty)
{
MessageBox.Show("请选择要执行的服务器");
return;
}
if (txtSql.Text.Trim() == string.Empty)
{
MessageBox.Show("请选择要执行的语句");
return;
}
TreeNode treeNode = tvwAnalyze.SelectedNode;
DataRow dr = treeNode.Tag as DataRow;
if (dr != null)
{
string strDbType = dr["DbType"].ToString();
string strExcuteType = dr["ExcuteType"].ToString();
TreeNode treeNodeFind = FindTreeNodeByName(cboServer.Text.Trim());
ServerTag serverTag = treeNodeFind.Tag as ServerTag;
if (serverTag != null)
{
SqlType dbType = GetSqlType(strDbType);
if (serverTag.DBType != dbType)
{
MessageBox.Show("选中的语句不支持在" + serverTag.DBType + "服务器下执行");
return;
}
IDBHelper dbHelper = DataBaseManager.GetDbHelper(dbType, serverTag.MasterConn);
switch (strExcuteType)
{
case "SQL语句":
dbHelper.CreateCommand(txtSql.Text.Trim());
break;
case "存储过程":
dbHelper.CreateStoredCommand(txtSql.Text.Trim());
break;
}
DataSet ds = dbHelper.ExecuteQueryDataSet();
if (ds.Tables.Count > 0)
{
grdResult.DataSource = ds.Tables[0];
}
}
}
}
/// <summary>获取服务器下所有库的表/视图/存储过程/函数 的名称，内容，类型等信息表
/// 获取服务器下所有库的表/视图/存储过程/函数 的名称，内容，类型等信息表
/// </summary>
/// <param name="dbType">dbType</param>
/// <param name="strConn">strConn</param>
/// <param name="dtDbNames">dtDBNames</param>
/// <param name="objType">objType</param>
/// <returns>DataSet</returns>
public static DataSet GetAllDbObject(SqlType dbType, string strConn, DataTable dtDbNames, DatabaseObjectType objType)
{
IDBHelper dbHelper = GetDbHelper(dbType, strConn);
string strSql = string.Empty;
switch (dbType)
{
case SqlType.SqlServer:
strSql = GetSqlServerObjectSqls(dtDbNames, objType);
break;
case SqlType.MySql:
break;
case SqlType.Oracle:
break;
}
dbHelper.CreateCommand(strSql);
return(dbHelper.ExecuteQueryDataSet());
}
public static DataTable GetTriggerss(SqlType dbType, string strConn, string strDbName)
{
IDBHelper dbHelper = GetDbHelper(dbType, strConn);
string strSql = string.Empty;
switch (dbType)
{
case SqlType.SqlServer:
strSql = " USE [" + strDbName + "] SELECT '" + strDbName + "' as DBName NAME ,object_definition(ID) AS ObjectContent, 'TRIGGER' AS Type FROM sysobjects WHERE TYPE='TR' ORDER BY Name ";
break;
case SqlType.MySql:
break;
case SqlType.Oracle:
break;
}
dbHelper.CreateCommand(strSql);
return(dbHelper.ExecuteQuery());
}
public static DataTable GetTables(SqlType dbType, string strConn, string strDbName)
{
IDBHelper dbHelper = GetDbHelper(dbType, strConn);
string strSql = string.Empty;
switch (dbType)
{
case SqlType.SqlServer:
strSql = " USE [" + strDbName + "] SELECT '" + strDbName + "' as DBName, name , 'Table' as Type FROM dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 ORDER BY Name ";
break;
case SqlType.MySql:
break;
case SqlType.Oracle:
break;
}
dbHelper.CreateCommand(strSql);
return(dbHelper.ExecuteQuery());
}
public static DataTable GetFunctions(SqlType dbType, string strConn, string strDbName)
{
IDBHelper dbHelper = GetDbHelper(dbType, strConn);
string strSql = string.Empty;
switch (dbType)
{
case SqlType.SqlServer:
strSql = " USE [" + strDbName + "] SELECT '" + strDbName + "' as DBName,name , object_definition(ID) AS ObjectContent,'FUNCTION' as Type FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY name ";
break;
case SqlType.MySql:
break;
case SqlType.Oracle:
break;
}
dbHelper.CreateCommand(strSql);
return(dbHelper.ExecuteQuery());
}
public static DataTable GetViews(SqlType dbType, string strConn, string strDbName)
{
IDBHelper dbHelper = GetDbHelper(dbType, strConn);
string strSql = string.Empty;
switch (dbType)
{
case SqlType.SqlServer:
//strSql = " USE [" + strDBName + "] SELECT * FROM dbo.sysobjects where OBJECTPROPERTY(id, N'IsView') = 1 ";
strSql = " USE [" + strDbName + "] SELECT '" + strDbName + "' as DBName, Name ,object_definition(OBJECT_ID) AS ObjectContent,'VIEW' as Type FROM sys.views ORDER BY Name ";
break;
case SqlType.MySql:
break;
case SqlType.Oracle:
break;
}
dbHelper.CreateCommand(strSql);
return(dbHelper.ExecuteQuery());
}
/// <summary>根据类型获取服务器的所有数据库
/// 根据类型获取服务器的所有数据库
/// </summary>
/// <param name="dbType">strDBType</param>
/// <param name="strConn">strConn</param>
/// <returns>表结构</returns>
public static DataTable GetDataBase(SqlType dbType, string strConn)
{
IDBHelper dbHelper = GetDbHelper(dbType, strConn);
string strSql = string.Empty;
switch (dbType)
{
case SqlType.SqlServer:
strSql = "SELECT name FROM Master.dbo.SysDatabases Where name not in ('master','model','msdb','tempdb') ";
break;
case SqlType.MySql:
strSql = " SELECT `SCHEMA_NAME` as name FROM `information_schema`.`SCHEMATA` where `SCHEMA_NAME` not in('information_schema','mysql','test')";
break;
case SqlType.Oracle:
break;
}
dbHelper.CreateCommand(strSql);
return(dbHelper.ExecuteQuery());
}
private DataSet Run(List <TreeNode> lstTreeNodeDb, string strSql)
{
foreach (TreeNode item in lstTreeNodeDb)
{
string strConn = item.Tag.ToString();
var serverTag = item.Parent.Tag as ServerTag;
if (serverTag != null)
{
m_dbHelper = DataBaseManager.GetDbHelper(serverTag.DBType, strConn);
}
m_dbHelper.CreateCommand(strSql);
DataSet ds = m_dbHelper.ExecuteQueryDataSet();
foreach (DataTable dt in ds.Tables)
{
if (dt != null)
{
DataTable dtNew = dt.Copy();
dtNew.TableName = Guid.NewGuid().ToString();
m_ds.Tables.Add(dtNew);
}
}
}
return(m_ds);
}
public static DataSet GetAllTriggers(SqlType dbType, string strConn, DataTable dtDbNames)
{
IDBHelper dbHelper = GetDbHelper(dbType, strConn);
string strSql = string.Empty;
switch (dbType)
{
case SqlType.SqlServer:
foreach (DataRow dr in dtDbNames.Rows)
{
string strDbName = dr[0].ToString();
strSql += " USE [" + strDbName + "] SELECT '" + strDbName + "' as DBName, NAME ,object_definition(ID) AS ObjectContent, 'TRIGGER' AS Type FROM sysobjects WHERE TYPE='TR' ORDER BY Name ";
}
break;
case SqlType.MySql:
break;
case SqlType.Oracle:
break;
}
dbHelper.CreateCommand(strSql);
return(dbHelper.ExecuteQueryDataSet());
}
public static DataSet GetAllTables(SqlType dbType, string strConn, DataTable dtDbNames)
{
IDBHelper dbHelper = GetDbHelper(dbType, strConn);
string strSql = string.Empty;
switch (dbType)
{
case SqlType.SqlServer:
foreach (DataRow dr in dtDbNames.Rows)
{
string strDbName = dr[0].ToString();
strSql += " USE [" + strDbName + "] SELECT '" + strDbName + "' as DBName, name ,'Table' as Type FROM dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 ORDER BY Name ";
}
break;
case SqlType.MySql:
break;
case SqlType.Oracle:
break;
}
dbHelper.CreateCommand(strSql);
return(dbHelper.ExecuteQueryDataSet());
}