Example #1
0
        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);
        }
Example #2
0
        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();
        }
Example #3
0
        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);
            }
        }
Example #4
0
        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);
            }
        }
Example #5
0
        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);
            }
        }
Example #6
0
        /// <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);
            }
        }
Example #7
0
        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();
        }
Example #10
0
 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);
             }
         }
     }
 }
Example #11
0
        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);
        }
Example #12
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();
                }
            }
        }
Example #14
0
        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();
            }
        }
Example #15
0
        //***************************//
        //Обновление значений поля 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();
        }
Example #16
0
    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 = "";
        }
    }
Example #17
0
 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);
             }
         }
     }
 }
Example #18
0
        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"));
        }
Example #19
0
        /// <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();
                }
            }
        }
Example #20
0
        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);
        }
Example #21
0
        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();
        }
Example #22
0
 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);
             }
         }
     }
 }
Example #23
0
        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);
        }
Example #24
0
        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);
        }
Example #25
0
        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();
        }
Example #26
0
        /////////////////////////////////////////////////////////////////////////////////////////
        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();
                }
            }
        }
Example #27
0
        /// <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();
        }
Example #28
0
        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);
        }
Example #29
0
        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);
        }
Example #30
0
        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();
			}
		}