public List<Producto> listaProductosFechaEliminados(string AFecha) { List<Producto> pResult = new List<Producto>(); Producto pProducto = null; string pSentencia = "SELECT DISTINCT(PRODUCTO) AS CLAVE, (SELECT DESCRIPCION FROM DRASPROD WHERE CLAVE = PRODUCTO) FROM DRASELIM WHERE FECHA = @FECHA AND CODIGOALTA IS NOT NULL"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@FECHA", FbDbType.TimeStamp).Value = AFecha; try { con.Open(); FbDataReader reader = com.ExecuteReader(); while (reader.Read()) { pProducto = new Producto(); pProducto.Clave = reader["DESCRIPCION"] != DBNull.Value ? (string)reader["DESCRIPCION"] : ""; pResult.Add(pProducto); } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return pResult; }
public bool ExisteEmpaque(Empaque AEmpaque) { string pSentencia = "SELECT ID FROM DRASEMPAQUE WHERE UPPER(TRIM(NOMBRE)) = @NOMBRE"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@NOMBRE", FbDbType.VarChar).Value = AEmpaque.Nombre.ToUpper().Trim(); try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()) { if ((int)reader["ID"] == AEmpaque.Clave) return false; else return true; } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return false; }
public List<Empaque> ObtenerEmpaques(string AOrderBy) { List<Empaque> pResult = new List<Empaque>(); Empaque pEmpaque = null; string pSentencia = "SELECT * FROM DRASEMPAQUE ORDER BY "+AOrderBy; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia,con); try { con.Open(); FbDataReader reader = com.ExecuteReader(); while (reader.Read()){ pEmpaque = new Empaque(); pEmpaque.Clave = (int)reader["ID"]; pEmpaque.IdTipoEmpaque = (int)reader["ID_TIPOEMPAQUE"]; pEmpaque.Nombre = (reader["NOMBRE"] != DBNull.Value) ? (string)reader["NOMBRE"] : ""; pEmpaque.CodigoSAP = (reader["CODIGOSAP"] != DBNull.Value) ? (string)reader["CODIGOSAP"] : ""; pEmpaque.Costo = (reader["COSTO"] != DBNull.Value) ? (decimal)reader["COSTO"] : 0; pResult.Add(pEmpaque); } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return pResult; }
public void PopulaDadosCancelamento(string sCodConhecimento, string sJustificativa) { try { StringBuilder sQuery = new StringBuilder(); sQuery.Append("Select "); sQuery.Append("conhecim.cd_chavecte chCTe, "); sQuery.Append("conhecim.cd_nprotcte nProt "); sQuery.Append("from conhecim "); sQuery.Append("where conhecim.cd_conheci ='" + sCodConhecimento + "' "); sQuery.Append("and conhecim.cd_empresa = '" + belStatic.CodEmpresaCte + "'"); FbCommand fbConn = new FbCommand(sQuery.ToString(), cx.get_Conexao()); cx.Open_Conexao(); fbConn.ExecuteNonQuery(); FbDataReader dr = fbConn.ExecuteReader(); dr.Read(); objBelCancelaCte.versao = "1.03"; objBelCancelaCte.Id = "ID" + dr["chCTe"].ToString(); objBelCancelaCte.tpAmb = belStatic.TpAmb.ToString(); objBelCancelaCte.xServ = "CANCELAR"; objBelCancelaCte.chCTe = dr["chCTe"].ToString(); objBelCancelaCte.nProt = dr["nProt"].ToString(); objBelCancelaCte.xJust = sJustificativa; } catch (Exception) { throw; } finally { cx.Close_Conexao(); } }
public List<EstimacionEmpaqueM> ObtenerLista() { List<EstimacionEmpaqueM> pResult = new List<EstimacionEmpaqueM>(); EstimacionEmpaqueM estimacionEmpM; string pSentencia = "SELECT * FROM DRASESTIEMPAQUEM"; FbConnection con = _Conexiones.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); try { con.Open(); FbDataReader reader = com.ExecuteReader(); while (reader.Read()) { estimacionEmpM = new EstimacionEmpaqueM(); estimacionEmpM.Id = (int)reader["ID"]; estimacionEmpM.Descripcion = reader["DESCRIPCION"] != DBNull.Value ? (string)reader["DESCRIPCION"] : ""; estimacionEmpM.FechaHoraSistema = reader["FECHAHORASISTEMA"] != DBNull.Value ? (DateTime?)reader["FECHAHORASISTEMA"] : null; pResult.Add(estimacionEmpM); } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return pResult; }
public Equipe buscarCod_Equipe(String cod_equipe) { SigletonConexaoFB.carregaStrcnx(); Equipe resultado = new Equipe(); FbCommand command = new FbCommand(); FbDataReader reader; try { command.Connection = SigletonConexaoFB.getConexao(); command.CommandText = "SELECT A.EQUIPE,A.NOME,A.COD_PERFIL_EQUIPE,B.NOME as NOME_PERFIL FROM TB_EQUIPE A " + "LEFT OUTER JOIN TB_PERFIL_EQUIPE B ON (B.COD_PERFIL_EQUIPE=A.COD_PERFIL_EQUIPE) WHERE EQUIPE='" + cod_equipe + "'"; command.Connection.Open(); reader = command.ExecuteReader(); if (reader.Read()) { resultado.Cod_Equipe = reader["EQUIPE"].ToString(); resultado.Nome = reader["NOME"].ToString(); resultado.Perfil.Cod_Perfil_Equipe = int.Parse(reader["COD_PERFIL_EQUIPE"].ToString()); resultado.Perfil.Nome = reader["NOME_PERFIL"].ToString(); } reader.Close(); } catch (Exception ex) { throw new Exception("Erro na busca da equipe pelo código: " + ex.Message); } finally { command.Connection.Close(); } return resultado; }
//SELECT T0.*, T1.DESCRIPCION FROM DRASSOBRANTESD T0 JOIN DRASPROD T1 ON T1.CLAVE = T0.PRODUCTO WHERE ID_SOBRANTE = ? public List<SobrantesD> obtener_lista(int Aid) { List<SobrantesD> pResult = new List<SobrantesD>(); string pSentencia = "SELECT T0.*, T1.DESCRIPCION FROM DRASSOBRANTESD T0 JOIN DRASPROD T1 ON T1.CLAVE = T0.PRODUCTO WHERE ID_SOBRANTE = @ID_SOBRANTE"; FbConnection con = _Conexiones.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@ID_SOBRANTE", FbDbType.Integer).Value = Aid; try { con.Open(); FbDataReader reader = com.ExecuteReader(); while (reader.Read()) { SobrantesD pSobrandesD = new SobrantesD(); pSobrandesD.id = (reader["ID"] != DBNull.Value) ? (int)reader["ID"] : -1; pSobrandesD.id_sobrante = (reader["ID_SOBRANTE"] != DBNull.Value) ? (int)reader["ID_SOBRANTE"] : -1; pSobrandesD.producto = (reader["PRODUCTO"] != DBNull.Value) ? (string)reader["PRODUCTO"] : ""; pSobrandesD.descripcion = (reader["DESCRIPCION"] != DBNull.Value) ? (string)reader["DESCRIPCION"] : ""; pResult.Add(pSobrandesD); } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return pResult; }
public void InsertGuidTest() { FbCommand createTable = new FbCommand("CREATE TABLE GUID_TEST (GUID_FIELD CHAR(16) CHARACTER SET OCTETS)", Connection); createTable.ExecuteNonQuery(); createTable.Dispose(); Guid newGuid = Guid.Empty; Guid guidValue = Guid.NewGuid(); // Insert the Guid FbCommand insert = new FbCommand("INSERT INTO GUID_TEST (GUID_FIELD) VALUES (@GuidValue)", Connection); insert.Parameters.Add("@GuidValue", FbDbType.Guid).Value = guidValue; insert.ExecuteNonQuery(); insert.Dispose(); // Select the value FbCommand select = new FbCommand("SELECT * FROM GUID_TEST", Connection); using (FbDataReader r = select.ExecuteReader()) { if (r.Read()) { newGuid = r.GetGuid(0); } } Assert.AreEqual(guidValue, newGuid); }
public IModel Select(FbConnection conn, FbTransaction trans, string code) { string query = "SELECT * FROM models WHERE modelcode = UPPER(@modelcode)"; IModel ret = null; try { using (FbCommand cmd = new FbCommand(query, conn, trans)) { cmd.Parameters.AddWithValue("modelcode", code.ToUpper()); using (FbDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { ret = new Model { Id = reader.GetInt32(0), Code = reader.GetString(1), ObjectIdRoot = reader.GetInt32(2) }; } } } } catch (Exception ex) { throw new Exception(String.Format("Ошибка SQL запроса. {0}", ex.Message)); } return ret; }
public string RetPrestadorEmail() { belConnection cx = new belConnection(); try { string sMsgPadraoEmail = "{5}Razão Social:{1}{0}{5}E-mail: {2}{0}{5}CCM :{3}{0}{5}CNPJ:{4}{0}{0}"; StringBuilder sQuery = new StringBuilder(); sQuery.Append(" select empresa.cd_cgc, empresa.cd_inscrmu, empresa.cd_email, empresa.nm_empresa from empresa "); sQuery.Append(" where empresa.cd_empresa = '" + belStatic.codEmpresaNFe + "'"); FbCommand Comand = new FbCommand(sQuery.ToString(), cx.get_Conexao()); cx.Open_Conexao(); Comand.ExecuteNonQuery(); FbDataReader dr = Comand.ExecuteReader(); dr.Read(); objtcIdentificacaoPrestador = new tcIdentificacaoPrestador(); return string.Format(sMsgPadraoEmail, "<br>", dr["nm_empresa"].ToString(), dr["cd_email"].ToString(), dr["cd_inscrmu"].ToString(), dr["cd_cgc"].ToString(), " "); } catch (Exception ex) { throw; } finally { cx.Close_Conexao(); } }
public bool ExisteLote(int ALote) { string pSentencia = "SELECT * FROM LOTES_NO_INVENTARIABLES WHERE LOTE=@LOTE"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@LOTE", FbDbType.Integer).Value = ALote; try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()) { return true; } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return false; }
//Retorna Datos de Maquila especifica public CostoMaquilaM ObtenerCostosMaquila(int Id) { CostoMaquilaM pResult = new CostoMaquilaM(); string pSentencia = "SELECT * FROM DRASCOSTOSMAQUILAM WHERE ID = @ID "; FbConnection con = _Conexiones.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@ID", FbDbType.TimeStamp).Value = Id; try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()) { pResult.Id = reader.GetInt32(0); pResult.Fecha = reader.GetString(1); pResult.FechaFinal = reader.GetString(2); pResult.Descripcion = reader.GetString(3); pResult.Activo = reader.GetString(4); pResult.FechaHoraSistema = reader.GetString(5); } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return pResult; }
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 bool ExisteDestino(Destino ADestino) { string pSentencia = "SELECT CLAVE FROM DRASDEST WHERE UPPER(TRIM(DESTINO)) = @DESTINO"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@DESTINO", FbDbType.VarChar).Value = ADestino._Destino.ToUpper().Trim(); try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()) { int clave = (int)reader["CLAVE"]; if (clave == ADestino.Clave) return false; else return true; } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return false; }
protected List<Dictionary<string, string>> _executeReader(FbConnection conn, FbTransaction trans, string query, Dictionary<string, object> prms) { List<Dictionary<string, string>> ret = new List<Dictionary<string, string>>(); using (FbCommand cmd = new FbCommand(query, conn, trans)) { foreach (var prm in prms) cmd.Parameters.AddWithValue(prm.Key, prm.Value); using (FbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Dictionary<string, string> row = new Dictionary<string, string>(); Object[] values = new Object[reader.FieldCount]; reader.GetValues(values); for (int jj = 0; jj < reader.FieldCount; jj++) row.Add(reader.GetName(jj).ToLower(), values[jj].ToString()); ret.Add(row); } } } return ret; }
public ClasificacionCorte ClasificacionCorteObtener(int AClave) { ClasificacionCorte pResult = null; string pSentencia = "SELECT ID, DESCRIPCION, LOTES FROM CLASIFICACIONCORTES WHERE ID = @ID"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@ID", 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; }
//Retorna la lista de Costos Maquila public List<CostoMaquilaM> ListaCostosMaquila() { List<CostoMaquilaM> pResult = new List<CostoMaquilaM>(); string pSentencia = "SELECT * FROM DRASCOSTOSMAQUILAM"; FbConnection con = _Conexiones.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); try { con.Open(); FbDataReader reader = com.ExecuteReader(); while (reader.Read()) { CostoMaquilaM pCostoMaquila = new CostoMaquilaM(); pCostoMaquila.Id = reader.GetInt32(0); pCostoMaquila.Fecha = reader.GetString(1); pCostoMaquila.FechaFinal = reader.GetString(2); pCostoMaquila.Descripcion = reader.GetString(3); pCostoMaquila.Activo = reader.GetString(4); pCostoMaquila.FechaHoraSistema = reader.GetString(5); pResult.Add(pCostoMaquila); } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return pResult; }
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 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 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 Row[] Select() { FbTransaction transaction = connection.BeginTransaction(); FbCommand cmd = new FbCommand(selectCommand, connection, transaction); List<Row> rows = new List<Row>(); Row nr; using (FbDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { nr = new Row(); nr.ID = dr.GetInt32(0); nr.Name = dr.GetString(1); nr.Description = dr.GetString(2); using (System.IO.Stream stream = dr.GetStream(3)) { using (System.IO.MemoryStream mStream = new System.IO.MemoryStream()) { stream.CopyTo(mStream); nr.Image = mStream.ToArray(); } } rows.Add(nr); } } transaction.Commit(); return rows.ToArray(); }
public bool ExisteCamaraMismoId(Camara ACamara) { string pSentencia = "SELECT ID FROM DRASCAM WHERE ID = @ID"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@ID", FbDbType.Integer).Value = ACamara.Clave; try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()) { return true; } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return false; }
public void SimpleSelectTest() { FbConnectionStringBuilder csb = base.BuildConnectionStringBuilder(); csb.Enlist = true; using (TransactionScope scope = new TransactionScope()) { using (FbConnection c = new FbConnection(csb.ToString())) { c.Open(); using (FbCommand command = new FbCommand("select * from TEST where (0=1)", c)) { using (FbDataReader r = command.ExecuteReader()) { while (r.Read()) { } } } } scope.Complete(); } }
public string fNIP(string sNIP) { string sReturn = "0"; string query = "select count(*) from ad_firms where (UPPER(TAXID) = UPPER('" + sNIP + "'));"; //Open connection if (this.OpenConnection() == true) { //Create Command FbCommand cmd = new FbCommand(query, connection); //Create a data reader and Execute the command FbDataReader dataReader = cmd.ExecuteReader(); //MessageBox.Show(query, "qry", MessageBoxButtons.OK); //Read the data and store them in the list while (dataReader.Read()) { sReturn = dataReader[0].ToString() + ""; } //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); return sReturn; } else { return sReturn; } }
public bool ExisteClasificacionCorte(ClasificacionCorte AClasificacionCorte) { string pSentencia = "SELECT ID FROM CLASIFICACIONCORTES WHERE UPPER(TRIM(DESCRIPCION)) = @DESCRIPCION"; FbConnection con = _Conexion.ObtenerConexion(); FbCommand com = new FbCommand(pSentencia, con); com.Parameters.Add("@DESCRIPCION", FbDbType.VarChar).Value = AClasificacionCorte.Descripcion.ToUpper().Trim(); try { con.Open(); FbDataReader reader = com.ExecuteReader(); if (reader.Read()) { if ((int)reader["ID"] == AClasificacionCorte.Id) return false; else return true; } } finally { if (con.State == System.Data.ConnectionState.Open) { con.Close(); } } return false; }
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 Produto buscarCodmat(String codmat) { SigletonConexaoFB.carregaStrcnx(); Produto resultado = new Produto(); FbCommand command = new FbCommand(); FbDataReader reader; try { command.Connection = SigletonConexaoFB.getConexao(); command.CommandText = "SELECT CODMAT,DESCRICAO,UNID FROM TB_MATERIAL " + "WHERE CODMAT='" + codmat + "'"; command.Connection.Open(); reader = command.ExecuteReader(); if (reader.Read()) { resultado.Codmat = reader["CODMAT"].ToString(); resultado.Descricao = reader["DESCRICAO"].ToString(); resultado.Unid = reader["UNID"].ToString(); } reader.Close(); } catch (Exception ex) { throw new Exception("Erro na busca de produto pelo código: " + ex.Message); } finally { command.Connection.Close(); } return resultado; }
public TcPedidoCancelamento BuscaDadosParaCancelamento(FbConnection Conn, string sCodCancelamento, string sSequencia) { try { StringBuilder sQuery = new StringBuilder(); sQuery.Append("select cidades.cd_municipio, empresa.cd_cgc Cnpj, empresa.cd_inscrmu, "); sQuery.Append("coalesce(nf.cd_numero_nfse,'')cd_numero_nfse "); sQuery.Append("from nf inner join empresa on nf.cd_empresa = empresa.cd_empresa "); sQuery.Append("inner join cidades on (cidades.nm_cidnor = empresa.nm_cidnor) "); sQuery.Append("where nf.cd_nfseq = '" + sSequencia + "' and "); sQuery.Append("nf.cd_empresa = '" + belStatic.codEmpresaNFe + "'"); FbCommand Command = new FbCommand(sQuery.ToString(), Conn); FbDataReader dr = Command.ExecuteReader(); dr.Read(); TcPedidoCancelamento objCancelamento = new TcPedidoCancelamento(); objCancelamento.InfPedidoCancelamento = new tcInfPedidoCancelamento(); objCancelamento.InfPedidoCancelamento.CodigoCancelamento = sCodCancelamento; objCancelamento.InfPedidoCancelamento.IdentificacaoNfse = new tcIdentificacaoNfse(); objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.CodigoMunicipio = dr["cd_municipio"].ToString(); objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.Numero = dr["cd_numero_nfse"].ToString(); objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.Cnpj = dr["Cnpj"].ToString(); objCancelamento.InfPedidoCancelamento.IdentificacaoNfse.InscricaoMunicipal = dr["cd_inscrmu"].ToString(); return objCancelamento; } catch (Exception) { throw; } }
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 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; }
public override DataTable GetSchemaTable() { this.CheckState(); if (this.schemaTable != null) { return(this.schemaTable); } #region Variables DataRow schemaRow = null; int tableCount = 0; string currentTable = string.Empty; this.schemaTable = GetSchemaTableStructure(); const Int16 batchLimit = 90; //Could be adjusted as needed. Int16 paramCounter = 0; //Counter for the whole batch process Int16 batchRounds = 0; //counter for each batch (limited by batchlimit) Hashtable relationList = new Hashtable(); //HashTable to store the query's unique Field Tables Names. List <RDBTableInfo> fieldList = new List <RDBTableInfo>(this.fields.Count + 1); //List to store the whole statement Schema Field Values. const Int16 metadataColSize = 31; //Firebird MAX Column Size. Int16 batchID = 0; //Batch marker. When batchlimit reaches its limit it increases by one the value. StringBuilder sb = new StringBuilder(); //Stores dynamic generated schema query. #endregion // Prepare statement for schema fields information //Asign current active schema command connection and transaccion FbCommand schemaCmd = new FbCommand(); schemaCmd.Connection = this.command.Connection; schemaCmd.Transaction = this.command.Connection.InnerConnection.ActiveTransaction; for (paramCounter = 0; paramCounter < this.FieldCount; paramCounter++) { if (batchRounds >= batchLimit) //Process field params until batch limit is reached. { batchID++; batchRounds = 0; } RDBTableInfo rdbinfo = new RDBTableInfo(); rdbinfo.Ordinal = paramCounter; rdbinfo.FieldName = this.fields[paramCounter].Name; rdbinfo.RelationName = this.fields[paramCounter].Relation; rdbinfo.BatchID = batchID; fieldList.Add(rdbinfo); batchRounds++; } //Process batch schema query for (Int16 i = 0; i <= batchID; i++) { sb.Length = 0; relationList.Clear(); List <RDBTableInfo> rdblBatch = new List <RDBTableInfo>(this.fields.Count + 1); //Find all RDBTableInfo elements according to batchID rdblBatch = fieldList.FindAll(rdbti => rdbti.BatchID == i); //Just add the needed tables according to the fieldnames on the current batch. for (Int16 j = 0; j < rdblBatch.Count; j++) { //Keep a list of unique relation names (tables) from all the fieldlist. if (!relationList.ContainsValue(rdblBatch[j].RelationName)) { relationList.Add(relationList.Count, rdblBatch[j].RelationName); } } if (schemaCmd.Parameters.Count > 0) //Clear previous command parameters. { schemaCmd.Parameters.Clear(); } //Get the Base Squema query to start generating Dynamic Schema query sb.Append(GetSchemaCommandTextBase()); //Perform batch field query against table schema //Add relation (table names) to schemaCmd for (int j = 0; j < relationList.Count; j++) { if (j > 0) //More than one table in query statement { sb.Append(" OR "); } List <RDBTableInfo> tmpList = rdblBatch.FindAll(rdbti => rdbti.RelationName.Equals(relationList[j])); sb.AppendFormat(" (rfr.rdb$field_name in {0} AND rfr.rdb$relation_name='{1}') ", GetParamExpression(tmpList.Count), relationList[j]); for (int k = 0; k < tmpList.Count; k++) { schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, metadataColSize).Value = tmpList[k].FieldName; } tmpList = null; } //set order to schema query sb.Append(" ORDER BY rfr.rdb$relation_name, rfr.rdb$field_position"); schemaCmd.CommandText = sb.ToString(); schemaCmd.Prepare(); schemaTable.BeginLoadData(); //Reset Column Values int Ordinal = 0; int batchCount = 0; //perform batch query using (FbDataReader r = schemaCmd.ExecuteReader()) { batchCount = 0;//reset batch counter while (r.Read()) { rdblBatch[batchCount].isReadOnly = (IsReadOnly(r) || IsExpression(r)) ? true : false; rdblBatch[batchCount].isKeyColumn = (r.GetInt32(2) == 1) ? true : false; rdblBatch[batchCount].isUnique = (r.GetInt32(3) == 1) ? true : false; rdblBatch[batchCount].precision = r.IsDBNull(4) ? -1 : r.GetInt32(4); rdblBatch[batchCount].isExpression = IsExpression(r); batchCount++; } } for (int j = 0; j < rdblBatch.Count; j++) { Ordinal = rdblBatch[j].Ordinal; // Create new row for the Schema Table schemaRow = schemaTable.NewRow(); schemaRow["ColumnName"] = this.GetName(Ordinal); schemaRow["ColumnOrdinal"] = Ordinal; schemaRow["ColumnSize"] = this.fields[Ordinal].GetSize(); if (fields[Ordinal].IsDecimal()) { schemaRow["NumericPrecision"] = schemaRow["ColumnSize"]; if (rdblBatch[j].precision > 0) { schemaRow["NumericPrecision"] = rdblBatch[j].precision; } schemaRow["NumericScale"] = this.fields[Ordinal].NumericScale * (-1); } schemaRow["DataType"] = this.GetFieldType(Ordinal); schemaRow["ProviderType"] = this.GetProviderType(Ordinal); schemaRow["IsLong"] = this.fields[Ordinal].IsLong(); schemaRow["AllowDBNull"] = this.fields[Ordinal].AllowDBNull(); schemaRow["IsRowVersion"] = false; schemaRow["IsAutoIncrement"] = false; schemaRow["IsReadOnly"] = rdblBatch[j].isReadOnly; schemaRow["IsKey"] = rdblBatch[j].isKeyColumn; schemaRow["IsUnique"] = rdblBatch[j].isUnique; schemaRow["IsAliased"] = this.fields[Ordinal].IsAliased(); schemaRow["IsExpression"] = rdblBatch[j].isExpression; schemaRow["BaseSchemaName"] = DBNull.Value; schemaRow["BaseCatalogName"] = DBNull.Value; schemaRow["BaseTableName"] = this.fields[Ordinal].Relation; schemaRow["BaseColumnName"] = this.fields[Ordinal].Name; schemaTable.Rows.Add(schemaRow); if (!String.IsNullOrEmpty(this.fields[Ordinal].Relation) && currentTable != this.fields[Ordinal].Relation) { tableCount++; currentTable = this.fields[Ordinal].Relation; } } schemaTable.EndLoadData(); rdblBatch = null; }//Finish Batch Round Iteration schemaCmd.Close(); if (tableCount > 1) { foreach (DataRow row in schemaTable.Rows) { row["IsKey"] = false; row["IsUnique"] = false; } } //Dispose command schemaCmd.Dispose(); relationList = null; fieldList = null; return(schemaTable); }
public override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig) { if (datasetConfig.DatasetTableName != "") //Add the posibility to use a specific table_name (for using the table more then ones) { this.dataTable = datasetConfig.DatasetTableName; } else { this.dataTable = dataTable; } this.datasetConfig = datasetConfig; this.fieldList = datasetConfig.DatasetConfigRows; //Look if Table exists, when not, create it! try { string sql = "SELECT * FROM " + dataTable + ";"; myCmd = new FbCommand(sql, myDBConn); myReader = myCmd.ExecuteReader(); } catch (FbException ex) { /*if (ex.ErrorCode == 0) * { * try * { * string sql = "CREATE TABLE " + dataTable + " (id INTEGER PRIMARY KEY ASC AUTOINCREMENT); "; * * myCmd = new FbCommand(sql, myDBConn); * myCmd.ExecuteNonQuery(); * * sql = "SELECT * FROM " + dataTable + ";"; * myCmd = new FbCommand(sql, myDBConn); * myReader = myCmd.ExecuteReader(); * } * catch (System.Data.SQLite.SQLiteException ex_ex) * { * throw ex_ex; * } * } * else * { * throw ex; * }*/ } //Look for the Fields, create or alter them! List <String> existDBFelderliste = new List <string>(); for (int n = 0; n < myReader.FieldCount; n++) { existDBFelderliste.Add(myReader.GetName(n)); } myReader.Close(); foreach (DatasetConfigRow myFeld in fieldList) { foreach (string existMyFeld in existDBFelderliste) { if (myFeld.DatabaseField.ToLower() == existMyFeld.ToLower()) { goto nextFeld; } } //Feld existiert nicht -> erzeugen string sql = "ALTER TABLE " + dataTable + " ADD COLUMN " + myFeld.DatabaseField + " " + myFeld.DatabaseFieldType; try { myCmd = new FbCommand(sql, myDBConn); myCmd.ExecuteNonQuery(); } catch (FbException ex) { throw ex; } nextFeld: //Irgendeine anweisung, da sonst der Sprung nicht geht... { } } //Create Insert Command string wertliste = "", felderliste = ""; foreach (DatasetConfigRow myFeld in fieldList) { if (wertliste != "") { wertliste += ","; felderliste += ","; } felderliste += myFeld.DatabaseField; wertliste += "@" + myFeld.DatabaseField; } insertCommand = "INSERT INTO " + this.dataTable + "(" + felderliste + ") values(" + wertliste + ")"; }
public override DataTable GetSchemaTable() { CheckState(); if (_schemaTable != null) { return(_schemaTable); } DataRow schemaRow = null; var tableCount = 0; var currentTable = string.Empty; _schemaTable = GetSchemaTableStructure(); /* Prepare statement for schema fields information */ var schemaCmd = new FbCommand(GetSchemaCommandText(), _command.Connection, _command.Connection.InnerConnection.ActiveTransaction); try { schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31); schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31); schemaCmd.Prepare(); _schemaTable.BeginLoadData(); for (var i = 0; i < _fields.Count; i++) { var isKeyColumn = false; var isUnique = false; var isReadOnly = false; var precision = 0; var isExpression = false; /* Get Schema data for the field */ schemaCmd.Parameters[0].Value = _fields[i].Relation; schemaCmd.Parameters[1].Value = _fields[i].Name; var reader = schemaCmd.ExecuteReader(CommandBehavior.Default); try { if (reader.Read()) { isReadOnly = (IsReadOnly(reader) || IsExpression(reader)) ? true : false; isKeyColumn = (reader.GetInt32(2) == 1) ? true : false; isUnique = (reader.GetInt32(3) == 1) ? true : false; precision = reader.IsDBNull(4) ? -1 : reader.GetInt32(4); isExpression = IsExpression(reader); } } finally { #if NET48 || NETSTANDARD2_0 reader.Dispose(); #else reader.Dispose(); #endif } /* Create new row for the Schema Table */ schemaRow = _schemaTable.NewRow(); schemaRow["ColumnName"] = GetName(i); schemaRow["ColumnOrdinal"] = i; schemaRow["ColumnSize"] = _fields[i].GetSize(); if (_fields[i].IsDecimal()) { schemaRow["NumericPrecision"] = schemaRow["ColumnSize"]; if (precision > 0) { schemaRow["NumericPrecision"] = precision; } schemaRow["NumericScale"] = _fields[i].NumericScale * (-1); } schemaRow["DataType"] = GetFieldType(i); schemaRow["ProviderType"] = GetProviderType(i); schemaRow["IsLong"] = _fields[i].IsLong(); schemaRow["AllowDBNull"] = _fields[i].AllowDBNull(); schemaRow["IsRowVersion"] = false; schemaRow["IsAutoIncrement"] = false; schemaRow["IsReadOnly"] = isReadOnly; schemaRow["IsKey"] = isKeyColumn; schemaRow["IsUnique"] = isUnique; schemaRow["IsAliased"] = _fields[i].IsAliased(); schemaRow["IsExpression"] = isExpression; schemaRow["BaseSchemaName"] = DBNull.Value; schemaRow["BaseCatalogName"] = DBNull.Value; schemaRow["BaseTableName"] = _fields[i].Relation; schemaRow["BaseColumnName"] = _fields[i].Name; _schemaTable.Rows.Add(schemaRow); if (!string.IsNullOrEmpty(_fields[i].Relation) && currentTable != _fields[i].Relation) { tableCount++; currentTable = _fields[i].Relation; } schemaCmd.Close(); } if (tableCount > 1) { foreach (DataRow row in _schemaTable.Rows) { row["IsKey"] = false; row["IsUnique"] = false; } } _schemaTable.EndLoadData(); } finally { #if NET48 || NETSTANDARD2_0 schemaCmd.Dispose(); #else schemaCmd.Dispose(); #endif } return(_schemaTable); }
public override DataTable GetSchemaTable() { this.CheckState(); if (this.schemaTable != null) { return(this.schemaTable); } DataRow schemaRow = null; int tableCount = 0; string currentTable = string.Empty; this.schemaTable = this.GetSchemaTableStructure(); /* Prepare statement for schema fields information */ FbCommand schemaCmd = new FbCommand( this.GetSchemaCommandText(), this.command.Connection, this.command.Connection.InnerConnection.ActiveTransaction); schemaCmd.Parameters.Add("@TABLE_NAME", FbDbType.Char, 31); schemaCmd.Parameters.Add("@COLUMN_NAME", FbDbType.Char, 31); schemaCmd.Prepare(); schemaTable.BeginLoadData(); for (int i = 0; i < this.fields.Count; i++) { bool isKeyColumn = false; bool isUnique = false; bool isReadOnly = false; int precision = 0; bool isExpression = false; /* Get Schema data for the field */ schemaCmd.Parameters[0].Value = this.fields[i].Relation; schemaCmd.Parameters[1].Value = this.fields[i].Name; using (FbDataReader r = schemaCmd.ExecuteReader()) { if (r.Read()) { isReadOnly = (this.IsReadOnly(r) || this.IsExpression(r)) ? true : false; isKeyColumn = (r.GetInt32(2) == 1) ? true : false; isUnique = (r.GetInt32(3) == 1) ? true : false; precision = r.IsDBNull(4) ? -1 : r.GetInt32(4); isExpression = this.IsExpression(r); } } /* Create new row for the Schema Table */ schemaRow = schemaTable.NewRow(); schemaRow["ColumnName"] = this.GetName(i); schemaRow["ColumnOrdinal"] = i; schemaRow["ColumnSize"] = this.fields[i].GetSize(); if (fields[i].IsDecimal()) { schemaRow["NumericPrecision"] = schemaRow["ColumnSize"]; if (precision > 0) { schemaRow["NumericPrecision"] = precision; } schemaRow["NumericScale"] = this.fields[i].NumericScale * (-1); } schemaRow["DataType"] = this.GetFieldType(i); schemaRow["ProviderType"] = this.GetProviderType(i); schemaRow["IsLong"] = this.fields[i].IsLong(); schemaRow["AllowDBNull"] = this.fields[i].AllowDBNull(); schemaRow["IsRowVersion"] = false; schemaRow["IsAutoIncrement"] = false; schemaRow["IsReadOnly"] = isReadOnly; schemaRow["IsKey"] = isKeyColumn; schemaRow["IsUnique"] = isUnique; schemaRow["IsAliased"] = this.fields[i].IsAliased(); schemaRow["IsExpression"] = isExpression; schemaRow["BaseSchemaName"] = DBNull.Value; schemaRow["BaseCatalogName"] = DBNull.Value; schemaRow["BaseTableName"] = this.fields[i].Relation; schemaRow["BaseColumnName"] = this.fields[i].Name; schemaTable.Rows.Add(schemaRow); if (!String.IsNullOrEmpty(this.fields[i].Relation) && currentTable != this.fields[i].Relation) { tableCount++; currentTable = this.fields[i].Relation; } /* Close statement */ schemaCmd.Close(); } if (tableCount > 1) { foreach (DataRow row in schemaTable.Rows) { row["IsKey"] = false; row["IsUnique"] = false; } } schemaTable.EndLoadData(); /* Dispose command */ schemaCmd.Dispose(); return(schemaTable); }