public static async Task <bool> AchievementType(FbConnection fbConnection, EcfTableReader ecfTableReader)
{
var success = 0;
var sql =
"UPDATE \"Leistungsarten\" " +
"SET \"Bezeichnung\" = @Name " +
"WHERE " +
" \"Kuerzel\" = @Code";
using var fbTransaction = fbConnection.BeginTransaction();
try
{
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
// Token based table with max. length of 20, code will be cut, if longer
string code = ecfTableReader.GetValue <string>("Code");
if (code.Length > 20)
{
code = code.Substring(0, 20);
}
Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, code);
Helper.SetParamValue(fbCommand, "@Name", FbDbType.VarChar, ecfTableReader.GetValue <string>("Name"));
success = await fbCommand.ExecuteNonQueryAsync();
await fbTransaction.CommitAsync();
}
catch (Exception e)
{
fbTransaction.Rollback();
Console.WriteLine($"[UPDATE ERROR] [Leistungsarten] {e.Message}");
}
return(success > 0);
}
public void reQry(DataTable dataTable, FbCommand fbCommand)
{
Program.MF.startSW();
int nor = 0;
Cursor.Current = Cursors.WaitCursor;
FbConnection c = new FbConnection(Properties.Settings.Default.ConnectionString);
c.Open();
FbTransaction trans = c.BeginTransaction();
try
{
fbCommand.CommandType = CommandType.Text;
fbCommand.Connection = c;
fbCommand.Transaction = trans;
FbDataAdapter fbDataAdapter = new FbDataAdapter();
fbDataAdapter.SelectCommand = fbCommand;
nor = fbDataAdapter.Fill(dataTable);
trans.Commit();
fbCommand.Parameters["@LM_TS"].Value = (DateTime)Program.MF.mainQueriesTableAdapter.GET_SERVERTIME();
//Program.MF.Text = nor.ToString() + " reread " + fbCommand.Parameters["@LM_TS"].Value.ToString() + " " + fbCommand.CommandText;
}
catch
{
trans.Rollback();
}
finally
{
c.Close();
Cursor.Current = Cursors.Default;
Program.MF.stopSW("Query", nor);
}
}
public List <Sostzakaz> GetSostzakaz()
{
FbConnection connection = connect.connection;
List <Sostzakaz> sostzakaz = new List <Sostzakaz>();
try
{
connection.Open();
FbTransaction transaction = connection.BeginTransaction();
FbCommand command = new FbCommand("SELECT * FROM SOSTZAKAZ", connection, transaction);
FbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Sostzakaz zakaz = new Sostzakaz()
{
};
zakaz.SOSTZAKAZID = reader.GetInt32(0);
zakaz.SOSTZAKAZ = reader.GetString(1);
zakaz.COLOR = reader.IsDBNull(2) ? (int?)null : reader.GetInt32(2);
sostzakaz.Add(zakaz);
}
reader.Close();
connection.Close();
}
catch (Exception ex)
{
logger.Error(ex.ToString());
connection.Close();
}
return(sostzakaz);
}
public static async Task <DbResult> TokenCatalog(FbConnection fbConnection, string tableName, string code)
{
// Tokens in MAGELLAN have a max length of 20 chars, if the token is bigger, it will be cut
if (code.Length > 20)
{
code = code.Substring(0, 20);
}
string sql =
$"SELECT \"Kuerzel\" FROM \"{tableName}\" " +
"WHERE " +
" \"Kuerzel\" = @Code";
using var fbTransaction = fbConnection.BeginTransaction();
using var fbCommand = new FbCommand(sql, fbConnection, fbTransaction);
Helper.SetParamValue(fbCommand, "@Code", FbDbType.VarChar, code);
var sqlReader = await fbCommand.ExecuteReaderAsync();
code = String.Empty;
var numberOfRecords = 0;
while (sqlReader.Read())
{
code = (string)sqlReader["Kuerzel"];
numberOfRecords++;
}
if (numberOfRecords == 1)
{
return(new DbResult(true, code));
}
return(new DbResult(false, null));
}
// test app to show difference in performance, related to https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient/pull/84
private static void FBd()
{
string s = null;
using (var ms = new MemoryStream())
using (var sr = new StreamReader(ms, Encoding.Unicode))
s = sr.ReadToEnd();
var sw = new Stopwatch();
using (var conn = new FbConnection(fb2))
{
conn.Open();
using (var tr = conn.BeginTransaction())
using (var cmd = new FbCommand(query, conn, tr))
{
sw.Start();
for (int i = 0; i < 100; i++)
{
using (var rdr = cmd.ExecuteReader())
while (rdr.Read())
{
}
}
sw.Stop();
tr.Commit();
}
}
Console.WriteLine($"Elapsed: {sw.Elapsed}");
Console.ReadLine();
}
private static bool IsInDatabase(Action <FbCommand> adjustCommand)
{
var result = false;
using (var connection = new FbConnection(IntegrationTestOptions.Firebird.ConnectionString))
{
connection.Open();
using (var tx = connection.BeginTransaction())
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = tx;
adjustCommand(cmd);
using (var reader = cmd.ExecuteReader())
{
result = reader.Read();
}
}
tx.Commit();
}
connection.Close();
}
return(result);
}
private void refreshBookList()
{
BooksDG.Items.Clear();
using (var transaction = connection.BeginTransaction())
{
using (var command = new FbCommand("select ID_KSIAZKA, TYTUL, ks.ID_KATEGORIA, KATEGORIA, ks.ID_AUTOR, IMIE || ' ' || NAZWISKO, ks.ID_WYDAWNICTWO, WYDAWNICTWO, " +
"ROK_WYDANIA, ILOSC from KSIAZKA ks " +
"inner join KATEGORIE ka on ks.ID_KATEGORIA = ka.ID_KATEGORIA " +
"inner join AUTORZY a on ks.ID_AUTOR = a.ID_AUTOR " +
"inner join WYDAWNICTWA w on ks.ID_WYDAWNICTWO = w.ID_WYDAWNICTWO order by TYTUL", connection, transaction))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
IDataRecord record = reader;
Book tmp = new Book((int)record[0], (string)record[1], (int)record[2], (string)record[3], (int)record[4], (string)record[5],
(int)record[6], (string)record[7], (int)record[8], (int)record[9]);
BooksDG.Items.Add(tmp);
}
}
}
}
}
public void get()
{
this.Clear();
try
{
FbConnection fb = new FbConnection(connection.connectionString());
fb.Open();
FbTransaction fbt = fb.BeginTransaction(); //стартуем транзакцию; стартовать транзакцию можно только для открытой базы (т.е. мутод Open() уже был вызван ранее, иначе ошибка)
FbCommand SelectSQL = new FbCommand();
SelectSQL.CommandText = "select d.name,a.price,a.quanshop, a.usernameback from backshop a, goods b, name d where a.goodscode=b.goodscode and b.namecode=d.namecode and a.datatimeback>@datetime"; //задаем запрос на выборку
SelectSQL.Parameters.Add("@datetime", DateTime.Now.Date);
SelectSQL.Connection = fb;
SelectSQL.Transaction = fbt; //необходимо проинициализить транзакцию для объекта SelectSQL
FbDataReader reader = SelectSQL.ExecuteReader(); //для запросов, которые возвращают результат в виде набора данных надо использоваться метод ExecuteReader()
try
{
while (reader.Read()) //пока не прочли все данные выполняем...
{
var nch = new strokavozvrata(reader.GetString(0), reader.GetInt16(2), reader.GetDouble(1), reader.GetString(3));
this.Add(nch);
}
}
finally
{
//всегда необходимо вызывать метод Close(), когда чтение данных завершено
reader.Close();
fb.Close(); //закрываем соединение, т.к. оно нам больше не нужно
}
SelectSQL.Dispose();
}
catch (Exception e)
{
}
}
/// <inheritdoc/>
public override void BeginTransaction(IsolationLevel isolationLevel)
{
EnsureTrasactionIsNotActive();
var transactionOptions = new FbTransactionOptions {
WaitTimeout = TimeSpan.FromSeconds(10)
};
switch (SqlHelper.ReduceIsolationLevel(isolationLevel))
{
case IsolationLevel.ReadCommitted:
transactionOptions.TransactionBehavior = FbTransactionBehavior.ReadCommitted
| FbTransactionBehavior.NoRecVersion
| FbTransactionBehavior.Write
| FbTransactionBehavior.NoWait;
break;
case IsolationLevel.Serializable:
transactionOptions.TransactionBehavior = FbTransactionBehavior.Concurrency
| FbTransactionBehavior.Write
| FbTransactionBehavior.Wait;
break;
}
activeTransaction = underlyingConnection.BeginTransaction(transactionOptions);
}
public void ComandoLoteScript(string SQL)
{
FbConnection connection = new FbConnection(connectionString);
connectionString = BmsSoftware.ConfigSistema1.Default.ConexaoFB + txtLocalBancoDados.Text;
try
{
connection.Open();
FbTransaction transaction = connection.BeginTransaction();
FbCommand command = new FbCommand(SQL, connection, transaction);
command.CommandType = CommandType.Text;
command.ExecuteScalar();
transaction.Commit();
connection.Close();
}
catch (Exception)
{
if (connection != null)
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
MessageBox.Show("Não foi possível executar o Script!",
BmsSoftware.ConfigSistema1.Default.NomeEmpresa,
MessageBoxButtons.OK,
MessageBoxIcon.Error,
MessageBoxDefaultButton.Button1);
}
}
/// <summary>
/// записати нове заавдання
/// </summary>
/// <param name="newTask">перелік значень полів</param>
public void InsertTask(List <string> newTask)
{
if (newTask.Count != 8)
{
return;
}
DateTime dt1 = new DateTime();
dt1 = DateTime.Now;
string newId = GenerateNewID();
string connectionString = cs.ToString();
FbConnection connection = new FbConnection(connectionString);
connection.Open();
string ins_str;
ins_str = @"INSERT INTO TASKS(ID, SUBJECT, SHORTDESCRIPTION, EXECUTORID, DEADLINE, MANAGERID, DATEINIT, PRIORITY, DATEDOC, NUMBERDOC)
VALUES (@ID, @SUBJECT, @SHORTDESCRIPTION, @EXECUTORID, @DEADLINE, @MANAGERID, @DATEINIT, @PRIORITY, @DATEDOC, @NUMBERDOC)";
FbTransaction ins_tr = connection.BeginTransaction();
FbCommand ins_com = new FbCommand(ins_str, connection, ins_tr);
ins_com.Parameters.Add(new FbParameter("@ID", newId));
ins_com.Parameters.Add(new FbParameter("@SUBJECT", newTask[0]));
ins_com.Parameters.Add(new FbParameter("@SHORTDESCRIPTION", newTask[1]));
ins_com.Parameters.Add(new FbParameter("@EXECUTORID", newTask[2]));
ins_com.Parameters.Add(new FbParameter("@DEADLINE", newTask[3]));
ins_com.Parameters.Add(new FbParameter("@MANAGERID", newTask[4]));
ins_com.Parameters.Add(new FbParameter("@DATEINIT", dt1.ToShortDateString()));
ins_com.Parameters.Add(new FbParameter("@PRIORITY", newTask[5]));
ins_com.Parameters.Add(new FbParameter("@DATEDOC", newTask[6]));
ins_com.Parameters.Add(new FbParameter("@NUMBERDOC", newTask[7]));
ins_com.ExecuteNonQuery();
ins_tr.Commit();
connection.Close();
SaveSubject(newTask[0]);
}
public static void SavePointList(Database database, List <PermitPoint> pointList)
{
string query = EntryControl.Resources.Doc.Permit.SetPermitPoint;
using (FbConnection connection = new FbConnection(database.ConnectionString))
{
FbCommand command = new FbCommand(query, connection);
command.Parameters.AddWithValue("permit", 0);
command.Parameters.AddWithValue("point", 0);
command.Parameters.AddWithValue("isAllowed", 0);
connection.Open();
FbTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
foreach (PermitPoint pp in pointList)
{
command.Parameters["permit"].Value = pp.Permit.Id;
command.Parameters["point"].Value = pp.Point.Id;
command.Parameters["isAllowed"].Value = (short)(pp.IsAllowed ? 1 : 0);
command.ExecuteNonQuery();
}
transaction.Commit();
connection.Close();
}
catch (Exception)
{
transaction.Rollback();
connection.Close();
throw new Exception(EntryControl.Resources.Message.Error.SavingError);
}
}
}
/// <summary>
/// Проверяет наличие дубликата файла
/// </summary>
/// <param name="taskName">Имя задачи</param>
/// <param name="filePath">Путь к файлу</param>
/// <returns></returns>
public static bool CheckForDuplicate(string taskName, string filePath)
{
string fileName = Path.GetFileName(filePath);
string hash = Pbkdf2Cryptography.GetMD5Hash(File.ReadAllBytes(filePath));
string connStr = @"SELECT 1 FROM " + taskName +
@" WHERE FILENAME = '" + fileName + "' " +
@"AND HASH = '" + hash + "'";
object result;
using (FbConnection connection = new FbConnection(ConnectionString)) {
connection.Open();
using (FbCommand command = new FbCommand(connStr, connection)) {
using (FbTransaction transaction = connection.BeginTransaction()) {
command.Transaction = transaction;
result = command.ExecuteScalar();
transaction.Rollback();
}
}
}
return(result != null);
}
/// <summary>
/// Добавляет запись с признаком уникальности обработанного файла
/// </summary>
/// <param name="taskName">Имя задачи</param>
/// <param name="filePath">Путь к файлу</param>
public static void CreateFileFingerprint(string taskName, string filePath)
{
string fileName = Path.GetFileName(filePath);
FileInfo fileInfo = new FileInfo(filePath);
string hash = Pbkdf2Cryptography.GetMD5Hash(File.ReadAllBytes(filePath));
string connStr = "INSERT INTO " + taskName +
" (FILENAME, HASH, OPERATIONDATE, MODIFYDATE) " +
string.Format("VALUES('{0}','{1}','{2}','{3}');", fileName, hash,
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), fileInfo.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss"));
using (FbConnection connection = new FbConnection(ConnectionString)) {
connection.Open();
using (FbCommand command = new FbCommand(connStr, connection)) {
using (FbTransaction transaction = connection.BeginTransaction()) {
command.Transaction = transaction;
command.ExecuteNonQuery();
transaction.Commit();
}
}
}
}
public int GetMaxId(bool commit = false)
{
if (IsConnected)
{
try
{
FbTransaction myTransaction = connecting.BeginTransaction();
FbCommand myCommand = new FbCommand();
myCommand.CommandText =
"select MAX(ID) from object";
myCommand.Connection = connecting;
myCommand.Transaction = myTransaction;
var result = myCommand.ExecuteReader();
if (result.HasRows) // если есть данные
{
// выводим названия столбцов
// Console.WriteLine("{0}", result.GetName(0));
while (result.Read()) // построчно считываем данные
{
object id = result.GetValue(0);
return(int.Parse(id.ToString()));
//Console.WriteLine("{0}", id);
}
}
if (commit)
{
myTransaction.Commit();
}
myCommand.Dispose();
}
catch (Exception ex)
{
LastErrore = ex.Message + "---" + ex.StackTrace;
return(-1);
}
}
return(-1);
}
protected override bool Run(object parameters)
{
List <IpCity> rangeData = (List <IpCity>)parameters;
rangeData.Clear();
FbConnection db = new FbConnection(_settings.DatabaseConnectionString);
try
{
db.Open();
string SQL = "SELECT ipc.WD$ID, COALESCE(c.WD$COUNTRY_CODE, 'Unknown'), COALESCE(ipc.WD$REGION, ''), " +
"COALESCE(ipc.WD$CITY, ''), COALESCE(ipc.WD$LATITUDE, 0.0), COALESCE(ipc.WD$LONGITUDE, 0.0), " +
"COALESCE(c.WD$FROM_IP, 0), COALESCE(c.WD$TO_IP, 0) FROM WD$IPTOCOUNTRY c " +
"LEFT JOIN WD$IPCITY ipc ON (ipc.WD$ID = c.WD$CITY_ID) ";
string whereClause = String.Empty;
foreach (string countryCode in _settings.CountryList)
{
if (String.IsNullOrEmpty(countryCode))
{
continue;
}
if (whereClause.Length > 0)
{
whereClause += ", ";
}
whereClause += $"'{countryCode}'";
}
if (!String.IsNullOrEmpty(whereClause))
{
SQL += $"WHERE c.WD$COUNTRY_CODE IN ({whereClause})";
}
FbTransaction tran = db.BeginTransaction();
try
{
FbCommand cmd = new FbCommand(SQL, db, tran);
try
{
FbDataReader rdr = cmd.ExecuteReader();
try
{
while (rdr.Read())
{
rangeData.Add(new IpCity(rdr.GetInt64(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3),
rdr.GetDecimal(4), rdr.GetDecimal(5), rdr.GetInt64(6), rdr.GetInt64(7)));
}
}
finally
{
rdr.Close();
rdr = null;
}
}
finally
{
cmd.Dispose();
cmd = null;
}
}
finally
{
tran.Rollback();
tran.Dispose();
tran = null;
}
}
finally
{
db.Close();
db.Dispose();
}
rangeData.Sort();
return(false);
}
/// <summary>
/// Rollback Transaction: contrário ao commit, o rollback cancela a transação.
/// Assim, todos os comandos executados no bloco da transação são descartados e a base de dados não sofre nenhuma alteração.
/// Esse comando é geralmente utilizado caso ocorra algum erro na execução de uma das instruções do bloco.
/// </summary>
public static void RollBack()
{
conn.BeginTransaction().Rollback();
}
private static void InsertTestData(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(@int_field, @char_field, @varchar_field, @bigint_field, @smallint_field, @float_field, @double_field, @numeric_field, @date_field, @time_field, @timestamp_field, @clob_field, @blob_field)");
FbTransaction transaction = connection.BeginTransaction();
FbCommand command = new FbCommand(commandText.ToString(), connection, transaction);
try
{
// Add command parameters
command.Parameters.Add("@int_field" , FbDbType.Integer);
command.Parameters.Add("@char_field" , FbDbType.Char);
command.Parameters.Add("@varchar_field" , FbDbType.VarChar);
command.Parameters.Add("@bigint_field" , FbDbType.BigInt);
command.Parameters.Add("@smallint_field" , FbDbType.SmallInt);
command.Parameters.Add("@float_field" , FbDbType.Double);
command.Parameters.Add("@double_field" , FbDbType.Double);
command.Parameters.Add("@numeric_field" , FbDbType.Numeric);
command.Parameters.Add("@date_field" , FbDbType.Date);
command.Parameters.Add("@time_Field" , FbDbType.Time);
command.Parameters.Add("@timestamp_field" , FbDbType.TimeStamp);
command.Parameters.Add("@clob_field" , FbDbType.Text);
command.Parameters.Add("@blob_field" , FbDbType.Binary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10)/5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10)/5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value= DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
// Commit transaction
transaction.Commit();
}
catch (FbException)
{
transaction.Rollback();
throw;
}
finally
{
command.Dispose();
connection.Close();
}
}
public FbTransaction CrieTransacao()
{
return(_conexao.BeginTransaction());
}
/// <summary>
/// Rebuilds all replication triggers based on rules within REPLICATE$TABLES
/// </summary>
/// <returns>Physical SQL File on disk containing all the updates</returns>
private string RebuildReplicationTriggers(string connectionString, bool generateOnly)
{
// get temp file for triggers
string Result = Path.GetTempFileName();
try
{
//connect to local DB
FbConnection db = new FbConnection(connectionString);
db.Open();
try
{
FbTransaction tran = db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
StreamWriter updateFile = new StreamWriter(Result, false);
try
{
string tableNames = String.Empty;
string SQL = "SELECT r.TABLE_NAME, r.OPERATION, r.TRIGGER_NAME, r.EXCLUDE_FIELDS, r.LOCAL_ID_COLUMN \n" +
"FROM REPLICATE$TABLES r \nORDER BY r.TABLE_NAME, r.OPERATION ";
FbDataReader rdr = null;
FbCommand cmd = new FbCommand(SQL, db, tran);
try
{
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (!tableNames.Contains(rdr.GetString(0).Trim()))
{
if (tableNames.Length > 0)
{
tableNames += String.Format(",'{0}'\n", rdr.GetString(0).Trim());
}
else
{
tableNames += String.Format("'{0}'\n", rdr.GetString(0).Trim());
}
}
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
// have any tables been removed from the list since the last time this was run?
SQL = String.Format("SELECT TRIM(a.RDB$TRIGGER_NAME) FROM RDB$TRIGGERS a " +
"WHERE ((TRIM(a.RDB$TRIGGER_NAME) LIKE 'REPLICATE$%_ID')) " +
"OR ((TRIM(a.RDB$TRIGGER_NAME) <> 'REPLICATE$PK_CHANGES') AND TRIM(a.RDB$TRIGGER_NAME) LIKE 'REPLICATE$%' " +
"AND a.RDB$RELATION_NAME NOT IN ( \n" +
tableNames + "\n)) OR a.RDB$TRIGGER_NAME LIKE 'REPLICATE$%_ID';");
cmd = new FbCommand(SQL, db, tran);
try
{
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
updateFile.WriteLine(String.Format("DROP TRIGGER {0};", rdr.GetString(0).Trim()));
string hashDatabase = "D" + Shared.Utilities.HashStringMD5(GetDatabaseName(db));
string hashCode = "C";
string triggerHash = "T" + Shared.Utilities.HashStringMD5(
rdr.GetString(0).Trim().Replace("REPLICATE$", ""));
_xmlHashUpdates.Add(String.Format("{0}${1}${2}", hashDatabase, triggerHash, hashCode));
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
SQL = "SELECT DISTINCT r.TABLE_NAME \n" +
"FROM REPLICATE$TABLES r ";
cmd = new FbCommand(SQL, db, tran);
try
{
tableNames = String.Empty;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
updateFile.Write(ReplicateTableHasReplicateFields(db, tran, rdr.GetString(0).Trim()));
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
SQL = "SELECT r.TABLE_NAME, r.OPERATION, r.TRIGGER_NAME, r.EXCLUDE_FIELDS, r.LOCAL_ID_COLUMN, r.OPTIONS \n" +
"FROM REPLICATE$TABLES r \nORDER BY r.TABLE_NAME, r.OPERATION ";
cmd = new FbCommand(SQL, db, tran);
try
{
tableNames = String.Empty;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
string triggerCode = String.Empty;
switch (rdr.GetString(1))
{
case "INSERT":
triggerCode = ReplicateCreateTriggerInsert(db, tran, generateOnly,
rdr.GetString(0).Trim(), rdr.GetString(2).Trim(),
rdr.GetString(3), (TableOptions)rdr.GetInt64(5));
break;
case "UPDATE":
triggerCode = ReplicateCreateTriggerUpdate(db, tran, generateOnly,
rdr.GetString(0).Trim(), rdr.GetString(2).Trim(),
rdr.GetString(3), rdr.GetString(4), (TableOptions)rdr.GetInt64(5));
break;
case "DELETE":
triggerCode = ReplicateCreateTriggerDelete(db, tran, generateOnly,
rdr.GetString(0).Trim(), rdr.GetString(2).Trim(),
rdr.GetString(3), (TableOptions)rdr.GetInt64(5));
break;
}
if (!String.IsNullOrEmpty(triggerCode))
{
updateFile.Write(triggerCode);
}
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
}
finally
{
updateFile.Flush();
updateFile.Close();
updateFile = null;
}
}
finally
{
tran.Rollback();
}
}
finally
{
db.Close();
db.Dispose();
db = null;
}
}
catch (Exception e)
{
Shared.EventLog.Add(e);
throw;
}
return(Result);
}
public ViewFinalExam(int examid)
{
InitializeComponent();
this.examid = examid;
string[] foo;
string bar = "";
if (fb.State == ConnectionState.Closed) //SELECT CURRENT EXAM
{
fb.Open();
}
FbTransaction fbt = fb.BeginTransaction();
FbCommand SelectSQL = new FbCommand("SELECT final_exams.*, users.name, users.surname, users.patronymic, course.name FROM final_exams, users, course WHERE final_exams.id = " + examid + " AND users.id = final_exams.user_id AND course.id = final_exams.course_id", fb);
SelectSQL.Transaction = fbt;
FbDataReader reader = SelectSQL.ExecuteReader();
reader.Read();
for (int i = 0; i < 10; i++)
{
examinfo[i] = reader[i].ToString();
}
reader.Close();
SelectSQL.Dispose();
fbt.Commit();
string questionlist = examinfo[3];
FbTransaction fbt1 = fb.BeginTransaction(); //SELECT CURRENT QUESTIONS
FbCommand SelectSQL1 = new FbCommand("SELECT * FROM question WHERE id IN (" + questionlist + ")", fb);
SelectSQL1.Transaction = fbt1;
FbDataReader reader1 = SelectSQL1.ExecuteReader();
while (reader1.Read())
{
bar = bar + reader1[0].ToString() + "|" + reader1[1].ToString() + "|" + reader1[2].ToString() + "|" + reader1[3].ToString() + "|" + reader1[4].ToString() + "|" + reader1[5].ToString() + "|" + reader1[6].ToString() + "|" + reader1[7].ToString() + "|" + reader1[8].ToString() + "\n";
}
reader1.Close();
SelectSQL1.Dispose();
fbt1.Commit();
fb.Close();
bar = bar.Substring(0, bar.Length - 1);
foo = bar.Split('\n');
string[][] qList = new string[foo.Length][];
for (int i = 0; i < foo.Length; i++)
{
qList[i] = foo[i].Split('|');
}
this.questionlist = qList;
int min = 0;
int sec = 0;
string[] time = examinfo[5].Split(':');
if (time.Count() > 1)
{
min = int.Parse(time[0]);
sec = int.Parse(time[1]);
}
ExamTimeLabel.Text = ExamTimeLabel.Text + min.ToString() + ":" + sec.ToString();
ResultLabel.Text = ResultLabel.Text + " " + calculate.Percent(examid) + "%";
UserNameLabel.Text = examinfo[6] + " " + examinfo[7] + " " + examinfo[8];
}
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if (checkBox1.Checked)
{
if (Current_Row == 0 || Current_Row == e.RowIndex)
{
if (fb.State != ConnectionState.Open)
{
method_connect_to_fb(Program.f1.connecting_path);
}
if (dataGridView1.RowCount - 1 == e.RowIndex)//добавление новой строки в БД
{
dataGridView1.AllowUserToAddRows = false;
checkBox1.Enabled = false;
need_to_end_new_line = true;
label3.Visible = true;
int reason_absence = -1;
label3.ForeColor = Color.Red;
label3.Text = "Необходимо завершить ввод/изменение причины отсутвия на рабочем месте";
for (int i = 0; i < dataGridView1.RowCount - 1; i++)//ограничение ввода в процессе заполненя новой стороки
{
dataGridView1.Rows[i].Cells[0].ReadOnly = true;
dataGridView1.Rows[i].Cells[1].ReadOnly = true;
dataGridView1.Rows[i].Cells[2].ReadOnly = true;
}
if (!Regex.IsMatch(Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[1].Value), pattern))//подсветка ячеек при неверном вводе
{
dataGridView1.Rows[e.RowIndex].Cells[1].Style.BackColor = Color.Red;
}
else
{
dataGridView1.Rows[e.RowIndex].Cells[1].Style.BackColor = Color.White;
}
if (!Regex.IsMatch(Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[2].Value), pattern))
{
dataGridView1.Rows[e.RowIndex].Cells[2].Style.BackColor = Color.Red;
}
else
{
dataGridView1.Rows[e.RowIndex].Cells[2].Style.BackColor = Color.White;
}
if (Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[0].Value) == "больничный")
{
reason_absence = 0;
}
if (Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[0].Value) == "отпуск")
{
reason_absence = 1;
}
if (Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[0].Value) == "командировка")
{
reason_absence = 2;
}
if (Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[0].Value) == "удаленная работа")
{
reason_absence = 3;
}
if (Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[0].Value) == "отгул")
{
reason_absence = 4;
}
if ((Regex.IsMatch(Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[1].Value), pattern) && Regex.IsMatch(Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[2].Value), pattern)) && reason_absence != (-1))
{
try
{
dataGridView1.Rows[e.RowIndex].Cells[2].Style.BackColor = Color.White;
FbCommand InsertSQL = new FbCommand("insert into deviation(deviation.peopleid,deviation.devfrom,deviation.devto,deviation.devtype) values('" + User_ID + "','" + dataGridView1.Rows[e.RowIndex].Cells[1].Value + "','" + dataGridView1.Rows[e.RowIndex].Cells[2].Value + "','" + reason_absence + "')", fb); //задаем запрос вовод данных
if (fb.State == ConnectionState.Open)
{
FbTransaction fbt = fb.BeginTransaction(); //необходимо проинициализить транзакцию для объекта InsertSQL
InsertSQL.Transaction = fbt;
int result = InsertSQL.ExecuteNonQuery();
//MessageBox.Show("Добавление причины отсутвия на рабочем месте выполнено");
fbt.Commit();
fbt.Dispose();
InsertSQL.Dispose();
need_to_end_new_line = false;
label3.Visible = false;
for (int i = 0; i < dataGridView1.RowCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].ReadOnly = false;
dataGridView1.Rows[i].Cells[1].ReadOnly = false;
dataGridView1.Rows[i].Cells[2].ReadOnly = false;
}
checkBox1.Enabled = true;
Program.f1.method_of_deviation(ref Program.f1.arr_of_deviation);
this.BeginInvoke(new MethodInvoker(() =>
{
method_DataGridDeviation(false, ref checkBox1, ref dataGridView1);//запуск асинхронного метода
}));
//method_DataGridDeviation(false, ref checkBox1, ref dataGridView1); // необходимо решить проблему с вылетом когда при заполнении выбирается другая ячейка
dataGridView1.AllowUserToAddRows = true;
Current_Row = 0;
}
}
catch (Exception r)
{
MessageBox.Show(r.Message, "Сообщение", MessageBoxButtons.OK);
}
}
}
else if (!need_to_end_new_line)//изменение текущей строки в БД
{
int reason_absence = -1;
bool can_run_query = false;
checkBox1.Enabled = false;
dataGridView1.AllowUserToAddRows = false;
label3.Visible = true;
label3.ForeColor = Color.Red;
label3.Text = "Необходимо завершить ввод/изменение причины отсутвия на рабочем месте";
for (int i = 0; i < dataGridView1.RowCount - 1; i++)//ограничение ввода в процессе заполненя новой стороки
{
dataGridView1.Rows[i].Cells[0].ReadOnly = true;
dataGridView1.Rows[i].Cells[1].ReadOnly = true;
dataGridView1.Rows[i].Cells[2].ReadOnly = true;
}
if (Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[0].Value) == "больничный")
{
reason_absence = 0;
can_run_query = true;
}
if (Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[0].Value) == "отпуск")
{
reason_absence = 1;
can_run_query = true;
}
if (Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[0].Value) == "командировка")
{
reason_absence = 2;
can_run_query = true;
}
if (Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[0].Value) == "удаленная работа")
{
reason_absence = 3;
can_run_query = true;
}
if (Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[0].Value) == "отгул")
{
reason_absence = 4;
can_run_query = true;
}
if ((Regex.IsMatch(Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[1].Value), pattern)) && can_run_query)
{
can_run_query = true;
dataGridView1.Rows[e.RowIndex].Cells[1].Style.BackColor = Color.White;
}
else
{
can_run_query = false;
dataGridView1.Rows[e.RowIndex].Cells[1].Style.BackColor = Color.Red;
}
if ((Regex.IsMatch(Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells[2].Value), pattern)) && can_run_query)
{
can_run_query = true;
dataGridView1.Rows[e.RowIndex].Cells[2].Style.BackColor = Color.White;
}
else
{
can_run_query = false;
dataGridView1.Rows[e.RowIndex].Cells[2].Style.BackColor = Color.Red;
}
if (can_run_query)
{
try
{
FbCommand InsertSQL = new FbCommand("update deviation set deviation.devfrom='" + dataGridView1.Rows[e.RowIndex].Cells[1].Value + "', deviation.devto='" + dataGridView1.Rows[e.RowIndex].Cells[2].Value + "', deviation.devtype='" + reason_absence + "'where deviation.deviationid='" + dataGridView1.Rows[e.RowIndex].Cells[3].Value + "'", fb); //задаем запрос на получение данных
if (fb.State == ConnectionState.Open)
{
FbTransaction fbt = fb.BeginTransaction(); //необходимо проинициализить транзакцию для объекта InsertSQL
InsertSQL.Transaction = fbt;
int result = InsertSQL.ExecuteNonQuery();
//MessageBox.Show("Изменение причины отсутвия на рабочем месте выполнено");
fbt.Commit();
fbt.Dispose();
InsertSQL.Dispose();
label3.Visible = false;
for (int i = 0; i < dataGridView1.RowCount - 1; i++)
{
dataGridView1.Rows[i].Cells[0].ReadOnly = false;
dataGridView1.Rows[i].Cells[1].ReadOnly = false;
dataGridView1.Rows[i].Cells[2].ReadOnly = false;
}
checkBox1.Enabled = true;
Program.f1.method_of_deviation(ref Program.f1.arr_of_deviation);
this.BeginInvoke(new MethodInvoker(() =>
{
method_DataGridDeviation(false, ref checkBox1, ref dataGridView1);// запуск асинхронного метода
}
));
//method_DataGridDeviation(false, ref checkBox1, ref dataGridView1);// необходимо решить проблему с вылетом когда при заполнении выбирается другая ячейка
dataGridView1.AllowUserToAddRows = true;
Current_Row = 0;
}
}
catch (Exception r)
{
MessageBox.Show(r.Message, "Сообщение", MessageBoxButtons.OK);
}
}
}
}
}
}
public IEnumerable <Faculty> ReadFaculty()
{
if (Open())
{
using (FbTransaction dbtran = conn.BeginTransaction())
{
using (FbCommand selectCommand = new FbCommand())
{
selectCommand.CommandText = "select * from faculty";
selectCommand.Connection = conn;
selectCommand.Transaction = dbtran;
FbDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
yield return(new Faculty
{
CodeOfFaculty = reader.GetInt32(0),
NameOfFaculty = reader.GetString(1)
});
}
}
dbtran.Commit();
}
}
}
/// <summary>
/// Starts the ordered execution of the SQL statements that are in <see cref="SqlStatements"/> collection.
/// </summary>
/// <param name="autoCommit">Specifies if the transaction should be committed after a DDL command execution</param>
public void Execute(bool autoCommit = true)
{
if ((_statements?.Count ?? 0) == 0)
{
throw new InvalidOperationException("There are no commands for execution.");
}
_shouldClose = false;
foreach (var statement in Statements)
{
if (!(statement.StatementType == SqlStatementType.Connect ||
statement.StatementType == SqlStatementType.CreateDatabase ||
statement.StatementType == SqlStatementType.Disconnect ||
statement.StatementType == SqlStatementType.DropDatabase ||
statement.StatementType == SqlStatementType.SetAutoDDL ||
statement.StatementType == SqlStatementType.SetDatabase ||
statement.StatementType == SqlStatementType.SetNames ||
statement.StatementType == SqlStatementType.SetSQLDialect))
{
ProvideCommand();
_sqlCommand.CommandText = statement.Text;
if (_sqlTransaction == null && !(statement.StatementType == SqlStatementType.Commit || statement.StatementType == SqlStatementType.Rollback))
{
_sqlTransaction = _sqlConnection.BeginTransaction();
}
_sqlCommand.Transaction = _sqlTransaction;
}
try
{
switch (statement.StatementType)
{
case SqlStatementType.AlterCharacterSet:
case SqlStatementType.AlterDatabase:
case SqlStatementType.AlterDomain:
case SqlStatementType.AlterException:
case SqlStatementType.AlterFunction:
case SqlStatementType.AlterIndex:
case SqlStatementType.AlterPackage:
case SqlStatementType.AlterProcedure:
case SqlStatementType.AlterRole:
case SqlStatementType.AlterSequence:
case SqlStatementType.AlterTable:
case SqlStatementType.AlterTrigger:
case SqlStatementType.AlterView:
case SqlStatementType.CommentOn:
case SqlStatementType.CreateCollation:
case SqlStatementType.CreateDomain:
case SqlStatementType.CreateException:
case SqlStatementType.CreateFunction:
case SqlStatementType.CreateGenerator:
case SqlStatementType.CreateIndex:
case SqlStatementType.CreatePackage:
case SqlStatementType.CreatePackageBody:
case SqlStatementType.CreateProcedure:
case SqlStatementType.CreateRole:
case SqlStatementType.CreateSequence:
case SqlStatementType.CreateShadow:
case SqlStatementType.CreateTable:
case SqlStatementType.CreateTrigger:
case SqlStatementType.CreateView:
case SqlStatementType.DeclareCursor:
case SqlStatementType.DeclareExternalFunction:
case SqlStatementType.DeclareFilter:
case SqlStatementType.DeclareStatement:
case SqlStatementType.DeclareTable:
case SqlStatementType.Delete:
case SqlStatementType.DropCollation:
case SqlStatementType.DropDomain:
case SqlStatementType.DropException:
case SqlStatementType.DropExternalFunction:
case SqlStatementType.DropFunction:
case SqlStatementType.DropFilter:
case SqlStatementType.DropGenerator:
case SqlStatementType.DropIndex:
case SqlStatementType.DropPackage:
case SqlStatementType.DropPackageBody:
case SqlStatementType.DropProcedure:
case SqlStatementType.DropSequence:
case SqlStatementType.DropRole:
case SqlStatementType.DropShadow:
case SqlStatementType.DropTable:
case SqlStatementType.DropTrigger:
case SqlStatementType.DropView:
case SqlStatementType.EventInit:
case SqlStatementType.EventWait:
case SqlStatementType.Execute:
case SqlStatementType.ExecuteImmediate:
case SqlStatementType.ExecuteProcedure:
case SqlStatementType.Grant:
case SqlStatementType.Insert:
case SqlStatementType.InsertCursor:
case SqlStatementType.Merge:
case SqlStatementType.Open:
case SqlStatementType.Prepare:
case SqlStatementType.Revoke:
case SqlStatementType.RecreateFunction:
case SqlStatementType.RecreatePackage:
case SqlStatementType.RecreatePackageBody:
case SqlStatementType.RecreateProcedure:
case SqlStatementType.RecreateTable:
case SqlStatementType.RecreateTrigger:
case SqlStatementType.RecreateView:
case SqlStatementType.SetGenerator:
case SqlStatementType.Update:
case SqlStatementType.Whenever:
OnCommandExecuting(_sqlCommand, statement.StatementType);
var rowsAffected = ExecuteCommand(autoCommit);
_requiresNewConnection = false;
OnCommandExecuted(null, statement.Text, statement.StatementType, rowsAffected);
break;
case SqlStatementType.ExecuteBlock:
case SqlStatementType.Select:
#warning Who's disposing this?
ProvideCommand().CommandText = statement.Text;
OnCommandExecuting(_sqlCommand, statement.StatementType);
using (var dataReader = _sqlCommand.ExecuteReader())
{
_requiresNewConnection = false;
OnCommandExecuted(dataReader, statement.Text, statement.StatementType, -1);
}
break;
case SqlStatementType.Commit:
OnCommandExecuting(null, statement.StatementType);
CommitTransaction();
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.Rollback:
OnCommandExecuting(null, statement.StatementType);
RollbackTransaction();
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.CreateDatabase:
OnCommandExecuting(null, statement.StatementType);
CreateDatabase(statement.CleanText);
_requiresNewConnection = false;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.DropDatabase:
OnCommandExecuting(null, statement.StatementType);
FbConnection.DropDatabase(_connectionString.ToString());
_requiresNewConnection = true;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.Connect:
OnCommandExecuting(null, statement.StatementType);
ConnectToDatabase(statement.CleanText);
_requiresNewConnection = false;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.Disconnect:
OnCommandExecuting(null, statement.StatementType);
_sqlConnection.Close();
FbConnection.ClearPool(_sqlConnection);
_requiresNewConnection = false;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.SetAutoDDL:
OnCommandExecuting(null, statement.StatementType);
SetAutoDdl(statement.CleanText, ref autoCommit);
_requiresNewConnection = false;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.SetNames:
OnCommandExecuting(null, statement.StatementType);
SetNames(statement.CleanText);
_requiresNewConnection = true;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.SetSQLDialect:
OnCommandExecuting(null, statement.StatementType);
SetSqlDialect(statement.CleanText);
_requiresNewConnection = true;
OnCommandExecuted(null, statement.Text, statement.StatementType, -1);
break;
case SqlStatementType.Fetch:
case SqlStatementType.Describe:
break;
case SqlStatementType.SetDatabase:
case SqlStatementType.SetStatistics:
case SqlStatementType.SetTransaction:
case SqlStatementType.ShowSQLDialect:
throw new NotImplementedException();
}
}
catch (Exception ex)
{
RollbackTransaction();
CloseConnection();
throw new FbException(string.Format("An exception was thrown when executing command: {1}.{0}Batch execution aborted.{0}The returned message was: {2}.",
Environment.NewLine,
statement.Text,
ex.Message),
ex);
}
}
CommitTransaction();
CloseConnection();
}
/// <summary>
/// Executes SQL Update Scripts
/// </summary>
/// <param name="SQLFile">Script File to process</param>
/// <returns>true if succesful, otherwise false</returns>
public bool UpdateDatabase(string connectionString, string sqlFile, int newVersion, ref bool tableUpdated)
{
bool Result = false;
if (tableUpdated)
{
tableUpdated = true;
}
else
{
tableUpdated = false;
}
FbConnectionStringBuilder cb = new FbConnectionStringBuilder(connectionString);
cb.Pooling = false;
//connect to local DB
FbConnection database = new FbConnection(cb.ToString());
try
{
database.Open();
FbScript script = new FbScript(Shared.Utilities.FileRead(sqlFile, false));
script.Parse();
int idx = 0;
foreach (FbStatement cmd in script.Results)
{
try
{
if (cmd.Text == "COMMIT" || cmd.Text == "ROLLBACK")
{
continue;
}
if (!tableUpdated &&
(
cmd.Text.ToUpper().Contains("ALTER TABLE") || // any table changes
cmd.Text.ToUpper().Contains("CREATE TABLE") || // any new tables
cmd.Text.ToUpper().Contains("REPLICATE$TABLES") // anything relating to replicate tables
)
)
{
tableUpdated = true;
}
if (OnUpdateDatabase != null)
{
OnUpdateDatabase(null, new FileProgressArgs(String.Empty, script.Results.Count, idx));
idx++;
}
FbBatchExecution fbe = new FbBatchExecution(database);
try
{
fbe.Statements.Add(cmd);
fbe.Execute();
fbe.Statements.Clear();
}
finally
{
fbe = null;
}
}
catch (Exception err)
{
if ((!err.Message.Contains("unsuccessful metadata update") &&
!err.Message.Contains("does not exist")) &&
!err.Message.ToUpper().Contains("ATTEMPT TO STORE DUPLICATE VALUE") &&
!err.Message.ToUpper().Contains("ALREADY EXISTS") &&
!err.Message.Contains("violation of PRIMARY or UNIQUE") &&
!err.Message.Contains("violation of FOREIGN KEY constraint") &&
!err.Message.Contains("GRANT USAGE ON "))
{
Shared.EventLog.Add(err, cmd.Text);
throw;
}
}
}
if (newVersion > 0)
{
//update the version
string SQL = String.Format("UPDATE REPLICATE$OPTIONS SET DATABASE_VERSION = {0}", newVersion);
FbTransaction tran = database.BeginTransaction();
try
{
FbCommand cmdUpdate = new FbCommand(SQL, database, tran);
try
{
cmdUpdate.ExecuteNonQuery();
}
finally
{
cmdUpdate.Dispose();
cmdUpdate = null;
}
}
finally
{
tran.Commit();
tran.Dispose();
}
}
Result = true;
}
catch (Exception e)
{
Shared.EventLog.Add(e);
Result = false;
}
finally
{
database.Close();
database.Dispose();
database = null;
}
return(Result);
}
public static ITransactionHandle BeginTransaction(FirebirdConnectionPool connectionPool, IsolationLevel level)
{
FbConnection connection = connectionPool.AcquireConnection();
return(new FirebirdTransactionHandle(connectionPool, connection, connection.BeginTransaction(level)));
}
/*
* Print reports selected via selection of the reports family for every result
*
* */
private void PrintFamily()
{
FbConnection fb_con = new FbConnection(ConnectionString);
if (fb_con.State == System.Data.ConnectionState.Closed)
{
fb_con.Open();
}
FbTransaction fb_trans = null;
fb_trans = fb_con.BeginTransaction();
FbCommand SelectReports = new FbCommand();
FbDataReader readerReports = null;
int IdFam = GetFamilyd();
int FamIndex = GetFamilyIndex(IdFam);
int IdReport = 0;
ReportBuilder rep_builder = null;
try
{
try
{
// Prepare the query, receiving the report's ID
SelectReports.CommandText = "select first 1 id from get_family_result_report(@idr, @famid)";
SelectReports.Connection = fb_con;
SelectReports.Transaction = fb_trans;
// ... and its parameters
FbParameter par_res = new FbParameter("@idr", FbDbType.Integer);
FbParameter par_fam = new FbParameter("@famid", FbDbType.Integer);
par_fam.Value = IdFam;
// For every result we'll print the report, that is corresponding the selected family
for (int i = 0; i < results.Count; i++)
{
// ... prepare query's parameters
SelectReports.Parameters.Clear();
par_res.Value = results[i].IdRes;
SelectReports.Parameters.Add(par_res);
SelectReports.Parameters.Add(par_fam);
// ... execute query and obtain the report's ID
readerReports = SelectReports.ExecuteReader();
if (readerReports.Read())
{
IdReport = (int)readerReports[0];
for (int copies = Convert.ToInt32(Copies.Value); copies > 0; copies--)
{
// Create a new report builder
// ConnectionString - parameters to connect to the database
// results[i].IdRes - the ID of the current result
// ReqId - the current value of the Requiered ID (ID of the session)
// ShowPrint == false (the 4th parameter) - in this case the builder will print the report, but don't show the report's preview
// ShowFrDialog (the 5th parameter) - the dialog for the selection of the fractions will be shown, if just ...
// ... the selected family requires to select the fractions and groups before the report's building
// ... this is the very 1st reports ought to be printed
// ... this is the very 1st cpoy of the report ought to be printed
// NeedClear (the 6th parameter) influes at the process of the database cleaning
// (in part of the temporary data, which were used during the report creation)
// It'll be settled to the TRUE, if ...
// ... it is the very last report in the queue of the reports ought to be printed
// ... it is the last copy of the report (because of the parameters of the cycle the last copy will have the number 1)
rep_builder = new ReportBuilder(ConnectionString, results[i].IdRes, ReqId, false,
families[FamIndex].FrSelect == 1 && i == 0 && copies == Convert.ToInt32(Copies.Value),
i == results.Count - 1 && copies == 1);
rep_builder.BuildReport(IdReport);
}
}
readerReports.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Error Info = " + ex.ToString());
fb_trans.Rollback();
}
}
finally
{
if (readerReports != null)
{
readerReports.Close();
}
if (fb_trans != null)
{
fb_trans.Dispose();
}
if (SelectReports != null)
{
SelectReports.Dispose();
}
if (fb_con != null && fb_con.State == System.Data.ConnectionState.Open)
{
fb_con.Close();
}
}
}
public string GenerateTriggerRemoveScript(string connectionString, bool generateOnly,
DatabaseRemoteUpdate remoteUpdate)
{
// get temp file for triggers
string Result = Path.GetTempFileName();
try
{
_xmlHashUpdates.Clear();
//connect to local DB
FbConnection db = new FbConnection(connectionString);
db.Open();
try
{
FbTransaction tran = db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
StreamWriter updateFile = new StreamWriter(Result, false);
try
{
// have any tables been removed from the list since the last time this was run?
string SQL = "SELECT TRIM(a.RDB$TRIGGER_NAME) " +
"FROM RDB$TRIGGERS a WHERE ((TRIM(a.RDB$TRIGGER_NAME) LIKE 'REPLICATE$%'));";
FbDataReader rdr = null;
FbCommand cmd = new FbCommand(SQL, db, tran);
try
{
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
updateFile.WriteLine(String.Format("DROP TRIGGER {0};", rdr.GetString(0).Trim()));
string hashDatabase = "D" + Shared.Utilities.HashStringMD5(GetDatabaseName(db));
string hashCode = "C";
string triggerHash = "T" + Shared.Utilities.HashStringMD5(
rdr.GetString(0).Trim().Replace("REPLICATE$", ""));
_xmlHashUpdates.Add(String.Format("{0}${1}${2}", hashDatabase, triggerHash, hashCode));
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
if (!generateOnly)
{
foreach (string update in _xmlHashUpdates)
{
string[] parts = update.Split('$');
Shared.XML.SetXMLValue(parts[0], parts[1], parts[2]);
}
}
SQL = "SELECT TRIM(a.RDB$RELATION_NAME) FROM RDB$RELATION_FIELDS a " +
"WHERE a.RDB$FIELD_NAME = 'REPLICATE$HASH'";
cmd = new FbCommand(SQL, db, tran);
try
{
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
updateFile.WriteLine(String.Format("ALTER TABLE {0} DROP REPLICATE$HASH;", rdr.GetString(0)));
}
}
finally
{
CloseAndDispose(ref cmd, ref rdr);
}
}
finally
{
updateFile.Flush();
updateFile.Close();
updateFile = null;
}
if (generateOnly)
{
return(Result);
}
bool tableUpdated = false;
if (remoteUpdate.UpdateDatabase(connectionString, Result, -1, ref tableUpdated))
{
File.Delete(Result);
}
else
{
throw new Exception("Error creating replication triggers");
}
}
finally
{
tran.Rollback();
tran.Dispose();
}
}
finally
{
db.Close();
db.Dispose();
db = null;
}
}
catch (Exception e)
{
Shared.EventLog.Add(e);
throw;
}
return(Result);
}
private void FamilySelectForm_Shown(object sender, EventArgs e)
{
FbConnection fb_con = new FbConnection(ConnectionString);
if (fb_con.State == System.Data.ConnectionState.Closed)
{
fb_con.Open();
}
FbTransaction fb_trans = null;
fb_trans = fb_con.BeginTransaction();
FbCommand SelectFamilies = new FbCommand();
FbDataReader readerFamilies = null;
try
{
try
{
/* Select full list of the reports families */
SelectFamilies.CommandText = "select id, pos, name, displayname, visible, frselect from reportfamily where visible = 1 order by pos";
SelectFamilies.Connection = fb_con;
SelectFamilies.Transaction = fb_trans;
readerFamilies = SelectFamilies.ExecuteReader();
// ... Create a list of the families
while (readerFamilies.Read())
{
families.Add(new ReportFamily((int)readerFamilies["id"], (int)readerFamilies["pos"], (string)readerFamilies["name"],
(string)readerFamilies["displayname"], (int)readerFamilies["visible"], (int)readerFamilies["frselect"], 0));
}
readerFamilies.Close();
/* Select families for every report */
SelectFamilies.CommandText = "select famid from get_family_result(@idr)";
SelectFamilies.Connection = fb_con;
SelectFamilies.Transaction = fb_trans;
// ... create new parameter
FbParameter par_resid = new FbParameter("@idr", FbDbType.Integer);
// ... for every result, which was passed into the form
for (int i = 0; i < results.Count; i++)
{
// ... clear the parameters set of the query
SelectFamilies.Parameters.Clear();
// ... set new value of the parameter
par_resid.Value = results[i].IdRes;
// ... add current parameter to the query
SelectFamilies.Parameters.Add(par_resid);
readerFamilies = SelectFamilies.ExecuteReader();
// ... calculate numver of the families, we'd been able to build for given result
while (readerFamilies.Read())
{
int fam_id = GetFamilyIndex(families, (int)readerFamilies[0]);
if (fam_id > -1)
{
families[fam_id].RepCount++;
}
}
readerFamilies.Close();
}
fb_trans.Commit();
// Before showing of the families list, we need to clear the families list
// and delete families with RepCount == 0
for (int i = 0; i < families.Count;)
{
if (families[i].RepCount == 0)
{
families.RemoveAt(i); // if RepCount == 0, then we remove the Family
}
else
{
i++; // else we should take the next element of the families list
}
}
// And now we can show selected families
this.groupBox1.AutoSize = true;
this.groupBox1.Controls.Clear();
for (int i = 0; i < families.Count; i++)
{
RadioButton rb = new RadioButton();
rb.Text = families[i].DispName;
rb.Name = "RadioButton" + i.ToString();
rb.Location = new Point(5, 30 * (i + 1));
rb.AutoSize = true;
// checkedListBox1.Items.Add(readerReports.GetString(1), false);
groupBox1.Controls.Add(rb);
}
}
catch (Exception ex)
{
MessageBox.Show("Error Info = " + ex.ToString());
fb_trans.Rollback();
}
}
finally
{
if (readerFamilies != null)
{
readerFamilies.Close();
}
if (fb_trans != null)
{
fb_trans.Dispose();
}
if (SelectFamilies != null)
{
SelectFamilies.Dispose();
}
if (fb_con != null && fb_con.State == System.Data.ConnectionState.Open)
{
fb_con.Close();
}
}
}
//[Ignore]
public void GlobalTransactionTest()
{
using (TransactionScope t = new TransactionScope(TransactionScopeOption.Required))
{
using (FbConnection connection = new FbConnection(@"Database=C:\Work\Database\maptest.fdb;DataSource=localhost; Port=3050; Dialect=3; Charset=WIN1251;User=SYSDBA;Password=masterkey"))
{
connection.Open();
//К сожалению, Firebird'овцы не поддерживают интеграцию с TransactionScope
//поэтому придется использоватья явное управление транзакцией
//connection.EnlistTransaction(Transaction.Current);
onecserviceClient client = new onecserviceClient();
try
{
//Первая база 1С через веб-сервис
ResultSet resultSet = client.ExecuteScript(
@"C:\Work\OneCService\Base\First",
"", "",
"сотр = Справочники.Сотрудники.НайтиПоКоду(10);\n" +
"Если Не сотр.Пустая() Тогда сотр.ПолучитьОбъект().Удалить(); КонецЕсли;" +
"сотр = Справочники.Сотрудники.СоздатьЭлемент();\n" +
"сотр.Код = 10; " +
"сотр.Наименование = \"Сиськин\"; " +
"сотр.Записать();"
);
Console.WriteLine("Error: " + resultSet.Error);
Assert.AreEqual(resultSet.Error, "");
//Вторая база 1С через веб-сервис
resultSet = client.ExecuteScript(
@"C:\Work\OneCService\Base\Second",
"", "",
"сотр = Справочники.Сотрудники.НайтиПоКоду(10);\n" +
"Если Не сотр.Пустая() Тогда сотр.ПолучитьОбъект().Удалить(); КонецЕсли;" +
"сотр = Справочники.Сотрудники.СоздатьЭлемент();\n" +
"сотр.Код = 10; " +
"сотр.Наименование = \"Сиськин\"; " +
"сотр.Записать();"
);
Console.WriteLine("Error: " + resultSet.Error);
Assert.AreEqual(resultSet.Error, "");
//СУБД через ADO.NET
DbCommand cmd = (DbCommand)connection.CreateCommand();
cmd.Transaction = connection.BeginTransaction();
Assert.IsNotNull(cmd.Transaction);
cmd.CommandText = "DELETE FROM MARKERS";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO MARKERS (CODE, TITLE, X, Y) VALUES (1, 'ЙЦУКЕН', 10, 10)";
cmd.ExecuteNonQuery();
//К сожалению, Firebird'овцы не поддерживают интеграцию с TransactionScope
//поэтому придется использоватья явное управление транзакцией
cmd.Transaction.Commit();
//Завершение транзакции
t.Complete();
}
finally
{
client.Close();
}
} //using connection
} //using TransactionScope
}
/// <summary>
/// Создает таблицу для хранения данных об обработанных файлах в случае её отсутствия
/// </summary>
public static void CreateTableOperationInfo()
{
if (!StartFirebirdService())
{
throw new Exception("Не удалось запустить службу Firebird");
}
string connStr = @"SELECT 1 FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME = 'FILES'";
object result;
using (FbConnection connection = new FbConnection(ConnectionString)) {
connection.Open();
using (FbCommand command = new FbCommand(connStr, connection)) {
using (FbTransaction transaction = connection.BeginTransaction()) {
command.Transaction = transaction;
result = command.ExecuteScalar();
transaction.Rollback();
}
}
}
if (result != null)
{
return;
}
connStr = "CREATE TABLE FILES " +
@"(ID INTEGER NOT NULL,
FILENAME VARCHAR(256) NOT NULL,
FILETYPE VARCHAR(256) NOT NULL,
OPERATIONDATE TIMESTAMP NOT NULL
);";
using (FbConnection connection = new FbConnection(ConnectionString)) {
connection.Open();
using (FbCommand command = new FbCommand(connStr, connection)) {
using (FbTransaction transaction = connection.BeginTransaction()) {
command.Transaction = transaction;
command.ExecuteNonQuery();
transaction.Commit();
}
}
connStr = "ALTER TABLE FILES ADD CONSTRAINT PK_FILES PRIMARY KEY (ID);";
using (FbCommand command = new FbCommand(connStr, connection)) {
using (FbTransaction transaction = connection.BeginTransaction()) {
command.Transaction = transaction;
command.ExecuteNonQuery();
transaction.Commit();
}
}
connStr = "CREATE GENERATOR GEN_FILES_ID;";
using (FbCommand command = new FbCommand(connStr, connection)) {
using (FbTransaction transaction = connection.BeginTransaction()) {
command.Transaction = transaction;
command.ExecuteNonQuery();
transaction.Commit();
}
}
connStr = "CREATE OR ALTER TRIGGER FILES_BI FOR FILES " +
"ACTIVE BEFORE INSERT POSITION 0 " +
"as " +
"begin " +
"if (new.id is null) then " +
"new.id = gen_id(gen_files_id,1); " +
"end";
using (FbCommand command = new FbCommand(connStr, connection)) {
using (FbTransaction transaction = connection.BeginTransaction()) {
command.Transaction = transaction;
command.ExecuteNonQuery();
transaction.Commit();
}
}
}
}