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; } }