public static bool check_func(FbConnectionStringBuilder fc)
{
bool res_ = false;
using (FbConnection fb = new FbConnection(fc.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_func,fb,ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
res_ = true;
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch { }
finally { fb.Close(); }
fb.Dispose();
}
return res_;
}
protected FbConnection OpenConnection()
{
CreateDatabase();
var connection = new FbConnection(this.connString);
try
{
connection.Open();
return connection;
}
catch (Exception)
{
connection.Dispose();
throw;
}
}
public bool TryConnect(int id)
{
bool _yes = false;
try
{
var t = db.tModerator.Find(id);
if (t != null)
{
FbConnectionStringBuilder fc = new FbConnectionStringBuilder();
fc.Database = t.tModerator_database;
fc.UserID = t.tModerator_userId;
fc.Password = t.tModerator_password;
fc.Pooling = false;
fc.Charset = "win1251";
using (FbConnection fb = new FbConnection(fc.ConnectionString))
{
try
{
fb.Open();
_yes = true;
}
catch
{
_yes = false;
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
}
catch
{
_yes = false;
}
return _yes;
}
static void SyncBranch(string username)
{
using (var db = new ApplicationDbContext())
{
var t = db.tModerator.First(a => a.tUsers.UserName == username);
if (t != null)
{
var fc = new FbConnectionStringBuilder
{
UserID = t.tModerator_userId,
Password = t.tModerator_password,
Database = t.tModerator_database,
Charset = "win1251",
Pooling = false,
Role = "R_ADMIN"
};
using (var fb = new FbConnection(fc.ConnectionString))
{
try
{
fb.Open();
using (var ft = fb.BeginTransaction())
{
using (var fcon = new FbCommand("select b.id_branch, b.mnemo from branch b", fb, ft))
{
using (var fr = fcon.ExecuteReader())
{
while (fr.Read())
{
if (db.tBranch.Find(fr[0]) != null)
{
var m = db.tBranch.Find(fr[0]);
m.tBranch_name = fr[1].ToString();
db.Entry(m).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
else
{
var m = new tBranch { Id = (int)fr[0], tBranch_name = fr[1].ToString() };
db.tBranch.Add(m);
db.SaveChanges();
}
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch
{
//Пропускаем все ошибки - сихн е удалась
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
db.Dispose();
}
}
public bool try_connection(string database)
{
bool yes_ = true;
//в любом случае копируем базу!
//возможно база находится на другом компутере
//попробуем ее скопировать
//проверка соединения
FbConnectionStringBuilder fc_ch = new FbConnectionStringBuilder();
try
{
fc_ch.Database = Sett.Default.database_tmp ;//база, которую нужно конвертировать
fc_ch.Pooling = false; //пул соединения - отсутствует - для более быстрого освобождения базы
fc_ch.ServerType = FbServerType.Embedded;//встроенный сервер
fc_ch.ClientLibrary = ".\\fbembed.dll";
//fc_old.Charset = "win1251"; //кодировка для FB 1/5 не указывается - здесь нужно было переводить в форматы UTF
fc_ch.UserID = "sysdba";//пользователь по умолчанию
fc_ch.Password = "******"; //Пароль можно не указывать
}
catch (Exception ex)
{
yes_ = false;
MessageBox.Show(ex.Message, "Неверные параметры подключения к базе", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (yes_)
{
using (FbConnection fb = new FbConnection(fc_ch.ConnectionString))
{
try
{
fb.Open();
}
catch (FbException ex)
{
yes_ = false;
MessageBox.Show(ex.Message, "Ошибка при подключения к базе", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
return yes_;
}
public void PREPARE_DATA()
{
if (is_reserv(table_name))
{
table_name = "\"" + table_name + "\"";
}
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_data.Replace("@a", table_name), fb, ft))
{
fcon.Parameters.Add("@skip", FbDbType.Integer);
fcon.Parameters["@skip"].Value = skip_;
//дополнительные условия для отбора
switch (table_name.Trim().ToUpper())
{
case "DELETED":
fcon.CommandText += " where Date_Deleted >= cast('NOW' as date) - 180";
break;
case "ERR_LOG":
fcon.CommandText += " where id_err_log is null";
break;
case "BACKUP_LOG":
fcon.CommandText += " where id_BACKUP_LOG is null";
break;
case "SHADOWGUARD":
fcon.CommandText += " where id_SHADOW is null";
break;
case "USER_ACTIVITY":
fcon.CommandText += " where id_user_activity is null";
break;
case "MESSAGES":
fcon.CommandText += " where lastdate >= cast('NOW' as date) - 100";
break;
case "SCHEDULER":
fcon.CommandText += " where id_status is null";
break;
case "USER_CONNECTIONS":
fcon.CommandText += " where ID_USER_CONNECTIONS is null";
break;
case "REPORT_REPLICATION" :
fcon.CommandText += " where DATE_CREATE >= cast('NOW' as date) - 30";
break;
}
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
var sqlinsert = "insert into " + table_name + " values(";
for (int t = 0; t <= fr.FieldCount - 1; t++)
{
if (sqlinsert != "insert into " + table_name + " values(")
{
sqlinsert += ",";
}
if (fr[t] == DBNull.Value)
{
sqlinsert += "null";
}
else
{
if (fr[t].GetType().ToString().ToUpper() == "SYSTEM.DOUBLE")
{
sqlinsert += fr[t].ToString().Replace(",", ".");
}
else
//заменяем все ' на ''
sqlinsert += "'" + fr[t].ToString().Replace("'", "''").Replace(" 0:00:00", " ") + "'";
}
}
sqlinsert += ");";
com2.Add(sqlinsert);
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void go_load()
{
using (FbConnection fb = new FbConnection(fc_new.ConnectionString))
{
try
{
fb.Open();
int _i = 0;
int _j = q_data.Count;
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand("", fb, ft))
{
while (q_data.Count != 0)
{
_i++;
p_cur_2 = (int)(((float)_i / (float)_j) * 100);
p_text_2 = p_cur_2.ToString() + @"%";
fcon.CommandText = q_data.Dequeue();
fcon.ExecuteNonQuery();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void execute_com()
{
using (FbConnection fb = new FbConnection(fc_new.ConnectionString))
{
string last_com = "";
try
{
fb.Open();
int i = 0;
int j = com.Count;
using (FbTransaction ft = fb.BeginTransaction())
{
foreach (string cmd in com)
{
last_com = cmd;
i++;
p_cur = (int)(((float) i / (float) j) * 100);
p_text = i.ToString() + "/" + j.ToString();
using (FbCommand fcon = new FbCommand(cmd, fb,ft))
{
try
{
fcon.ExecuteNonQuery();
}
catch(FbException ex)
{
sb.AppendLine("**********");
sb.AppendLine(ex.Message);
sb.AppendLine("**********");
sb.AppendLine(cmd);
}
fcon.Dispose();
}
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("EXECUTE COM");
sb.AppendLine(ex.Message);
sb.AppendLine("**********Command");
sb.AppendLine(last_com);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void m_GENERATORS()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_GENERATORS, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
//основа
string dll_command = "CREATE GENERATOR " + fr[0].ToString() + "\n";
//теперь значение
string dll_val = "";
using (FbCommand fcon_a = new FbCommand(sql_GENERATORS_VAL.Replace("@a", fr[0].ToString()), fb, ft))
{
using (FbDataReader fr_a = fcon_a.ExecuteReader())
{
while (fr_a.Read())
{
dll_val = "SET GENERATOR " + fr[0].ToString() + " TO " + fr_a[0].ToString();
}
fr_a.Dispose();
}
fcon_a.Dispose();
}
//добавляем наши команды
com_generators.Add(dll_command);
com_generators.Add(dll_val);
}
fr.Dispose();
}
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("GENERATORS");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void m_DOMAINS()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_domains, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
//основа
string dll_command = "create domain " + fr[0].ToString() + "\n" + "as" + "\n";
//теперь тип данных
dll_command += get_field_type(fr[10].ToString(), fr[8].ToString(), fr[9].ToString(), fr[11].ToString(), fr[17].ToString(), fr[27].ToString()) + "\n";
//есть ли у него Character set
if (fr[28].ToString() != DBNull.Value.ToString())
{
dll_command += " character set " + fr[28].ToString() + "\n";
}
//дальше идет значение DEFAULT
if (fr[7].ToString() != DBNull.Value.ToString())
{
dll_command += fr[7].ToString() + "\n";
}
//проверка
if (fr[3].ToString() != DBNull.Value.ToString())
{
dll_command += fr[3].ToString() + "\n";
}
//COLLATE
if (fr[29].ToString() != DBNull.Value.ToString())
{
dll_command += fr[29].ToString() + "\n";
}
//записываем
com_domains.Add(dll_command);
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("DOMAINS");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void m_UDF()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
try
{
//сформируем список функций - их создание
using (FbCommand fcon = new FbCommand(sql_udf, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
//будующая заготовка
string dll_command = "DECLARE EXTERNAL FUNCTION " + fr[0].ToString().Trim();
//будующий возврат
string dll_return = "RETURNS ";
//оконцовка
string dll_end = "ENTRY_POINT '" + fr[5].ToString().Trim() + "'" + " MODULE_NAME '" + fr[4].ToString() + "'";
//Теперь нам нужны возвращаемые параметры
string dll_argument = "";
#region Формирование аргументов
using (FbCommand fcon_a = new FbCommand(sql_udf_a, fb, ft))
{
fcon_a.Parameters.Add("@a", FbDbType.VarChar, 31);
fcon_a.Parameters[0].Value = fr[0].ToString();
using (FbDataReader fr_a = fcon_a.ExecuteReader())
{
while (fr_a.Read())
{
if (fr_a[0].ToString() == fr[6].ToString())
{
if (fr[6].ToString() == "0")
{
//Значит выходной
dll_return += get_field_type(fr_a[2].ToString(), fr_a[4].ToString(), fr_a[3].ToString(), fr_a[5].ToString(), "0", fr_a[8].ToString(), true);
//теперь как значение или как ссылка
if (fr_a[1].ToString() == "-1")
{
//FREE_IT
dll_return += " FREE_IT";
}
else
if (fr_a[1].ToString() == "0")
{
dll_return += " BY VALUE";
}
}
else
{
dll_return += "PARAMETER " + fr_a[0].ToString();
if (dll_argument == "")
dll_argument += get_field_type(fr_a[2].ToString(), fr_a[4].ToString(), fr_a[3].ToString(), fr_a[5].ToString(), "0", fr_a[8].ToString(), true);
else
{
dll_argument += "," + "\n" + get_field_type(fr_a[2].ToString(), fr_a[4].ToString(), fr_a[3].ToString(), fr_a[5].ToString(), "0", fr_a[8].ToString(), true);
}
}
}
else
{
//а вот теперь начнем формировать строку создания
if (dll_argument == "")
dll_argument += get_field_type(fr_a[2].ToString(), fr_a[4].ToString(), fr_a[3].ToString(), fr_a[5].ToString(), "0", fr_a[8].ToString(), true);
else
{
dll_argument += "," + "\n" + get_field_type(fr_a[2].ToString(), fr_a[4].ToString(), fr_a[3].ToString(), fr_a[5].ToString(), "0", fr_a[8].ToString(), true);
}
}
}
fr_a.Dispose();
}
fcon_a.Dispose();
}
#endregion
//и вот она наша собранная строка
dll_command += "\n" + dll_argument + "\n" + dll_return + "\n" + dll_end;
dll_command = regexTrim.Replace(dll_command, "TRIM_");
dll_command = regexIIF.Replace(dll_command, "IIF_");
//теперь добавим ее в список команд
com_udf.Add(dll_command);
}
fr.Dispose();
}
fcon.Dispose();
}
}
catch (FbException ex2)
{
MessageBox.Show(ex2.Message);
}
ft.Commit();
}
}
catch (FbException ex)
{
sb.AppendLine("udf");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
fb.Dispose();
}
}
}
public void run_user()
{
using (FbConnection fb = new FbConnection(fc_new.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_user, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
fb_add(fr[0].ToString(), this.t_pass.Text, false, null);
}
}
fcon.Dispose();
}
ft.Dispose();
}
}
catch { }
finally { fb.Close(); }
fb.Dispose();
}
}
/// <summary>
/// Осуществляет инициализацию работы с БД службы.
/// </summary>
public static void Init()
{
// Инициализируем подключение к базе данных
_DB_FullFileName = AppDomain.CurrentDomain.BaseDirectory + "DEVICES.FB";
_DB_ClientLibraryPath = AppDomain.CurrentDomain.BaseDirectory + "fbdb\\fbembed.dll";
try
{
// Задаем параметры подключения
FbConnectionStringBuilder conn_str = new FbConnectionStringBuilder();
conn_str.ServerType = FbServerType.Embedded;
conn_str.Charset = "WIN1251";
conn_str.Dialect = 3;
conn_str.Role = "";
conn_str.Database = _DB_FullFileName;
//conn_str.DataSource = "localhost";
//conn_str.Port = 3050;
//conn_str.ConnectionLifeTime = 0;
//conn_str.Pooling = true;
//conn_str.MinPoolSize = 0;
//conn_str.MaxPoolSize = 50;
conn_str.UserID = _DB_Login;
conn_str.Password = _DB_Paswd;
conn_str.ClientLibrary = _DB_ClientLibraryPath;
conn_str.PacketSize = 16384;
// Создаем подключение к БД
FB_dbConnection = new FbConnection(conn_str.ToString());
// Инициализируем и задаем параметры транзакций
FB_dbReadTransactionOptions = new FbTransactionOptions();
FB_dbReadTransactionOptions.TransactionBehavior = FbTransactionBehavior.Read | FbTransactionBehavior.ReadCommitted | FbTransactionBehavior.RecVersion;
FB_dbCommitTransactionOptions = new FbTransactionOptions();
FB_dbCommitTransactionOptions.TransactionBehavior = FbTransactionBehavior.NoWait | FbTransactionBehavior.ReadCommitted | FbTransactionBehavior.RecVersion;
}
catch (Exception exc)
{
// Делаем запись в журнал событий
WinLog.Write(string.Format("Ошибка инициализации БД: ", exc.Message), System.Diagnostics.EventLogEntryType.Error);
// Освобождаем ресурсы
if (FB_dbConnection != null)
FB_dbConnection.Dispose();
FB_dbConnection = null;
}
// Устанавливаем признак того, что в данный момент транзакций нет
IsLockedTransaction = false;
}
public ActionResult CreateOffer(CreateOffer t)
{
int doc_number = 0;
var tu = db.Users.Where(a => a.UserName == User.Identity.Name).First().tModerator;
if (tu.Count != 0)
{
FbConnectionStringBuilder fc = new FbConnectionStringBuilder();
fc.Database = tu.First().tModerator_database;
fc.UserID = tu.First().tModerator_userId;
fc.Password = tu.First().tModerator_password;
fc.Role = "R_ADMIN";
fc.Pooling = false;
fc.Charset = "win1251";
using (FbConnection fb = new FbConnection(fc.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand("select G.NUM " +
"from GET_DC_DOCUMENT_NUMBER(85) G", fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
doc_number = (int)fr[0];
}
fr.Dispose();
}
fcon.Dispose();
}
var _com = "execute procedure IUD_BUGS('I', null, @IS_ERROR, @LOCATION, @ID_RELEASE_PROJECTS, @ID_RELEASE_PROJECTS_EXEC," +
" null, null, @ID_SUBSYSTEM," +
"@ID_BRANCH, null, 85, 1, (select list_id from sel_filter_budg(2)),@DOC_NUMBER, 'NOW'," +
"null, @comment, null, @DETAIL_COMMENT, 1, @ID_PROJECTS, null,null) ";
using (FbCommand fcon = new FbCommand(_com, fb, ft))
{
switch (t._iserror)
{
case true:
fcon.Parameters.AddWithValue("@IS_ERROR", '1');
break;
default:
fcon.Parameters.AddWithValue("@IS_ERROR", '0');
break;
}
fcon.Parameters.AddWithValue("@LOCATION", t._location);
fcon.Parameters.AddWithValue("@ID_RELEASE_PROJECTS", t.id_release_projects);
fcon.Parameters.AddWithValue("@ID_RELEASE_PROJECTS_EXEC", t.id_release_projects_exec);
fcon.Parameters.AddWithValue("@ID_SUBSYSTEM", t.id_subsystem);
fcon.Parameters.AddWithValue("@ID_BRANCH", t.id_branch);
if (t._message.Id != 0)
{
var val = this.Url.RequestContext.HttpContext.Request.Url.Scheme;
fcon.Parameters.AddWithValue("@comment", t._message.tForumThemes.tForumThemes_name);
fcon.Parameters.AddWithValue("@DETAIL_COMMENT",
"<em><a href ='" +
Url.Action("Index", "ForumMessages", new { id = t._message.tForumThemes.Id, id_list = t._message.tForumThemes.tForumList.Id }, val) +
"'> " +
Url.Action("Index", "ForumMessages", new { id = t._message.tForumThemes.Id, id_list = t._message.tForumThemes.tForumList.Id }, val) +
"</a></em>" +
"<p>" + t._message.tForumMessages_messages + "</p>");
}
else
{
var val = this.Url.RequestContext.HttpContext.Request.Url.Scheme;
fcon.Parameters.AddWithValue("@comment", t._themes.tForumThemes_name);
fcon.Parameters.AddWithValue("@DETAIL_COMMENT",
"<em><a href ='" +
Url.Action("Index", "ForumMessages", new { id = t._themes.Id, id_list = t._themes.tForumList.Id }, val) +
"'> " +
Url.Action("Index", "ForumMessages", new { id = t._themes.Id, id_list = t._themes.tForumList.Id }, val) +
"</a></em>" +
"<p>" + t._themes.tForumThemes_desc + "</p>");
}
fcon.Parameters.AddWithValue("@ID_PROJECTS", t.id_projects);
fcon.Parameters.AddWithValue("@DOC_NUMBER", doc_number);
try
{
fcon.ExecuteNonQuery();
ft.Commit();
}
catch (FbException ex1)
{
ModelState.AddModelError("", ex1.Message);
ft.Rollback();
}
finally
{
fcon.Dispose();
}
}
ft.Dispose();
}
}
catch (FbException ex)
{
ModelState.AddModelError("", ex.Message);
return View(t);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
else
{
return RedirectToAction("Settings");
}
return RedirectToAction("Index", "ForumList");
}
public void m_FOREIGN_KEY()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_foreign_key, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
string dll_command = "";
dll_command += fr[0].ToString();
if (is_reserv(fr[1].ToString().Trim()))
{
dll_command += "\"" + fr[1].ToString().Trim() + "\"";
}
else
{
dll_command += fr[1].ToString().Trim();
}
dll_command += fr[2].ToString();
if (is_reserv(fr[3].ToString().Trim()))
{
dll_command += "\"" + fr[3].ToString().Trim() + "\"";
}
else
{
dll_command += fr[3].ToString().Trim();
}
dll_command += fr[4].ToString();
com_foreign_key.Add(dll_command);
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void m_ROLES()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_roles, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
string dll_command = "create role " + fr[0].ToString();
com_roles.Add(dll_command);
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("ROLES");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void m_INDEXES()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_index, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
string dll_command = "";
dll_command += fr[0].ToString();
if (is_reserv(fr[1].ToString().Trim()))
{
dll_command += "\"" + fr[1].ToString().Trim() + "\"";
}
else
{
dll_command += fr[1].ToString().Trim();
}
//соостав индекса
string fields_ = "";
using (FbCommand fcon_f = new FbCommand(sql_index_f, fb, ft))
{
fcon_f.Parameters.Add("@a", FbDbType.VarChar, 31);
fcon_f.Parameters[0].Value = fr[2].ToString();
using (FbDataReader fr_f = fcon_f.ExecuteReader())
{
while (fr_f.Read())
{
if (fields_ == "")
{
fields_ += "(" + fr_f[0].ToString();
}
else
{
fields_ += "," + fr_f[0].ToString();
}
}
fields_ += ")";
fr_f.Dispose();
}
fcon_f.Dispose();
}
dll_command += " " + fields_;
com_indexes.Add(dll_command);
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void m_TABLES()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_tables, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
tables_.Add(fr[0].ToString());//добавляем сразу в список таблиц
string dll_command = "create table ";
if (is_reserv(fr[0].ToString()))
{
dll_command += "\"" + fr[0].ToString() + "\"" + "(" + "\n";
}
else
{
dll_command += fr[0].ToString() + "(" + "\n";
}
//спикос полей
using (FbCommand fcon_a = new FbCommand(sql_tables_f, fb, ft))
{
fcon_a.Parameters.Add("@a", FbDbType.VarChar, 31);
fcon_a.Parameters[0].Value = fr[0].ToString();
using (FbDataReader fr_a = fcon_a.ExecuteReader())
{
string fields_ = "";
while (fr_a.Read())
{
//если у нас поле происходит из простого типа, то найдем его
if (fr_a[1].ToString().Substring(0, 4) == "RDB$")
{
if (fields_ != "")
{
fields_ += "," + "\n";
}
fields_ += fr_a[0].ToString() + " " + get_field_type(fr_a[3].ToString(),
fr_a[4].ToString(),
fr_a[5].ToString(),
fr_a[6].ToString(),
fr_a[7].ToString(),
fr_a[8].ToString());
}
else
{
if (fields_ != "")
{
fields_ += "," + "\n";
}
fields_ += fr_a[0].ToString() + " " + fr_a[1].ToString();
}
//Обязательное?
if (fr_a[2].ToString().Trim() == "1")
{
fields_ += " NOT NULL";
}
}
dll_command += fields_ + "\n" + ")";
fr_a.Dispose();
}
fcon_a.Dispose();
}
com_tables.Add(dll_command);
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void go_data()
{
//запускаем заполнение списка
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
int i_ = 0;
int j_ = tables_.Count;
foreach (string t_ in tables_)
{
i_++;
p_text = i_.ToString() + "/" + j_.ToString() + " " + t_; //прогресс таблиц
p_cur = (int)(((float)i_ / (float)j_) * 100);
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand("Select * from " + t_,fb,ft))
{
switch (t_.Trim().ToUpper())
{
case "DELETED":
fcon.CommandText += " where Date_Deleted >= cast('NOW' as date) - 180";
break;
case "ERR_LOG":
fcon.CommandText += " where id_err_log is null";
break;
case "BACKUP_LOG":
fcon.CommandText += " where id_BACKUP_LOG is null";
break;
case "SHADOWGUARD":
fcon.CommandText += " where id_SHADOW is null";
break;
case "USER_ACTIVITY":
fcon.CommandText += " where id_user_activity is null";
break;
case "MESSAGES":
fcon.CommandText += " where lastdate >= cast('NOW' as date) - 100";
break;
case "SCHEDULER":
fcon.CommandText += " where id_status is null";
break;
case "USER_CONNECTIONS":
fcon.CommandText += " where ID_USER_CONNECTIONS is null";
break;
case "REPORT_REPLICATION":
fcon.CommandText += " where DATE_CREATE >= cast('NOW' as date) - 30";
break;
}
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
var sqlinsert = "insert into " + t_ + " values(";
for (int t = 0; t <= fr.FieldCount - 1; t++)
{
if (sqlinsert != "insert into " + t_ + " values(")
{
sqlinsert += ",";
}
if (fr[t] == DBNull.Value)
{
sqlinsert += "null";
}
else
{
if (fr[t].GetType().ToString().ToUpper() == "SYSTEM.DOUBLE")
{
sqlinsert += fr[t].ToString().Replace(",", ".");
}
else
//заменяем все ' на ''
sqlinsert += "'" + fr[t].ToString().Replace("'", "''").Replace(" 0:00:00", " ") + "'";
}
}
sqlinsert += ");";
q_data.Enqueue(sqlinsert);//добавляем INSERT
if (q_data.Count >= 100000)
{
//запускаем создание записей на базе
using (Task t_load = new Task(go_load))
{
t_load.Start();
t_load.Wait();
t_load.Dispose();
q_data.Clear();
}
}
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
//и в конце запустим еще раз
using (Task t_load = new Task(go_load))
{
t_load.Start();
t_load.Wait();
t_load.Dispose();
q_data.Clear();
}
p_text = "Анализ данных завершен"; //конец анализа
p_cur = 100;
is_close_analize = true;//флаг, что анализ данных завершен
}
catch (FbException ex)
{
sb.AppendLine("");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void m_VIEWS()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_views, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
string dll_command = "create view ";
if (is_reserv(fr[0].ToString()))
{
dll_command += "\"" + fr[0].ToString() + "\"" + "(" + "\n";
}
else
{
dll_command += fr[0].ToString() + "(" + "\n";
}
//поля
using (FbCommand fcon_a = new FbCommand(sql_views_f, fb, ft))
{
fcon_a.Parameters.Add("@a", FbDbType.VarChar, 31);
fcon_a.Parameters[0].Value = fr[0].ToString();
using (FbDataReader fr_a = fcon_a.ExecuteReader())
{
string fields_ = "";
while (fr_a.Read())
{
if (fields_ != "")
{
fields_ += "," + "\n";
}
fields_ += fr_a[0].ToString();
}
dll_command += fields_ + "\n" + ")" + "\nAS\n";
fr_a.Dispose();
}
fcon_a.Dispose();
}
//Содерживое
using (FbCommand fcon_b = new FbCommand(sql_views_b, fb, ft))
{
fcon_b.Parameters.Add("@a", FbDbType.VarChar, 31);
fcon_b.Parameters[0].Value = fr[0].ToString();
using (FbDataReader fr_b = fcon_b.ExecuteReader())
{
while (fr_b.Read())
{
dll_command += "\n" + fr_b.GetString(0);
}
fr_b.Dispose();
}
fcon_b.Dispose();
}
com_views.Add(dll_command);
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public int data_count()
{
int count_ = 0;
string t_name = table_name;
if (is_reserv(t_name))
{
t_name = "\"" + t_name + "\"";
}
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand("select count(*) from " + t_name, fb, ft))
{
switch (table_name.Trim().ToUpper())
{
case "DELETED":
fcon.CommandText += " where Date_Deleted >= cast('NOW' as date) - 180";
break;
case "ERR_LOG":
fcon.CommandText += " where id_err_log is null";
break;
case "BACKUP_LOG":
fcon.CommandText += " where id_BACKUP_LOG is null";
break;
case "SHADOWGUARD":
fcon.CommandText += " where id_SHADOW is null";
break;
case "USER_ACTIVITY":
fcon.CommandText += " where id_user_activity is null";
break;
case "MESSAGES":
fcon.CommandText += " where lastdate >= cast('NOW' as date) - 100";
break;
case "SCHEDULER":
fcon.CommandText += " where id_status is null";
break;
case "USER_CONNECTIONS":
fcon.CommandText += " where ID_USER_CONNECTIONS is null";
break;
case "REPORT_REPLICATION":
fcon.CommandText += " where DATE_CREATE >= cast('NOW' as date) - 30";
break;
}
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
count_ = (int)fr[0];
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
finally { fb.Close(); }
fb.Dispose();
}
return count_;
}
public void m_TRIGGERS_PROTOTYPE()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_triggers, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
string dll_command = "create or alter trigger " + fr[0].ToString().Trim() + " FOR " + fr[1].ToString();
//активность
if (fr[5].ToString().Trim() == "1")
{
dll_command += "\n" + "INACTIVE";
}
else
{
dll_command += "\n" + "ACTIVE";
}
//теперь узнаем что за тип триггера
switch (fr[3].ToString().Trim())
{
case "1":
dll_command += " before insert ";
break;
case "2":
dll_command += " after insert ";
break;
case "3":
dll_command += " before update ";
break;
case "4":
dll_command += " after update ";
break;
case "5":
dll_command += " before delete ";
break;
case "6":
dll_command += " after delete ";
break;
case "17":
dll_command += " before insert or update ";
break;
case "25":
dll_command += " before insert or delete";
break;
case "113":
dll_command += " before insert or update or delete ";
break;
case "27":
dll_command += " before update or delete ";
break;
case "18":
dll_command += " after insert or update ";
break;
case "26":
dll_command += " after insert or delete";
break;
case "114":
dll_command += " after insert or update or delete ";
break;
case "28":
dll_command += " after update or delete ";
break;
}
//позиция триггера
dll_command += " position " + fr[2].ToString().Trim();
dll_command += "\nAS\nBEGIN\n\nEND";
com_triggers_prototype.Add(dll_command);
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("TRIGGERS PROTOTYPE");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void COPY_DATA()
{
int i = 0;
int j = com2.Count;
//а теперь накатываем
using (FbConnection fb = new FbConnection(fc_new.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand("", fb, ft))
{
foreach (string cmd in com2)
{
i++;
p_cur = (int)(((float)i / (float)j) * 100);
p_text = table_name + " " + i.ToString() + "/" + j.ToString();
fcon.CommandText = cmd;
try
{
fcon.ExecuteNonQuery();
}
catch (FbException ex2)
{
sb.AppendLine("**********");
sb.AppendLine("ERROR DATA");
sb.AppendLine("**********");
sb.AppendLine(cmd);
sb.AppendLine("");
sb.AppendLine(ex2.Message);
sb.AppendLine("**********");
}
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("DATA - " + table_name);
sb.AppendLine(ex.Message);
}
fb.Dispose();
}
}
public void m_PROCEDURES()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_procedures, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
string dll_command = "create or alter procedure ";
if (is_reserv(fr[0].ToString()))
{
dll_command += "\"" + fr[0].ToString() + "\"" + "\n";
}
else
{
dll_command += fr[0].ToString() + "\n";
}
//Список полей
string fields_in = "";
string fields_out = "";
bool is_out = false;
using (FbCommand fcon_a = new FbCommand(sql_procedures_f, fb, ft))
{
fcon_a.Parameters.Add("@a", FbDbType.VarChar, 31);
fcon_a.Parameters[0].Value = fr[0].ToString();
using (FbDataReader fr_a = fcon_a.ExecuteReader())
{
while (fr_a.Read())
{
//резервное?
var fields_name = fr_a[0].ToString().Trim();
if (is_reserv(fields_name))
{
fields_name = "\"" + fields_name + "\"";
}
//Извлекаем тип данных
string field_type = get_field_type(fr_a[3].ToString(), fr_a[4].ToString(), fr_a[5].ToString(), fr_a[6].ToString(), fr_a[7].ToString(), fr_a[8].ToString());
//теперь в зависимости от типа параметра (вх/вых) - запишем в определенный блок
if (fr_a[2].ToString() == "0")
{
//входной
if (fields_in == "")
{
fields_in += "( " + fields_name + " " + field_type;
}
else
{
fields_in += ",\n " + fields_name + " " + field_type;
}
}
else
{
//значит выходной
if (!is_out)
{
//ставим флаг, что есть выходные параметры
is_out = true;
}
if (fields_out == "")
{
fields_out += "returns ( " + fields_name + " " + field_type;
}
else
{
fields_out += ",\n " + fields_name + " " + field_type;
}
}
}
fr_a.Dispose();
}
fcon_a.Dispose();
}
//теперь соединяем
if (fields_in != "")
{
fields_in += "\n )";
}
if (fields_out != "")
{
fields_out += "\n )";
}
dll_command += fields_in + fields_out + "\n AS\n";
//Добавляем содержимое
try
{
using (FbCommand fcon_b = new FbCommand(sql_procedures_b, fb, ft))
{
fcon_b.Parameters.Add("@a", FbDbType.VarChar, 31);
fcon_b.Parameters[0].Value = fr[0].ToString();
using (FbDataReader fr_b = fcon_b.ExecuteReader())
{
while (fr_b.Read()) {
dll_command += fr_b.GetString(0);
//.Replace("TRIM", "TRIM_").Replace("Trim", "TRIM_").Replace("trim", "TRIM_").Replace("IIF", "IIF_").Replace("iif", "IIF_").Replace("Iif", "IIF_");
}
fr_b.Dispose();
}
fcon_b.Dispose();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
dll_command = regexTrim.Replace(dll_command, "TRIM_");
dll_command = regexIIF.Replace(dll_command, "IIF_");
com_procedures_content.Add(dll_command);
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void update_param(string pass_)
{
using (FbConnection fb = new FbConnection(fc_new.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand("select RF.RDB$FIELD_NAME "+
" from RDB$RELATION_FIELDS RF "+
" where RF.RDB$RELATION_NAME = 'PARAMS' and "+
" RF.RDB$FIELD_NAME in ('PARAM_VALUE_DEF') ",fb,ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
using (FbCommand fcon_p = new FbCommand("update PARAMS P "+
" set P.PARAM_VALUE_DEF = @a " +
" where (P.MNEMO = 'SYSDBAPASS')",fb,ft))
{
fcon_p.Parameters.Add("@a", FbDbType.VarChar, 8);
fcon_p.Parameters[0].Value = pass_;
fcon_p.ExecuteNonQuery();
fcon_p.Dispose();
}
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("*****Ошибка при смене параметра пароль SYSDBA*****");
sb.AppendLine(ex.Message);
sb.AppendLine("**********");
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
public void m_TRIGGERS()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_triggers, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
string dll_command = "create or alter trigger " + fr[0].ToString().Trim() + " FOR " + fr[1].ToString();
//активность
if (fr[5].ToString().Trim() == "1")
{
dll_command += "\n" + "INACTIVE";
}
else
{
dll_command += "\n" + "ACTIVE";
}
//теперь узнаем что за тип триггера
switch (fr[3].ToString().Trim())
{
case "1":
dll_command += " before insert ";
break;
case "2":
dll_command += " after insert ";
break;
case "3":
dll_command += " before update ";
break;
case "4":
dll_command += " after update ";
break;
case "5":
dll_command += " before delete ";
break;
case "6":
dll_command += " after delete ";
break;
case "17":
dll_command += " before insert or update ";
break;
case "25":
dll_command += " before insert or delete";
break;
case "113":
dll_command += " before insert or update or delete ";
break;
case "27":
dll_command += " before update or delete ";
break;
case "18":
dll_command += " after insert or update ";
break;
case "26":
dll_command += " after insert or delete";
break;
case "114":
dll_command += " after insert or update or delete ";
break;
case "28":
dll_command += " after update or delete ";
break;
}
//позиция триггера
dll_command += " position " + fr[2].ToString().Trim();
//Добавляем содержимое
using (FbCommand fcon_b = new FbCommand(sql_triggers_b, fb, ft))
{
fcon_b.Parameters.Add("@a", FbDbType.VarChar, 31);
fcon_b.Parameters[0].Value = fr[0].ToString();
using (FbDataReader fr_b = fcon_b.ExecuteReader())
{
while (fr_b.Read())
{
dll_command += "\n" + fr_b.GetString(0);
}
fr_b.Dispose();
}
fcon_b.Dispose();
}
dll_command = regexTrim.Replace(dll_command, "TRIM_");
dll_command = regexIIF.Replace(dll_command, "IIF_");
com_triggers_content.Add(dll_command);
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("TRIGGERS Content");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
static bool TryConnect(string username)
{
bool res = false;
using (var db = new ApplicationDbContext())
{
foreach (var item in db.tModerator.Where(a => a.tUsers.UserName == username))
{
try
{
var fc = new FbConnectionStringBuilder
{
UserID = item.tModerator_userId,
Password = item.tModerator_password,
Database = item.tModerator_database,
Pooling = false,
Charset = "win1251"
};
using (var fb = new FbConnection(fc.ConnectionString))
{
try
{
fb.Open();
fb.Close();
res = true;
}
catch { }
fb.Dispose();
}
}
catch { }
}
db.Dispose();
}
return res;
}
public void m_PRIMARY_KEY()
{
using (FbConnection fb = new FbConnection(fc_old.ConnectionString))
{
try
{
fb.Open();
using (FbTransaction ft = fb.BeginTransaction())
{
using (FbCommand fcon = new FbCommand(sql_primary_key, fb, ft))
{
using (FbDataReader fr = fcon.ExecuteReader())
{
while (fr.Read())
{
string dll_command = "";
dll_command += "ALTER TABLE ";
if (is_reserv(fr[1].ToString().Trim()))
{
dll_command += "\"" + fr[1].ToString().Trim() + "\"";
}
else
{
dll_command += fr[1].ToString().Trim();
}
if (fr[0].ToString().Substring(0, 5).ToUpper() == "INTEG")
{
//если это просто индекс без наименования - то есть созданный системой
dll_command += " ADD PRIMARY KEY ";
}
else
{
dll_command += " ADD CONSTRAINT " + fr[0].ToString().Trim() + " PRIMARY KEY ";
}
//соостав индекса
string fields_ = "";
using (FbCommand fcon_f = new FbCommand(sql_primary_key_f, fb, ft))
{
fcon_f.Parameters.Add("@a", FbDbType.VarChar, 31);
fcon_f.Parameters[0].Value = fr[2].ToString();
using (FbDataReader fr_f = fcon_f.ExecuteReader())
{
while (fr_f.Read())
{
if (fields_ == "")
{
fields_ += "(" + fr_f[0].ToString();
}
else
{
fields_ += "," + fr_f[0].ToString();
}
}
fields_ += ")";
fr_f.Dispose();
}
fcon_f.Dispose();
}
dll_command += " " + fields_;
com_primary_key.Add(dll_command);
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch (FbException ex)
{
sb.AppendLine("");
sb.AppendLine(ex.Message);
}
finally
{
fb.Close();
}
fb.Dispose();
}
}
static void SyncProject(string username)
{
//вывожу ее отдельно, так как здесь немного другая логика работы
using (var db = new ApplicationDbContext())
{
var t = db.tModerator.First(a => a.tUsers.UserName == username);
if (t != null)
{
var fc = new FbConnectionStringBuilder
{
UserID = t.tModerator_userId,
Password = t.tModerator_password,
Database = t.tModerator_database,
Charset = "win1251",
Pooling = false,
Role = "R_ADMIN"
};
using (var fb = new FbConnection(fc.ConnectionString))
{
try
{
fb.Open();
using (var ft = fb.BeginTransaction())
{
using (
var fcon = new FbCommand(
"select p.ID_PROJECTS, p.MNEMO from PROJECTS p order by 1", fb, ft))
{
using (var fr = fcon.ExecuteReader())
{
while (fr.Read())
{
//Добавляем проект
if (db.tProject.Find(fr[0]) != null)
{
var m = db.tProject.Find(fr[0]);
m.tProject_name = fr[1].ToString();
db.Entry(m).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
else
{
var m = new tProject
{
Id = (int)fr[0],
tProject_name = fr[1].ToString()
};
db.tProject.Add(m);
db.SaveChanges();
}
//теперь добавим подсистему
using (
var fconSub =
new FbCommand(
"select s.ID_SUBSYSTEM, s.MNEMO from SUBSYSTEMS s where s.ID_PROJECTS = @id order by 1",
fb, ft))
{
fconSub.Parameters.AddWithValue("@id", (int)fr[0]);
using (var frSub = fconSub.ExecuteReader())
{
while (frSub.Read())
{
if (db.tSubsystem.Find(frSub[0]) != null)
{
var sub = db.tSubsystem.Find(frSub[0]);
sub.tSubsystem_name = frSub[1].ToString();
db.Entry(sub).State =
System.Data.Entity.EntityState.Modified;
}
else
{
var sub = new tSubsystem
{
Id = (int)frSub[0],
tSubsystem_name = frSub[1].ToString(),
tproject = db.tProject.Find(fr[0])
};
db.tSubsystem.Add(sub);
db.SaveChanges();
}
}
frSub.Dispose();
}
fconSub.Dispose();
}
//Релизы проектов
using (
var fconSub =
new FbCommand(
"select pr.ID_RELEASE_PROJECTS, pr.MNEMO from RELEASE_PROJECTS pr where pr.ID_PROJECTS = @id order by pr.MNEMO",
fb, ft))
{
fconSub.Parameters.AddWithValue("@id", (int)fr[0]);
using (var frSub = fconSub.ExecuteReader())
{
while (frSub.Read())
{
if (db.tReleaseProject.Find(frSub[0]) != null)
{
var sub = db.tReleaseProject.Find(frSub[0]);
sub.tReleaseProject_name = frSub[1].ToString();
db.Entry(sub).State =
System.Data.Entity.EntityState.Modified;
}
else
{
var sub = new tReleaseProject
{
Id = (int)frSub[0],
tReleaseProject_name = frSub[1].ToString(),
tproject = db.tProject.Find(fr[0])
};
db.tReleaseProject.Add(sub);
db.SaveChanges();
}
}
frSub.Dispose();
}
fconSub.Dispose();
}
}
fr.Dispose();
}
fcon.Dispose();
}
ft.Commit();
ft.Dispose();
}
}
catch{}
finally
{
fb.Close();
}
fb.Dispose();
}
}
db.Dispose();
}
}
static int Create(int id, string username, string href)
{
var docNumber = 0;
using (var db = new ApplicationDbContext())
{
var t = db.tOffer.Find(id);
var m = db.tModerator.First(a => a.tUsers.UserName == username);
var fc = new FbConnectionStringBuilder
{
UserID = m.tModerator_userId,
Password = m.tModerator_password,
Database = m.tModerator_database,
Charset = "win1251",
Pooling = false,
Role = "R_ADMIN"
};
using (var fb = new FbConnection(fc.ConnectionString))
{
try
{
fb.Open();
using (var ft = fb.BeginTransaction())
{
using (var fcon = new FbCommand("select G.NUM " +
"from GET_DC_DOCUMENT_NUMBER(85) G", fb, ft))
{
using (var fr = fcon.ExecuteReader())
{
while (fr.Read())
{
docNumber = (int)fr[0];
}
fr.Dispose();
}
fcon.Dispose();
}
const string com = "execute procedure IUD_BUGS('I', null, @IS_ERROR, @LOCATION, @ID_RELEASE_PROJECTS, @ID_RELEASE_PROJECTS_EXEC," +
" null, null, @ID_SUBSYSTEM," +
"@ID_BRANCH, null, 85, 1, (select list_id from sel_filter_budg(2)),@DOC_NUMBER, 'NOW'," +
"null, @comment, null, @DETAIL_COMMENT, 1, @ID_PROJECTS, null,null) ";
using (var fcon = new FbCommand(com, fb, ft))
{
switch (t.tOffer_error)
{
case true:
fcon.Parameters.AddWithValue("@IS_ERROR", '1');
break;
default:
fcon.Parameters.AddWithValue("@IS_ERROR", '0');
break;
}
fcon.Parameters.AddWithValue("@LOCATION", t.tOffer_place);
fcon.Parameters.AddWithValue("@ID_RELEASE_PROJECTS", t.tOffer_tReleaseProject_id);
fcon.Parameters.AddWithValue("@ID_RELEASE_PROJECTS_EXEC", t.tOffer_tReleaseProject_exec_id);
fcon.Parameters.AddWithValue("@ID_SUBSYSTEM", t.tOffer_tSubsystem_id);
fcon.Parameters.AddWithValue("@ID_BRANCH", t.tOffer_tBranch_id);
if (t.tforummessages != null)
{
fcon.Parameters.AddWithValue("@comment", t.tforummessages.tForumThemes.tForumThemes_name);
fcon.Parameters.AddWithValue("@DETAIL_COMMENT",
"ФОРУМ<hr/><em><a href ='" + href + "'> " +
href +
"</a></em>" +
"<p>" + t.tforummessages.tForumMessages_messages + "</p>");
}
else
{
fcon.Parameters.AddWithValue("@comment", t.tforumthemes.tForumThemes_name);
fcon.Parameters.AddWithValue("@DETAIL_COMMENT",
"ФОРУМ<hr/><em><a href ='" +
href +
"'> " +
href +
"</a></em>" +
"<p>" + t.tforumthemes.tForumThemes_desc + "</p>");
}
fcon.Parameters.AddWithValue("@ID_PROJECTS", t.tOffer_tProject_id);
fcon.Parameters.AddWithValue("@DOC_NUMBER", docNumber);
try
{
fcon.ExecuteNonQuery();
ft.Commit();
}
catch (FbException ex1)
{
ft.Rollback();
}
finally
{
fcon.Dispose();
}
}
ft.Dispose();
}
}
catch { }
finally
{
fb.Close();
}
fb.Dispose();
}
db.Dispose();
}
return docNumber;
}
