public static string GetBuiltConnectionString(string inputConnectionString)
{
MSDASC.DataLinks oDL = null;
string res = string.Empty;
try
{
oDL = new MSDASC.DataLinksClass();
ADODB.Connection conn = new ADODB.ConnectionClass();
conn.ConnectionString = inputConnectionString;
object oConn = (object)conn;
if (oDL.PromptEdit(ref oConn))
{
res = conn.ConnectionString;
}
}
catch
{
try
{
ADODB._Connection oConn = (ADODB._Connection)oDL.PromptNew();
if (oConn != null)
{
res = oConn.ConnectionString.ToString();
}
}
catch (Exception ex)
{
MyExceptionHandler.NewEx(ex);
}
}
return(string.IsNullOrEmpty(res) ? null : res);
}
private void openProject()
{
if (!saveCurrentProjectIfModified())
{
return;
}
try
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.DefaultExt = "xml";
ofd.Filter = "SCD Merge Wizard Project Files|*.mwpxml";
ofd.RestoreDirectory = true;
ofd.Title = "Open Project";
if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
ReadFile(ofd.FileName);
}
}
catch (Exception ex)
{
MyExceptionHandler.NewEx(ex);
}
}
private void buttonOpenInSSMS_Click(object sender, EventArgs e)
{
try
{
string fileName = Path.GetTempPath() + "SCD Merge Wizard";
if (!Directory.Exists(fileName))
{
Directory.CreateDirectory(fileName);
}
fileName = Path.GetTempPath() + "SCD Merge Wizard\\ScdMergeWizard_" + Guid.NewGuid() + ".sql";
File.WriteAllLines(fileName, myRichTextBox1.RichTextBox.Lines);
System.Diagnostics.Process process = new System.Diagnostics.Process();
System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo();
//startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
startInfo.FileName = "ssms.exe";
startInfo.Arguments = "-e \"" + fileName + "\"";
process.StartInfo = startInfo;
process.Start();
}
catch (Exception ex)
{
MyExceptionHandler.NewEx(ex);
}
}
public static void Application_ThreadException(object sender, System.Threading.ThreadExceptionEventArgs e)
{
try
{
MyExceptionHandler.NewEx(e.Exception);
}
finally
{
Process.GetCurrentProcess().Kill();
}
}
static void CurrentDomain_UnhandledException(object sender, UnhandledExceptionEventArgs e)
{
try
{
Exception ex = (Exception)e.ExceptionObject;
MyExceptionHandler.NewEx(ex);
}
finally
{
Process.GetCurrentProcess().Kill();
}
}
public override DbConnection GetConn()
{
try
{
if (_conn == null && !string.IsNullOrEmpty(_connectionString))
{
_conn = new OleDbConnection(_connectionString);
}
if (_conn != null && _conn.State != ConnectionState.Open)
{
_conn.Open();
}
return(_conn as OleDbConnection);
}
catch (Exception ex)
{
MyExceptionHandler.NewEx(ex);
}
return(null);
}
private void buttonSaveQuery_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.DefaultExt = "sql";
sfd.Filter = "SQL Files|*.sql";
sfd.RestoreDirectory = true;
sfd.Title = "Save SCD Merge Query";
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
File.WriteAllLines(sfd.FileName, myRichTextBox1.RichTextBox.Lines);
MessageBox.Show("Query saved successfully", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MyExceptionHandler.NewEx(ex);
}
}
}
private void checkUsingSourceQuery()
{
string commandText = "";
var initialCursor = this.Cursor;
object[] meta = new object[GlobalVariables.ColumnMappings.Count(cm => cm.TransformationCode == ETransformationCode.BUSINESS_KEY) + 1];
this.Cursor = Cursors.WaitCursor;
try
{
DbCommand cmd = GlobalVariables.SourceConnection.GetConn().CreateCommand();
string businessKeysCsv = string.Join(",", GlobalVariables.ColumnMappings.Where(cm => cm.TransformationCode == ETransformationCode.BUSINESS_KEY).Select(c => c.SourceColumn).ToArray());
commandText += string.Format("WITH cte as ( SELECT {0} FROM {1} ) SELECT {0}, COUNT(*) AS [COUNT] FROM cte GROUP BY {0} HAVING COUNT(*) > 1", businessKeysCsv, GlobalVariables.SourceObjectName);
cmd.CommandText = commandText;
DbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
if (reader.FieldCount > 0)
{
for (int i = 0; i < reader.FieldCount; i++)
{
DataGridViewColumn col = new DataGridViewColumn();
DataGridViewCell cell = new DataGridViewTextBoxCell();
col.CellTemplate = cell;
col.Name = reader.GetName(i);
dataGridView1.Columns.Add(col);
}
int rowsCount = 0;
while (reader.Read())
{
reader.GetValues(meta);
dataGridView1.Rows.Add(meta);
rowsCount++;
if (rowsCount >= MAX_ROWS)
{
break;
}
}
reader.Close();
MessageBox.Show("Unfortunately, there are some duplicate Business Keys found. Check table for details.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
reader.Close();
MessageBox.Show("Congratulations, no duplicate Business Keys found", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
this.Cursor = initialCursor;
MyExceptionHandler.NewEx(ex);
}
this.Cursor = initialCursor;
}
private void checkUsingDotNetDataTable()
{
string commandText;
try
{
if (GlobalVariables.SourceIsTableOrViewMode == true)
{
commandText = "SELECT * FROM " + GlobalVariables.SourceObjectName;
}
else
{
commandText = GlobalVariables.SourceCommandText;
}
DbDataAdapter adapter = GlobalVariables.SourceConnection.CreateAdapter(commandText); // new DbDataAdapter(commandText, GlobalVariables.SourceConnection.GetConn());
DataTable dt = new DataTable();
adapter.Fill(dt);
string[] columnNames = GlobalVariables.ColumnMappings.Where(cm => cm.TransformationCode == ETransformationCode.BUSINESS_KEY).Select(c => c.SourceColumn.Substring(1, c.SourceColumn.Length - 2)).ToArray();
DataView dv = new DataView(dt);
//getting distinct values for group column
DataTable dtGroup = dv.ToTable(true, columnNames);
//adding column for the row count
dtGroup.Columns.Add("Count", typeof(int));
//looping thru distinct values for the group, counting
foreach (DataRow dr in dtGroup.Rows)
{
string filter = "";
for (int i = 0; i < columnNames.Length; i++)
{
if (!string.IsNullOrEmpty(filter))
{
filter += " AND ";
}
MyDbColumn c1 = GlobalVariables.SourceColumns.Find(sc => sc.ColumnName.Equals("[" + columnNames[i] + "]"));
filter += c1.ColumnName + " = ";
if (!c1.IsNumeric)
{
filter += "'";
}
filter += dr[columnNames[i]].ToString();
if (!c1.IsNumeric)
{
filter += "'";
}
}
string func = string.Format("Count({0})", GlobalVariables.ColumnMappings.Where(cm => cm.TransformationCode != ETransformationCode.SKIP && cm.TransformationCode != ETransformationCode.BUSINESS_KEY && cm.IsSourceColumnDefined).First().SourceColumn);
dr["Count"] = dt.Compute(func, filter);
}
dt.Dispose();
DataRow[] rows = (from r in dtGroup.AsEnumerable()
where r.Field <int>("Count") > 1
select r).ToArray();
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
if (rows.Length > 0)
{
for (int i = 0; i < rows[0].Table.Columns.Count; i++)
{
DataGridViewColumn col = new DataGridViewColumn();
DataGridViewCell cell = new DataGridViewTextBoxCell();
col.CellTemplate = cell;
col.Name = rows[0].Table.Columns[i].ColumnName;
dataGridView1.Columns.Add(col);
}
foreach (var row in rows)
{
dataGridView1.Rows.Add(row.ItemArray);
}
MessageBox.Show("Unfortunately, there are some duplicate Business Keys found. Check table for details.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show("Congratulations, no duplicate Business Keys found", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
dataGridView1.Rows.Clear();
MyExceptionHandler.NewEx(ex);
}
}
public void ReadFile(string fileName)
{
XmlNode node;
string src, tgt, val;
StringBuilder sbLoadingErrors = new StringBuilder();
if (!File.Exists(fileName))
{
MessageBox.Show("File does not exists!");
return;
}
if (!thePageNavigator1.GotoBeginning())
{
return;
}
GlobalVariables.LoadedColumnMappings.Clear();
GlobalVariables.LoadedUserColumnsDefinitions.Clear();
src = tgt = val = string.Empty;
//trfType = ETransformationType.SKIP;
pageSourceConnection.rtbSrcConnStr.Text = string.Empty;
pageSourceConnection.cbxSrcTableOrViewName.Text = string.Empty;
pageTargetConnection.rtbTgtConnStr.Text = string.Empty;
pageTargetConnection.cbxTgtTableOrViewName.Text = string.Empty;
try
{
XmlDocument doc = new XmlDocument();
doc.Load(fileName);
// Source Connection
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/SourceConnection/ConnectionString");
if (node == null)
{
node = doc.DocumentElement.SelectSingleNode("/MERGER/SourceConnection/ConnectionString");
}
if (node != null)
{
pageSourceConnection.rtbSrcConnStr.Text = node.InnerText;
}
else
{
sbLoadingErrors.AppendLine("Cannot read Source ConnectionString");
}
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/SourceConnection/IsTableOrView");
if (node == null)
{
node = doc.DocumentElement.SelectSingleNode("/MERGER/SourceConnection/IsTableOrView");
}
if (node != null)
{
pageSourceConnection.rbIsTableOrView.Checked = (node.InnerText == "True");
pageSourceConnection.rbIsCommandText.Checked = (node.InnerText != "True");
}
else
{
sbLoadingErrors.AppendLine("Cannot read IsTableOrView");
}
if (pageSourceConnection.rbIsTableOrView.Checked)
{
GlobalVariables.SourceConnection = DbHelper.CreateConnection(pageSourceConnection.rtbSrcConnStr.Text);
pageSourceConnection.cbxSrcTableOrViewName.AddItems(DbHelper.GetTablesViewsAndSynonyms(GlobalVariables.SourceConnection));
}
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/SourceConnection/TableOrViewName");
if (node == null)
{
node = doc.DocumentElement.SelectSingleNode("/MERGER/SourceConnection/TableOrView");
}
if (node != null)
{
pageSourceConnection.cbxSrcTableOrViewName.Text = node.InnerText;
}
else
{
sbLoadingErrors.AppendLine("Cannot read Source TableOrViewName");
}
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/SourceConnection/CommandText");
if (node == null)
{
node = doc.DocumentElement.SelectSingleNode("/MERGER/SourceConnection/CommandText");
}
if (node != null)
{
pageSourceConnection.rtbCommandText.Text = node.InnerText.Replace("<![CDATA[", "").Replace("]]>", "");
}
else
{
sbLoadingErrors.AppendLine("Cannot read CommandText");
}
// Target Connection
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/TargetConnection/ConnectionString");
if (node == null)
{
node = doc.DocumentElement.SelectSingleNode("/MERGER/TargetConnection/ConnectionString");
}
if (node != null)
{
pageTargetConnection.rtbTgtConnStr.Text = node.InnerText;
}
else
{
sbLoadingErrors.AppendLine("Cannot read Target ConnectionString");
}
GlobalVariables.TargetConnection = DbHelper.CreateConnection(pageTargetConnection.rtbTgtConnStr.Text);
pageTargetConnection.cbxTgtTableOrViewName.AddItems(DbHelper.GetTablesViewsAndSynonyms(GlobalVariables.TargetConnection));
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/TargetConnection/TableOrViewName");
if (node == null)
{
node = doc.DocumentElement.SelectSingleNode("/MERGER/TargetConnection/TableOrView");
}
if (node != null)
{
pageTargetConnection.cbxTgtTableOrViewName.Text = node.InnerText;
}
else
{
sbLoadingErrors.AppendLine("Cannot read TableOrViewName");
}
// User Variables
foreach (XmlNode n in doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/UserVariables").ChildNodes)
{
MyUserVariable uv = new MyUserVariable();
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/UserVariables/{0}/Name", n.Name));
uv.Name = node.InnerText;
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/UserVariables/{0}/DataType", n.Name));
uv.DataType = node.InnerText;
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/UserVariables/{0}/Value", n.Name));
uv.Definition = node.InnerText;
GlobalVariables.LoadedUserColumnsDefinitions.Add(uv);
}
// Options
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/Options/RecordsOnTargetNotFoundOnSourceMode");
if (node != null)
{
GlobalVariables.Options.RecordsOnTargetNotFoundOnSourceMode = (ERecordsOnTargetNotFoundOnSourceMode)Enum.Parse(typeof(ERecordsOnTargetNotFoundOnSourceMode), node.InnerText);
}
else
{
node = doc.DocumentElement.SelectSingleNode("/MERGER/Options/RecordsOnTargetNotFoundOnSourceEx");
if (node != null)
{
if (node.InnerText.Equals("UpdateDateTo"))
{
GlobalVariables.Options.RecordsOnTargetNotFoundOnSourceMode = ERecordsOnTargetNotFoundOnSourceMode.UpdateTargetField;
}
else if (node.InnerText.Equals("PhysicallyDelete"))
{
GlobalVariables.Options.RecordsOnTargetNotFoundOnSourceMode = ERecordsOnTargetNotFoundOnSourceMode.PhysicallyDelete;
}
else if (node.InnerText.Equals("DoNothing"))
{
GlobalVariables.Options.RecordsOnTargetNotFoundOnSourceMode = ERecordsOnTargetNotFoundOnSourceMode.DoNothing;
}
}
else
{
sbLoadingErrors.AppendLine("Cannot read RecordsOnTargetNotFoundOnSourceMode");
}
}
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/Options/IgnoreDatabasePrefix");
if (node == null)
{
node = doc.DocumentElement.SelectSingleNode("/MERGER/Options/IgnoreDatabasePrefix");
}
if (node != null)
{
GlobalVariables.Options.IgnoreDatabasePrefix = (node.InnerText == "True");
}
else
{
sbLoadingErrors.AppendLine("Cannot read IgnoreDatabasePrefix");
}
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/Options/ShowExtendedComments");
if (node == null)
{
node = doc.DocumentElement.SelectSingleNode("/MERGER/Options/ShowExtendedComments");
}
if (node != null)
{
GlobalVariables.Options.ShowExtendedComments = (node.InnerText == "True");
}
else
{
sbLoadingErrors.AppendLine("Cannot read ShowExtendedComments");
}
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/Options/SCD2VersionNumberMode");
if (node != null)
{
GlobalVariables.Options.SCD2VersionNumberMode = (ESCD2VersionNumberMode)Enum.Parse(typeof(ESCD2VersionNumberMode), node.InnerText);
}
else
{
sbLoadingErrors.AppendLine("Cannot read VersionNumberResetOnSCD2");
}
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/Options/SCD13UpdateMode");
if (node != null)
{
GlobalVariables.Options.SCD13UpdateMode = (ESCD13UpdateMode)Enum.Parse(typeof(ESCD13UpdateMode), node.InnerText);
}
else
{
sbLoadingErrors.AppendLine("Cannot read SCD13UpdateMode");
}
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/Options/ComparisonMethod");
if (node != null)
{
GlobalVariables.Options.ComparisonMethod = (EComparisonMethod)Enum.Parse(typeof(EComparisonMethod), node.InnerText);
}
else
{
sbLoadingErrors.AppendLine("Cannot read ComparisonMethod");
}
// Column Mappings
node = doc.DocumentElement.SelectSingleNode("/ScdMergeWizard/ColumnMappings");
if (node != null)
{
foreach (XmlNode n in node.ChildNodes)
{
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/ColumnMappings/{0}/SourceColumn", n.Name));
var srcc = node.InnerText;
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/ColumnMappings/{0}/TransformationType", n.Name));
var trfc = (ETransformationCode)Enum.Parse(typeof(ETransformationCode), node.InnerText);
ETargetDatabaseType tgtdbt;
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/ColumnMappings/{0}/TargetDatabaseType", n.Name));
if (node != null)
{
tgtdbt = (ETargetDatabaseType)Enum.Parse(typeof(ETargetDatabaseType), node.InnerText);
}
else
{
tgtdbt = ETargetDatabaseType.TARGET;
}
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/ColumnMappings/{0}/TargetColumn", n.Name));
var tgtc = node.InnerText;
string val1c = string.Empty, val2c = string.Empty, cins = string.Empty, cupd = string.Empty, cdel = string.Empty;
// Old version
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/ColumnMappings/{0}/Value1Column", n.Name));
if (node != null)
{
val1c = node.InnerText;
}
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/ColumnMappings/{0}/Value2Column", n.Name));
if (node != null)
{
val2c = node.InnerText;
}
// New version
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/ColumnMappings/{0}/CustomInsertValue", n.Name));
if (node != null)
{
cins = node.InnerText;
}
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/ColumnMappings/{0}/CustomUpdateValue", n.Name));
if (node != null)
{
cupd = node.InnerText;
}
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/ColumnMappings/{0}/CustomDeleteValue", n.Name));
if (node != null)
{
cdel = node.InnerText;
}
EColumnCompareMethod ccm;
node = doc.DocumentElement.SelectSingleNode(string.Format("/ScdMergeWizard/ColumnMappings/{0}/ColumnCompareMethod", n.Name));
if (node != null)
{
ccm = (EColumnCompareMethod)Enum.Parse(typeof(EColumnCompareMethod), node.InnerText);
}
else
{
ccm = EColumnCompareMethod.Default;
}
if (string.IsNullOrEmpty(cins) && !string.IsNullOrEmpty(val1c) && (trfc == ETransformationCode.SCD2_IS_ACTIVE || trfc == ETransformationCode.SCD3_PREVIOUS_VALUE || trfc == ETransformationCode.CREATED_DATE))
{
cins = val1c;
}
if (string.IsNullOrEmpty(cupd) && !string.IsNullOrEmpty(val1c) && (trfc == ETransformationCode.SCD2_DATE_FROM || trfc == ETransformationCode.SCD2_DATE_TO || trfc == ETransformationCode.SCD3_DATE_FROM || trfc == ETransformationCode.MODIFIED_DATE))
{
cupd = val1c;
}
if (string.IsNullOrEmpty(cdel) && !string.IsNullOrEmpty(val1c) && (trfc == ETransformationCode.DELETED_DATE || trfc == ETransformationCode.IS_DELETED))
{
cdel = val1c;
}
if (string.IsNullOrEmpty(cins) && !string.IsNullOrEmpty(val2c) && (trfc == ETransformationCode.SCD2_DATE_FROM || trfc == ETransformationCode.SCD2_DATE_TO || trfc == ETransformationCode.SCD3_DATE_FROM || trfc == ETransformationCode.MODIFIED_DATE || trfc == ETransformationCode.DELETED_DATE || trfc == ETransformationCode.IS_DELETED))
{
cins = val2c;
}
if (string.IsNullOrEmpty(cupd) && !string.IsNullOrEmpty(val2c) && (trfc == ETransformationCode.SCD2_IS_ACTIVE))
{
cupd = val2c;
}
GlobalVariables.LoadedColumnMappings.Add(new ColumnMapping(srcc, tgtdbt, tgtc, trfc, cins, cupd, cdel, ccm));
}
}
else
{
node = doc.DocumentElement.SelectSingleNode("/MERGER/Mappings");
if (node != null)
{
foreach (XmlNode n in node.ChildNodes)
{
node = doc.DocumentElement.SelectSingleNode(string.Format("/MERGER/Mappings/{0}/Source", n.Name));
var srcc = node.InnerText;
node = doc.DocumentElement.SelectSingleNode(string.Format("/MERGER/Mappings/{0}/Transformation", n.Name));
ETransformationCode tt = ETransformationCode.SKIP;
try
{
tt = (ETransformationCode)Enum.Parse(typeof(ETransformationCode), node.InnerText);
}
catch { }
var trfc = tt;
node = doc.DocumentElement.SelectSingleNode(string.Format("/MERGER/Mappings/{0}/Target", n.Name));
var tgtc = node.InnerText;
GlobalVariables.LoadedColumnMappings.Add(new ColumnMapping(srcc, ETargetDatabaseType.TARGET, tgtc, trfc, string.Empty, string.Empty, string.Empty, EColumnCompareMethod.Default));
}
}
else
{
sbLoadingErrors.AppendLine("Cannot load ColumnMappings");
}
}
RecentFilesManager.Add(fileName);
loadRecentFiles();
GlobalVariables.IsProjectModified = false;
}
catch (Exception ex)
{
MyExceptionHandler.NewEx(ex);
}
FileName = fileName;
}