using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Configuration; // ConfigurationManager
using System.Collections.Specialized;
using System.Collections; // Hashtable
using MySql.Data.MySqlClient;
using System.Data.Common;
namespace SQLMgtConsole.Database
{
public partial class MySQL : UserControl, IToolWindow
{
#region Common Interface
UserControl IToolWindow.View
{
get { return this; }
}
string IToolWindow.Name
{
get { return "MySQL"; }
}
string IToolWindow.Description
{
get { return "MySQL"; }
}
Image IToolWindow.Image
{
get { return null; }
}
// for IDE to get the data
public DataTable GetData(string strSelectedDB, string strQuery, ref Exception exErrHandle)
{
DataTable dtResult = new DataTable();
if (!strSelectedDB.Trim().Equals("") && !strQuery.Trim().Equals(""))
{
dtResult = dbfac.fnExecDBCommonQuery(m_strDBProvider,
fnCreateConnection(strSelectedDB),
strQuery, ref exErrHandle);
}
else
{
MessageBox.Show("No query executed");
exErrHandle = null;
dtResult = null;
}
return dtResult;
}
#endregion
#region Constants
private const string SERVICETYPE_DB = "Databases";
private const string SYSOBJ_FUNC = "Scalar function";
private const string SYSOBJ_PROC = "Stored procedure";
private const string SYSOBJ_SYSTBL = "System table";
//private const string SYSOBJ_TBLFUNC = "Table function";
private const string SYSOBJ_TRIG = "Trigger";
private const string SYSOBJ_USRTBL = "User table";
private const string SYSOBJ_VIEW = "View";
private const string SECURITY_USER = "Users";
//private const string SECURITY_ROLE = "Roles";
private const string SYSCOL_COL = "Columns";
private const string SYSCOL_KEY = "Keys";
//private const string SYSCOL_CONS = "Constraints";
private const string SYSCOL_TRIG = "Triggers";
//private const string SYSCOL_IND = "Indexes";
private const string SYSSEC_PRIV = "Privilege";
private const string DEFAULT_DB = "information_schema";
public enum TreeLevel
{
Instance = 0,
ServiceFolder = 1,
Database = 2,
SysObjFolder = 3,
SystemObject = 4,
SysColFolder = 5,
SystemColumn = 6
}
public enum IconType
{
Instance = 0,
Database = 1,
FolderClosed = 2,
FolderOpen = 3,
SysObject = 4,
SysColumn = 5
}
#endregion
#region Private variables
private RichTextBox rtbMySQL; // from host control
private ToolStripComboBox tscbMySQLDBList; // from host control
private string m_strConnectionString = "";
private string m_strDBProvider = "";
private string m_strDBInstanceName = ""; // init at fnCreateConnection()
private string m_strDBVersion = ""; // init at fnCreateConnection()
Hashtable htSProc = new Hashtable(); // from App.Config
// This is for actual node selected on mouse-up/right-click
private TreeNode m_CurrSelectNode = new TreeNode();
// This is to capture on mouse-click, since context menu is loaded before treeView's mouse-up event
private int m_CurrNodeLevel = 0;
private DBFactory dbfac = new DBFactory();
#endregion
public MySQL(Control ctrlHostRTB, object ctrlDBList,
string strConnString, string strDataProvider)
{
InitializeComponent();
// Controls setting
this.rtbMySQL = ctrlHostRTB as RichTextBox;
this.tscbMySQLDBList = ctrlDBList as ToolStripComboBox;
this.AutoScroll = true;
// will be overriden by user options
this.rtbMySQL.Font = new Font("Courier New", 10, FontStyle.Regular);
// ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_fxmclictl/html/cdeb42e1-86e8-410c-ba46-18aec264ef5f.htm
this.rtbMySQL.WordWrap = false;
m_strDBProvider = strDataProvider;
m_strConnectionString = strConnString;
fnGetAppSettings();
fnInitTreeView();
}
private void fnGetAppSettings()
{
try
{
string[] arrStrAppKeys = ConfigurationManager.AppSettings.AllKeys;
NameValueCollection nvcAppStgs = ConfigurationManager.AppSettings;
for (int i = 0; i < nvcAppStgs.Count; i++)
{
htSProc.Add(arrStrAppKeys[i], nvcAppStgs[i]);
}
}
catch (ConfigurationErrorsException ConfigErr)
{
Console.WriteLine(ConfigErr.ToString());
}
}
#region Construct MySQL treeview
private void fnInitTreeView()
{
if (!m_strConnectionString.Trim().Equals(""))
{
// Assign the ImageList to the TreeView.
tvMySQL.ImageList = ilPictures;
// This is for database
if (ilPictures.Images.Count >= (int)IconType.Instance + 1)
{
// Set the TreeView control's default image and selected image indexes.
tvMySQL.ImageIndex = (int)IconType.Instance;
tvMySQL.SelectedImageIndex = (int)IconType.Instance;
}
MySqlConnection connSQL = fnCreateConnection("");
if (connSQL != null)
{
// To maintain performance while items are added one at a time to the TreeView,
// call the BeginUpdate method.
// The BeginUpdate method prevents the control from painting until the EndUpdate method is called.
tvMySQL.BeginUpdate();
tvMySQL.ShowPlusMinus = true;
// Level 0
tvMySQL.Nodes.Add(m_strDBInstanceName + " (" + m_strDBVersion + ")");
// Level 1 : Prepare for replication/notification
tvMySQL.Nodes[0].Nodes.Add(SERVICETYPE_DB);
tvMySQL.Nodes[0].Nodes[0].ImageIndex = (int)IconType.FolderClosed;
tvMySQL.Nodes[0].Nodes[0].SelectedImageIndex = (int)IconType.FolderClosed;
tvMySQL.EndUpdate();
tvMySQL.ContextMenuStrip = cmsRightClick;
fnAddDatabase(tvMySQL.Nodes[0].Nodes[0], false);
}
}
}
private void fnAddDatabase(TreeNode tnBaseNode, bool bRefresh)
{
// if it's the first time, force it to re-fill
if (tnBaseNode.Nodes.Count == 0)
bRefresh = true;
if (bRefresh)
{
Exception exErrHandle = new Exception();
DataTable dtAllDB = dbfac.fnExecDBCommonQuery(m_strDBProvider, fnCreateConnection(""),
"SHOW DATABASES", ref exErrHandle);
if (dtAllDB != null)
{
tvMySQL.BeginUpdate();
foreach (DataRow drDatabase in dtAllDB.Rows)
{
// Level 2 : All DB
tnBaseNode.Nodes.Add(drDatabase["DATABASE"].ToString());
}
// assign icon
if (ilPictures.Images.Count >= (int)IconType.Database + 1)
{
foreach (TreeNode tnNode in tnBaseNode.Nodes)
{
tnNode.ImageIndex = (int)IconType.Database;
tnNode.SelectedImageIndex = (int)IconType.Database;
}
}
tvMySQL.EndUpdate();
// Update the available database on combobox
tscbMySQLDBList.BeginUpdate();
foreach (DataRow drDatabase in dtAllDB.Rows)
{
tscbMySQLDBList.Items.Add(drDatabase["DATABASE"].ToString());
}
// select the default system database
if (tscbMySQLDBList.Items.Count > 0)
{
if (tscbMySQLDBList.Items.Contains(DEFAULT_DB))
tscbMySQLDBList.SelectedIndex = tscbMySQLDBList.Items.IndexOf(DEFAULT_DB);
else
tscbMySQLDBList.SelectedIndex = 0;
}
tscbMySQLDBList.EndUpdate();
}
}
}
private void fnAddSysObjFolder(TreeNode tnBaseNode, bool bRefresh)
{
// if it's the first time, force it to re-fill
if (tnBaseNode.Nodes.Count == 0)
bRefresh = true;
if (bRefresh)
{
// add folder to hold system objects
// Display a wait cursor while the TreeNodes are being created.
Cursor.Current = Cursors.WaitCursor;
tvMySQL.BeginUpdate();
// Clear all children
tnBaseNode.Nodes.Clear();
// Level 3 : SysObj folder
tnBaseNode.Nodes.Add(SYSOBJ_FUNC);
tnBaseNode.Nodes.Add(SYSOBJ_PROC);
tnBaseNode.Nodes.Add(SYSOBJ_SYSTBL);
//tnBaseNode.Nodes.Add(SYSOBJ_TBLFUNC);
tnBaseNode.Nodes.Add(SYSOBJ_TRIG);
tnBaseNode.Nodes.Add(SYSOBJ_USRTBL);
tnBaseNode.Nodes.Add(SYSOBJ_VIEW);
tnBaseNode.Nodes.Add(SECURITY_USER);
//tnBaseNode.Nodes.Add(SECURITY_ROLE);
// Will be overwritten on treeview's AfterExpand & AfterCollapse events
if (ilPictures.Images.Count >= (int)IconType.FolderClosed + 1)
{
foreach (TreeNode tnNode in tnBaseNode.Nodes)
{
tnNode.ImageIndex = (int)IconType.FolderClosed;
tnNode.SelectedImageIndex = (int)IconType.FolderClosed;
}
}
tvMySQL.EndUpdate();
// Reset the cursor to the default for all controls.
Cursor.Current = Cursors.Default;
}
}
private void fnChannelSysObj(TreeNode tnBaseNode, bool bRefresh)
{
switch (tnBaseNode.Text)
{
case SYSOBJ_USRTBL:
fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
"Select * from INFORMATION_SCHEMA.TABLES " +
" where Table_Schema = '" + tnBaseNode.Parent.Text +
"' and Table_Type in ('Base Table')",
new string[] { "TABLE_NAME" },
IconType.SysObject, false);
break;
case SYSOBJ_VIEW:
fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
"Select * from INFORMATION_SCHEMA.TABLES " +
" where Table_Schema = '" + tnBaseNode.Parent.Text +
"' and Table_Type = 'View'",
new string[] { "TABLE_NAME" },
IconType.SysObject, false);
break;
//----------------------------------------------------------------
case SYSOBJ_FUNC:
fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
"Select * from INFORMATION_SCHEMA.ROUTINES " +
" where ROUTINE_Schema = '" + tnBaseNode.Parent.Text +
"' and ROUTINE_Type = 'FUNCTION'",
new string[] { "SPECIFIC_NAME" },
IconType.SysObject, false);
break;
case SYSOBJ_PROC:
fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
"Select * from INFORMATION_SCHEMA.ROUTINES " +
" where ROUTINE_Schema = '" + tnBaseNode.Parent.Text +
"' and ROUTINE_Type = 'PROCEDURE'",
new string[] { "SPECIFIC_NAME" },
IconType.SysObject, false);
break;
case SYSOBJ_SYSTBL:
fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
"Select * from INFORMATION_SCHEMA.TABLES " +
" where Table_Schema = '" + tnBaseNode.Parent.Text +
"' and Table_Type in ('SYSTEM VIEW')",
new string[] { "TABLE_NAME" },
IconType.SysObject, false);
break;
//case SYSOBJ_TBLFUNC:
// strXType = "'TF'";
// break;
case SYSOBJ_TRIG:
fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
"Select * from INFORMATION_SCHEMA.TRIGGERS " +
" where TRIGGER_Schema = '" + tnBaseNode.Parent.Text + "' ",
new string[] { "TRIGGER_NAME" },
IconType.SysObject, false);
break;
//----------------------------------------------------------------
case SECURITY_USER:
// assume root user have the access of all tables
fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
"Select GRANTEE from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES " +
"where TABLE_SCHEMA = '" + tnBaseNode.Parent.Text + "' " +
"group by GRANTEE " +
"UNION " +
"SELECT CONCAT('''', usr.User, '''@''', usr.Host, '''') as GRANTEE " +
"FROM mysql.user usr where User = 'root'",
new string[] { "GRANTEE" }, IconType.SysObject, false);
break;
default:
break;
}
}
private void fnAddSysObj_SysCol(TreeNode tnBaseNode, string strDBName, bool bRefresh,
string strQuery, string[] arrStrFullName,
IconType itIcon, bool bAddDescription)
{
// if it's the first time, force it to re-fill
if (tnBaseNode.Nodes.Count == 0)
bRefresh = true;
if (bRefresh)
{
Exception exErrHandle = new Exception();
DataTable dtSysObj = dbfac.fnExecDBCommonQuery(m_strDBProvider, fnCreateConnection(strDBName),
strQuery, ref exErrHandle);
if (dtSysObj != null)
{
// Display a wait cursor while the TreeNodes are being created.
Cursor.Current = Cursors.WaitCursor;
tvMySQL.BeginUpdate();
// Clear all children
tnBaseNode.Nodes.Clear();
// use generic to sort particular treenode instead of whole treeview
List<string> lstStrSortedNode = new List<string>();
// Level 4 : All System objects
foreach (DataRow drSysObj in dtSysObj.Rows)
{
string strNodeName = "";
if (arrStrFullName != null)
{
foreach (string strColumnName in arrStrFullName)
{
if (strNodeName.Trim().Equals(""))
{
strNodeName = drSysObj[strColumnName].ToString();
}
else
{
strNodeName = strNodeName + "." + drSysObj[strColumnName].ToString();
}
}
}
else
{
strNodeName = drSysObj["name"].ToString();
}
if (bAddDescription)
{
// Get the DataTable of a DataRow
DataTable dtFindColumn = drSysObj.Table;
if (dtFindColumn.Columns.IndexOf("COLUMN_NAME") > 0)
{
strNodeName = strNodeName + " (" + drSysObj["COLUMN_NAME"].ToString() + ")";
}
// For SYSSEC_PRIV
if (dtFindColumn.Columns.IndexOf("IS_GRANTABLE") > 0)
{
if (drSysObj["IS_GRANTABLE"].ToString().ToUpper().Equals("YES"))
{
strNodeName = strNodeName + " (Grantable)";
}
else
{
strNodeName = strNodeName + " (Not Grantable)";
}
}
}
lstStrSortedNode.Add(strNodeName);
//tnBaseNode.Nodes.Add(strNodeName);
}
lstStrSortedNode.Sort();
foreach (string item in lstStrSortedNode)
{
tnBaseNode.Nodes.Add(item.ToString());
}
// This is for SysObject
if (ilPictures.Images.Count >= (int)itIcon + 1)
{
foreach (TreeNode tnNode in tnBaseNode.Nodes)
{
tnNode.ImageIndex = (int)itIcon;
tnNode.SelectedImageIndex = (int)itIcon;
}
}
// Need to be sorted since TABLE_SCHEMA is important; Resource-hunger??
//tnBaseNode.Sort();
tvMySQL.EndUpdate();
// Reset the cursor to the default for all controls.
Cursor.Current = Cursors.Default;
}
}
}
private void fnAddSysColFolder(TreeNode tnBaseNode, bool bRefresh)
{
// if it's the first time, force it to re-fill
if (tnBaseNode.Nodes.Count == 0)
bRefresh = true;
if (bRefresh)
{
// Level 5 : All SysColumns
switch (tnBaseNode.Parent.Text)
{
case SYSOBJ_USRTBL:
tvMySQL.BeginUpdate();
tnBaseNode.Nodes.Clear();
tnBaseNode.Nodes.Add(SYSCOL_COL);
tnBaseNode.Nodes.Add(SYSCOL_KEY);
//tnBaseNode.Nodes.Add(SYSCOL_CONS);
tnBaseNode.Nodes.Add(SYSCOL_TRIG);
//tnBaseNode.Nodes.Add(SYSCOL_IND);
if (ilPictures.Images.Count >= (int)IconType.FolderClosed + 1)
{
foreach (TreeNode tnNode in tnBaseNode.Nodes)
{
tnNode.ImageIndex = (int)IconType.FolderClosed;
tnNode.SelectedImageIndex = (int)IconType.FolderClosed;
}
}
tvMySQL.EndUpdate();
break;
//case SYSOBJ_VIEW:
// break;
case SECURITY_USER:
tvMySQL.BeginUpdate();
tnBaseNode.Nodes.Clear();
tnBaseNode.Nodes.Add(SYSSEC_PRIV);
if (ilPictures.Images.Count >= (int)IconType.FolderClosed + 1)
{
foreach (TreeNode tnNode in tnBaseNode.Nodes)
{
tnNode.ImageIndex = (int)IconType.FolderClosed;
tnNode.SelectedImageIndex = (int)IconType.FolderClosed;
}
}
tvMySQL.EndUpdate();
break;
default:
break;
}
}
}
private void fnChannelSysCol(TreeNode tnBaseNode, bool bRefresh)
{
// if it's the first time, force it to re-fill
if (tnBaseNode.Nodes.Count == 0)
bRefresh = true;
if (bRefresh)
{
// Already declare constant, no need .Trim().ToUpper()
switch (tnBaseNode.Text)
{
case SYSCOL_COL:
fnAddSysColumn(tnBaseNode, tnBaseNode.Parent.Parent.Parent.Text, bRefresh);
break;
case SYSCOL_KEY:
fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Parent.Parent.Text, bRefresh,
"Select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE " +
" where TABLE_SCHEMA = '" + tnBaseNode.Parent.Parent.Parent.Text + "'" +
" and TABLE_NAME = '" + tnBaseNode.Parent.Text + "'",
new string[] { "CONSTRAINT_NAME" }, IconType.SysColumn, true);
break;
//case SYSCOL_CONS:
// break;
case SYSCOL_TRIG:
fnAddTrigIndex(tnBaseNode, tnBaseNode.Parent.Parent.Parent.Text, bRefresh);
break;
//case SYSCOL_IND:
// break;
case SYSSEC_PRIV:
string strQuery = "";
if (tnBaseNode.Parent.Text.Equals("'root'@'localhost'") || tnBaseNode.Parent.Text.Equals("'root'@'%'"))
{
strQuery = "Select * from INFORMATION_SCHEMA.USER_PRIVILEGES " +
" where GRANTEE = '" + tnBaseNode.Parent.Text.Replace("'", "''") + "'";
}
else
{
strQuery = "Select * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES " +
" where TABLE_SCHEMA = '" + tnBaseNode.Parent.Parent.Parent.Text + "'" +
" and GRANTEE = '" + tnBaseNode.Parent.Text.Replace("'", "''") + "'";
}
fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Parent.Parent.Text, bRefresh,
strQuery, new string[] { "PRIVILEGE_TYPE" }, IconType.SysColumn, true);
break;
default:
break;
}
}
}
private void fnAddSysColumn(TreeNode tnBaseNode, string strDBName, bool bRefresh)
{
// if it's the first time, force it to re-fill
if (tnBaseNode.Nodes.Count == 0)
bRefresh = true;
if (bRefresh)
{
string strNodeText = "";
string strTableName = tnBaseNode.Parent.Text;
Exception exErrHandle = new Exception();
DataTable dtSystemColumn = dbfac.fnExecDBCommonQuery(m_strDBProvider, fnCreateConnection(strDBName),
"SHOW COLUMNS FROM " + strDBName + "." + strTableName,
ref exErrHandle);
if (dtSystemColumn != null)
{
tvMySQL.BeginUpdate();
tnBaseNode.Nodes.Clear();
// Level 6 : All System Columns
foreach (DataRow drSpecificSysCol in dtSystemColumn.Rows)
{
strNodeText = drSpecificSysCol["Field"].ToString() + " (" +
drSpecificSysCol["TYPE"].ToString();
if (drSpecificSysCol["Key"].ToString().ToUpper().Equals("PRI"))
{
strNodeText = strNodeText + ", (PRI)";
}
if (drSpecificSysCol["NULL"].ToString().ToUpper().Equals("NO"))
{
strNodeText = strNodeText + ", not null)";
}
else
{
strNodeText = strNodeText + ", null)";
}
tnBaseNode.Nodes.Add(strNodeText);
}
if (ilPictures.Images.Count >= (int)IconType.SysColumn + 1)
{
foreach (TreeNode tnNode in tnBaseNode.Nodes)
{
tnNode.ImageIndex = (int)IconType.SysColumn;
tnNode.SelectedImageIndex = (int)IconType.SysColumn;
}
}
tvMySQL.EndUpdate();
}
}
}
private void fnAddTrigIndex(TreeNode tnBaseNode, string strDBName, bool bRefresh)
{
// if it's the first time, force it to re-fill
if (tnBaseNode.Nodes.Count == 0)
bRefresh = true;
if (bRefresh)
{
string strNodeText = "";
string strTableName = tnBaseNode.Parent.Text;
Exception exErrHandle = new Exception();
DataTable dtSystemColumn = dbfac.fnExecDBCommonQuery(m_strDBProvider, fnCreateConnection(strDBName),
"SELECT * FROM INFORMATION_SCHEMA.TRIGGERS "
+ " where Trigger_Schema = '" +strDBName + "'"
+ " and EVENT_OBJECT_TABLE = '" + strTableName + "'",
ref exErrHandle);
if (dtSystemColumn != null)
{
tvMySQL.BeginUpdate();
tnBaseNode.Nodes.Clear();
// Level 6 : All Triggers
foreach (DataRow drSpecificSysCol in dtSystemColumn.Rows)
{
strNodeText = drSpecificSysCol["TRIGGER_NAME"].ToString() + " (" +
drSpecificSysCol["ACTION_TIMING"].ToString() + ", " +
drSpecificSysCol["EVENT_MANIPULATION"].ToString() + ")";
tnBaseNode.Nodes.Add(strNodeText);
}
if (ilPictures.Images.Count >= (int)IconType.SysColumn + 1)
{
foreach (TreeNode tnNode in tnBaseNode.Nodes)
{
tnNode.ImageIndex = (int)IconType.SysColumn;
tnNode.SelectedImageIndex = (int)IconType.SysColumn;
}
}
tvMySQL.EndUpdate();
}
}
}
#endregion
#region DB properties
public MySqlConnection fnCreateConnection(string strDBName)
{
MySqlConnection sqlConnect = new MySqlConnection();
try
{
DbConnectionStringBuilder builder = new DbConnectionStringBuilder();
// if search for a particular database
if (!strDBName.Trim().Equals(""))
{
builder.ConnectionString = m_strConnectionString + ";initial catalog=" + strDBName.Trim();
}
else
{
builder.ConnectionString = m_strConnectionString;
}
sqlConnect = new MySqlConnection(builder.ConnectionString);
// need to open conenction to get server version
// just need to get once
if (m_strDBInstanceName.Trim().Equals("") &&
m_strDBVersion.Trim().Equals("") && sqlConnect != null)
{
sqlConnect.Open();
// Maybe this need to be retrieved only when needed
//m_strDBInstanceName = sqlConnect.WorkstationId;
m_strDBVersion = sqlConnect.ServerVersion;
sqlConnect.Close();
}
}
catch (MySqlException mymsqlEx)
{
MessageBox.Show("mymsqlEx : " + mymsqlEx.Message + Environment.NewLine + mymsqlEx.StackTrace);
sqlConnect = null;
}
catch (Exception ex)
{
MessageBox.Show("Ex : " + ex.Message + Environment.NewLine + ex.StackTrace);
sqlConnect = null;
}
finally
{
}
return sqlConnect;
}
#endregion
#region Control events
private void tvMySQL_MouseUp(object sender, MouseEventArgs e)
{
// Show menu only if Right Mouse button is clicked
if (e.Button == MouseButtons.Right)
{
// Point where mouse is clicked
Point ptClick = new Point(e.X, e.Y);
// Go to the node that the user clicked
TreeNode node = tvMySQL.GetNodeAt(ptClick);
if (node != null)
{
m_CurrSelectNode = node;
// Highlight the node that the user clicked.
// The node is highlighted until the Menu is displayed on the screen
tvMySQL.SelectedNode = node;
}
}
}
private void tvMySQL_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
Console.WriteLine("e.Node from NodeMouseClick: " + e.Node.Text);
// keep a copy for context-menu loading
m_CurrNodeLevel = e.Node.Level;
// Will fail to capture sometimes if user clicks on the plus sign; flicker if do in tvMSSQL_AfterExpand()
if (e.Button == MouseButtons.Left)
{
switch (e.Node.Level)
{
case (int)TreeLevel.ServiceFolder:
// add sysobj folder
foreach (TreeNode tnChild in e.Node.Nodes)
{
fnAddSysObjFolder(tnChild, false);
}
break;
case (int)TreeLevel.Database:
// add system object
foreach (TreeNode tnChild in e.Node.Nodes)
{
fnChannelSysObj(tnChild, false);
}
break;
case (int)TreeLevel.SysObjFolder:
// add syscol folder
foreach (TreeNode tnChild in e.Node.Nodes)
{
fnAddSysColFolder(tnChild, false);
}
break;
case (int)TreeLevel.SystemObject:
// get columns, PKs, constraints, triggers
foreach (TreeNode tnChild in e.Node.Nodes)
{
fnChannelSysCol(tnChild, false);
}
break;
default:
break;
}
}
else if (e.Button == MouseButtons.Right)
{
m_CurrSelectNode = e.Node;
}
}
private void tvMySQL_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
{
if (e.Node.IsExpanded)
{
tvMySQL_NodeMouseClick(sender, e);
}
}
private void tvMySQL_AfterCollapse(object sender, TreeViewEventArgs e)
{
// use closed Folder
if (e.Node.Level == (int)TreeLevel.SysObjFolder
|| e.Node.Level == (int)TreeLevel.ServiceFolder
|| e.Node.Level == (int)TreeLevel.SysColFolder
&& !e.Node.IsExpanded
&& ilPictures.Images.Count >= (int)IconType.FolderClosed + 1)
{
e.Node.ImageIndex = (int)IconType.FolderClosed;
e.Node.SelectedImageIndex = (int)IconType.FolderClosed;
}
}
private void tvMySQL_AfterExpand(object sender, TreeViewEventArgs e)
{
Console.WriteLine("e.Node from AfterExpand: " + e.Node.Text);
if (e.Node.Level == (int)TreeLevel.SysObjFolder
|| e.Node.Level == (int)TreeLevel.ServiceFolder
|| e.Node.Level == (int)TreeLevel.SysColFolder
&& e.Node.IsExpanded
&& ilPictures.Images.Count >= (int)IconType.FolderOpen + 1)
{
e.Node.ImageIndex = (int)IconType.FolderOpen;
e.Node.SelectedImageIndex = (int)IconType.FolderOpen;
}
}
#endregion
#region Menu & Context-Menu
private void cmsRightClick_Opening(object sender, CancelEventArgs e)
{
// Only PROC/FUNC/TBLFUNC/TRIG/VIEW need "ScriptToEditor" button
if (m_CurrNodeLevel == (int)TreeLevel.SystemObject)
{
if (m_CurrSelectNode != null && m_CurrSelectNode.Parent != null
&& m_CurrSelectNode.Parent.Text.Trim().Equals(SYSOBJ_PROC)
|| m_CurrSelectNode.Parent.Text.Trim().Equals(SYSOBJ_FUNC)
|| m_CurrSelectNode.Parent.Text.Trim().Equals(SYSOBJ_TRIG)
|| m_CurrSelectNode.Parent.Text.Trim().Equals(SYSOBJ_VIEW))
{
tsmiScriptToEditor.Visible = true;
}
else
{
tsmiScriptToEditor.Visible = false;
}
}
else
{
tsmiScriptToEditor.Visible = false;
}
}
private void tsmiScriptToEditor_Click(object sender, EventArgs e)
{
if (m_CurrSelectNode != null)
{
string strQuery = "";
switch (m_CurrSelectNode.Parent.Text)
{
case SYSOBJ_FUNC:
strQuery = "Select ROUTINE_DEFINITION as Text from INFORMATION_SCHEMA.ROUTINES " +
" where ROUTINE_SCHEMA = '" + m_CurrSelectNode.Parent.Parent.Text +
"' and ROUTINE_TYPE = 'FUNCTION' " +
"and ROUTINE_NAME = '" + m_CurrSelectNode.Text + "'";
break;
case SYSOBJ_PROC:
strQuery = "Select ROUTINE_DEFINITION as Text from INFORMATION_SCHEMA.ROUTINES " +
" where ROUTINE_SCHEMA = '" + m_CurrSelectNode.Parent.Parent.Text +
"' and ROUTINE_TYPE = 'PROCEDURE' " +
" and ROUTINE_NAME = '" + m_CurrSelectNode.Text + "'";
break;
//case SYSOBJ_TBLFUNC:
// strXType = "TF";
// break;
case SYSOBJ_TRIG:
strQuery = "Select ACTION_STATEMENT as Text from INFORMATION_SCHEMA.TRIGGERS " +
" where TRIGGER_SCHEMA = '" + m_CurrSelectNode.Parent.Parent.Text +
"' and TRIGGER_NAME = '" + m_CurrSelectNode.Text + "'";
break;
case SYSOBJ_VIEW:
strQuery = "SELECT VIEW_DEFINITION as Text FROM INFORMATION_SCHEMA.VIEWS " +
" where TABLE_SCHEMA = '" + m_CurrSelectNode.Parent.Parent.Text +
"' and TABLE_NAME = '" + m_CurrSelectNode.Text + "'";
break;
default:
return;
}
Exception exErrHandle = new Exception();
DataTable dtGetText = dbfac.fnExecDBCommonQuery(m_strDBProvider,
fnCreateConnection(m_CurrSelectNode.Parent.Parent.Text),
strQuery, ref exErrHandle);
if (dtGetText != null)
{
// should be one line only
StringBuilder sbContent = new StringBuilder();
String strLine = "";
foreach (DataRow drGetText in dtGetText.Rows)
{
strLine = drGetText["Text"].ToString();
if (!strLine.Trim().Equals(""))
{
sbContent.Append(strLine);
}
}
rtbMySQL.Text = sbContent.ToString();
}
}
}
private void tsmiRefresh_Click(object sender, EventArgs e)
{
//TODO : Solve the one in MSSQL first
}
#endregion
}
}
Submit an article or tip