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