public void BigIntArrayPartialUpdateTest()
{
string updateText = "update TEST set larray_field = @array_field " +
"WHERE int_field = 1";
long[] new_values = new long[4];
new_values[0] = 900;
new_values[1] = 1000;
new_values[2] = 1100;
new_values[3] = 1200;
FbCommand update = new FbCommand(updateText, Connection);
update.Parameters.Add("@array_field", FbDbType.Array).Value = new_values;
update.ExecuteNonQuery();
update.Dispose();
PrintArrayValues(new_values, false);
}
private void btnexcluir_Click(object sender, EventArgs e)
{
string strDelete = "DELETE FROM DEPTO WHERE DEPCODI = " + txtdepcodi.Text;
Conexao.Active(true);
try
{
FbCommand cmd = new FbCommand(strDelete, Conexao.FbCnn);
cmd.ExecuteNonQuery();
MessageBox.Show("Registro excluído com sucesso !!!");
limpar();
txtdepcodi.Focus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Conexao.Active(false);
limpar();
}
private void btnexcluir_Click(object sender, EventArgs e)
{
try
{
Conexao.Active(true);
string sql = $"DELETE FROM PEDIDO WHERE PEDCODI = {txtpedcodi.Text}";
FbCommand cmd = new FbCommand(sql, Conexao.FbCnn);
cmd.ExecuteNonQuery();
MessageBox.Show("Registro excluido com sucesso!!!");
limpar();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
Conexao.Active(false);
}
}
private void btngravar_Click(object sender, EventArgs e)
{
try
{
Conexao.Active(true);
string sql = $"update pedido set pedcodi = {txtpedcodi.Text}, peddtem = '{dtppeddtem.Text.Replace("/", ".")}', peddtba = '{dtppeddtba.Text.Replace("/", ".")}', pedtota = {txtpedtota.Text}, pedperc = {txtpedperc.Text}, peddesc = {txtpeddesc.Text}, pedliqu = {txtpedliqu.Text}, pedcond = '{txtpedcond.Text}', pedobs = '{txtpedobs.Text}', clicodi = {cmbcliente.SelectedValue}, funcodi = {cmbfuncionario.SelectedValue} where pedcodi = {txtpedcodi.Text}";
FbCommand cmd = new FbCommand(sql, Conexao.FbCnn);
cmd.ExecuteNonQuery();
MessageBox.Show("Registro gravado com sucesso!!!");
limpar();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
Conexao.Active(false);
}
}
private void btnincluir_Click(object sender, EventArgs e)
{
try
{
Conexao.Active(true);
string sql = $"INSERT INTO PEDIDO VALUES('{txtpedcodi.Text}', '{dtppeddtem.Text.Replace("/", ".")}', '{dtppeddtba.Text.Replace("/", ".")}', {txtpedtota.Text}, {txtpedperc.Text}, {txtpeddesc.Text}, {txtpedliqu.Text}, '{txtpedcond.Text}', '{txtpedobs.Text}', {cmbcliente.SelectedValue}, {cmbfuncionario.SelectedValue})";
FbCommand cmd = new FbCommand(sql, Conexao.FbCnn);
cmd.ExecuteNonQuery();
MessageBox.Show("Registro incluido com sucesso!!!");
ctrlUpdate();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
Conexao.Active(false);
}
}
/// <summary>
/// Inclui uma configuração
/// </summary>
private void Inclui()
{
string sql = "insert into EMAIL_APP values(" +
"'" + remetente + "'," +
"'" + usuario + "'," +
"'" + senha + "'," +
"'" + destinatarios + "'," +
"'" + assunto + "'," +
"'" + texto + "')";
FbCommand cmd = new FbCommand(sql, Globais.bd);
try
{
Log.Grava(Globais.sUsuario, cmd.CommandText);
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Log.Grava(Globais.sUsuario, "erro:" + e.Message);
}
}
public bool Exclui(string data, string usuario, ref string msg)
{
DateTime dt = DateTime.Parse(data);
string sql = "delete from MENSAGENS " +
"where DATA='" + dt.ToString("M/d/yyyy HH:mm:ss") + "' and USUARIO = '" + usuario + "'";
FbCommand cmd = new FbCommand(sql, Globais.bd);
try
{
Log.Grava(Globais.sUsuario, cmd.CommandText);
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Log.Grava(Globais.sUsuario, "erro:" + err.Message);
msg = err.Message;
return(false);
}
msg = "OK";
return(true);
}
public void VarCharArrayTest()
{
Transaction = Connection.BeginTransaction();
int id_value = GetId();
string selectText = "SELECT varray_field FROM TEST WHERE int_field = " + id_value.ToString();
string insertText = "INSERT INTO TEST (int_field, varray_field) values(@int_field, @array_field)";
string[] insert_values = new string[4];
insert_values[0] = "abc";
insert_values[1] = "abcdef";
insert_values[2] = "abcdefghi";
insert_values[3] = "abcdefghijkl";
FbCommand insert = new FbCommand(insertText, Connection, Transaction);
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
insert.ExecuteNonQuery();
insert.Dispose();
Transaction.Commit();
FbCommand select = new FbCommand(selectText, Connection);
FbDataReader reader = select.ExecuteReader();
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
string[] select_values = new string[insert_values.Length];
Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
CollectionAssert.AreEqual(insert_values, select_values);
}
}
reader.Close();
select.Dispose();
}
public void TimeArrayTest()
{
Transaction = Connection.BeginTransaction();
int id_value = GetId();
string selectText = "SELECT tarray_field FROM TEST WHERE int_field = " + id_value.ToString();
string insertText = "INSERT INTO TEST (int_field, tarray_field) values(@int_field, @array_field)";
TimeSpan[] insert_values = new TimeSpan[4];
insert_values[0] = new TimeSpan(3, 9, 10);
insert_values[1] = new TimeSpan(4, 11, 12);
insert_values[2] = new TimeSpan(6, 13, 14);
insert_values[3] = new TimeSpan(8, 15, 16);
FbCommand insert = new FbCommand(insertText, Connection, Transaction);
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
insert.ExecuteNonQuery();
insert.Dispose();
Transaction.Commit();
FbCommand select = new FbCommand(selectText, Connection);
FbDataReader reader = select.ExecuteReader();
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
TimeSpan[] select_values = new TimeSpan[insert_values.Length];
Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
CollectionAssert.AreEqual(insert_values, select_values);
}
}
reader.Close();
select.Dispose();
}
public static bool alterarConfiguracao(Configuracao config)
{
using (FbConnection fbConn = new FbConnection(Util.DAO.Conn))
{
using (FbCommand cmd = new FbCommand())
{
try
{
fbConn.Open();
cmd.Connection = fbConn;
cmd.CommandText = "UPDATE CONFIGURACOES SET PORTA = @PORTA, ATUALIZACAO = @ATUALIZACAO, INTERVALO = @INTERVALO WHERE ID = 1";
cmd.Parameters.AddWithValue("@PORTA", config.porta);
cmd.Parameters.AddWithValue("@ATUALIZACAO", config.atualizacao);
cmd.Parameters.AddWithValue("@INTERVALO", config.intervalo);
cmd.ExecuteNonQuery();
return(true);
}
catch (FbException fbError)
{
LogErro logErro = new LogErro();
logErro.crg = 0;
logErro.descricao = "Erro ao alterar as Configurações";
logErro.data = DateTime.Now;
logErro.maisDetalhes = fbError.Message;
LogErroDAO.inserirLogErro(logErro, 0);
return(false);
}
catch (Exception error)
{
LogErro logErro = new LogErro();
logErro.crg = 0;
logErro.descricao = "Erro ao alterar as Configurações";
logErro.data = DateTime.Now;
logErro.maisDetalhes = error.Message;
LogErroDAO.inserirLogErro(logErro, 0);
return(false);
}
}
}
}
private bool createAccredit(String name, DateTime start, DateTime end)
{
int ret = 0;
DateTime baseDate = new DateTime(1970, 1, 1);
double start_d = (start - baseDate).TotalSeconds;
double current_d = (start - baseDate).TotalSeconds + 1;
double end_d = (end - baseDate).TotalSeconds;
FbConnection.CreateDatabase(GetConnectionString(), true);
using (FbConnection conn = new FbConnection(GetConnectionString()))
{
conn.Open();
using (FbCommand createTable = conn.CreateCommand())
{
createTable.CommandText = "create table ACCREDIT (ID int, NAME varchar(50) character set UTF8, ST varchar(100) character set UTF8, CU varchar(100) character set UTF8, EN varchar(100) character set UTF8);";
ret = createTable.ExecuteNonQuery();
}
using (FbCommand insertData = conn.CreateCommand())
{
String start_s = encrypt.EncryptString(start_d.ToString());
String current_s = encrypt.EncryptString(current_d.ToString());
String end_s = encrypt.EncryptString(end_d.ToString());
insertData.CommandText = "insert into ACCREDIT values (@id, @name, @start, @current, @end);";
insertData.Parameters.Clear();
insertData.Parameters.Add("@id", FbDbType.Integer).Value = 1;
insertData.Parameters.Add("@name", FbDbType.VarChar).Value = name;
insertData.Parameters.Add("@start", FbDbType.VarChar).Value = start_s;
insertData.Parameters.Add("@current", FbDbType.VarChar).Value = current_s;
insertData.Parameters.Add("@end", FbDbType.VarChar).Value = end_s;
ret = insertData.ExecuteNonQuery();
}
}
return(ret > 0);
}
private void regButton_Click(object sender, EventArgs e)
{
if ((surnameTextBox.Text.Length < 2) || (nameTextBox.Text.Length < 2) || (orgTextBox.Text.Length < 1))
{
MessageBox.Show("Введите все данные");
return;
}
FbConnection fb = new FbConnection(connection.conString());
if (fb.State == ConnectionState.Closed)
{
fb.Open();
}
FbTransaction fbt = fb.BeginTransaction();
FbCommand InsertSQL = new FbCommand("INSERT INTO users VALUES (0, @NAME, @SURNAME, @PATR, @CITY, @ORG, @POSIT, @PASS)", fb);
InsertSQL.Parameters.Add("NAME", FbDbType.Text).Value = nameTextBox.Text.Trim();
InsertSQL.Parameters.Add("SURNAME", FbDbType.Text).Value = surnameTextBox.Text.Trim();
InsertSQL.Parameters.Add("PATR", FbDbType.Text).Value = patrTextBox.Text.Trim();
InsertSQL.Parameters.Add("CITY", FbDbType.Text).Value = cityTextBox.Text.Trim();
InsertSQL.Parameters.Add("ORG", FbDbType.Text).Value = orgTextBox.Text.Trim();
InsertSQL.Parameters.Add("POSIT", FbDbType.Text).Value = posTextBox.Text.Trim();
InsertSQL.Parameters.Add("PASS", FbDbType.Text).Value = passTextBox.Text.Trim();
InsertSQL.Transaction = fbt;
try
{
int res = InsertSQL.ExecuteNonQuery();
MessageBox.Show("Успешно!");
fbt.Commit();
InsertSQL.Dispose();
fb.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Close();
}
public void SimplifiedChineseTest()
{
const string Value = "中文";
try
{
using (var cmd = new FbCommand("CREATE TABLE TABLE1 (FIELD1 varchar(20))", Connection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new FbCommand("INSERT INTO TABLE1 VALUES (@value)", Connection))
{
cmd.Parameters.Add("@value", FbDbType.VarChar).Value = Value;
cmd.ExecuteNonQuery();
}
using (var cmd = new FbCommand($"INSERT INTO TABLE1 VALUES ('{Value}')", Connection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new FbCommand("SELECT * FROM TABLE1", Connection))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Assert.AreEqual(Value, reader[0]);
}
}
}
}
finally
{
using (var cmd = new FbCommand("DROP TABLE TABLE1", Connection))
{
cmd.ExecuteNonQuery();
}
}
}
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();
}
}
//***************************//
//Обновление значений поля ID//
//***************************//
private void Button5_Click(object sender, EventArgs e)
{
FbCommand InsertSQL = new FbCommand("UPDATE base_friends SET ID = " + textBox9.Text + " WHERE ID = " + textBox8.Text + ";", fb);
FbTransaction fbt = fb.BeginTransaction();
InsertSQL.Transaction = fbt;
try
{
int res = InsertSQL.ExecuteNonQuery();
MessageBox.Show("Выполненно: " + res.ToString());
fbt.Commit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
InsertSQL.Dispose();
//ДАННЫЕ ТАБЛИЦЫ DATA_FRIENDS//
DataTable dt2 = new DataTable();
FbDataAdapter da2 = new FbDataAdapter();
FbCommand cmd2 = new FbCommand("SELECT * FROM DATA_FRIENDS", fb);
try
{
cmd2.CommandType = CommandType.Text;
FbDataReader dr = cmd2.ExecuteReader();
dt2.Load(dr);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
dataGridView3.DataSource = dt2;
dataGridView4.DataSource = dt2;
textBox8.Clear();
textBox9.Clear();
}
protected void btnConfirmar_Click(object sender, EventArgs e)
{
string strConn = ConfigurationManager.ConnectionStrings["ConnectionStringFB"].ConnectionString.ToString();
FbConnection Conn = new FbConnection(strConn);
FbCommand cmdInsertAviso = new FbCommand();
cmdInsertAviso.CommandText = "SELECT GEN_ID(AVISO_WEB, 1) FROM RDB$DATABASE";
cmdInsertAviso.Connection = Conn;
Conn.Open();
string sCD_AVISO = "";
FbDataReader dr = cmdInsertAviso.ExecuteReader();
while (dr.Read())
{
sCD_AVISO = dr["GEN_ID"].ToString();
}
cmdInsertAviso = new FbCommand();
cmdInsertAviso.Connection = Conn;
cmdInsertAviso.CommandText = "INSERT INTO AVISO_WEB VALUES (@CD_AVISO, @CD_REPRESENTANTE, @DS_AVISO, @DS_TITULO, @DT_FINALAVISO, @ST_TIPOAVISO)";
cmdInsertAviso.Parameters.Add("@CD_AVISO", sCD_AVISO.PadLeft(7, '0'));
cmdInsertAviso.Parameters.Add("@CD_REPRESENTANTE", cboTipo.SelectedIndex == 1 ? cboRepresentante.SelectedValue : null);
cmdInsertAviso.Parameters.Add("@DS_AVISO", txtAviso.Text);
cmdInsertAviso.Parameters.Add("@DS_TITULO", txtTitulo.Text);
cmdInsertAviso.Parameters.Add("@DT_FINALAVISO", txtDataFinal.Text.Replace("/", "."));
cmdInsertAviso.Parameters.Add("@ST_TIPOAVISO", cboTipo.SelectedValue);
int sRetorno = cmdInsertAviso.ExecuteNonQuery();
if (sRetorno > 0)
{
MessageHLP.ShowPopUpMsg("Aviso cadastrado com sucesso!", this.Page);
txtTitulo.Text = "";
txtAviso.Text = "";
txtDataFinal.Text = "";
}
}
public static void inserirLogErro(LogErro logErro, int crg)
{
using (FbConnection fbConn = new FbConnection(Util.DAO.Conn))
{
using (FbCommand cmd = new FbCommand())
{
try
{
fbConn.Open();
cmd.Connection = fbConn;
cmd.CommandText = "INSERT INTO LOGERRO(DESCRICAO, CRG, MAISDETALHES, DATA, HORA) VALUES(@DESCRICAO, @CRG, @MAISDETALHES, @DATA, @HORA)";
cmd.Parameters.AddWithValue("@DESCRICAO", logErro.descricao);
cmd.Parameters.AddWithValue("@CRG", logErro.crg);
cmd.Parameters.AddWithValue("@MAISDETALHES", logErro.maisDetalhes);
cmd.Parameters.AddWithValue("@DATA", logErro.data.ToString("dd.MM.yyyy"));
cmd.Parameters.AddWithValue("@HORA", logErro.data.ToString("HH:mm:ss"));
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (FbException fbError)
{
LogErro LogErro = new LogErro();
LogErro.descricao = "Erro ao inserir LogErro";
LogErro.crg = crg;
LogErro.data = DateTime.Now;
LogErro.maisDetalhes = fbError.Message;
inserirLogErro(LogErro, crg);
}
catch (Exception error)
{
LogErro LogErro = new LogErro();
LogErro.descricao = "Erro ao inserir LogErro";
LogErro.crg = crg;
LogErro.data = DateTime.Now;
LogErro.maisDetalhes = error.Message;
inserirLogErro(LogErro, crg);
}
}
}
}
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"));
}
/// <summary>
/// UPDATE_INVEST_PROJECT_ALL(
///ID_PROJECT integer,
///NAME_PROJECT NAME_PROJECT_DOMAIN,
///NUMBER_PROJECT NUMBER_PROJECT,
///ID_DEPT integer,
///ID_USER integer,
///DATE_BEGIN_PLAN date,
///DATE_END_PLAN date,
///DATE_BEGIN_PROG date,
///DATE_END_PROG date,
///ABOUT_PROJECT ABOUT)
/// </summary>
/// <param name="NAME_PROJECT"></param>
/// <param name="NUMBER_PROJECT"></param>
/// <param name="integer"></param>
/// <param name="integer"></param>
/// <param name="date"></param>
/// <param name="date"></param>
/// <param name="date"></param>
/// <param name="date"></param>
/// <param name="ABOUT"></param>
public void update(InvestProject investProject)
{
FbConnection connection = null;
FbCommand statement = null;
FbTransaction transaction = null;
string sql = @"execute procedure update_invest_project_all (@id_project, @name_project, @number_project, @id_dept, @id_user,
@date_begin, @date_end, @date_begin_prog, @date_end_prog, @about)";
try
{
connection = daoFactory.getConnection();
connection.Open();
transaction = connection.BeginTransaction();
statement = new FbCommand(sql, connection, transaction);
statement.Parameters.Add("@id_project", investProject.idProject);
statement.Parameters.Add("@name_project", investProject.nameProject);
statement.Parameters.Add("@number_project", investProject.numberProject);
statement.Parameters.Add("@id_dept", investProject.department.idDepartment);
statement.Parameters.Add("id_user", investProject.user.Id);
statement.Parameters.Add("@date_begin", investProject.dateBegin);
statement.Parameters.Add("@date_end", investProject.dateEnd);
statement.Parameters.Add("date_begin_prog", investProject.dateBeginProg);
statement.Parameters.Add("@date_end_prog", investProject.dateEndProg);
statement.Parameters.Add("@about", investProject.aboutProject);
statement.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw new DAOException("Insert Project error", e);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public bool Inclui(string parceiro, string codigo, string nome, string fone1, string fone2,
string celular, string email, string papel,
bool idt_nascimento, DateTime dat_nascimento,
string ativo, ref string msg)
{
string sql = "insert into CONTATOS values(" +
"'" + parceiro + "'," +
"'" + codigo + "'," +
"'" + nome + "'," +
"'" + fone1 + "'," +
"'" + fone2 + "'," +
"'" + celular + "'," +
"'" + email + "'," +
"'" + papel + "'," +
"'" + ativo + "',";
if (idt_nascimento)
{
sql += "'" + dat_nascimento.ToString("M/d/yyyy") + "')";
}
else
{
sql += "null)";
}
FbCommand cmd = new FbCommand(sql, Globais.bd);
try
{
Log.Grava(Globais.sUsuario, cmd.CommandText);
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Log.Grava(Globais.sUsuario, "erro:" + err.Message);
msg = err.Message;
return(false);
}
msg = "OK";
return(true);
}
private static void CreateTriggers(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// new_row
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER new_row FOR test ACTIVE\r\n");
commandText.Append("AFTER INSERT POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'new row';\r\n");
commandText.Append("END");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// update_row
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER update_row FOR test ACTIVE\r\n");
commandText.Append("AFTER UPDATE POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'updated row';\r\n");
commandText.Append("END");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
public static bool InserirBackup(Backup backup)
{
using (FbConnection fbConn = new FbConnection(Util.DAO.Conn))
{
using (FbCommand cmd = new FbCommand())
{
try
{
fbConn.Open();
cmd.Connection = fbConn;
cmd.CommandText = "INSERT INTO BACKUP(ID,PERIODO,PASTADESTINO) VALUES(@ID,@PERIODO,@PASTADESTINO)";
cmd.Parameters.AddWithValue("@ID", 1);
cmd.Parameters.AddWithValue("@PERIODO", backup.Periodo);
cmd.Parameters.AddWithValue("@PASTADESTINO", backup.CaminhoBackup);
cmd.ExecuteNonQuery();
return(true);
}
catch (FbException fbError)
{
LogErro logErro = new LogErro();
logErro.crg = 0;
logErro.descricao = "Erro no inserir Backup";
logErro.data = DateTime.Now;
logErro.maisDetalhes = fbError.Message;
LogErroDAO.inserirLogErro(logErro, 0);
return(false);
}
catch (Exception error)
{
LogErro logErro = new LogErro();
logErro.crg = 0;
logErro.descricao = "Erro no inserir Backup";
logErro.data = DateTime.Now;
logErro.maisDetalhes = error.Message;
LogErroDAO.inserirLogErro(logErro, 0);
return(false);
}
}
}
}
public int TransactionQuery(string strQuery, FbParameter[] paramArray,
CommandType cmdType, out FbParameterCollection col)
{
int Li_RowCnt = 0;
FbCommand cmd = null;
try
{
cmd = new FbCommand(strQuery, Conn);
cmd.CommandType = cmdType;
if (paramArray != null)
{
foreach (FbParameter param in paramArray)
{
cmd.Parameters.Add(param);
}
}
if (Trans == null)
{
BeginTrans();
}
cmd.Transaction = Trans;
Li_RowCnt = cmd.ExecuteNonQuery();
col = cmd.Parameters;
cmd.Transaction.Commit();
}
catch (Exception)
{
cmd.Transaction.Rollback();
Li_RowCnt = -1;
throw;
}
return(Li_RowCnt);
}
public bool Atualiza(DataGridView grid, int acao, ref string msg)
{
string sql = "delete from PRODUTOS_POR_ACAO " +
"where SEQ_ACAO=" + acao.ToString();
FbCommand cmd = new FbCommand(sql, Globais.bd);
try
{
Log.Grava(Globais.sUsuario, cmd.CommandText);
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Log.Grava(Globais.sUsuario, "erro:" + err.Message);
msg = err.Message;
return(false);
}
foreach (DataGridViewRow row in grid.Rows)
{
if (!(bool)row.Cells["Seleciona"].Value)
{
continue;
}
sql = "insert into PRODUTOS_POR_ACAO values(" +
acao.ToString() + ", '" + row.Cells["Código"].Value + "')";
cmd = new FbCommand(sql, Globais.bd);
try
{
Log.Grava(Globais.sUsuario, cmd.CommandText);
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Log.Grava(Globais.sUsuario, "erro:" + err.Message);
msg = err.Message;
return(false);
}
}
return(true);
}
public void ShortArrayTest()
{
Transaction = Connection.BeginTransaction();
var id_value = GetId();
var selectText = "SELECT sarray_field FROM TEST WHERE int_field = "+ id_value.ToString();
var insertText = "INSERT INTO TEST (int_field, sarray_field) values(@int_field, @array_field)";
var insert_values = new short[4];
insert_values[0] = 50;
insert_values[1] = 60;
insert_values[2] = 70;
insert_values[3] = 80;
var insert = new FbCommand(insertText, Connection, Transaction);
insert.Parameters.Add("@int_field", FbDbType.Integer).Value = id_value;
insert.Parameters.Add("@array_field", FbDbType.Array).Value = insert_values;
insert.ExecuteNonQuery();
insert.Dispose();
Transaction.Commit();
var select = new FbCommand(selectText, Connection);
var reader = select.ExecuteReader();
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
var select_values = new short[insert_values.Length];
Array.Copy((Array)reader.GetValue(0), select_values, select_values.Length);
CollectionAssert.AreEqual(insert_values, select_values);
}
}
reader.Close();
select.Dispose();
}
/////////////////////////////////////////////////////////////////////////////////////////
private void exec_proc_ADD_DOSSIERPOPUPS(ref FbConnection Connection, ref FbTransaction Transaction, ref BattleResult_v2 BRv2, int BATTLE_ID)
{
FbCommand myCommand;
if (BRv2.Personal["dossierPopUps"].Count > 0)
{
foreach (IList <object> val in BRv2.Personal["dossierPopUps"])
{
myCommand = new FbCommand();
myCommand.CommandText = "execute procedure ADD_DOSSIERPOPUPS(" +
BATTLE_ID.ToString() + "," +
((int)val[0]).ToString() + "," +
((int)val[1]).ToString() + ")";
myCommand.Connection = Connection;
myCommand.Transaction = Transaction;
myCommand.ExecuteNonQuery();
myCommand.Dispose();
}
}
}
/// <summary>
/// Executes a previously prepared bindable query, inserting a new set of parameters
/// </summary>
/// <param name="bindableQuery">The prepared query to be executed</param>
/// <param name="values">The values to be inserted by using the query</param>
public void RunBindableQuery(object bindableQuery, IEnumerable <object> values)
{
FbCommand cmd = (FbCommand)bindableQuery;
int i = 0;
foreach (var value in values)
{
cmd.Parameters[i].Value = value;
// Enums have an underlying type of Int32, but we want to store
// their string representation, not their integer value
if (value.GetType().IsEnum)
{
cmd.Parameters[i].Value = value.ToString();
}
else
{
cmd.Parameters[i].Value = value;
}
i++;
}
cmd.ExecuteNonQuery();
}
public bool Altera(string codigo, string descricao, string ativo, ref string msg)
{
string sql = "update FORMAS_PAGAMENTO set " +
"DES_FORMA='" + descricao + "'," +
"IDT_ATIVO='" + ativo + "' " +
"where COD_FORMA='" + codigo + "'";
FbCommand cmd = new FbCommand(sql, Globais.bd);
try
{
Log.Grava(Globais.sUsuario, cmd.CommandText);
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Log.Grava(Globais.sUsuario, "erro:" + err.Message);
msg = err.Message;
return(false);
}
msg = "OK";
return(true);
}
public bool Altera(int sistema, string codigo, string descricao, ref string msg)
{
string sql = "update PROGRAMAS set " +
"DES_PROGRAMA='" + descricao + "' " +
"where COD_SISTEMA=" + sistema + " " +
"and COD_PROGRAMA='" + codigo + "'";
FbCommand cmd = new FbCommand(sql, Globais.bd);
try
{
Log.Grava(Globais.sUsuario, cmd.CommandText);
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Log.Grava(Globais.sUsuario, "erro:" + err.Message);
msg = err.Message;
return(false);
}
msg = "OK";
return(true);
}
public bool Inclui(string codigo, string descricao, string ativo, ref string msg)
{
string sql = "insert into FORMAS_PAGAMENTO values(" +
"'" + codigo + "'," +
"'" + descricao + "'," +
"'" + ativo + "')";
FbCommand cmd = new FbCommand(sql, Globais.bd);
try
{
Log.Grava(Globais.sUsuario, cmd.CommandText);
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Log.Grava(Globais.sUsuario, "erro:" + err.Message);
msg = err.Message;
return(false);
}
msg = "OK";
return(true);
}
private static void CreateTriggers(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// new_row
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER new_row FOR test ACTIVE\r\n");
commandText.Append("AFTER INSERT POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'new row';\r\n");
commandText.Append("END");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// update_row
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER update_row FOR test ACTIVE\r\n");
commandText.Append("AFTER UPDATE POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'updated row';\r\n");
commandText.Append("END");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
private static void CreateTables(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// Table for general purpouse tests
commandText.Append("CREATE TABLE TEST (");
commandText.Append("INT_FIELD INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,");
commandText.Append("CHAR_FIELD CHAR(30),");
commandText.Append("VARCHAR_FIELD VARCHAR(100),");
commandText.Append("BIGINT_FIELD BIGINT,");
commandText.Append("SMALLINT_FIELD SMALLINT,");
commandText.Append("DOUBLE_FIELD DOUBLE PRECISION,");
commandText.Append("FLOAT_FIELD FLOAT,");
commandText.Append("NUMERIC_FIELD NUMERIC(15,2),");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2),");
commandText.Append("DATE_FIELD DATE,");
commandText.Append("TIME_FIELD TIME,");
commandText.Append("TIMESTAMP_FIELD TIMESTAMP,");
commandText.Append("CLOB_FIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80,");
commandText.Append("BLOB_FIELD BLOB SUB_TYPE 0 SEGMENT SIZE 80,");
commandText.Append("IARRAY_FIELD INTEGER [0:3],");
commandText.Append("SARRAY_FIELD SMALLINT [0:4],");
commandText.Append("LARRAY_FIELD BIGINT [0:5],");
commandText.Append("FARRAY_FIELD FLOAT [0:3],");
commandText.Append("BARRAY_FIELD DOUBLE PRECISION [1:4],");
commandText.Append("NARRAY_FIELD NUMERIC(10,6) [1:4],");
commandText.Append("DARRAY_FIELD DATE [1:4],");
commandText.Append("TARRAY_FIELD TIME [1:4],");
commandText.Append("TSARRAY_FIELD TIMESTAMP [1:4],");
commandText.Append("CARRAY_FIELD CHAR(21) [1:4],");
commandText.Append("VARRAY_FIELD VARCHAR(30) [1:4],");
commandText.Append("BIG_ARRAY INTEGER [1:32767],");
commandText.Append("EXPR_FIELD COMPUTED BY (smallint_field * 1000),");
commandText.Append("CS_FIELD CHAR(1) CHARACTER SET UNICODE_FSS,");
commandText.Append("UCCHAR_ARRAY CHAR(10) [1:10] CHARACTER SET UNICODE_FSS);");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
private static void CreateProcedures(string connectionString)
{
FbConnection connection = new FbConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
// SELECT_DATA
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE SELECT_DATA \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("INT_FIELD INTEGER, \r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100), \r\n");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2)) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("FOR SELECT INT_FIELD, VARCHAR_FIELD, DECIMAL_FIELD FROM TEST INTO :INT_FIELD, :VARCHAR_FIELD, :DECIMAL_FIELD \r\n");
commandText.Append("DO \r\n");
commandText.Append("SUSPEND; \r\n");
commandText.Append("end;");
FbCommand command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETRECORDCOUNT
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETRECORDCOUNT \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("RECCOUNT SMALLINT) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("for select count(*) from test into :reccount \r\n");
commandText.Append("do \r\n");
commandText.Append("suspend; \r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETVARCHARFIELD
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETVARCHARFIELD (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select varchar_field from test where int_field = :id into :varchar_field\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETASCIIBLOB
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETASCIIBLOB (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("ASCII_BLOB BLOB SUB_TYPE 1)\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select clob_field from test where int_field = :id into :ascii_blob\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// DATAREADERTEST
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE DATAREADERTEST\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("content VARCHAR(128))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("content = 'test';\r\n");
commandText.Append("end\r\n");
command = new FbCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
}
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();
}
}
