C# FirebirdClient.FbCommand类代码示例

        public int atualizar(dsFROGIOS.FORNECEDORRow fornecedor)
        {
            FbCommand comando = null;
            string sql =
                "UPDATE FORNECEDOR "
                + "SET FORNECEDOR_ENDERECO = " + paramEndereco + ", "
                + "FORNECEDOR_BAIRRO = " + paramBairro + ", "
                + "FORNECEDOR_PERIMETRO = " + paramPerimetro + ", "
                + "FORNECEDOR_CIDADE = " + paramCidade + ", "
                + "FORNECEDOR_UF = " + paramUf + ", "
                + "FORNECEDOR_CEP = " + paramCep + ", "
                + "FORNECEDOR_TELEFONE = " + paramTelefone + ", "
                + "FORNECEDOR_CELULAR = " + paramCelular + ", "
                + "FORNECEDOR_EMAIL = " + paramEmail + ", "
                + "FORNECEDOR_OBSERVACOES = " + paramObservacoes + " "
                + "WHERE FORNECEDOR_CODIGO = " + paramCodigo + ";";

            comando = new FbCommand(sql, Conexao.getConexao, Conexao.getTransacao);
            comando.Parameters.AddWithValue(paramCodigo, fornecedor.FORNECEDOR_CODIGO);
            comando.Parameters.AddWithValue(paramEndereco, fornecedor.FORNECEDOR_ENDERECO);
            comando.Parameters.AddWithValue(paramBairro, fornecedor.FORNECEDOR_BAIRRO);
            comando.Parameters.AddWithValue(paramPerimetro, fornecedor.FORNECEDOR_PERIMETRO);
            comando.Parameters.AddWithValue(paramCidade, fornecedor.FORNECEDOR_CIDADE);
            comando.Parameters.AddWithValue(paramUf, fornecedor.FORNECEDOR_UF);
            comando.Parameters.AddWithValue(paramCep, fornecedor.FORNECEDOR_CEP);
            comando.Parameters.AddWithValue(paramTelefone, fornecedor.FORNECEDOR_TELEFONE);
            comando.Parameters.AddWithValue(paramCelular, fornecedor.FORNECEDOR_CELULAR);
            comando.Parameters.AddWithValue(paramEmail, fornecedor.FORNECEDOR_EMAIL);
            comando.Parameters.AddWithValue(paramObservacoes, fornecedor.FORNECEDOR_OBSERVACOES);

            return comando.ExecuteNonQuery();
        }

        //int totalRecords = 0;
        public void ChangePassword( string appName, string username, string newPassword, string newSalt, int passwordFormat, string newPasswordAnswer)
        {
            using (FbCommand cmd = new FbCommand(MsSqlDbAccess.GetObjectName("P_changepassword")))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new FbParameter("@i_applicationname", FbDbType.VarChar));
                cmd.Parameters[0].Value = appName;

                // Nonstandard args

                cmd.Parameters.Add(new FbParameter("@I_USERNAME", FbDbType.VarChar));
                cmd.Parameters[1].Value = username;

                cmd.Parameters.Add(new FbParameter("@i_password", FbDbType.VarChar));
                cmd.Parameters[2].Value = newPassword;

                cmd.Parameters.Add(new FbParameter("@i_passwordsalt", FbDbType.VarChar));
                cmd.Parameters[3].Value = newSalt;

                cmd.Parameters.Add(new FbParameter("@i_passwordformat", FbDbType.VarChar));
                cmd.Parameters[4].Value = passwordFormat;

                cmd.Parameters.Add(new FbParameter("@i_passwordanswer", FbDbType.VarChar));
                cmd.Parameters[5].Value = newPasswordAnswer;

                _dbAccess.ExecuteNonQuery(cmd );
            }
        }

        public void AlterarSenha(Usuario obj)
        {

            FbCommand sqlCommand = new FbCommand();

            try
            {
                sqlCommand.Connection = this._conexao;
                sqlCommand.CommandText = "UPDATE SYS_USERS SET USUARIOWEB = @USUARIO, SENHAWEB = @SENHA WHERE CODIGO  = @CODIGO";
                sqlCommand.Parameters.AddWithValue("@USUARIO", obj.NomeUsuario);
                sqlCommand.Parameters.AddWithValue("@SENHA", obj.Senha);
                sqlCommand.Parameters.AddWithValue("@CODIGO", obj.Codigo);
                sqlCommand.ExecuteNonQuery();
            }
            catch (FbException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlCommand.Dispose();
            }
        }

 private void button1_Click(object sender, EventArgs e)
 {
     string query = "insert into VENTA (ID_CLIENTE, VEN_CODI, VEN_ESTA, CREATED_BY, CREATED_AT) values ( '1', '1', 'a', '1', '2015/06/01')";
     FbCommand insert = new FbCommand(query, conexion);
     insert.ExecuteNonQuery();
     //conexion.Close();
 }

        public Destino DestinoObtener(int AClave){
			Destino pResult = null;
			
			string pSentencia = "SELECT CLAVE, DESTINO FROM DRASDEST WHERE CLAVE = @CLAVE";
			FbConnection con  = _Conexion.ObtenerConexion();
			
			FbCommand com = new FbCommand(pSentencia, con);
			com.Parameters.Add("@CLAVE", FbDbType.Integer).Value = AClave;
			
			try
			{
				con.Open();
				
				FbDataReader reader = com.ExecuteReader();
				
				if (reader.Read()){
					pResult = ReaderToEntidad(reader);
				}
			}
			finally
			{
				if (con.State == System.Data.ConnectionState.Open){
                    con.Close();
                }
			}
			
			return pResult;
		}

        public int ObtenerCajasAplicadasCorte(string AFecha, int ACamara, string AEmbarcado)
        {
            string pSentencia = "SELECT COUNT(*) FROM DRASCORT WHERE "+
                               "FECHA = @FECHA "+
                               "AND CAMARA = @CAMARA "+
                               "AND EMBARCADO = @EMBARCADO "+
                               "AND ENTRADA_APLICADA = 'Si'";
            FbConnection con = _Conexion.ObtenerConexion();

            FbCommand com = new FbCommand(pSentencia, con);
            com.Parameters.Add("@FECHA", FbDbType.TimeStamp).Value   = AFecha;
            com.Parameters.Add("@CAMARA", FbDbType.Integer).Value    = ACamara;
            com.Parameters.Add("@EMBARCADO", FbDbType.VarChar).Value = AEmbarcado;
            try
            {

                con.Open();
                FbDataReader reader = com.ExecuteReader();

                if (reader.Read())
                {
                    return (int)reader["COUNT"];
                }
            }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                {
                    con.Close();
                }
            }

            return 0;
        }

        public void CancelarNFseSistema(string sNumNfse, FbConnection Conn)
        {
            try
            {
                StringBuilder sSql = new StringBuilder();
                sSql.Append("update nf ");
                sSql.Append("set cd_recibocanc = '");
                sSql.Append("CANCELADA");
                sSql.Append("' ");
                sSql.Append("where ");
                sSql.Append("cd_empresa = '");
                sSql.Append(belStatic.codEmpresaNFe);
                sSql.Append("' ");
                sSql.Append("and ");
                sSql.Append("cd_numero_nfse = '");
                sSql.Append(sNumNfse);
                sSql.Append("'");
                using (FbCommand cmdUpdate = new FbCommand(sSql.ToString(), Conn))
                {
                    cmdUpdate.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw;
            }

        }

        public void PopulainfCarga(belinfCte objbelinfCte, FbConnection conn, string sCte)
        {
            try
            {
                StringBuilder sQuery = new StringBuilder();
                sQuery.Append("Select ");
                sQuery.Append("coalesce(conhecim.ds_prodpred,'')proPred, ");
                sQuery.Append("coalesce(nfconhec.vl_nf,'')vMerc ");
                sQuery.Append("from conhecim ");
                sQuery.Append("join nfconhec on  conhecim.nr_lanc = nfconhec.nr_lancconhecim ");
                sQuery.Append("join empresa on conhecim.cd_empresa = empresa.cd_empresa ");
                sQuery.Append("where   conhecim.nr_lanc ='" + sCte + "' ");
                sQuery.Append("and empresa.cd_empresa ='" + belStatic.CodEmpresaCte + "'");



                FbCommand fbConn = new FbCommand(sQuery.ToString(), conn);
                fbConn.ExecuteNonQuery();
                FbDataReader dr = fbConn.ExecuteReader();

                objbelinfCte.infCTeNorm = new belinfCTeNorm();
                objbelinfCte.infCTeNorm.infCarga = new belinfCarga();
                while (dr.Read())
                {
                    objbelinfCte.infCTeNorm.infCarga.vCarga += Convert.ToDecimal(dr["vMerc"].ToString().Replace(".", ","));
                    objbelinfCte.infCTeNorm.infCarga.proPred =belUtil.TiraSimbolo( dr["proPred"].ToString(),"");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public BaseTreeNavigator()
        {
            InitializeComponent();

            //SetConnections();

            // get the connection string from app.config and create the connections
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["sqlServerConnectionString"].ConnectionString;
            string fbConnectionString = ConfigurationManager.ConnectionStrings["firebirdConnectionString"].ConnectionString;

            //sql server
            sqlConnection = new SqlConnection(sqlConnectionString);
            sqlConnectionDaughter = new SqlConnection(sqlConnectionString);

            sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;

            sqlCommandDaughter = new SqlCommand();
            sqlCommandDaughter.Connection = sqlConnectionDaughter;

            //firebird
            fbConnection = new FbConnection(fbConnectionString);
            fbConnectionDaughter = new FbConnection(fbConnectionString);

            fbCommand = new FbCommand();
            fbCommand.Connection = fbConnection;

            fbCommandDaughter = new FbCommand();
            fbCommandDaughter.Connection = fbConnectionDaughter;
        }

        public tcDadosConstrucaoCivil RettcDadosConstrucaoCivil(FbConnection Conn, string sNota)
        {

            try
            {
                StringBuilder sQuery = new StringBuilder();
                sQuery.Append("select coalesce(clifor.cd_art,'') Art, coalesce(clifor.cd_obra,'')CodigoObra {0}");
                sQuery.Append("from nf inner join clifor on nf.cd_clifor = clifor.cd_clifor {0}");
                sQuery.Append("where nf.cd_nfseq = '{1}' and nf.cd_empresa = '{2}' {0}");

                string sQueryEnd = string.Format(sQuery.ToString(), Environment.NewLine, sNota, belStatic.codEmpresaNFe);

                FbCommand cmd = new FbCommand(sQueryEnd, Conn);
                Conn.Open();
                FbDataReader dr = cmd.ExecuteReader();
                objtcDadosConstrucaoCivil = new tcDadosConstrucaoCivil();
                while (dr.Read())
                {
                    objtcDadosConstrucaoCivil.Art = dr["Art"].ToString();
                    objtcDadosConstrucaoCivil.CodigoObra = dr["CodigoObra"].ToString();
                }

            }
            catch (Exception)
            {
                throw;
            }
            finally { Conn.Close(); }


            return objtcDadosConstrucaoCivil;
        }

 public string BuscaUltimoNumeroConhecimento(string sEmp)
 {
     try
     {
         string sQuery = "";
         if (belStatic.sNomeEmpresa.ToUpper().Equals("SICUPIRA") || belStatic.sNomeEmpresa.ToUpper().Equals("TRANSLILO") || belStatic.sNomeEmpresa.ToUpper().Equals("GCA"))  
         {
             string sGenerator = "CONHECIM_CTE" + belStatic.CodEmpresaCte;
             sQuery = "SELECT GEN_ID(" + sGenerator + ", 0 ) FROM RDB$DATABASE";
             using (FbCommand cmd = new FbCommand(sQuery, cx.get_Conexao()))
             {
                 cx.Open_Conexao();
                 return Convert.ToString(cmd.ExecuteScalar());
             }
         }
         else
         {
             sQuery = "select max(c.cd_conheci) from conhecim c where c.cd_empresa = '" + sEmp + "'";
             using (FbCommand cmd = new FbCommand(sQuery, cx.get_Conexao()))
             {
                 cx.Open_Conexao();
                 return Convert.ToString(cmd.ExecuteScalar());
             }
         }
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         cx.Close_Conexao();
     }
 }

        public string RetornaGenString(string sGen, int Tamanho)
        {
            string sNumArq = "";
            try
            {
                FbCommand sSql = new FbCommand();
                sSql.Connection = cx.get_Conexao();
                cx.Open_Conexao();
                sSql.CommandText = "SP_CHAVEPRI";
                sSql.CommandType = CommandType.StoredProcedure;
                sSql.Parameters.Clear();

                sSql.Parameters.Add("@SNOMEGENERATOR", FbDbType.VarChar, 31).Value = "GEN_NOMEARQXML";

                sNumArq = sSql.ExecuteScalar().ToString();

            }
            catch (FbException Ex)
            {               
                Console.WriteLine("Erro.: ", Ex.Message);
            }
            finally
            {
                cx.Close_Conexao();
            }
            return sNumArq.PadLeft(Tamanho, '0');
        }

        public List<string> BuscaCodigoEmpresas()
        {
            List<string> slCodigos = new List<string>();
            belConnection cx = new belConnection();

            try
            {
                using (FbCommand cmd = new FbCommand("Select empresa.cd_empresa from empresa order by empresa.cd_empresa", cx.get_Conexao()))
                {
                    cx.Open_Conexao();
                    cmd.ExecuteNonQuery();
                    FbDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        slCodigos.Add(dr["cd_empresa"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                cx.Close_Conexao();
            }

            return slCodigos;
        }

        public void DeletaTramite(int idTramite)
        {
            string query = "select datarecebimento , usuariorecebimento from webtramite where id = " + idTramite;
            var connection = Persist.GetConn.getConn();
            var cmd = new FbCommand(query, connection);

            var datarecebimento = string.Empty;
            var usuariorecebimento = string.Empty;

            using (connection)
            {
                connection.Open();

                var dr = cmd.ExecuteReader();
                while (dr.Read()) {
                    datarecebimento = dr["datarecebimento"].ToString();
                    usuariorecebimento = dr["usuariorecebimento"].ToString();
                }
            }

            if (string.IsNullOrEmpty(datarecebimento) && string.IsNullOrEmpty(usuariorecebimento))
            {
                string sql = "delete from webtramite where id = " + idTramite;
                AcessoDados.AcessoDados.executar(sql);
            }
        }

 public HistAtualizReg buscarAtualizLicenca(int id_licenca_cliente)
 {
     SigletonConexaoFB.carregaStrcnx();
     HistAtualizReg resultado = new HistAtualizReg();
     FbCommand command = new FbCommand();
     FbDataReader reader;
     try
     {
         command.Connection = SigletonConexaoFB.getConexao();
         command.CommandText = "SELECT ID,ID_LICENCA_CLIENTE,DATA_ENVIO,QTDE_LICENCA,LIBERADO_ATE,VERSAO_ANIEL " +
             "FROM TB_ATUALIZ_REG_ANIEL WHERE ID_LICENCA_CLIENTE=" + id_licenca_cliente.ToString() +
             " AND LIBERADO_CLIENTE='" + 'S' + "'";
         command.Connection.Open();
         reader = command.ExecuteReader();
         if (reader.Read())
         {
             resultado.Id = int.Parse(reader["ID"].ToString());
             resultado.Licenca_cliente.Id = int.Parse(reader["ID_LICENCA_CLIENTE"].ToString());
             resultado.Data_envio = DateTime.Parse(reader["DATA_ENVIO"].ToString());
             resultado.Qtde_licenca = int.Parse(reader["QTDE_LICENCA"].ToString());
             resultado.Liberado_ate = DateTime.Parse(reader["LIBERADO_ATE"].ToString());
             resultado.Versao_aniel = reader["VERSAO_ANIEL"].ToString();
         }
         reader.Close();
     }
     catch (Exception ex)
     {
         throw new Exception("Erro ao carregar dados do registro do Aniel: " + ex.Message);
     }
     finally
     {
         command.Connection.Close();
     }
     return resultado;
 }