public void DisposeTransaction()
{
if (_activeTransaction != null && !IsEnlisted)
{
_activeTransaction.Dispose();
_activeTransaction = null;
}
}
public static void CreateDataBase()
{
//First run
if (IsServer && !IsDbAlreadyCreated)
{
if (File.Exists(DbPath))
{
FbConnection.DropDatabase(GetConnectionString());
}
FbConnection.CreateDatabase(GetConnectionString());
FbConnection connection = GetConnection();
Open();
FbTransaction mtransaction = connection.BeginTransaction(IsolationLevel.Serializable);
FbCommand command = new FbCommand("create table MethodLogs (Token varchar(50), MethodName varchar(200), Status int, " +
"TimeTaken bigint, Datetime timestamp, EndDatetime timestamp, Error BLOB SUB_TYPE TEXT)", connection, mtransaction);
command.ExecuteNonQuery();
mtransaction.Commit();
command.Dispose(); // Thus!
mtransaction.Dispose();
Close();
IsDbAlreadyCreated = !IsDbAlreadyCreated;
}
//subsequent runs
//do not recreate db, only refresh the database (drop and recreate MethodLogs table)
else if (IsDbAlreadyCreated)
{
Close();
FbConnection connection = GetConnection();
Open();
FbTransaction mtransaction = connection.BeginTransaction(IsolationLevel.Serializable);
FbCommand command = new FbCommand("drop table MethodLogs", connection, mtransaction);
command.ExecuteNonQuery();
mtransaction.Commit();
command.Dispose(); // Thus!
mtransaction.Dispose();
mtransaction = connection.BeginTransaction(IsolationLevel.Serializable);
command = new FbCommand("create table MethodLogs (Token varchar(50), MethodName varchar(200), Status int, " +
"TimeTaken bigint, Datetime timestamp, EndDatetime timestamp, Error BLOB SUB_TYPE TEXT)", connection, mtransaction);
command.ExecuteNonQuery();
mtransaction.Commit();
command.Dispose(); // Thus!
mtransaction.Dispose();
Close();
}
}
//-----------------------------------------------------------------------------------------------------
public override void Dispose()
{
if (tr != null)
{
tr.Dispose();
tr = null;
}
if (db != null)
{
db.Dispose();
db = null;
}
}
private async Task CommitTransaction(AsyncWrappingCommonArgs async)
{
if (_sqlTransaction != null)
{
await _sqlTransaction.CommitImpl(async).ConfigureAwait(false);
#if NET48 || NETSTANDARD2_0
_sqlTransaction.Dispose();
#else
await async.AsyncSyncCallNoCancellation(_sqlTransaction.DisposeAsync, _sqlTransaction.Dispose).ConfigureAwait(false);
#endif
_sqlTransaction = null;
}
}
private void DisposeAll()
{
//Prevent Dispos if have transaction
if (_tran != null && _tran.Connection != null)
{
return;
}
if (_tran != null)
{
_tran.Dispose();
_tran = null;
}
if (_com != null)
{
_com.Dispose();
_com = null;
}
if (_con != null)
{
_con.Dispose();
_con = null;
}
if (_adapter != null)
{
_adapter.Dispose();
_adapter = null;
}
}
/// <summary>
/// Insert a range of rows
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnNames"></param>
/// <param name="values"></param>
/// <returns></returns>
public int InsertRows(string tableName, List <string> columnNames, List <object[]> values)
{
// lock (lockThis)
{
FbTransaction myTransaction = fbDBConnection.BeginTransaction();
int index = 0;
try
{
// Create an insert query
string sql = CreateInsertSQL(tableName, columnNames);
for (int rowIndex = 0; rowIndex < values.Count; rowIndex++)
{
index = rowIndex;
BindParametersAndRunQuery(myTransaction, sql, values[rowIndex]);
}
myTransaction.Commit();
}
catch (Exception ex)
{
throw new FirebirdException("Exception " + ex.Message + "\r\nCannot insert row for " + tableName + " in InsertRows():" + String.Join(", ", values[index].ToString()).ToArray());
}
finally
{
myTransaction.Dispose();
}
}
return(0);
}
public virtual void TearDown()
{
string cs = BuildConnectionString(_fbServerType);
if (_withTransaction)
{
try
{
if (!_transaction.IsUpdated)
{
_transaction.Commit();
}
}
catch
{
}
try
{
_transaction.Dispose();
}
catch
{
}
}
if (_connection != null)
{
_connection.Dispose();
}
DeleteAllData(cs);
FbConnection.ClearAllPools();
}
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_);
}
public Boolean delete(DateTime date1, string prodavec)
{
try
{
check deletingCheck = this.Where(x => x.date == date1 && x.prodavec == prodavec).FirstOrDefault();
if (deletingCheck == null)
{
return(false);
}
FbConnection fb = new FbConnection(connection.connectionString());
fb.Open();
FbTransaction fbt = fb.BeginTransaction(); //стартуем транзакцию; стартовать транзакцию можно только для открытой базы (т.е. мутод Open() уже был вызван ранее, иначе ошибка)
FbCommand SelectSQL = new FbCommand();
SelectSQL.CommandText = "update shopheads set closed=1 where saledate=@datetime and saler=@prodavec";
SelectSQL.Parameters.Add("@datetime", date1);
SelectSQL.Parameters.Add("@prodavec", deletingCheck.prodavecCode);
SelectSQL.Connection = fb;
SelectSQL.Transaction = fbt;
SelectSQL.ExecuteNonQuery();
fbt.Commit();
fb.Close();
SelectSQL.Dispose(); //в документации написано, что ОЧЕНЬ рекомендуется убивать объекты этого типа, если они больше не нужны
fbt.Dispose();
fb.Dispose();
this.Remove(deletingCheck);
return(true);
}
catch (Exception e)
{
return(false);
}
}
public void Dispose()
{
_conexao.Dispose();
if (_inTransacao)
{
_transacao.Dispose();
}
}
public void get()
{
try
{
FbConnection fb = new FbConnection(connection.connectionString());
fb.Open();
FbTransaction fbt = fb.BeginTransaction(); //стартуем транзакцию; стартовать транзакцию можно только для открытой базы (т.е. мутод Open() уже был вызван ранее, иначе ошибка)
FbCommand SelectSQL = new FbCommand();
SelectSQL.CommandText = "select a.saledate,c.fullname,d.shortname,coalesce((select sum(b.price * b.quanshop) as summ from shoplog b where b.datatime = a.saledate and b.staff_id = a.saler group by datatime, staff_id),0) as summ, a.saler,a.closed,c.inn,(c.fam || ' ' || c.im || ' ' || c.otch ) as fn FROM shopheads a join staff c on c.id = a.saler left join pokupat d on a.pokupatid = d.pokupatcode where a.saledate > @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 d1 = reader.GetDateTime(0);
var d2 = reader.GetString(1);
var d3 = reader.GetString(2);
var d4 = reader.GetDouble(3);
var d5 = reader.GetInt16(4);
var d6 = reader.GetString(6);
var d7 = reader.GetString(7);
var nch = new check(d1, d2, d3, d4, d5, d6, d7);
var j = reader.GetInt16(5);
if (reader.GetInt16(5) == 0)
{
if (this.Where(x => x.date == nch.date && x.prodavec == nch.prodavec).Count() == 0)
{
this.Add(nch);
}
}
else
{
var dch = this.Where(x => x.date == nch.date && x.prodavec == nch.prodavec).FirstOrDefault();
if (dch != null)
{
this.Remove(dch);
}
}
}
}
finally
{
//всегда необходимо вызывать метод Close(), когда чтение данных завершено
reader.Close();
fbt.Commit();
fb.Close(); //закрываем соединение, т.к. оно нам больше не нужно
}
SelectSQL.Dispose(); //в документации написано, что ОЧЕНЬ рекомендуется убивать объекты этого типа, если они больше не нужны
fbt.Dispose();
fb.Dispose();
}
catch (Exception e) { }
}
public List <DbDataRecord> FbQuery(string sql)
{
var cnn = FbContext.getConnection(0);
FbTransaction tran = cnn.BeginTransaction();
FbCommand fbCmd = new FbCommand();
fbCmd.CommandText = sql;
// myCommand.CommandText ="UPDATE TEST_TABLE_01 SET CLOB_FIELD = @CLOB_FIELD WHERE INT_FIELD = @INT_FIELD";
fbCmd.Connection = cnn;
fbCmd.Transaction = tran;
//myCommand.Parameters.Add("@INT_FIELD", FbType.Integer, "INT_FIELD");
// myCommand.Parameters.Add("@CLOB_FIELD", FbType.Text, "CLOB_FIELD");
//myCommand.Parameters[0].Value = 1;
//myCommand.Parameters[1].Value = GetFileContents(@"GDS.CS");
// Execute
var result = fbCmd.ExecuteReader();
var rows = new List <DbDataRecord>();
foreach (var record in result)
{
var values = ((DbDataRecord)record);
rows.Add(values);
// Console.WriteLine(values["clicodigo"] + " / " + values["clinome"]);
}
/*
* using (var reader = fbCmd.ExecuteReader())
* {
* var rows = new List<object[]>();
* while (reader.Read())
* {
* var columns = new object[reader.FieldCount];
* reader.GetValues(columns);
* rows.Add(columns);
* }
* return rows;
* }
*/
/*
* foreach (var item in result)
* {
* var values = ((DbDataRecord)item);
* Console.WriteLine(values["clicodigo"] + " / " + values["clinome"]);
* }
*/
// Commit changes
tran.Commit();
// Free command resources in Firebird Server
tran.Dispose();
// Close connection
//cnn.Close();
return(rows); // DbDataRecord in FbDataReader
}
// internal void DisposeReader()
// {
// if (_activeReader != null)
// {
//#if NET48 || NETSTANDARD2_0
// _activeReader.Dispose();
//#else
// _activeReader.Dispose();
//#endif
// _activeReader = null;
// }
// }
// internal async Task DisposeReaderAsync(CancellationToken cancellationToken = default)
// {
// if (_activeReader != null)
// {
//#if NET48 || NETSTANDARD2_0
// _activeReader.Dispose();
// await Task.CompletedTask.ConfigureAwait(false);
//#else
// await _activeReader.DisposeAsync().ConfigureAwait(false);
//#endif
// _activeReader = null;
// }
// }
//internal DbValue[] Fetch()
//{
// if (_statement != null)
// {
// try
// {
// return _statement.Fetch();
// }
// catch (IscException ex)
// {
// throw FbException.Create(ex);
// }
// }
// return null;
//}
//internal async Task<DbValue[]> FetchAsync(CancellationToken cancellationToken = default)
//{
// if (_statement != null)
// {
// try
// {
// return await _statement.FetchAsync(cancellationToken).ConfigureAwait(false);
// }
// catch (IscException ex)
// {
// throw FbException.Create(ex);
// }
// }
// return null;
//}
//internal Descriptor GetFieldsDescriptor()
//{
// if (_statement != null)
// {
// return _statement.Fields;
// }
// return null;
//}
//internal void SetOutputParameters()
//{
// SetOutputParameters(null);
//}
//internal Task SetOutputParametersAsync(CancellationToken cancellationToken = default)
//{
// return SetOutputParametersAsync(null, cancellationToken);
//}
//internal void SetOutputParameters(DbValue[] outputParameterValues)
//{
// if (Parameters.Count > 0 && _statement != null)
// {
// if (_statement != null &&
// _statement.StatementType == DbStatementType.StoredProcedure)
// {
// var values = outputParameterValues;
// if (outputParameterValues == null)
// {
// values = _statement.GetOutputParameters();
// }
// if (values != null && values.Length > 0)
// {
// var i = 0;
// foreach (FbParameter parameter in Parameters)
// {
// if (parameter.Direction == ParameterDirection.Output ||
// parameter.Direction == ParameterDirection.InputOutput ||
// parameter.Direction == ParameterDirection.ReturnValue)
// {
// parameter.Value = values[i].GetValue();
// i++;
// }
// }
// }
// }
// }
//}
//internal async Task SetOutputParametersAsync(DbValue[] outputParameterValues, CancellationToken cancellationToken = default)
//{
// if (Parameters.Count > 0 && _statement != null)
// {
// if (_statement != null &&
// _statement.StatementType == DbStatementType.StoredProcedure)
// {
// var values = outputParameterValues;
// if (outputParameterValues == null)
// {
// values = _statement.GetOutputParameters();
// }
// if (values != null && values.Length > 0)
// {
// var i = 0;
// foreach (FbParameter parameter in Parameters)
// {
// if (parameter.Direction == ParameterDirection.Output ||
// parameter.Direction == ParameterDirection.InputOutput ||
// parameter.Direction == ParameterDirection.ReturnValue)
// {
// parameter.Value = await values[i].GetValueAsync(cancellationToken).ConfigureAwait(false);
// i++;
// }
// }
// }
// }
// }
//}
internal void CommitImplicitTransaction()
{
if (HasImplicitTransaction && _transaction != null && _transaction.Transaction != null)
{
try
{
_transaction.Commit();
}
catch
{
RollbackImplicitTransaction();
throw;
}
finally
{
if (_transaction != null)
{
#if NET48 || NETSTANDARD2_0
_transaction.Dispose();
#else
_transaction.Dispose();
#endif
_transaction = null;
_implicitTransaction = false;
}
if (_statement != null)
{
_statement.Transaction = null;
}
}
}
}
public override bool CommitTran()
{
if (Transaction != null)
{
try
{
Transaction.Commit();
Transaction.Dispose();
Transaction = null;
return(true);
}
catch (Exception ex)
{
Korisno.LogError("Can't commit transaction.", ex);
return(false);
}
}
else
{
return(false);
}
}
public bool CommitTran()
{
try
{
if (Transaction != null)
{
Transaction.Commit();
Transaction.Dispose();
Transaction = null;
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
izuzeci.Add(ex);
Korisno.LogujGresku("Nisam uspeo da komitujem transakciju.", ex);
return(false);
}
}
/// <summary>Executes a query that returns no results</summary>
/// <param name="query">SQL query to execute</param>
public void ExecuteNonQuery(string query)
{
if (!IsOpen)
{
fbDBConnection.Open();
}
if (IsOpen)
{
var transactionOptions = new FbTransactionOptions()
{
TransactionBehavior = FbTransactionBehavior.Concurrency |
FbTransactionBehavior.Wait |
FbTransactionBehavior.NoAutoUndo
};
FbTransaction transaction = fbDBConnection.BeginTransaction(transactionOptions);
query = AdjustQuotedFields(query);
FbCommand myCmd = new FbCommand(query, fbDBConnection, transaction);
myCmd.CommandType = CommandType.Text;
try
{
myCmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Dispose();
// this.CloseDatabase();
throw new FirebirdException("Cannot execute the SQL statement\r\n " + query + "\r\n" + ex.Message);
}
finally
{
if (myCmd != null)
{
// transaction.Dispose();
myCmd.Dispose();
myCmd = null;
}
}
}
else
{
throw new FirebirdException("Firebird database is not open.");
}
}
// Atualiza data do último arquivo validado
private Boolean AtualizarDataUltimoArquivo(string dataValidacao)
{
string mSQL;
string strReplace = (dataValidacao).Replace("/", "");
DateTime date1 = new DateTime(Convert.ToInt32(strReplace.Substring(4, 4)), Convert.ToInt32(strReplace.Substring(2, 2)), Convert.ToInt32(strReplace.Substring(0, 2)), 0, 0, 0);
string dataConvertida = date1.ToString(CultureInfo.CreateSpecificCulture("pt-BR"));
using (FbConnection conexaoFireBird = daoFireBird.getInstancia().getConexao())
{
conexaoFireBird.Open();
try
{
transacao = conexaoFireBird.BeginTransaction(IsolationLevel.ReadCommitted);
mSQL = "Update CONFIG set CFG_DTULTIMO_ARQUIVO_VALIDADO = @data ";
FbCommand cmd = new FbCommand(mSQL, conexaoFireBird, transacao);
cmd.Parameters.AddWithValue("@data", dataConvertida);
cmd.ExecuteNonQuery();
transacao.Commit(); // commita a transação
transacao.Dispose();
transacao = null;
//
return(true);
}
catch (FbException fbex)
{
// Em caso de erros cancela (Rollback) a transação
transacao.Rollback();
//
MessageBox.Show("Erro de acesso ao Firebird : " + fbex.Message, "Erro");
return(false);
}
finally
{
conexaoFireBird.Close();
}
}
}
private void CommitTransaction()
{
if (_sqlTransaction != null)
{
_sqlTransaction.Commit();
#if NET48 || NETSTANDARD2_0
_sqlTransaction.Dispose();
#else
_sqlTransaction.Dispose();
#endif
_sqlTransaction = null;
}
}
public string GetIdInsert(string _table, List <string> _attributes, List <string> _values, string _returning)
{
string o = "";
string sqlCommand = GetCommand(_table, _attributes, _values);
sqlCommand += " returning " + _returning + ";";
FbCommand command = new FbCommand(sqlCommand, connection);
FbTransaction transaction = connection.BeginTransaction();
command.Transaction = transaction;
FbDataReader reader = command.ExecuteReader();
while (_ = reader.Read())
{
o += reader.GetString(0);
}
transaction.Commit();
transaction.Dispose();
command.Dispose();
return(o);
}
public Boolean ComandoScript2(string SQL)
{
string connString = BmsSoftware.ConfigSistema1.Default.ConexaoFB + BmsSoftware.ConfigSistema1.Default.UrlBd;
FbConnection connection = new FbConnection(connString);
connection.Open();
FbTransaction transaction = connection.BeginTransaction();
try
{
FbCommand command = new FbCommand(SQL, connection, transaction);
command.CommandType = CommandType.Text;
command.ExecuteScalar();
transaction.Commit();
connection.Close();
return(true);
}
catch (Exception ex)
{
transaction.Dispose();
connection.Close();
MessageBox.Show("Erro ao executar o comando: " + SQL,
BmsSoftware.ConfigSistema1.Default.NomeEmpresa,
MessageBoxButtons.OK,
MessageBoxIcon.Error,
MessageBoxDefaultButton.Button1);
MessageBox.Show("Erro técnico: " + ex.Message,
BmsSoftware.ConfigSistema1.Default.NomeEmpresa,
MessageBoxButtons.OK,
MessageBoxIcon.Error,
MessageBoxDefaultButton.Button1);
return(false);
}
}
public IHttpActionResult NuevoUsuario([FromUri] string user, [FromUri] string pass, [FromUri] int level)
{
FbConnection Conexion = new FbConnection(con.connectionString);
try
{
Conexion.Open();
int idnuevo = 0;
FbTransaction myTransaction = Conexion.BeginTransaction();
FbCommand myCommand = new FbCommand();
myCommand.CommandText = "SELECT MAX(id) FROM USERS";
myCommand.Connection = Conexion;
myCommand.Transaction = myTransaction;
FbDataReader dr = myCommand.ExecuteReader();
while (dr.Read())
{
idnuevo = Convert.ToInt32(dr.GetValue(0));
}
myCommand.Transaction.Dispose();
FbTransaction myTransactionI = Conexion.BeginTransaction();
myCommand.CommandText = "INSERT INTO USERS (id, usr, pass, level) VALUES (@id, @usr, @pass, @level)";
myCommand.Connection = Conexion;
myCommand.Transaction = myTransactionI;
myCommand.Parameters.Add("@id", FbDbType.Integer).Value = idnuevo + 1;
myCommand.Parameters.Add("@usr", FbDbType.Text).Value = user;
myCommand.Parameters.Add("@pass", FbDbType.Text).Value = pass;
myCommand.Parameters.Add("@level", FbDbType.Integer).Value = level;
myCommand.ExecuteNonQuery();
myTransactionI.Commit();
myTransactionI.Dispose();
}
catch (Exception e) {
return(BadRequest(e.Message));
}
Conexion.Close();
return(Ok("Registrado con exito"));
}
private void ForceDatabaseDisconnect(string connectionString, Int64 attachmentID)
{
#if DEBUG
Shared.EventLog.Debug("RepThread " + System.Reflection.MethodBase.GetCurrentMethod().Name);
#endif
AddToLogFile(String.Format("{2} Force Database Disconnect : {1} - {0}", "", attachmentID, ConnectionName));
FbConnection database = new FbConnection(connectionString);
database.Open();
try
{
FbTransaction tran = database.BeginTransaction();
try
{
string sql = String.Format("DELETE FROM MON$ATTACHMENTS a WHERE a.MON$ATTACHMENT_ID = {0};", attachmentID);
FbCommand cmd = new FbCommand(sql, database, tran);
try
{
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
finally
{
tran.Commit();
tran.Dispose();
}
}
catch (Exception err)
{
AddToLogFile(String.Format("{0} {1}", ConnectionName, err.Message));
}
finally
{
database.Close();
database.Dispose();
}
}
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params FbParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0)
{
throw new ArgumentNullException("connectionString");
}
using (FbConnection connection = new FbConnection(connectionString))
{
connection.Open();
FbTransaction transaction = null;
bool useTransaction = (commandText.Contains("EXECUTE") || commandText.Contains("INSERT"));
if (useTransaction)
{
transaction = connection.BeginTransaction();
}
using (FbCommand command = new FbCommand())
{
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
object result = command.ExecuteScalar();
if (transaction != null)
{
transaction.Commit();
transaction.Dispose();
transaction = null;
}
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
return(result);
}
}
}
public void Dispose()
{
try
{
if (Trans != null)
{
Trans.Dispose();
}
if (Conn != null)
{
if (Conn.State == System.Data.ConnectionState.Open)
{
Conn.Close();
}
Conn.Dispose();
}
}
catch (Exception)
{
throw;
}
}
private FbTransaction GetTransaction(bool readable = true)
{
if (readable)
{
if (write_transaction != null)
{
// if(conn.)
write_transaction.RollbackRetaining();
write_transaction.Dispose();
}
var tro = new FbTransactionOptions()
{
TransactionBehavior = FbTransactionBehavior.Read | FbTransactionBehavior.ReadCommitted | FbTransactionBehavior.RecVersion
};
read_transaction = conn.BeginTransaction(tro);
return(read_transaction);
}
else
{
if (read_transaction != null)
{
read_transaction.CommitRetaining();
read_transaction.Dispose();
}
var tro = new FbTransactionOptions()
{
TransactionBehavior = FbTransactionBehavior.NoWait | FbTransactionBehavior.ReadCommitted | FbTransactionBehavior.RecVersion
};
write_transaction = conn.BeginTransaction(tro);
return(write_transaction);
}
}
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);
}
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);
}
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);
}
}
}
}
}
}
/*
* 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();
}
}
}
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();
}
}
}