private void RefreshColumnList(TreeNode nd)
{
nd.Text += " (expanding...)";
Application.DoEvents();
this.BeginUpdate();
this.UseWaitCursor = true;
rsDbSql dbConn = this.GetDbObject(nd);
try
{
nd.Nodes.Clear();
foreach (ColumnParams cp in dbConn.GetColumns(nd.Parent.Parent.Parent.Text, nd.Parent.Text, (SqlObjType)nd.Parent.Tag == SqlObjType.View))
{
TreeNode ndCol = new TreeNode(cp.ToString());
ndCol.Name = string.Format("{0}.{1}.{2}", nd.Parent.Parent.Parent.Text, nd.Parent.Text, cp.ColumnName);
ndCol.Tag = SqlObjType.Column;
ndCol.ImageKey = ndCol.SelectedImageKey = "Column";
nd.Nodes.Add(ndCol);
}
}
catch (Exception ex)
{
MessageBox.Show(this, string.Format("Unable to retreive the list of columns from the SQL server:\n\n{0}\n\nApplication Version: {1}", ex.Message, Application.ProductVersion), "Error");
nd.Nodes.Clear();
nd.Nodes.Add("<EMPTY>");
nd.Collapse(false);
}
finally
{
nd.Text = nd.Text.Substring(0, nd.Text.LastIndexOf('(')).TrimEnd();
this.EndUpdate();
this.UseWaitCursor = false;
}
}
//***************************************************************************
// Private Methods
//
private void PopulateTableList()
{
this.lstSrcTables.BeginUpdate();
this.lstSrcTables.Items.Clear();
try
{
if (this._db.GetType().Name != "rsDbSql")
{
throw new Exception("Export wizrd currently only support SQL database sources.");
}
rsDbSql db = (rsDbSql)this._db;
string[] tbls = db.GetTableList(this._dbNm);
for (int i = 0; i < tbls.Length; i++)
{
this.lstSrcTables.Items.Add("T: " + tbls[i]);
}
string[] vws = db.GetViewList(this._dbNm);
for (int i = 0; i < vws.Length; i++)
{
this.lstSrcTables.Items.Add("V: " + vws[i]);
}
}
catch (Exception ex)
{
CrossThreadUI.ShowMessageBox(this, "Unable to retrieve table names: " + ex.Message, "Unexpected Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
this.Dispose();
}
finally
{
this.lstSrcTables.EndUpdate();
}
}
private void DoSearch(string srchValue, bool incCols)
{
this.UseWaitCursor = true;
this.lstSearchResults.BeginUpdate();
this.lstSearchResults.Items.Clear();
this.lstSearchResults.ShowGroups = true;
try
{
rsDbSql dbConn = this.GetDbObject(this._sqlExplorer.SelectedNode);
bool noResults = true;
foreach (string tbl in dbConn.SearchForTable(srchValue, true))
{
this.lstSearchResults.Items.Add(new ListViewItem(tbl, 2, this.lstSearchResults.Groups["grpTables"])); noResults = false;
}
foreach (string view in dbConn.SearchForView(srchValue, true))
{
this.lstSearchResults.Items.Add(new ListViewItem(view, 8, this.lstSearchResults.Groups["grpViews"])); noResults = false;
}
foreach (string sp in dbConn.SearchForStoredProceedure(srchValue, true))
{
this.lstSearchResults.Items.Add(new ListViewItem(sp, 4, this.lstSearchResults.Groups["grpStoredProcs"])); noResults = false;
}
foreach (string func in dbConn.SearchForFunction(srchValue, true))
{
this.lstSearchResults.Items.Add(new ListViewItem(func, 4, this.lstSearchResults.Groups["grpFuncs"])); noResults = false;
}
try
{
if (incCols)
{
try
{
foreach (string dbNm in dbConn.GetDatabaseList())
{
try
{
foreach (string tbl in dbConn.GetTableList(dbNm))
{
try
{
foreach (ColumnParams cp in dbConn.GetColumns(dbNm, tbl))
{
try
{
if (cp.ColumnName.Contains(srchValue))
{
this.lstSearchResults.Items.Add(new ListViewItem(string.Format("{0}.{1}.{2} ({3})", dbNm, tbl, cp.ColumnName, cp.DataType.ToString()), 5, this.lstSearchResults.Groups["grpCols"])); noResults = false;
}
}
catch (Exception ex)
{
if (ex.InnerException is System.Data.SqlClient.SqlException && ex.ToString().Contains("The server principal ") && ex.ToString().Contains(" is not able to access the database ") && ex.ToString().Contains(" under the current security context."))
{
// We're going to ignore this error and continue
// searching whichever databases we *are*
// allowed to access.
}
else
{
throw;
}
}
}
}
catch (Exception ex)
{
if (ex.InnerException is System.Data.SqlClient.SqlException && ex.ToString().Contains("The server principal ") && ex.ToString().Contains(" is not able to access the database ") && ex.ToString().Contains(" under the current security context."))
{
// We're going to ignore this error and continue
// searching whichever databases we *are*
// allowed to access.
}
else
{
throw;
}
}
}
}
catch (Exception ex)
{
if (ex.InnerException is System.Data.SqlClient.SqlException && ex.ToString().Contains("The server principal ") && ex.ToString().Contains(" is not able to access the database ") && ex.ToString().Contains(" under the current security context."))
{
// We're going to ignore this error and continue
// searching whichever databases we *are*
// allowed to access.
}
else
{
throw;
}
}
}
}
catch (Exception ex)
{
if (ex.InnerException is System.Data.SqlClient.SqlException && ex.ToString().Contains("The server principal ") && ex.ToString().Contains(" is not able to access the database ") && ex.ToString().Contains(" under the current security context."))
{
// We're going to ignore this error and continue
// searching whichever databases we *are*
// allowed to access.
}
else
{
throw;
}
}
}
}
catch (Exception ex)
{ MessageBox.Show(this, "Unexpected Error", "An error occured while trying to search for the specified column:\n" + ex.Message); }
// If nothing got added, we'll just add an entry informing the user
// that no objects matching their search were found.
if (noResults)
{
this.lstSearchResults.ShowGroups = false;
this.lstSearchResults.Items.Add(new ListViewItem("No matches found..."));
}
this.splitContainer1.Panel2Collapsed = false;
this.txtSearch.Text = string.Empty;
}
catch (Exception ex)
{
#if DEBUG
MessageBox.Show(this, "An error occured while trying to perform the search:\n\n" + ex.ToString() + "\n\nApplication Version: " + Application.ProductVersion, "Error");
#else
MessageBox.Show(this, "An error occured while trying to perform the search:\n\n" + ex.Message + "\n\nApplication Version: " + Application.ProductVersion, "Error");
#endif
}
finally
{
this.lstSearchResults.EndUpdate();
this.UseWaitCursor = false;
}
}
//***************************************************************************
// Event Handlers
//
private void mnuNd_onClick(object sender, EventArgs e)
{
if (this._lstRc == null)
{
return;
}
TreeNode nd = this._lstRc;
switch (((ToolStripMenuItem)sender).Name)
{
case "mnuNdObjEdit":
#region ObjEdit
{
rsDbSql db = this.GetDbObject(nd);
string dbName = this.GetDatabaseLevel(nd).Text;
string procName = nd.Text;
string dbConnectionString = db.ConnectionString;
if (dbConnectionString.ToLower().Contains("initial catalog="))
{
int icSt = dbConnectionString.ToLower().IndexOf("initial catalog=");
int icEd = dbConnectionString.IndexOf(';', icSt);
dbConnectionString = dbConnectionString.Substring(0, icSt) + "Initial Catalog=" + dbName + dbConnectionString.Substring(icEd);
}
try
{
string[] objText = null;
if ((SqlObjType)nd.Tag == SqlObjType.Index)
{
objText = db.ScriptCreateIndex(dbName, this.GetTableLevel(nd).Text, nd.Text);
}
else
{
objText = db.GetObjectTextAlter(dbName, procName);
}
this.RaiseEditObject(objText, dbConnectionString, procName);
}
catch (Exception ex)
{
MessageBox.Show(this.FindForm(), string.Format("Error retrieving meta-data:\n\n{0}\n\nApplication Version: {1}", ex.Message, Application.ProductVersion), "Error");
return;
}
}
break;
#endregion
case "mnuNdGroupRefresh":
#region GroupRefresh
{
nd.Collapse();
nd.Nodes.Clear();
nd.Nodes.Add("<EMPTY>");
this.RefreshNode(nd);
nd.Expand();
}
break;
#endregion
case "mnuNdScriptCreate":
#region ScriptCreate
{
rsDbSql db = this.GetDbObject(nd);
string dbName = this.GetDatabaseLevel(nd).Text;
string tableNm = nd.Text;
string dbConnectionString = db.ConnectionString;
if (dbConnectionString.ToLower().Contains("initial catalog="))
{
int icSt = dbConnectionString.ToLower().IndexOf("initial catalog=");
int icEd = dbConnectionString.IndexOf(';', icSt);
dbConnectionString = dbConnectionString.Substring(0, icSt) + "Initial Catalog=" + dbName + dbConnectionString.Substring(icEd);
}
try
{
string[] objText = db.ScriptCreateTable(dbName, tableNm);
this.RaiseEditObject(objText, dbConnectionString, tableNm);
}
catch (Exception ex)
{
MessageBox.Show(this.FindForm(), string.Format("Error scripting 'Create':\n\n{0}\n\nApplication Version: {1}", ex.Message, Application.ProductVersion), "Error");
return;
}
}
break;
#endregion
case "mnuNdScriptAlter":
#region ScriptAlter
{
rsDbSql db = this.GetDbObject(nd);
string dbName = this.GetDatabaseLevel(nd).Text;
string tableNm = nd.Text;
string dbConnectionString = db.ConnectionString;
if (dbConnectionString.ToLower().Contains("initial catalog="))
{
int icSt = dbConnectionString.ToLower().IndexOf("initial catalog=");
int icEd = dbConnectionString.IndexOf(';', icSt);
dbConnectionString = dbConnectionString.Substring(0, icSt) + "Initial Catalog=" + dbName + dbConnectionString.Substring(icEd);
}
try
{
string[] objText = db.ScriptAlterTable(dbName, tableNm);
this.RaiseEditObject(objText, dbConnectionString, tableNm);
}
catch (Exception ex)
{
MessageBox.Show(this.FindForm(), string.Format("Error scripting 'Create':\n\n{0}\n\nApplication Version: {1}", ex.Message, Application.ProductVersion), "Error");
return;
}
}
break;
#endregion
case "mnuScriptInsert":
#region ScriptInsert
{
rsDbSql db = this.GetDbObject(nd);
string dbName = this.GetDatabaseLevel(nd).Text;
string tableNm = nd.Text;
string dbConnString = db.ConnectionString;
if (dbConnString.ToLower().Contains("initial catalog="))
{
int icSt = dbConnString.ToLower().IndexOf("initial catalog=");
int icEd = dbConnString.IndexOf(';', icSt);
dbConnString = dbConnString.Substring(0, icSt) + "Initial Catalog=" + dbName + dbConnString.Substring(icEd);
}
try
{
string[] objText = db.ScriptInsert(dbName, tableNm, false);
this.RaiseEditObject(objText, dbConnString, tableNm);
}
catch (Exception ex)
{
MessageBox.Show(this.FindForm(), string.Format("Error scripting 'Insert':\n\n{0}\n\nApplication Version: {1}", ex.Message, Application.ProductVersion), "Error");
return;
}
}
break;
#endregion
case "mnuNdScriptDelete":
// NOTE: Despite the name, this will *actually* delete the table and all data!
#region ScriptDelete
if (MessageBox.Show(this, "This will delete the table and *ALL* data. This action is not recoverable!\n\nAre you sure you want to continue?", "WARNING", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
{
rsDbSql db = this.GetDbObject(nd);
string dbLevel = this.GetDatabaseLevel(nd).Text;
string tblNm = nd.Text;
string qry = string.Format("USE [{0}] DROP TABLE {1}", dbLevel, tblNm);
db.ExecuteNonQuery(qry);
this.RefreshTableList(nd.Parent);
}
break;
#endregion
case "mnuNdExportData":
#region ExportData
using (frmDataExport frm = new frmDataExport(this.GetDbObject(this.GetDatabaseLevel(this.SelectedNode)), this.SelectedNode.Text))
{
if (frm.IsDisposed)
{
return;
}
else if (frm.ShowDialog(this.FindForm()) == DialogResult.OK)
{
this.GetDbObject(this.SelectedNode);
}
}
break;
#endregion
case "mnuNdImportData":
#region ImportData
//using (frmDataImport frm = new frmDataImport(this.GetDbObject(this.GetDatabaseLevel(this.SelectedNode)), this.SelectedNode.Text))
//{
// if (frm.IsDisposed)
// return;
// else if (frm.ShowDialog(this.FindForm()) == DialogResult.OK)
// {
// this.GetDbObject(this.SelectedNode);
// }
//}
break;
#endregion
case "mnuNdGenScripts":
#region GenerateScripts
MessageBox.Show(this, "This functionality is not yet available.", "Sorry");
break;
#endregion
case "mnuNdDbInfo":
#region DatabaseInfo
using (frmDbInfo frm = new frmDbInfo(this.GetDbObject(this.GetDatabaseLevel(this.SelectedNode)).GetDatabaseInfo(this.SelectedNode.Text)))
frm.ShowDialog(this);
break;
#endregion
case "mnuNdScriptOpen":
#region Open Table
{
rsDbSql db = this.GetDbObject(nd);
string dbName = this.GetDatabaseLevel(nd).Text;
string tableNm = nd.Text;
string dbConnectionString = db.ConnectionString;
if (dbConnectionString.ToLower().Contains("initial catalog="))
{
int icSt = dbConnectionString.ToLower().IndexOf("initial catalog=");
int icEd = dbConnectionString.IndexOf(';', icSt);
dbConnectionString = dbConnectionString.Substring(0, icSt) + "Initial Catalog=" + dbName + dbConnectionString.Substring(icEd);
}
this.RaiseOpenTable(dbConnectionString, tableNm);
}
break;
#endregion
}
}
private void RefreshGroupList(TreeNode nd)
{
nd.Text += " (expanding...)";
Application.DoEvents();
this.BeginUpdate();
this.UseWaitCursor = true;
rsDbSql dbConn = this.GetDbObject(nd);
SqlObjType objType = (SqlObjType)nd.Tag;
try
{
nd.Nodes.Clear();
string[] objCol = new string[0];
switch (objType)
{
case SqlObjType.StoredProcGroup:
objCol = dbConn.GetStoredProceedureList(nd.Parent.Text);
break;
case SqlObjType.FunctionGroup:
if (nd.Text.ToLower().StartsWith("table"))
{
objCol = dbConn.GetTableValueFunctionList(nd.Parent.Parent.Text);
}
else
{
objCol = dbConn.GetScalarFunctionList(nd.Parent.Parent.Text);
}
break;
case SqlObjType.ConstraintGroup:
objCol = dbConn.GetConstraintList(nd.Parent.Parent.Parent.Text, nd.Parent.Text);
break;
case SqlObjType.IndexGroup:
objCol = dbConn.GetIndexList(nd.Parent.Parent.Parent.Text, nd.Parent.Text, (SqlObjType)nd.Parent.Parent.Tag == SqlObjType.ViewGroup);
break;
case SqlObjType.TriggerGroup:
objCol = dbConn.GetTriggerList(nd.Parent.Parent.Parent.Text, nd.Parent.Text, (SqlObjType)nd.Parent.Parent.Tag == SqlObjType.ViewGroup);
break;
case SqlObjType.KeyGroup:
objCol = dbConn.GetPrimaryKeyList(nd.Parent.Parent.Parent.Text, nd.Parent.Text);
break;
case SqlObjType.UserGroup:
objCol = dbConn.GetUserList(nd.Parent.Text);
break;
}
foreach (string obj in objCol)
{
TreeNode ndObj = new TreeNode(obj);
SqlObjType ndType = (SqlObjType)Enum.Parse(typeof(SqlObjType), objType.ToString().Substring(0, objType.ToString().IndexOf("Group")));
ndObj.ImageKey = ndObj.SelectedImageKey = ndType.ToString();
ndObj.ContextMenuStrip = this.mnuNdObj;
ndObj.Tag = ndType;
nd.Nodes.Add(ndObj);
}
if (objType == SqlObjType.KeyGroup)
{
foreach (string obj in dbConn.GetForeignKeyList(nd.Parent.Parent.Parent.Text, nd.Parent.Text))
{
TreeNode ndObj = new TreeNode(obj);
ndObj.Tag = SqlObjType.Key;
ndObj.ImageKey = ndObj.SelectedImageKey = "Key";
nd.Nodes.Add(ndObj);
}
}
}
catch (Exception ex)
{
MessageBox.Show(this, string.Format("Unable to retreive the list of columns from the SQL server:\n\n{0}\n\nApplication Version: {1}", ex.Message, Application.ProductVersion), "Error");
nd.Nodes.Clear();
nd.Nodes.Add("<EMPTY>");
nd.Collapse(false);
}
finally
{
nd.Text = nd.Text.Substring(0, nd.Text.LastIndexOf('(')).TrimEnd();
this.EndUpdate();
this.UseWaitCursor = false;
}
}
private void RefreshTableList(TreeNode nd)
{
nd.Text += " (expanding...)";
Application.DoEvents();
this.BeginUpdate();
this.UseWaitCursor = true;
rsDbSql dbConn = this.GetDbObject(nd);
try
{
nd.Nodes.Clear();
string[] objList = null;
if ((SqlObjType)nd.Tag == SqlObjType.TableGroup)
{
objList = dbConn.GetTableList(nd.Parent.Text);
}
else
{
objList = dbConn.GetViewList(nd.Parent.Text);
}
foreach (string tbl in objList)
{
TreeNode ndTbl = new TreeNode(tbl);
ndTbl.Name = string.Format("{0}.{1}", nd.Parent.Text, tbl);
if ((SqlObjType)nd.Tag == SqlObjType.TableGroup)
{
ndTbl.Tag = SqlObjType.Table;
ndTbl.ContextMenuStrip = this.mnuNdScript;
}
else
{
ndTbl.Tag = SqlObjType.View;
ndTbl.ContextMenuStrip = this.mnuNdObj;
}
ndTbl.ImageIndex = ((SqlObjType)ndTbl.Tag == SqlObjType.Table) ? 2 : 8;
ndTbl.SelectedImageIndex = ((SqlObjType)ndTbl.Tag == SqlObjType.Table) ? 2 : 8;
ndTbl.ImageKey = ndTbl.SelectedImageKey = ((SqlObjType)ndTbl.Tag).ToString(); // == SqlObjType.Table) ? "Table" : "StoredProc";
TreeNode ndColumns = new TreeNode("Columns");
ndColumns.Nodes.Add("<EMPTY>");
ndColumns.Tag = SqlObjType.ColumnGroup;
ndColumns.ContextMenuStrip = this.mnuNdGroup;
ndTbl.Nodes.Add(ndColumns);
if ((SqlObjType)ndTbl.Tag == SqlObjType.Table)
{
TreeNode ndKeys = new TreeNode("Keys");
ndKeys.Nodes.Add("<EMPTY>");
ndKeys.Tag = SqlObjType.KeyGroup;
ndKeys.ContextMenuStrip = this.mnuNdGroup;
ndTbl.Nodes.Add(ndKeys);
TreeNode ndConstraints = new TreeNode("Constraints");
ndConstraints.Nodes.Add("<EMPTY>");
ndConstraints.Tag = SqlObjType.ConstraintGroup;
ndConstraints.ContextMenuStrip = this.mnuNdGroup;
ndTbl.Nodes.Add(ndConstraints);
}
TreeNode ndTriggers = new TreeNode("Triggers");
ndTriggers.Nodes.Add("<EMPTY>");
ndTriggers.Tag = SqlObjType.TriggerGroup;
ndTriggers.ContextMenuStrip = this.mnuNdGroup;
ndTbl.Nodes.Add(ndTriggers);
TreeNode ndIndexes = new TreeNode("Indexes");
ndIndexes.Nodes.Add("<EMPTY>");
ndIndexes.Tag = SqlObjType.IndexGroup;
ndIndexes.ContextMenuStrip = this.mnuNdGroup;
ndTbl.Nodes.Add(ndIndexes);
nd.Nodes.Add(ndTbl);
}
}
catch (Exception ex)
{
MessageBox.Show(this, string.Format("Unable to retreive the list of tables from the SQL server:\n\n{0}\n\nApplication Version: {1}", ex.Message, Application.ProductVersion), "Error");
nd.Nodes.Clear();
nd.Nodes.Add("<EMPTY>");
nd.Collapse(false);
}
finally
{
nd.Text = nd.Text.Substring(0, nd.Text.LastIndexOf('(')).TrimEnd();
this.EndUpdate();
this.UseWaitCursor = false;
}
}
private void RefreshDbList(TreeNode nd)
{
nd.Text += " (expanding...)";
Application.DoEvents();
this.BeginUpdate();
this.UseWaitCursor = true;
try
{
rsDbSql dbConn = (rsDbSql)nd.Tag;
if (dbConn == null)
{
throw new ArgumentException("Selected TreeNode does not reference a connection string.");
}
nd.Nodes.Clear();
foreach (string db in dbConn.GetDatabaseList())
{
TreeNode ndDatabase = new TreeNode();
ndDatabase.Text = db;
ndDatabase.Name = db;
ndDatabase.Tag = SqlObjType.Database;
ndDatabase.ImageKey = ndDatabase.SelectedImageKey = "Database";
ndDatabase.ContextMenuStrip = this.mnuNdDb;
TreeNode ndTables = new TreeNode("Tables");
ndTables.Name = "grpTables";
ndTables.Nodes.Add("<EMPTY>");
ndTables.Tag = SqlObjType.TableGroup;
ndTables.ContextMenuStrip = this.mnuNdGroup;
ndDatabase.Nodes.Add(ndTables);
TreeNode ndViews = new TreeNode("Views");
ndViews.Name = "grpViews";
ndViews.Nodes.Add("<EMPTY>");
ndViews.Tag = SqlObjType.ViewGroup;
ndViews.ContextMenuStrip = this.mnuNdGroup;
ndDatabase.Nodes.Add(ndViews);
TreeNode ndStoredProcs = new TreeNode("Stored Proceedures");
ndStoredProcs.Name = "grpStoredProcs";
ndStoredProcs.Nodes.Add("<EMPTY>");
ndStoredProcs.Tag = SqlObjType.StoredProcGroup;
ndStoredProcs.ContextMenuStrip = this.mnuNdGroup;
ndDatabase.Nodes.Add(ndStoredProcs);
TreeNode ndFuncsT = new TreeNode("Table-valued Functions");
ndFuncsT.Name = "grpTFuncs";
ndFuncsT.Nodes.Add("<EMPTY>");
ndFuncsT.Tag = SqlObjType.FunctionGroup;
ndFuncsT.ContextMenuStrip = this.mnuNdGroup;
TreeNode ndFuncsS = new TreeNode("Scalar-valued Functions");
ndFuncsS.Name = "grpSFuncs";
ndFuncsS.Nodes.Add("<EMPTY>");
ndFuncsS.Tag = SqlObjType.FunctionGroup;
ndFuncsS.ContextMenuStrip = this.mnuNdGroup;
TreeNode ndFuncs = new TreeNode("Functions");
ndFuncs.Nodes.Add(ndFuncsT);
ndFuncs.Nodes.Add(ndFuncsS);
ndDatabase.Nodes.Add(ndFuncs);
TreeNode ndUsers = new TreeNode("Users");
ndUsers.Nodes.Add("<EMPTY>");
ndUsers.Tag = SqlObjType.UserGroup;
ndUsers.ContextMenuStrip = this.mnuNdGroup;
ndDatabase.Nodes.Add(ndUsers);
nd.Nodes.Add(ndDatabase);
}
}
catch (Exception ex)
{
MessageBox.Show(this, string.Format("Unable to retreive the list of databases from the SQL server:\n\n{0}\n\nApplication Version: {1}", ex.Message, Application.ProductVersion), "Error");
}
finally
{
nd.Text = nd.Text.Substring(0, nd.Text.LastIndexOf('(')).TrimEnd();
this.EndUpdate();
this.UseWaitCursor = false;
}
}
