public void Compare(Dictionary <string, string> baseConnectionString)
{
BaseConnectionString = baseConnectionString;
Objects = new Dictionary <string, List <SQLObject> >();
var listGetWork = new List <string>();
var timeFormatString = "yyyy'-'MM'-'dd' 'HH'.'mm'.'ss'.'fffffff";
UniqueName = "Comp " + DateTime.Now.ToString(timeFormatString, CultureInfo.InvariantCulture);
ThreadGetListBaseObjectsStat = new Dictionary <string, string>();
//предварительная проверка подключений
lock (ThreadGetListBaseObjectsStat)
{
var prog = "Проверка подключений...";
if (OnProgress != null)
{
OnProgress(prog);
}
}
var testFail = "";
foreach (var cs in baseConnectionString)
{
try
{
DALSql.SetConnectionString(cs.Value);
DataTable sqlResultCount = DALSql.ExecuteDataTable(
"select '123'", null);
if ((string)sqlResultCount.Rows[0][0] != "123")
{
testFail += cs.Key + ", ";
}
}
catch (Exception e)
{
if (OnError != null)
{
OnError("При подключении к базе " + cs.Key + " произошла ошибка!"
+ Environment.NewLine + Environment.NewLine + e.Message);
}
testFail += cs.Key + ", ";
}
}
if (!string.IsNullOrEmpty(testFail))
{
testFail = testFail.Remove(testFail.Length - 2);
lock (ThreadGetListBaseObjectsStat)
{
var prog = "Ошибка при подключении к базам: " + testFail + " :(";
if (OnProgress != null)
{
OnProgress(prog);
}
}
if (OnComplete != null)
{
OnComplete();
}
return;
}
//заполняем Objects
while (true)
{
lock (Objects)
{
//условие выхода
if (BaseConnectionString.Count == Objects.Count)
{
break;
}
//добавляем поток к работе
int nowConnecting = listGetWork.Count - Objects.Count;
if (BaseConnectionString.Count > listGetWork.Count && nowConnecting < MaxCountConnect)
{
var db = BaseConnectionString.Keys.First(bcs => !listGetWork.Any(lgw => lgw == bcs));
listGetWork.Add(db);
var th = new Thread(CompareDB);
th.IsBackground = true;
th.Start(db);
}
//обновляем статус в интерфейсе
lock (ThreadGetListBaseObjectsStat)
{
var prog = "Получение данных: " + (Objects.Count * 100 / BaseConnectionString.Count).ToString() + "%..."
+ " (сделано " + Objects.Count.ToString() + " из " + BaseConnectionString.Count.ToString() + ") "
+ Environment.NewLine
+ " в работе: "
+ listGetWork
.Where(w => !Objects.Keys.Any(o => o == w))
.Select(w => w
+ (ThreadGetListBaseObjectsStat.ContainsKey(BaseConnectionString[w])
? "[" + ThreadGetListBaseObjectsStat[BaseConnectionString[w]] + "]"
: ""))
.Aggregate("", (s, w) => (s == "" ? "" : s + ", ") + w) + ".";
if (OnProgress != null)
{
OnProgress(prog);
}
}
}
Thread.Sleep(100);
}
//вставлям отсутствующие объекты, которые есть на других базах
var distinctSQLName = Objects.SelectMany(b => b.Value.Select(o => o.SQLName)).Distinct().ToList();
DistinctNameTotal = distinctSQLName.Count;
foreach (var db in Objects.Values)
{
db.AddRange(distinctSQLName
.Where(don => !db.Any(o => don == o.SQLName))
//создаем пустой SQLObject, для обозначения, что объекта нет
.Select(don => new SQLObject(don))
);
}
//сравниваем Objects
CompareResult = Objects
//общий список с указанием базы
.SelectMany(ob => ob.Value.Select(obj => new { Base = ob.Key, SQLO = obj }))
//группируем по имени
.GroupBy(a => a.SQLO.SQLName)
.ToDictionary(gn => gn.Key
, gn => gn
//группируем по уникальности текста, и сохраняем список баз
.GroupBy(g => g.SQLO.Hash)
.Select(gh => new CompareObject()
{
Hash = gh.Key,
SQLName = gn.Key,
Bases = gh.ToDictionary(go => go.Base, go => go.SQLO),
FirstObject = gn.First(a => !string.IsNullOrEmpty(a.SQLO.TypeMark)).SQLO
})
.OrderBy(co => co.BasesString)
.ToList());
if (OnComplete != null)
{
OnComplete();
}
}
private void buttonExec_Click(object sender, EventArgs e)
{
if (ExecIsBusy)
{
return;
}
var bases = gridBasesExec.GetBases();
if (bases.Count == 0)
{
return;
}
var scriptRun = tbExec.Text;
if (string.IsNullOrEmpty(scriptRun))
{
return;
}
ExecIsBusy = true;
tbExec.ReadOnly = true;
buttonExec.Enabled = false;
buttonExec2.Enabled = false;
butTextClear.Enabled = false;
gridBasesExec.Enabled = false;
labelExecStat.Text = "Выполнение...";
tabPage2.Text = "Применение (0/" + bases.Count + ")";
tabPage2.ImageIndex = -1;
var statusErrors = 0;
var statusApps = 0;
//////////////////////////////////////////////////////////////
/// Всякие манипуляции со скриптом!
//GO - отключено, т.к. в кавычках GO не должно разбивать скрипт
/*
* var scrRuns = (Environment.NewLine + scriptRun.Trim() + Environment.NewLine)
* .Split(new string[] { Environment.NewLine + "GO" + Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
*/
//var scrRuns = new List<string>() { scriptRun };
//т.к. не учитываются комментарии строк, то поиск и разбитие по GO происходит только в первых topCount строках
int topCount = 10;
var topline = scriptRun
.Split(new string[] { Environment.NewLine }, topCount + 1, StringSplitOptions.None)
.ToList();
topline.RemoveAt(topline.Count - 1);
var posline = topline.Sum(s => s.Length + 2);
List <string> scrRuns = new List <string>();
int sr = 0;
int srs = 0; //позиция до которой все в scrRuns
while (true)
{
var srl = sr; //позиция прошлого найденного GO
sr = scriptRun.IndexOf(TempGO, srl, StringComparison.InvariantCultureIgnoreCase);
if (sr > posline)
{
sr = -1; //на всякий случай отлавливаем только в первых 1000 символах
}
if (sr < 0)
{
break;
}
int count = new Regex("'").Matches(scriptRun.Substring(srs, sr - srs)).Count;
sr += TempGO.Length;
if (count % 2 != 0)
{
continue;
}
scrRuns.Add(scriptRun.Substring(srs, sr - srs - TempGO.Length));
srs = sr;
}
scrRuns.Add(scriptRun.Substring(srs, scriptRun.Length - srs));
/////////////
// убираем последний GO, после которого ничего нет
var lsr = scrRuns[scrRuns.Count - 1];
sr = lsr.LastIndexOf(Environment.NewLine + "GO", StringComparison.InvariantCultureIgnoreCase);
if (sr > 0 && lsr.Substring(sr + 4).Trim().Length == 0)
{
scrRuns[scrRuns.Count - 1] = lsr.Substring(0, sr);
}
//////////////////////////////////////////////////////////////
string textBoxExecResText = "";
foreach (var scr in scrRuns)
{
textBoxExecResText += "Выполнение: "
+ scr.Substring(0, scr.Length > 200 ? 200 : scr.Length).Replace(Environment.NewLine, " ")
+ Environment.NewLine;
}
textBoxExecRes.Text = textBoxExecResText;
Action <string> actGetMessage = (msg) =>
{
this.Invoke((Action)(() =>
{
textBoxExecRes.Text += msg + Environment.NewLine;
}));
};
var basesReady = new List <string>();
var th = new Thread(() =>
{
foreach (var db in bases)
{
this.Invoke((Action)(() =>
{
textBoxExecRes.Text += Environment.NewLine
+ "-----------------------------------------" + Environment.NewLine
+ "Запрос к базе: " + db.Key + Environment.NewLine;
}));
Action <Exception> error = (ext) =>
{
textBoxExecRes.Text += Environment.NewLine
+ "Возникла ошибка: " + ext.Message;// +Environment.NewLine;
};
bool existError = false;
try
{
DALSql.SetConnectionString(db.Value);
DALSql.AddEventText(actGetMessage);
//открываем транзакцию
DALSql.ExecuteDataSet(
"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" + Environment.NewLine
+ "BEGIN TRANSACTION"
, null);
}
catch (Exception ext)
{
this.Invoke(error, ext);
existError = true;
}
foreach (var scrRun in scrRuns)
{
if (existError)
{
break;
}
DataSet sqlResult = null;
try
{
sqlResult = DALSql.ExecuteDataSet(scrRun, null);
}
catch (Exception ext)
{
this.Invoke(error, ext);
existError = true;
}
if (sqlResult != null && sqlResult.Tables.Count > 0)
{
this.Invoke((Action)(() =>
{
textBoxExecRes.Text += Environment.NewLine
+ "Запрос вернул результат: " + Environment.NewLine;
foreach (DataTable tab in sqlResult.Tables)
{
foreach (DataColumn c in tab.Columns)
{
textBoxExecRes.Text += c.ColumnName + ";";
}
textBoxExecRes.Text += Environment.NewLine;
foreach (DataRow r in tab.Rows)
{
foreach (var c in r.ItemArray)
{
textBoxExecRes.Text += c.ToString() + ";";
}
textBoxExecRes.Text += Environment.NewLine;
}
textBoxExecRes.Text += Environment.NewLine;
}
}));
}
else
{
this.Invoke((Action)(() =>
{
textBoxExecRes.Text += Environment.NewLine
+ "Запрос завершился" + Environment.NewLine;
}));
}
}
if (existError)
{
statusErrors++;
//отменяем транзакцию
try
{
DALSql.ExecuteDataSet("ROLLBACK TRANSACTION", null);
}
catch
{
}
}
else
{
//применяем транзакцию
try
{
DALSql.ExecuteDataSet("COMMIT TRANSACTION", null);
}
catch (Exception ext)
{
this.Invoke(error, ext);
existError = true;
statusErrors++;
}
}
this.Invoke((Action)(() =>
{
basesReady.Add(db.Key);
var basesString = basesReady.OrderBy(i => i).Aggregate("", (s, i) => (s == "" ? "" : s + ", ") + i);
labelExecStat.Text = "Завершено на базах: " + basesString + ". (" + basesReady.Count + " из " + bases.Count + ")";
tabPage2.Text = "Применение (" + basesReady.Count + "/" + bases.Count + ")"
+ (statusErrors > 0 ? " ошибки " + statusErrors + "!" : "");
if (statusErrors > 0)
{
tabPage2.ImageIndex = 0;
}
else
{
tabPage2.ImageIndex = -1;
}
}));
try
{
DALSql.CloseConnection();
}
catch (Exception ext)
{
if (!existError)
{
this.Invoke(error, ext);
}
}
}
ExecIsBusy = false;
this.Invoke((Action)(() =>
{
tbExec.ReadOnly = false;
buttonExec.Enabled = true;
buttonExec2.Enabled = true;
butTextClear.Enabled = true;
gridBasesExec.Enabled = true;
labelExecStat.Text = "Завершено!";
tabPage2.Text = "Применение"
+ (statusErrors > 0 ? " ошибки " + statusErrors + "!" : "");
}));
});
th.IsBackground = true;
th.Start();
}
public List <SQLObject> GetListBaseObjects(string connectionString)
{
lock (ThreadGetListBaseObjectsStat)
{
ThreadGetListBaseObjectsStat.Add(connectionString, "?");
}
//запрос
//if (OnStatusChange != null) OnStatusChange("Запрос к BD...", null);
DALSql.SetConnectionString(connectionString);
var srcs = new SQLScriptDB();
srcs.WithTable = Settings.Param.CompareWithTable;
srcs.TableWithTrigger = Settings.Param.CompareTableWithTrigger;
srcs.FilterPrefix = Settings.Param.Prefix;
srcs.FilterIgnoreByPrefix = Settings.Param.IgnoreByPrefix;
srcs.FilterIgnoreByPostfix = Settings.Param.IgnoreByPostfix;
//выясняем количество
DataTable sqlResultCount = DALSql.ExecuteDataTable(
srcs.GetScriptCountObjects(), null);
int sqlCountRow = (int)sqlResultCount.Rows[0][0];
int sqlCountRowReading = 0;
DataTable sqlResult = new DataTable();
//стартуем поток проверяющий кол-во считанных строк
var thSqlReading = new Thread(() => {
while (sqlCountRow > 0)
{
sqlCountRowReading = sqlResult.Rows.Count;
Thread.Sleep(100);
lock (ThreadGetListBaseObjectsStat)
{
if (sqlCountRow > 0)
{
ThreadGetListBaseObjectsStat[connectionString] = (sqlCountRowReading * 100 / sqlCountRow).ToString() + "%";
}
}
}
});
thSqlReading.IsBackground = true;
thSqlReading.Start();
//запускаем запрос
DALSql.ExecuteDataTable(
srcs.GetScriptDefinitionObjects()
, null, sqlResult);
//завершение считывания
lock (ThreadGetListBaseObjectsStat)
{
sqlCountRow = 0;
ThreadGetListBaseObjectsStat[connectionString] = "...";
}
DALSql.CloseConnection();
//маппинг
List <SQLObject> listSQL = new List <SQLObject>();
foreach (DataRow item in sqlResult.Rows)
{
var no = new SQLObject()
{
ShemaName = (string)item["schemaName"] ?? "",
ObjectName = (string)item["name"] ?? "",
Source = (string)item["definition"] ?? "",
CreateDate = item.IsNull("create_date") ? null : (DateTime?)(DateTime)item["create_date"],
ModifyDate = item.IsNull("modify_date") ? null : (DateTime?)(DateTime)item["modify_date"],
TypeMark = (string)item["type"] ?? "",
};
//фильтры перенесены в sql
//if (!Settings.Param.IgnoreByPrefix.Any(ip => no.SQLName.StartsWith(ip))
// && !Settings.Param.IgnoreByPostfix.Any(ip => no.SQLName.EndsWith(ip)))
listSQL.Add(no);
no.Source = SQLText.GetBodySQLProcedure(no.Source);
no.Hash = SQLText.GetHash(SQLText.GetSQLForHash(no.Source));
}
//if (OnStatusChange != null) OnStatusChange("Получено объектов: " + listSQL.Count, null);
return(listSQL);
}
