public static MultiDbData CreateMultiDbConfigFromQuery(ConnectionData connData, string query, out string message)
{
try
{
log.LogInformation($"Generating database override configuation from {connData.SQLServerName} : {connData.DatabaseName}");
log.LogDebug($"Override generation script: {query}");
SqlConnection conn = ConnectionHelper.GetConnection(connData);
SqlCommand cmd = new SqlCommand(query, conn);
cmd.CommandType = CommandType.Text;
DataTable tbl = new DataTable();
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
adapt.Fill(tbl);
MultiDbData multi = new MultiDbData();
int counter = 0;
foreach (DataRow row in tbl.Rows)
{
ServerData ser = multi[row[0].ToString().Trim()];
if (ser == null)
{
ser = new ServerData();
ser.ServerName = row[0].ToString().Trim();
}
DatabaseOverride ovr;
if (tbl.Columns.Count == 2)
{
ovr = new DatabaseOverride("client", row[1].ToString().Trim());
}
else
{
ovr = new DatabaseOverride(row[1].ToString().Trim(), row[2].ToString().Trim());
ovr.AppendedQueryRowData(row.ItemArray, 3, tbl.Columns);
}
ser.OverrideSequence.Add(counter.ToString(), ovr);
counter++;
multi[ser.ServerName] = ser;
}
message = string.Empty;
var dbs = multi.Sum(m => m.OverrideSequence.Count());
log.LogInformation($"Found {dbs} target databases across {multi.Count()} target servers");
return(multi);
}
catch (Exception exe)
{
message = exe.Message;
return(null);
}
}
public void ConvertMultiDbDataToTextConfigTest()
{
DbOverrideSequence sequenceA = new DbOverrideSequence();
sequenceA.Add("1", new DatabaseOverride("default1", "override1"));
sequenceA.Add("2", new DatabaseOverride("default2", "override2"));
sequenceA.Add("0", new DatabaseOverride("default0", "override0"));
DatabaseOverride ovrX = new DatabaseOverride("defaultX", "overrideX");
DatabaseOverride ovrY = new DatabaseOverride("defaultY", "overrideY");
List <DatabaseOverride> lstOvr = new List <DatabaseOverride>();
lstOvr.Add(ovrX);
lstOvr.Add(ovrY);
sequenceA.Add("M", lstOvr);
ServerData serverA = new ServerData();
serverA.OverrideSequence = sequenceA;
serverA.ServerName = "ServerA";
DbOverrideSequence sequenceB = new DbOverrideSequence();
sequenceB.Add("6", new DatabaseOverride("default6", "override6"));
sequenceB.Add("7", new DatabaseOverride("default7", "override7"));
sequenceB.Add("5", new DatabaseOverride("default5", "override5"));
ServerData serverB = new ServerData();
serverB.OverrideSequence = sequenceB;
serverB.ServerName = "ServerB";
MultiDbData cfg = new MultiDbData();
cfg.Add(serverA);
cfg.Add(serverB);
string expected =
@"ServerA:default1,override1
ServerA:default2,override2
ServerA:default0,override0
ServerA:defaultX,overrideX;defaultY,overrideY
ServerB:default6,override6
ServerB:default7,override7
ServerB:default5,override5
";
string actual;
actual = MultiDbHelper.ConvertMultiDbDataToTextConfig(cfg);
Assert.AreEqual(expected, actual);
}
public List <DatabaseOverride> GetOverrideData()
{
List <DatabaseOverride> vals = new List <DatabaseOverride>();
foreach (DataGridViewRow row in this.dataGridView1.Rows)
{
DatabaseOverride dBO = new DatabaseOverride();
dBO.DefaultDbTarget = row.Cells[0].Value.ToString();
if (((DataGridViewComboBoxCell)row.Cells[1]).Value == null)
{
dBO.OverrideDbTarget = dBO.DefaultDbTarget;
}
else
{
dBO.OverrideDbTarget = ((DataGridViewComboBoxCell)row.Cells[1]).Value.ToString();
}
vals.Add(dBO);
}
return(vals);
}
internal ServerData GetServerData()
{
ServerData srvData = new ServerData();
srvData.ServerName = this.serverName;
srvData.Databases = this.srvData.Databases;
//Keep track of the largest override sequence
int maxSet = 0;
//Get the configs sorted out into the server data...
for (int i = 0; i < tabControl1.TabPages.Count; i++)
{
foreach (Control ctrl in tabControl1.TabPages[i].Controls)
{
if (ctrl is MultiDbConfig)
{
MultiDbConfig cfg = (MultiDbConfig)ctrl;
foreach (string sequenceId in cfg.DatabaseOverrideSequence.Keys)
{
if (srvData.OverrideSequence.ContainsKey(sequenceId))
{
srvData.OverrideSequence[sequenceId].Add(cfg.DatabaseOverrideSequence[sequenceId]);
if (srvData.OverrideSequence[sequenceId].Count > maxSet)
{
maxSet = srvData.OverrideSequence[sequenceId].Count;
}
}
else
{
List <DatabaseOverride> tmp = new List <DatabaseOverride>();
tmp.Add(cfg.DatabaseOverrideSequence[sequenceId]);
srvData.OverrideSequence.Add(sequenceId, tmp);
if (maxSet == 0)
{
maxSet = 1;
}
}
}
}
}
}
//Next, we need to make sure we have the same number of overrides in each Override sequence collection. If not, add the a default "no override" setting...
foreach (string key in srvData.OverrideSequence.Keys)
{
//must be missing an override setting...
if (srvData.OverrideSequence[key].Count < maxSet)
{
foreach (DatabaseItem defaultDb in this.defaultDatabases)
{
bool found = false;
foreach (DatabaseOverride dbO in srvData.OverrideSequence[key])
{
if (dbO.DefaultDbTarget.ToLower() == defaultDb.DatabaseName.ToLower())
{
found = true;
break;
}
}
if (!found)
{
DatabaseOverride tmp = new DatabaseOverride(defaultDb.DatabaseName, defaultDb.DatabaseName);
srvData.OverrideSequence[key].Add(tmp);
}
}
}
}
return(srvData);
}
/// <summary>
/// Takes a simplified delimited text format to create a multi-database run configuration.
/// <example>
/// Expects each line to be in a server: override format below:
/// SERVER:defaultDb,override;default2,override2
/// </example>
/// </summary>
/// <param name="fileContents"></param>
/// <returns></returns>
public static MultiDbData ImportMultiDbTextConfig(string[] fileContents)
{
int dummySequence = 0;
MultiDbData cfg = new MultiDbData();
for (int i = 0; i < fileContents.Length; i++)
{
string line = fileContents[i];
//Skip empty lines...
if (line.Trim().Length == 0)
{
continue;
}
//need to have that colon!
if (line.IndexOf(':') == -1)
{
throw new MultiDbConfigurationException("Error in configuration file line #" + i + 1 + ". Missing \":\" separator. This is needed to separate server from database override values.");
}
string server = line.Split(':')[0];
string dbs = line.Split(':')[1];
ServerData sData = cfg[server];
if (sData == null)
{
sData = new ServerData();
sData.ServerName = server.Trim();
cfg[server] = sData;
}
string[] arrDb = dbs.Split(';');
List <DatabaseOverride> tmpDb = new List <DatabaseOverride>();
for (int j = 0; j < arrDb.Length; j++)
{
//Changing so that a default setting is not required...
//if (arrDb[j].IndexOf(',') == -1)
// throw new MultiDbConfigurationException("Error in configuration file line #" + i + 1 + ". Missing \",\" separator. This is needed to separate default and override database targets.");
string[] over = arrDb[j].Split(',');
DatabaseOverride ovr;
if (over.Length > 1)
{
ovr = new DatabaseOverride(over[0].Trim().Replace("'", ""), over[1].Trim());
}
else
{
ovr = new DatabaseOverride("", over[0].Trim());
}
tmpDb.Add(ovr);
}
if (tmpDb.Count > 0)
{
sData.OverrideSequence.Add(dummySequence.ToString(), tmpDb);
dummySequence++;
}
//cfg.Add(sData);
}
return(cfg);
}