public SQLRunner(SQLInfo sqlInfo) { BackgroundWorker backgroundWorker = new BackgroundWorker(); backgroundWorker.DoWork += this.BW_DoWork; backgroundWorker.RunWorkerCompleted += this.BW_RunWorkerCompleted; backgroundWorker.RunWorkerAsync(sqlInfo); }
private void CheckViewFinished(SQLInfo sqlInfo) { TDViews teradataView = null; foreach (TDViews view in this.ViewsListBox.Items) { if (view.DataBaseName == sqlInfo.DatabaseName && view.ViewName == sqlInfo.ViewName) { teradataView = view; } } if (teradataView == null) { return; } if (sqlInfo.ErrorOccured == false) { teradataView.ViewState = TDViews.TDViewState.NoErr; ViewsListBox.Refresh(); } else { teradataView.ViewState = TDViews.TDViewState.Err; teradataView.ErrorText = sqlInfo.Error.Message; this.errorViews++; SQLInfo sqlinfo2 = new SQLInfo(); sqlinfo2.DatabaseName = teradataView.DataBaseName; sqlinfo2.ViewName = teradataView.ViewName; sqlinfo2.OleDbConnectionString = new OleDbConnection(string.Format(ConnectionString, this.tbUserID.Text, this.tbPassword.Text)); sqlinfo2.OleDbCommandObject = new OleDbCommand("SHOW VIEW " + teradataView.DataBaseName + "." + teradataView.ViewName); SQLRunner sqlRunner = new SQLRunner(sqlinfo2); sqlRunner.OnSQLFinishedRunning += this.ShowViewFinished; ViewsListBox.Refresh(); } this.checkedViews++; double percentComplete = Math.Round(this.checkedViews / (double)ViewsListBox.Items.Count, 4) * 100; this.toolStripStatusLabel1.Text = percentComplete + "% Complete; Status: " + this.errorViews + "/" + ViewsListBox.Items.Count + " didn't run."; }
private SQLInfo GetQueryResults(SQLInfo sqlInfo) { OleDbConnection cn = sqlInfo.OleDbConnectionString; OleDbCommand cmd = sqlInfo.OleDbCommandObject; try { cmd.Connection = cn; cmd.CommandTimeout = 0; cn.Open(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet("report"); da.Fill(ds, "DataTable"); sqlInfo.ResultsDataTable = ds.Tables["DataTable"]; cn.Close(); } catch (OleDbException exception) { sqlInfo.ErrorOccured = true; sqlInfo.Error = exception; } finally { if (cn.State == ConnectionState.Open) { cn.Close(); } cn = null; cmd = null; } return sqlInfo; }
private void SQLR_OnSQLFinishedRunning(SQLInfo sqlInfo) { if (sqlInfo.ErrorOccured == false) { foreach (DataRow dr in sqlInfo.ResultsDataTable.Rows) { TDViews teradataViews = new TDViews(dr["DatabaseName"].ToString().Trim(), dr["TableName"].ToString().Trim()); teradataViews.CreateTimeStamp = dr.Table.Columns.Contains("CreateTimeStamp") ? dr["CreateTimeStamp"].ToString().Trim() : string.Empty; teradataViews.CreatorID = dr["CreatorName"].ToString().Trim(); teradataViews.CreatorName = dr.Table.Columns.Contains("OWNER_NAME") ? dr["OWNER_NAME"].ToString().Trim() : string.Empty; teradataViews.LastAlterID = dr.Table.Columns.Contains("LastAlterName") ? dr["LastAlterName"].ToString().Trim() : string.Empty; teradataViews.LastAlterName = dr.Table.Columns.Contains("Last_Alter_Name") ? dr["Last_Alter_Name"].ToString().Trim() : string.Empty; teradataViews.LastAccessTimeStamp = dr.Table.Columns.Contains("LastAccessTimeStamp") ? dr["LastAccessTimeStamp"].ToString().Trim() : string.Empty; ViewsListBox.Items.Add(teradataViews); } this.btnCheckViews.Enabled = true; this.toolStripStatusLabel1.Text = "Found " + sqlInfo.ResultsDataTable.Rows.Count.ToString() + " views"; } else { this.toolStripStatusLabel1.Text = "Error Occured: " + sqlInfo.Error.Message; } }
private void ShowViewFinished(SQLInfo sqlInfo) { if (sqlInfo.ErrorOccured == false) { foreach (TDViews teradataView in this.ViewsListBox.Items) { if (teradataView.DataBaseName.ToUpper() == sqlInfo.DatabaseName.ToUpper() && teradataView.ViewName.ToUpper() == sqlInfo.ViewName.ToUpper()) { teradataView.Script = sqlInfo.ResultsDataTable.Rows[0][0].ToString(); } } } }
private void GetViews() { this.toolStripStatusLabel1.Text = "Getting views please wait"; this.btnCheckViews.Enabled = false; ViewsListBox.Items.Clear(); SQLInfo sqlInfo = new SQLInfo(); sqlInfo.OleDbConnectionString = new OleDbConnection(this.ConnectionString); sqlInfo.OleDbCommandObject = new OleDbCommand("SELECT TBLS.* FROM DBC.TABLES TBLS WHERE (DATABASENAME LIKE '" + this.tbDatabaseName.Text + "') AND TABLEKIND LIKE '%V%'"); SQLRunner sqlRunner = new SQLRunner(sqlInfo); sqlRunner.OnSQLFinishedRunning += this.SQLR_OnSQLFinishedRunning; }
private void CheckViews() { foreach (TDViews teradataView in ViewsListBox.Items) { teradataView.ViewState = TDViews.TDViewState.Checking; ViewsListBox.Refresh(); SQLInfo sqlInfo = new SQLInfo(); sqlInfo.OleDbConnectionString = new OleDbConnection(string.Format(ConnectionString, this.tbUserID.Text, this.tbPassword.Text)); sqlInfo.OleDbCommandObject = new OleDbCommand("EXPLAIN SELECT * FROM " + teradataView.DataBaseName + "." + teradataView.ViewName); sqlInfo.DatabaseName = teradataView.DataBaseName; sqlInfo.ViewName = teradataView.ViewName; SQLRunner sqlRunner = new SQLRunner(sqlInfo); sqlRunner.OnSQLFinishedRunning += this.CheckViewFinished; } }