/// <summary> /// Test source connection and return bool based on validation /// </summary> /// <param name="Exception">will return the exception if any</param> /// <returns>bool, true is connection validated successfully</returns> private bool TestSourceConnection(out string Exception) { if (string.IsNullOrEmpty(txtSeverName.Text) || string.IsNullOrEmpty(txtSQLUserID.Text) || string.IsNullOrEmpty(txtSQLPwd.Text)) { Exception = ""; JobSettingsStatus.Text = "Few field(s) value are missing in Source details!"; MessageBox.Show("Few field(s) value are missing in Source details!", "Job Settings", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return(false); } string sConnectionString = Helper.ConnectionString(txtSeverName.Text, txtDbName.Text, txtSQLUserID.Text, txtSQLPwd.Text); ConnectionString = sConnectionString; if (PollPush.ValidateSQLConnection(sConnectionString, out Exception)) { Exception = ""; JobSettingsStatus.Text = "Source Connection validated!"; return(true); } else { JobSettingsStatus.Text = "Error - " + "Source Connection is not valid! " + Exception; JobSettingsStatus.ToolTipText = "Error - " + "Source Connection is not valid! " + Exception; return(false); } }
/// <summary> /// Purges Data if selected row is not in running state and based on user's response. /// </summary> private void PurgeData() { if (GridView.SelectedRows.Count > 0) { DataGridViewRow drSelected = GridView.SelectedRows[0]; if (drSelected.Cells[Helper.JOBSTATUS].Value.ToString() == "Stopped" || drSelected.Cells[Helper.JOBSTATUS].Value.ToString() == "Failed") { DialogResult drUserResponse = MessageBox.Show("Are you sure you want to purge the selected job?", "Live Feed", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2); if (drUserResponse == System.Windows.Forms.DialogResult.Yes) { XmlNode xnDestnDataset = m_xdData.SelectSingleNode("/Jobs/Job[@Name='" + drSelected.Cells[Helper.JOBNAME].Value.ToString() + "']"); if (xnDestnDataset != null) { XmlNode xnConnection = xnDestnDataset.SelectSingleNode("./Connection"); string sDestnDataset = xnConnection.Attributes["DatasetName"].Value.ToString(); string sTables = drSelected.Cells[Helper.JOBDATASET].Value.ToString(); string sPassword = Helper.Decrypt(xnConnection.Attributes["Password"].Value.ToString()); string datasetID = xnConnection.Attributes["DatasetID"].Value.ToString(); UserCredential uc = new UserCredential(xnConnection.Attributes["UserName"].Value.ToString(), sPassword); PollPush objPollPush = new PollPush(uc, xnConnection.Attributes["ClientID"].Value.ToString()); if (sTables.Contains(",")) //multiple tables in a job { string[] sarrTables = sTables.Split(','); foreach (string sTableName in sarrTables) { objPollPush.PurgeData(sDestnDataset, sTableName, datasetID); xnConnection.Attributes["CurrentVersion"].Value = "0"; xnDestnDataset.Attributes[Helper.JOBLASTPOLLED].Value = ""; xnDestnDataset.SelectSingleNode("./JobMetadata[@Datasetname='" + sTableName + "']").Attributes["Version"].Value = "0"; drSelected.Cells[Helper.JOBRECORDS].Value = "0"; drSelected.Cells[Helper.JOBLASTPOLLED].Value = ""; m_xdData.Save(Application.StartupPath + @"\JobSettings.xml"); } } else { objPollPush.PurgeData(sDestnDataset, sTables, datasetID); xnDestnDataset.SelectSingleNode("./JobMetadata[@Datasetname='" + sTables + "']").Attributes["Version"].Value = "0"; xnDestnDataset.Attributes[Helper.JOBLASTPOLLED].Value = ""; drSelected.Cells[Helper.JOBRECORDS].Value = "0"; drSelected.Cells[Helper.JOBLASTPOLLED].Value = ""; m_xdData.Save(Application.StartupPath + @"\JobSettings.xml"); } } } } else { JobStatus.Text = "Job with running state cannot be purged!"; } } else { JobStatus.Text = "No Job is selected to purge!"; } }
/// <summary> /// Test destination connection and return bool based on validation /// </summary> /// <returns>bool, true is connection validated successfully</returns> private bool TestDestinationConnection() { bool bIsValidated = false; PollPush objPollPush; if (string.IsNullOrEmpty(txtDatasetName.Text) || string.IsNullOrEmpty(txtClientID.Text) || string.IsNullOrEmpty(txtUserID.Text) || string.IsNullOrEmpty(txtPwd.Text)) { JobSettingsStatus.Text = "Few field(s) value are missing in Destination details!"; MessageBox.Show("Few field(s) value are missing in Destination details!", "Job Settings", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return(false); } if (listView1.CheckedItems.Count == 0) { MessageBox.Show("No table(s) are selected in Source dataset tab, please select at-least one table!", "Job Settings", MessageBoxButtons.OK, MessageBoxIcon.Warning); bIsValidated = false; return(bIsValidated); } UserCredential uc = new UserCredential(txtUserID.Text, txtPwd.Text); if (uc == null) { MessageBox.Show("User credentials provided are incorrect!", "Job Settings", MessageBoxButtons.OK, MessageBoxIcon.Error); bIsValidated = false; return(bIsValidated); } objPollPush = new PollPush(uc, txtClientID.Text); string Error; if (objPollPush.ValidatePowerBIConnecton(out Error)) { JobSettingsStatus.Text = "Destination Connection is validated!"; return(true); } else { if (Error != "") { if (Error.Contains("AADSTS70001")) { MessageBox.Show("Destination client id or credentials are incorrect.", "Job Settings", MessageBoxButtons.OK, MessageBoxIcon.Error); } JobSettingsStatus.Text = "Error - " + Error; JobSettingsStatus.ToolTipText = "Error - " + Error; } else { JobSettingsStatus.Text = "Destination Connection is not validated!"; } bIsValidated = false; } return(bIsValidated); }
/// <summary> /// Populates all tables for the source connection string /// </summary> private void PopulateDatasets() { if (txtSeverName.Text != "" && txtDbName.Text != "" && txtSQLUserID.Text != "") { listView1.Items.Clear(); string sConnectionString = Helper.ConnectionString(txtSeverName.Text, txtDbName.Text, txtSQLUserID.Text, txtSQLPwd.Text); ConnectionString = sConnectionString; string Exception = ""; if (TestSourceConnection(out Exception)) { PollPush objPollPush = new PollPush(null, null); objPollPush.ConnectionString = sConnectionString; DataSet dsTables = objPollPush.ListAllDatasets(); if (dsTables != null) { if (dsTables.Tables.Count > 0) { ListViewItem lviDataSetName = null; foreach (DataRow drData in dsTables.Tables[0].Rows) { lviDataSetName = new ListViewItem(drData["name"].ToString()); DataSet dsColumns = objPollPush.PopulateColumns(drData["name"].ToString()); lviDataSetName.Tag = PopulateColumns(dsColumns); lviDataSetName.ToolTipText = drData["name"].ToString(); listView1.Items.Add(lviDataSetName); } } JobSettingsStatus.Text = "Source Connection Datasets populated!"; } else { JobSettingsStatus.Text = "No dataset exists for given Source Connection!"; } } else { if (Exception == "") { JobSettingsStatus.Text = "Source Connection is not valid, datasets cannot be loaded!"; } else { JobSettingsStatus.Text = "Error - " + Exception; JobSettingsStatus.ToolTipText = "Error - " + Exception; } } } }
/// <summary> /// Starts infinite loop to pull and push data. This is executed on a separate thread from within the calling code. /// </summary> internal void PushData() { Hashtable objDatasetVersions = new Hashtable(); string sErrorMessage = ""; PollPush objPollPush = new PollPush(UserCredential, ClientID); while (true) { objPollPush.ConnectionString = ConnectionString; objPollPush.PowerBIDatasetName = PowerBIDatasetName; objPollPush.IsInitialLoad = IsIntialLoad; objPollPush.datasetId = datasetId; if (TableName.Contains(",")) { string[] sarrTables = TableName.Split(','); foreach (string sTableName in sarrTables) { objPollPush.CurrentVersion = CurrentVersion[sTableName].ToString(); string sColumns = JobDetails.SelectSingleNode("./JobMetadata[@Datasetname='" + sTableName + "']").Attributes["Columns"].Value; sErrorMessage = objPollPush.ValidatePushData(sTableName, sColumns, out RecordsAffected); CurrentVersion[sTableName] = objPollPush.CurrentVersion; if (RefreshJobDetailUI != null) { RefreshJobDetailUI(SelectedRow, sTableName, objPollPush.CurrentVersion, sErrorMessage, RecordsAffected); } } } else { if (CurrentVersion[TableName] != null) { objPollPush.CurrentVersion = CurrentVersion[TableName].ToString(); string sColumns = JobDetails.SelectSingleNode("./JobMetadata[@Datasetname='" + TableName + "']").Attributes["Columns"].Value; sErrorMessage = objPollPush.ValidatePushData(TableName, sColumns, out RecordsAffected); CurrentVersion[TableName] = objPollPush.CurrentVersion; if (RefreshJobDetailUI != null) { RefreshJobDetailUI(SelectedRow, TableName, objPollPush.CurrentVersion, sErrorMessage, RecordsAffected); } } } Thread.Sleep(Interval * 1000); } }
/// <summary> /// Creates the dataset in Power BI when user clicks save. Dataset will be created based on destination connection string and source tables /// </summary> /// <returns>bool, true if dataset does not exists while creating job</returns> private bool CreateDatasetPowerBI(out string[] NewDataset) { NewDataset = null; //Check the datasets exists or not... UserCredential uc = new UserCredential(txtUserID.Text, txtPwd.Text); PollPush objPollPush = new PollPush(uc, txtClientID.Text); bool bIsValidated = false; var datasets = objPollPush.GetAllDatasets().Datasets(txtDatasetName.Text); if (datasets.Count() != 0) { MessageBox.Show("'" + txtDatasetName.Text.ToUpper() + "' dataset already exists! Delete it before attempting to re-create.", "Job Settings", MessageBoxButtons.OK, MessageBoxIcon.Warning); txtDatasetName.Text = ""; txtDatasetName.Focus(); bIsValidated = false; } else { //Create new dataset objPollPush.ConnectionString = ConnectionString; objPollPush.PowerBIDatasetName = txtDatasetName.Text; objPollPush.IsInitialLoad = chkIsFullLoad.Checked; string sTableName = ""; foreach (ListViewItem lviChecked in listView1.CheckedItems) { if (sTableName == "") { sTableName = lviChecked.Text; } else { sTableName += "," + lviChecked.Text; } } bIsValidated = objPollPush.CreateDatasets(sTableName, xdData.SelectSingleNode("/Jobs/Job[@Name='" + txtJobName.Text + "']"), out NewDataset); } return(bIsValidated); }
/// <summary> /// Starts infinite loop to pull and push data. This is executed on a separate thread from within the calling code. /// </summary> internal void PushData() { Hashtable objDatasetVersions = new Hashtable(); string sErrorMessage = ""; PollPush objPollPush = new PollPush(UserCredential, ClientID); while (true) { objPollPush.ConnectionString = ConnectionString; objPollPush.PowerBIDatasetName = PowerBIDatasetName; objPollPush.IsInitialLoad = IsIntialLoad; objPollPush.datasetId = datasetId; if (TableName.Contains(",")) { string[] sarrTables = TableName.Split(','); foreach (string sTableName in sarrTables) { objPollPush.CurrentVersion = CurrentVersion[sTableName].ToString(); string sColumns = JobDetails.SelectSingleNode("./JobMetadata[@Datasetname='" + sTableName + "']").Attributes["Columns"].Value; sErrorMessage = objPollPush.ValidatePushData(sTableName,sColumns, out RecordsAffected); CurrentVersion[sTableName] = objPollPush.CurrentVersion; if (RefreshJobDetailUI != null) RefreshJobDetailUI(SelectedRow, sTableName, objPollPush.CurrentVersion, sErrorMessage, RecordsAffected); } } else { if (CurrentVersion[TableName] != null) { objPollPush.CurrentVersion = CurrentVersion[TableName].ToString(); string sColumns = JobDetails.SelectSingleNode("./JobMetadata[@Datasetname='" + TableName + "']").Attributes["Columns"].Value; sErrorMessage = objPollPush.ValidatePushData(TableName, sColumns, out RecordsAffected); CurrentVersion[TableName] = objPollPush.CurrentVersion; if (RefreshJobDetailUI != null) RefreshJobDetailUI(SelectedRow, TableName, objPollPush.CurrentVersion, sErrorMessage, RecordsAffected); } } Thread.Sleep(Interval * 1000); } }
/// <summary> /// Purges Data if selected row is not in running state and based on user's response. /// </summary> private void PurgeData() { if (GridView.SelectedRows.Count > 0) { DataGridViewRow drSelected = GridView.SelectedRows[0]; if (drSelected.Cells[Helper.JOBSTATUS].Value.ToString() == "Stopped" || drSelected.Cells[Helper.JOBSTATUS].Value.ToString() == "Failed") { DialogResult drUserResponse = MessageBox.Show("Are you sure you want to purge the selected job?", "Live Feed", MessageBoxButtons.YesNo, MessageBoxIcon.Question,MessageBoxDefaultButton.Button2); if (drUserResponse == System.Windows.Forms.DialogResult.Yes) { XmlNode xnDestnDataset = m_xdData.SelectSingleNode("/Jobs/Job[@Name='" + drSelected.Cells[Helper.JOBNAME].Value.ToString() + "']"); if (xnDestnDataset != null) { XmlNode xnConnection = xnDestnDataset.SelectSingleNode("./Connection"); string sDestnDataset = xnConnection.Attributes["DatasetName"].Value.ToString(); string sTables = drSelected.Cells[Helper.JOBDATASET].Value.ToString(); string sPassword = Helper.Decrypt(xnConnection.Attributes["Password"].Value.ToString()); string datasetID = xnConnection.Attributes["DatasetID"].Value.ToString(); UserCredential uc = new UserCredential(xnConnection.Attributes["UserName"].Value.ToString(), sPassword); PollPush objPollPush = new PollPush(uc, xnConnection.Attributes["ClientID"].Value.ToString()); if (sTables.Contains(","))//multiple tables in a job { string[] sarrTables = sTables.Split(','); foreach (string sTableName in sarrTables) { objPollPush.PurgeData(sDestnDataset, sTableName, datasetID); xnConnection.Attributes["CurrentVersion"].Value = "0"; xnDestnDataset.Attributes[Helper.JOBLASTPOLLED].Value = ""; xnDestnDataset.SelectSingleNode("./JobMetadata[@Datasetname='" + sTableName + "']").Attributes["Version"].Value = "0"; drSelected.Cells[Helper.JOBRECORDS].Value = "0"; drSelected.Cells[Helper.JOBLASTPOLLED].Value = ""; m_xdData.Save(Application.StartupPath + @"\JobSettings.xml"); } } else { objPollPush.PurgeData(sDestnDataset, sTables, datasetID); xnDestnDataset.SelectSingleNode("./JobMetadata[@Datasetname='" + sTables + "']").Attributes["Version"].Value = "0"; xnDestnDataset.Attributes[Helper.JOBLASTPOLLED].Value = ""; drSelected.Cells[Helper.JOBRECORDS].Value = "0"; drSelected.Cells[Helper.JOBLASTPOLLED].Value = ""; m_xdData.Save(Application.StartupPath + @"\JobSettings.xml"); } } } } else JobStatus.Text = "Job with running state cannot be purged!"; } else JobStatus.Text = "No Job is selected to purge!"; }
/// <summary> /// Test destination connection and return bool based on validation /// </summary> /// <returns>bool, true is connection validated successfully</returns> private bool TestDestinationConnection() { bool bIsValidated = false; PollPush objPollPush; if (string.IsNullOrEmpty(txtDatasetName.Text) || string.IsNullOrEmpty(txtClientID.Text) || string.IsNullOrEmpty(txtUserID.Text) || string.IsNullOrEmpty(txtPwd.Text)) { JobSettingsStatus.Text = "Few field(s) value are missing in Destination details!"; MessageBox.Show("Few field(s) value are missing in Destination details!", "Job Settings", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return false; } if (listView1.CheckedItems.Count == 0) { MessageBox.Show("No table(s) are selected in Source dataset tab, please select at-least one table!","Job Settings",MessageBoxButtons.OK,MessageBoxIcon.Warning); bIsValidated = false; return bIsValidated; } UserCredential uc = new UserCredential(txtUserID.Text, txtPwd.Text); if (uc == null) { MessageBox.Show("User credentials provided are incorrect!", "Job Settings", MessageBoxButtons.OK, MessageBoxIcon.Error); bIsValidated = false; return bIsValidated; } objPollPush = new PollPush(uc, txtClientID.Text); string Error; if (objPollPush.ValidatePowerBIConnecton(out Error)) { JobSettingsStatus.Text = "Destination Connection is validated!"; return true; } else { if (Error != "") { if (Error.Contains("AADSTS70001")) MessageBox.Show("Destination client id or credentials are incorrect.", "Job Settings", MessageBoxButtons.OK, MessageBoxIcon.Error); JobSettingsStatus.Text = "Error - " + Error; JobSettingsStatus.ToolTipText = "Error - " + Error; } else JobSettingsStatus.Text = "Destination Connection is not validated!"; bIsValidated = false; } return bIsValidated; }
/// <summary> /// Populates all tables for the source connection string /// </summary> private void PopulateDatasets() { if (txtSeverName.Text != "" && txtDbName.Text != "" && txtSQLUserID.Text != "") { listView1.Items.Clear(); string sConnectionString = Helper.ConnectionString(txtSeverName.Text, txtDbName.Text, txtSQLUserID.Text, txtSQLPwd.Text); ConnectionString = sConnectionString; string Exception = ""; if (TestSourceConnection(out Exception)) { PollPush objPollPush = new PollPush(null, null); objPollPush.ConnectionString = sConnectionString; DataSet dsTables = objPollPush.ListAllDatasets(); if (dsTables != null) { if (dsTables.Tables.Count > 0) { ListViewItem lviDataSetName = null; foreach (DataRow drData in dsTables.Tables[0].Rows) { lviDataSetName = new ListViewItem(drData["name"].ToString()); DataSet dsColumns = objPollPush.PopulateColumns(drData["name"].ToString()); lviDataSetName.Tag = PopulateColumns(dsColumns); lviDataSetName.ToolTipText = drData["name"].ToString(); listView1.Items.Add(lviDataSetName); } } JobSettingsStatus.Text = "Source Connection Datasets populated!"; } else JobSettingsStatus.Text = "No dataset exists for given Source Connection!"; } else { if (Exception == "") JobSettingsStatus.Text = "Source Connection is not valid, datasets cannot be loaded!"; else { JobSettingsStatus.Text = "Error - " + Exception; JobSettingsStatus.ToolTipText = "Error - " + Exception; } } } }
/// <summary> /// Creates the dataset in Power BI when user clicks save. Dataset will be created based on destination connection string and source tables /// </summary> /// <returns>bool, true if dataset does not exists while creating job</returns> private bool CreateDatasetPowerBI(out string[] NewDataset) { NewDataset = null; //Check the datasets exists or not... UserCredential uc = new UserCredential(txtUserID.Text, txtPwd.Text); PollPush objPollPush = new PollPush(uc,txtClientID.Text); bool bIsValidated = false; var datasets = objPollPush.GetAllDatasets().Datasets(txtDatasetName.Text); if (datasets.Count() != 0) { MessageBox.Show("'" + txtDatasetName.Text.ToUpper() + "' dataset already exists! Delete it before attempting to re-create.", "Job Settings", MessageBoxButtons.OK, MessageBoxIcon.Warning); txtDatasetName.Text = ""; txtDatasetName.Focus(); bIsValidated = false; } else { //Create new dataset objPollPush.ConnectionString = ConnectionString; objPollPush.PowerBIDatasetName = txtDatasetName.Text; objPollPush.IsInitialLoad = chkIsFullLoad.Checked; string sTableName = ""; foreach (ListViewItem lviChecked in listView1.CheckedItems) { if (sTableName == "") sTableName = lviChecked.Text; else sTableName += "," + lviChecked.Text; } bIsValidated = objPollPush.CreateDatasets(sTableName,xdData.SelectSingleNode("/Jobs/Job[@Name='" + txtJobName.Text + "']"),out NewDataset); } return bIsValidated; }
/// <summary> /// Check the selected list view item which is a table name has Change Tracking enabled or not. If not enable then that table cannot be selected /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void listView1_ItemCheck(object sender, ItemCheckEventArgs e) { if (IsNewJob) { if (e.NewValue == CheckState.Checked) { string sError = ""; if (PollPush.IsChangeTrackingEnable(ConnectionString, listView1.Items[e.Index].Text, out sError) == false) { if (sError == "") { MessageBox.Show("'" + listView1.Items[e.Index].Text + "' table does not have Change Tracking enable, so this cannot be selected!", "Job Settings", MessageBoxButtons.OK, MessageBoxIcon.Information); e.NewValue = CheckState.Unchecked; listView1.Items[e.Index].Checked = false; } else { JobSettingsStatus.Text = "Error - " + sError; JobSettingsStatus.ToolTipText = "Error - " + sError; } } else { CreateNode(listView1.Items[e.Index].Text, listView1.Items[e.Index].Tag.ToString()); if (listView2.Visible && listView1.Items[e.Index].Selected) { ViewColumns(listView1.Items[e.Index], true); if (listView2.CheckedItems.Count == listView2.Items.Count) { chkSelectAll.Checked = true; } if (xdData.SelectSingleNode("/Jobs/Job[@Name='" + txtJobName.Text + "']") != null) { SetDatasets(); } } } } else if (e.NewValue == CheckState.Unchecked) { if (listView1.Items[e.Index].Selected && listView2.CheckedItems.Count > 0) { foreach (ListViewItem lviChecked in listView2.CheckedItems) { lviChecked.Checked = false; } } if (listView1.Items[e.Index] != null && listView1.CheckedItems.Count <= 1) { XmlNode xnJobData = xdData.SelectSingleNode("/Jobs/Job[@Name='" + txtJobName.Text + "']"); xdData.DocumentElement.RemoveChild(xnJobData); } // end (if (listView1.SelectedItems.Count > 0)) else { XmlNode xnJobData = xdData.SelectSingleNode("/Jobs/Job[@Name='" + txtJobName.Text + "']"); XmlNode xnMetadata = xnJobData.SelectSingleNode("./JobMetadata[@Datasetname='" + listView1.Items[e.Index].Text + "']"); if (xnMetadata != null) { xnJobData.RemoveChild(xnMetadata); } } } } }