private void cboDatabaseList_TextChanged(object sender, EventArgs e) { TdConnectionStringBuilder stringBuilder = new TdConnectionStringBuilder(); stringBuilder.CommandTimeout = 300; stringBuilder.ConnectionTimeout = 100; stringBuilder.DataSource = host; stringBuilder.UserId = uid; stringBuilder.Password = pwd; using (TdConnection dbConnection = new TdConnection(stringBuilder.ConnectionString)) { dbConnection.Open(); TdDataAdapter adapter = new TdDataAdapter(); DataTable dt = new DataTable(); TdCommand myCommand = new TdCommand("select tablename from dbc.TablesVX where databasename='" + cboDatabaseList.Text + "'", dbConnection); TdDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { cboTables.Items.Add(myReader[0].ToString().Trim()); } myReader.Close(); myCommand.Dispose(); } }
private void populateComboes() { TdConnectionStringBuilder stringBuilder = new TdConnectionStringBuilder(); stringBuilder.CommandTimeout = 300; stringBuilder.ConnectionTimeout = 100; stringBuilder.DataSource = host; stringBuilder.UserId = uid; stringBuilder.Password = pwd; using (TdConnection dbConnection = new TdConnection(stringBuilder.ConnectionString)) { dbConnection.Open(); TdDataAdapter adapter = new TdDataAdapter(); DataTable dt = new DataTable(); TdCommand myCommand = new TdCommand("select databasename from dbc.databases", dbConnection); TdDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { cboDatabaseList.Items.Add(myReader[0].ToString().Trim()); } myReader.Close(); myCommand.Dispose(); } }
private void RunTeradata(List <SQLResult> result, string cmd) { try { TdCommand toGo = this.TdConnection.CreateCommand(); toGo.CommandTimeout = 3600 * 12; toGo.CommandText = cmd; TdDataReader reader = toGo.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { int nCol = reader.FieldCount; SQLResult newItem = new SQLResult(); if (nCol > 0) { newItem.Column0 = reader.IsDBNull(0) ? String.Empty : reader.GetValue(0).ToString(); } if (nCol > 1) { newItem.Column1 = reader.IsDBNull(1) ? String.Empty : reader.GetValue(1).ToString(); } if (nCol > 2) { newItem.Column2 = reader.IsDBNull(2) ? String.Empty : reader.GetValue(2).ToString(); } if (nCol > 3) { newItem.Column3 = reader.IsDBNull(3) ? String.Empty : reader.GetValue(3).ToString(); } if (nCol > 4) { newItem.Column4 = reader.IsDBNull(4) ? String.Empty : reader.GetValue(4).ToString(); } if (nCol > 5) { newItem.Column5 = reader.IsDBNull(5) ? String.Empty : reader.GetValue(5).ToString(); } if (nCol > 6) { newItem.Column6 = reader.IsDBNull(6) ? String.Empty : reader.GetValue(6).ToString(); } result.Add(newItem); } } reader.Close(); toGo.Dispose(); } catch (Exception ex) { throw; } }
public List <NumeralCambiario> Get_NumeralCambiarioDAL() { List <NumeralCambiario> list = new List <NumeralCambiario>(); using (TdConnection oSqlConnection = new TdConnection(Cnn)) { try { oSqlConnection.Open(); using (TdCommand oSqlCmd = new TdCommand()) { oSqlCmd.Parameters.Clear(); oSqlCmd.CommandText = "Select * from " + @instancia + ".V_D_RCNumeralCambiario ORDER BY Desc_NumeralCambiario "; oSqlCmd.CommandTimeout = 30; oSqlCmd.Connection = oSqlConnection; TdDataReader oReader = oSqlCmd.ExecuteReader(); if (oReader != null) { if (oReader.HasRows) { while (oReader.Read()) { list.Add(new NumeralCambiario() { Sk_RCNumeralCambiario = int.Parse(oReader["Sk_RCNumeralCambiario"].ToString()), Desc_NumeralCambiario = oReader["Desc_NumeralCambiario"].ToString(), }); } oReader.Close(); } oReader.Dispose(); } } oSqlConnection.Close(); } catch (SqlException ex) { throw ex; } catch (IndexOutOfRangeException ex) { throw ex; } catch (TdException ex) { throw ex; } catch (FormatException ex) { throw ex; } } return(list); }
public int GetHistoryid(int DefectID) { //Define the Command Object int ID = 0; string defect1 = ""; //IDDefect = Convert.ToInt32(DefectID); TdConnection con = new TdConnection(strConnString); Teradata.Client.Provider.TdCommand AdvancedDefectSearch = new Teradata.Client.Provider.TdCommand("Select max(defecthistid)+ 1 as test from coebatch.tbl_Defect_history where DefectID = " + DefectID + ";", con); try { //Open the Connection to connect to the database con.Open(); //Execute the Query //AdvancedDefectSearch.ExecuteNonQuery(); TdDataReader r = AdvancedDefectSearch.ExecuteReader(); if (r.Read()) { defect1 = r["test"].ToString(); } r.Close(); if (defect1 == "") { con.Close(); return(ID); } else { ID = Convert.ToInt32(defect1); return(ID); } // nextDefectId = (int)cmdNewDefect.ExecuteScalar(); } catch (TdException ex) { return(ID); throw ex; } finally { //Close the Connection from the Database con.Close(); } }
public int GetDefectcount() { string query = ""; int ID = 0; TdCommand cmd = new TdCommand(query); TdConnection con = new TdConnection(strConnString); Teradata.Client.Provider.TdCommand AdvancedDefectSearch = new Teradata.Client.Provider.TdCommand("select count(*) as test from coebatch.tbl_defect ", con); try { //Open the Connection to connect to the database con.Open(); //Execute the Query //AdvancedDefectSearch.ExecuteNonQuery(); TdDataReader r = AdvancedDefectSearch.ExecuteReader(); if (r.Read()) { query = r["test"].ToString(); } r.Close(); if (query == "") { con.Close(); return(ID); } else { ID = Convert.ToInt32(query); } // nextDefectId = (int)cmdNewDefect.ExecuteScalar(); } catch (TdException ex) { return(ID); } finally { //Close the Connection from the Database con.Close(); } return(ID); }
public int GetNextDefectID() { TdConnection con = new TdConnection(strConnString); Teradata.Client.Provider.TdCommand cmdNewDefect = new Teradata.Client.Provider.TdCommand("SELECT MAX(DefectId)+1 as test FROM coebatch.tbl_Defect", con); int nextDefectId = 0; string defectid = ""; try { //Open the Connection to connect to the database con.Open(); //Execute the Query //nextDefectId = (int)cmdNewDefect.ExecuteScalar(); TdDataReader r = cmdNewDefect.ExecuteReader(); if (r.Read()) { defectid = r["test"].ToString(); } r.Close(); // nextDefectId = cmdNewDefect.ExecuteScalar(); if (defectid == "") { nextDefectId = 1; } else { nextDefectId = Convert.ToInt32(defectid); } } catch (Teradata.Client.Provider.TdException ex) { //Throw the exception to calling environment throw ex; } finally { //Close the Connection from the Database con.Close(); } return(nextDefectId); }
public List <ResultadoNodo> Get_RC_EstructuraAgregacionNumeralesByIdNameDAL(int Id, string desc) { List <ResultadoNodo> list = new List <ResultadoNodo>(); using (TdConnection oSqlConnection = new TdConnection(Cnn)) { try { oSqlConnection.Open(); using (TdCommand oSqlCmd = new TdCommand()) { oSqlCmd.Parameters.Clear(); oSqlCmd.CommandText = "SEL NOD.Sk_NodoContable Sk_NodoContable, NOD.Desc_NodoContable, NOD.Id_NodoContable, COALESCE(PAD.Id_NodoContable, 0) Id_NodoContablePadre, PAD.Desc_NodoContable Desc_NodoContablePadre ," + " MAX(CASE WHEN TRIM(AGR.Id_Fuente)='1' THEN agr.Sk_RCNumeralCambiario ELSE 0 END ) Sk_RCNumeralCambiario, NOD.Num_Nivel,NOD.Num_Orden," + " MAX(CASE WHEN TRIM(AGR.Id_Fuente)='2' THEN AGR.Sk_RCNumeralCambiario ELSE 0 END) idnumeralcco FROM " + @instancia + ".V_RC_EstructuraAgregacionNumerales EST" + " JOIN " + @instancia + ".V_RC_NodoContableAgregacionNumerales NOD " + " ON EST.Id_Estructura = NOD.Id_Estructura" + " LEFT JOIN " + @instancia + ".V_RC_NodoContableAgregacionNumerales PAD" + " ON NOD.sk_NodoContablePadre = PAD.sk_NodoContable" + " LEFT JOIN " + @instancia + ".V_RC_Rel_NodoAgrNumerales_NumeralCambiario AGR" + " ON AGR.Sk_NodoContable = NOD.Sk_NodoContable" + " where EST.Id_Estructura = ? and EST.Desc_Estructura = ? and EST.Cb_Eliminado <> 'S' and NOD.Cb_eliminado <> 'S' order by NOD.Num_Nivel,NOD.Num_Orden" + " GROUP BY 1,2,3,4,5,7,8;"; oSqlCmd.CommandType = CommandType.Text; oSqlCmd.CommandTimeout = 30; oSqlCmd.Connection = oSqlConnection; TdParameter idP = oSqlCmd.CreateParameter(); idP.DbType = DbType.Int64; idP.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(idP); idP.Value = Id; TdParameter descP = oSqlCmd.CreateParameter(); descP.DbType = DbType.String; descP.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(descP); descP.Value = desc; oSqlCmd.Prepare(); TdDataReader oReader = oSqlCmd.ExecuteReader(); if (oReader != null) { if (oReader.HasRows) { while (oReader.Read()) { ResultadoNodo nodo = new ResultadoNodo(); nodo.Sk_NodoContable = int.Parse(oReader["Sk_NodoContable"].ToString()); nodo.name = oReader["Desc_NodoContable"].ToString(); nodo.Id_NodoContable = int.Parse(oReader["Id_NodoContable"].ToString()); nodo.Id_NodoContablePadre = int.Parse(oReader["Id_NodoContablePadre"].ToString()); nodo.Desc_NodoContablePadre = oReader["Desc_NodoContablePadre"].ToString(); nodo.Sk_RCNumeralCambiario = int.Parse(oReader["Sk_RCNumeralCambiario"].ToString()); nodo.level = int.Parse(oReader["Num_Nivel"].ToString()); nodo.idnumeralcco = int.Parse(oReader["idnumeralcco"].ToString()); nodo.Sk_NodoContable = int.Parse(oReader["Sk_NodoContable"].ToString()); nodo.orden = int.Parse(oReader["Num_Orden"].ToString()); nodo.formulacion = formulacionDAL.Get_RC_EstructuraAgregacionNumeralesByIdNameDAL(nodo.Sk_NodoContable); list.Add(nodo); } oReader.Close(); } oReader.Dispose(); } } oSqlConnection.Close(); return(list); } catch (SqlException ex) { throw ex; } catch (IndexOutOfRangeException ex) { throw ex; } catch (TdException ex) { throw ex; } catch (FormatException ex) { throw ex; } } }
public List <RC_ResultadoAgregacionNumerales> Get_RC_ResultadoAgregacionNumeralesBySkDAL(int Id_Estructura, int Sk_Consulta, int Id_Periodicidad) { List <RC_ResultadoAgregacionNumerales> list = new List <RC_ResultadoAgregacionNumerales>(); using (TdConnection oSqlConnection = new TdConnection(Cnn)) { try { oSqlConnection.Open(); using (TdCommand oSqlCmd = new TdCommand()) { oSqlCmd.Parameters.Clear(); if (Id_Periodicidad == 1) { oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta," + " DG.Anio ||'-'|| CASE WHEN LENGTH (TRIM(DG.Id_Mes))=1 THEN '0' || TRIM(DG.Id_Mes) ELSE TRIM(DG.Id_Mes) END ||'-'|| CASE WHEN LENGTH(TRIM(DG.DiaDelMes)) = 1 THEN '0' || TRIM(DG.DiaDelMes) ELSE TRIM(DG.DiaDelMes) end Fecha_DeclaracionInicial, " + "NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " + " from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " + " INNER JOIN " + instancia + ".V_DG_Fecha DG " + " ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " + " INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " + " ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " + " INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " + " ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " + " WHERE NOD.Id_Estructura= " + Id_Estructura + "" + " AND RAN.Sk_Consulta = " + Sk_Consulta + " " + " GROUP BY 1,2,3,4,5,6,7 " + " ORDER BY 1, 6 desc"; } else if (Id_Periodicidad == 2) { oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta," + " DG.Anio ||'-'|| CASE WHEN LENGTH (TRIM(DG.id_semana))=1 THEN '0' || TRIM(DG.id_semana) else TRIM(DG.id_semana) END Fecha_DeclaracionInicial, NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " + " from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " + " INNER JOIN " + instancia + ".V_DG_Fecha DG " + " ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " + " INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " + " ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " + " INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " + " ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " + " WHERE NOD.Id_Estructura= " + Id_Estructura + "" + " AND RAN.Sk_Consulta = " + Sk_Consulta + " " + " GROUP BY 1,2,3,4,5,6,7 " + " ORDER BY 1, 6 desc"; } else if (Id_Periodicidad == 3) { oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta," + " DG.Anio ||'-'|| CASE WHEN LENGTH (TRIM(DG.Id_Mes))=1 THEN '0' || TRIM(DG.Id_Mes) ELSE TRIM(DG.Id_Mes) end Fecha_DeclaracionInicial, " + "NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " + " from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " + " INNER JOIN " + instancia + ".V_DG_Fecha DG " + " ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " + " INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " + " ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " + " INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " + " ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " + " WHERE NOD.Id_Estructura= " + Id_Estructura + "" + " AND RAN.Sk_Consulta = " + Sk_Consulta + " " + " GROUP BY 1,2,3,4,5,6,7 " + " ORDER BY 1, 6 desc"; } else if (Id_Periodicidad == 4) { oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta, " + "DG.Anio || '-' || CASE WHEN trim(DG.Id_Trimestre) = '1' THEN 'I ' WHEN trim(DG.Id_Trimestre) = '2' THEN 'II ' WHEN trim(DG.Id_Trimestre) = '3' THEN 'III ' WHEN trim(DG.Id_Trimestre) = '4' THEN 'IV ' END Fecha_DeclaracionInicial," + "NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " + " from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " + " INNER JOIN " + instancia + ".V_DG_Fecha DG " + " ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " + " INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " + " ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " + " INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " + " ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " + " WHERE NOD.Id_Estructura= " + Id_Estructura + "" + " AND RAN.Sk_Consulta = " + Sk_Consulta + " " + " GROUP BY 1,2,3,4,5,6,7 " + " ORDER BY 1, 6 desc"; } else if (Id_Periodicidad == 5) { oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta, " + "DG.ANIO || '-'|| CASE WHEN trim(DG.Id_Semestre)='1' THEN 'I' WHEN trim(DG.Id_Semestre) = '2' THEN 'II' END Fecha_DeclaracionInicial," + " NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " + " from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " + " INNER JOIN " + instancia + ".V_DG_Fecha DG " + " ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " + " INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " + " ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " + " INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " + " ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " + " WHERE NOD.Id_Estructura= " + Id_Estructura + "" + " AND RAN.Sk_Consulta = " + Sk_Consulta + " " + " GROUP BY 1,2,3,4,5,6,7 " + " ORDER BY 1, 6 desc"; } else if (Id_Periodicidad == 6) { oSqlCmd.CommandText = " SEL NOD.Num_Orden , NOD.Id_NodoContable Id_NodoContable, RAN.Id_Estructura, RAN.Sk_NodoContable, RAN.Fecha_Consulta," + " SUBSTR(CAST(Sk_Fecha AS VARCHAR(4)),1,2 ) ||'-'|| SUBSTR(CAST(Sk_Fecha AS VARCHAR(4)),3,4 ) Fecha_DeclaracionInicial, " + " NOD.desc_NodoContable desc_NodoContable, SUM(Cv_ValorUSD) Cv_ValorUSD " + " from " + @instancia + ".V_RC_ResultadoAgregacionNumerales RAN " + " INNER JOIN " + instancia + ".V_DG_Fecha DG " + " ON (DG.DiaDate=RAN.Fecha_DeclaracionInicial) " + " INNER JOIN " + instancia + ".V_RC_ConsultaAgregacionNumerales CAN " + " ON(CAN.Sk_Consulta=RAN.Sk_Consulta) " + " INNER JOIN " + instancia + ".V_RC_NodoContableAgregacionNumerales NOD " + " ON (NOD.Id_Estructura=RAN.Id_Estructura AND Nod.Sk_NodoContable = RAN.Sk_NodoContable) " + " WHERE NOD.Id_Estructura= " + Id_Estructura + "" + " AND RAN.Sk_Consulta = " + Sk_Consulta + " " + " GROUP BY 1,2,3,4,5,6,7 " + " ORDER BY 1, 6 desc"; } oSqlCmd.CommandTimeout = 30; oSqlCmd.Connection = oSqlConnection; TdDataReader oReader = oSqlCmd.ExecuteReader(); if (oReader != null) { if (oReader.HasRows) { while (oReader.Read()) { RC_ResultadoAgregacionNumerales item = new RC_ResultadoAgregacionNumerales(); item.Id_NodoContable = int.Parse(oReader["Id_NodoContable"].ToString()); item.Id_Estructura = int.Parse(oReader["Id_Estructura"].ToString()); item.Desc_NodoContable = oReader["Desc_NodoContable"].ToString(); item.Fecha_Consulta = DateTime.Parse(oReader["Fecha_Consulta"].ToString()); item.Fecha_DeclaracionInicial = oReader["Fecha_DeclaracionInicial"].ToString(); item.Cv_ValorUSD = decimal.Parse(oReader["Cv_ValorUSD"].ToString()); list.Add(item); } oReader.Close(); } oReader.Dispose(); } } oSqlConnection.Close(); } catch (SqlException ex) { throw ex; } catch (IndexOutOfRangeException ex) { throw ex; } catch (TdException ex) { throw ex; } catch (FormatException ex) { throw ex; } } return(list); }
public List <RC_PeriodicidadAgregacionNumerales> Get_RC_PeriodicidadAgregacionNumeralesDAL(int id_fuente) { List <RC_PeriodicidadAgregacionNumerales> list = new List <RC_PeriodicidadAgregacionNumerales>(); using (TdConnection oSqlConnection = new TdConnection(Cnn)) { try { oSqlConnection.Open(); using (TdCommand oSqlCmd = new TdCommand()) { oSqlCmd.Parameters.Clear(); if (id_fuente == 1) { oSqlCmd.CommandText = "Select * from " + @instancia + ".V_RC_PeriodicidadAgregacionNumerales order by Desc_Periodicidad "; } else { oSqlCmd.CommandText = "Select * from " + @instancia + ".V_RC_PeriodicidadAgregacionNumerales where Id_Periodicidad >2 order by Desc_Periodicidad "; } oSqlCmd.CommandTimeout = 30; oSqlCmd.Connection = oSqlConnection; TdDataReader oReader = oSqlCmd.ExecuteReader(); if (oReader != null) { if (oReader.HasRows) { while (oReader.Read()) { RC_PeriodicidadAgregacionNumerales item = new RC_PeriodicidadAgregacionNumerales(); item.Id_Periodicidad = int.Parse(oReader["Id_Periodicidad"].ToString()); item.Desc_Periodicidad = oReader["Desc_Periodicidad"].ToString(); item.Sk_Lote = int.Parse(oReader["Sk_Lote"].ToString()); item.Sk_Lote_Upd = null; item.Cod_Severidad = int.Parse(oReader["Cod_Severidad"].ToString()); list.Add(item); } oReader.Close(); } oReader.Dispose(); } } oSqlConnection.Close(); } catch (SqlException ex) { throw ex; } catch (IndexOutOfRangeException ex) { throw ex; } catch (TdException ex) { throw ex; } catch (FormatException ex) { throw ex; } } return(list); }
public List <ResultadoFormulacion> Get_RC_EstructuraAgregacionNumeralesByIdNameDAL(int Sk_NodoContable) { List <ResultadoFormulacion> list = new List <ResultadoFormulacion>(); using (TdConnection oSqlConnection = new TdConnection(Cnn)) { try { oSqlConnection.Open(); using (TdCommand oSqlCmd = new TdCommand()) { oSqlCmd.Parameters.Clear(); oSqlCmd.CommandText = "SEL PAD.Id_NodoContable, PAD.Desc_NodoContable, FORM.Desc_Signo FROM " + @instancia + ".V_RC_NodoContableAgregacionNumerales NOD" + " JOIN " + @instancia + ".V_RC_FormulacionVerticalNodoagrNumerales FORM" + " ON FORM.Sk_NodoContable = NOD.Sk_NodoContable" + " LEFT JOIN " + @instancia + ".V_RC_NodoContableAgregacionNumerales PAD" + " ON FORM.Sk_NodoContableRelacionado = PAD.sk_NodoContable" + " where NOD.Sk_NodoContable = ? and NOD.Cb_Eliminado <> 'S' ;"; oSqlCmd.CommandType = CommandType.Text; oSqlCmd.CommandTimeout = 60; oSqlCmd.Connection = oSqlConnection; TdParameter idP = oSqlCmd.CreateParameter(); idP.DbType = DbType.Int64; idP.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(idP); idP.Value = Sk_NodoContable; oSqlCmd.Prepare(); TdDataReader oReader = oSqlCmd.ExecuteReader(); if (oReader != null) { if (oReader.HasRows) { while (oReader.Read()) { list.Add(new ResultadoFormulacion() { name = oReader["Desc_NodoContable"].ToString(), Id_NodoContable = int.Parse(oReader["Id_NodoContable"].ToString()), Signo = oReader["Desc_Signo"].ToString() }); } oReader.Close(); } oReader.Dispose(); } } oSqlConnection.Close(); } catch (SqlException ex) { throw ex; } catch (IndexOutOfRangeException ex) { throw ex; } catch (TdException ex) { throw ex; } catch (FormatException ex) { throw ex; } } return(list); }
public List <RC_ConsultaAgregacionNumerales> Get_RC_ConsultaAgregacionNumeralesBySkDAL(int Id_Estructura, int Sk_Consulta) { List <RC_ConsultaAgregacionNumerales> list = new List <RC_ConsultaAgregacionNumerales>(); using (TdConnection oSqlConnection = new TdConnection(Cnn)) { try { oSqlConnection.Open(); using (TdCommand oSqlCmd = new TdCommand()) { oSqlCmd.Parameters.Clear(); oSqlCmd.CommandText = "Select EAN.Desc_Estructura Desc_Estructura, PAN.Desc_Periodicidad Desc_Periodicidad, FAN.Desc_Fuente Desc_Fuente, CAN.* from " + @instancia + ".V_RC_ConsultaAgregacionNumerales CAN " + "INNER JOIN " + @instancia + ".V_RC_EstructuraAgregacionNumerales EAN" + " ON (EAN.Id_Estructura=CAN.Id_Estructura)" + " INNER JOIN " + instancia + ".V_RC_PeriodicidadAgregacionNumerales PAN" + " ON (trim(CAN.Id_Periodicidad)=TRIM(PAN.Id_Periodicidad))" + " INNER JOIN " + instancia + ".V_RC_FuenteAgregacionNumerales FAN" + " ON(trim(CAN.Id_Fuente)=TRIM(FAN.Id_Fuente))" + " Where CAN.Sk_Consulta=" + Sk_Consulta + "" + " AND CAN.Id_Estructura=" + Id_Estructura + ""; oSqlCmd.CommandTimeout = 30; oSqlCmd.Connection = oSqlConnection; TdDataReader oReader = oSqlCmd.ExecuteReader(); if (oReader != null) { if (oReader.HasRows) { while (oReader.Read()) { RC_ConsultaAgregacionNumerales item = new RC_ConsultaAgregacionNumerales(); item.Sk_Consulta = int.Parse(oReader["Sk_Consulta"].ToString()); item.Desc_Estructura = oReader["Desc_Estructura"].ToString(); item.Desc_Periodicidad = oReader["Desc_Periodicidad"].ToString(); item.Desc_Fuente = oReader["Desc_Fuente"].ToString(); item.Id_Estructura = int.Parse(oReader["Id_Estructura"].ToString()); item.Fecha_Consulta = DateTime.Parse(oReader["Fecha_Consulta"].ToString()); item.Id_Fuente = int.Parse(oReader["Id_Fuente"].ToString()); item.Fecha_Inicial = DateTime.Parse(oReader["Fecha_Inicial"].ToString()); item.Fecha_Final = DateTime.Parse(oReader["Fecha_Final"].ToString()); item.Id_Periodicidad = int.Parse(oReader["Id_Periodicidad"].ToString()); item.Nombre_UsuarioCreacion = oReader["Nombre_UsuarioCreacion"].ToString(); item.Fecha_Creacion = DateTime.Parse(oReader["Fecha_Creacion"].ToString()); item.Sk_Lote = int.Parse(oReader["Sk_Lote"].ToString()); item.Sk_Lote_Upd = null; item.Cod_Severidad = int.Parse(oReader["Cod_Severidad"].ToString()); list.Add(item); } oReader.Close(); } oReader.Dispose(); } } oSqlConnection.Close(); } catch (SqlException ex) { throw ex; } catch (IndexOutOfRangeException ex) { throw ex; } catch (TdException ex) { throw ex; } catch (FormatException ex) { throw ex; } } return(list); }
public List <RC_ConsultaAgregacionNumerales> Get_SkConsulta(int Id_Estructura, string Fecha_Consulta, int Id_Fuente, string Fecha_Inicial, string Fecha_Final, int Id_Periodicidad) { List <RC_ConsultaAgregacionNumerales> list = new List <RC_ConsultaAgregacionNumerales>(); using (TdConnection oSqlConnection = new TdConnection(Cnn)) { try { oSqlConnection.Open(); using (TdCommand oSqlCmd = new TdCommand()) { oSqlCmd.Parameters.Clear(); oSqlCmd.CommandText = "Select Sk_Consulta, Id_Estructura from " + @instancia + ".V_RC_ConsultaAgregacionNumerales " + " where Id_Estructura = ? and CAST(CAST(Fecha_Consulta AS DATE FORMAT 'yyyymmdd' ) AS VARCHAR(8)) = ?" + " AND Id_Fuente= ? AND CAST(CAST(Fecha_Inicial AS DATE FORMAT 'yyyymmdd' ) AS VARCHAR(8))=? AND " + "CAST(CAST(Fecha_Final AS DATE FORMAT 'yyyymmdd' ) AS VARCHAR(8))=? " + " AND Id_Periodicidad=? ;"; oSqlCmd.CommandType = CommandType.Text; oSqlCmd.CommandTimeout = 30; oSqlCmd.Connection = oSqlConnection; TdParameter Id_EstructuraP = oSqlCmd.CreateParameter(); Id_EstructuraP.DbType = DbType.String; Id_EstructuraP.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(Id_EstructuraP); Id_EstructuraP.Value = Id_Estructura; TdParameter Fecha_ConsultaP = oSqlCmd.CreateParameter(); Fecha_ConsultaP.DbType = DbType.String; Fecha_ConsultaP.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(Fecha_ConsultaP); Fecha_ConsultaP.Value = Fecha_Consulta; TdParameter Id_FuenteP = oSqlCmd.CreateParameter(); Id_FuenteP.DbType = DbType.String; Id_FuenteP.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(Id_FuenteP); Id_FuenteP.Value = Id_Fuente; TdParameter Fecha_Inicialp = oSqlCmd.CreateParameter(); Fecha_Inicialp.DbType = DbType.String; Fecha_Inicialp.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(Fecha_Inicialp); Fecha_Inicialp.Value = Fecha_Inicial; TdParameter Fecha_Finalp = oSqlCmd.CreateParameter(); Fecha_Finalp.DbType = DbType.String; Fecha_Finalp.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(Fecha_Finalp); Fecha_Finalp.Value = Fecha_Final; TdParameter Id_Periodicidadp = oSqlCmd.CreateParameter(); Id_Periodicidadp.DbType = DbType.String; Id_Periodicidadp.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(Id_Periodicidadp); Id_Periodicidadp.Value = Id_Periodicidad; oSqlCmd.Prepare(); TdDataReader oReader = oSqlCmd.ExecuteReader(); if (oReader != null) { if (oReader.HasRows) { while (oReader.Read()) { RC_ConsultaAgregacionNumerales item = new RC_ConsultaAgregacionNumerales(); item.Sk_Consulta = int.Parse(oReader["Sk_Consulta"].ToString()); item.Id_Estructura = int.Parse(oReader["Id_Estructura"].ToString()); list.Add(item); } oReader.Close(); } oReader.Dispose(); } } oSqlConnection.Close(); } catch (SqlException ex) { throw ex; } catch (IndexOutOfRangeException ex) { throw ex; } catch (TdException ex) { throw ex; } catch (FormatException ex) { throw ex; } } return(list); }
public List <RC_ConsultaAgregacionNumerales> Get_RC_ConsultaAgregacionNumeralesDAL(int Id_Estructura, DateTime Fecha_Consulta, int Id_Fuente, DateTime Fecha_Inicial, DateTime Fecha_Final, int Id_Periodicidad) { List <RC_ConsultaAgregacionNumerales> list = new List <RC_ConsultaAgregacionNumerales>(); using (TdConnection oSqlConnection = new TdConnection(Cnn)) { try { oSqlConnection.Open(); using (TdCommand oSqlCmd = new TdCommand()) { oSqlCmd.Parameters.Clear(); oSqlCmd.CommandText = "Select * from " + @instancia + ".V_RC_ConsultaAgregacionNumerales " + " where Id_Estructura = ? and Fecha_Consulta = ?" + " AND Id_Fuente= ? AND Fecha_Inicial=? AND Fecha_Final=? " + " AND Id_Periodicidad=? ;"; oSqlCmd.CommandType = CommandType.Text; oSqlCmd.CommandTimeout = 30; oSqlCmd.Connection = oSqlConnection; TdParameter Id_EstructuraP = oSqlCmd.CreateParameter(); Id_EstructuraP.DbType = DbType.String; Id_EstructuraP.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(Id_EstructuraP); Id_EstructuraP.Value = Id_Estructura; TdParameter Fecha_ConsultaP = oSqlCmd.CreateParameter(); Fecha_ConsultaP.DbType = DbType.DateTime; Fecha_ConsultaP.Direction = ParameterDirection.Input; Fecha_ConsultaP.IsNullable = true; object v; if (Fecha_Consulta != null) { v = Fecha_Consulta; } else { v = System.DBNull.Value; } oSqlCmd.Parameters.Add(Fecha_ConsultaP); Fecha_ConsultaP.Value = v; TdParameter Id_FuenteP = oSqlCmd.CreateParameter(); Id_FuenteP.DbType = DbType.String; Id_FuenteP.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(Id_FuenteP); Id_FuenteP.Value = Id_Fuente; TdParameter Fecha_Inicialp = oSqlCmd.CreateParameter(); Fecha_Inicialp.DbType = DbType.DateTime; Fecha_Inicialp.Direction = ParameterDirection.Input; Fecha_Inicialp.IsNullable = true; if (Fecha_Inicial != null) { v = Fecha_Inicial; } else { v = System.DBNull.Value; } oSqlCmd.Parameters.Add(Fecha_Inicialp); Fecha_Inicialp.Value = v; TdParameter Fecha_Finalp = oSqlCmd.CreateParameter(); Fecha_Finalp.DbType = DbType.DateTime; Fecha_Finalp.Direction = ParameterDirection.Input; Fecha_Finalp.IsNullable = true; if (Fecha_Final != null) { v = Fecha_Final; } else { v = System.DBNull.Value; } oSqlCmd.Parameters.Add(Fecha_Finalp); Fecha_Finalp.Value = v; TdParameter Id_Periodicidadp = oSqlCmd.CreateParameter(); Id_Periodicidadp.DbType = DbType.String; Id_Periodicidadp.Direction = ParameterDirection.Input; oSqlCmd.Parameters.Add(Id_Periodicidadp); Id_Periodicidadp.Value = Id_Periodicidad; oSqlCmd.Prepare(); TdDataReader oReader = oSqlCmd.ExecuteReader(); if (oReader != null) { if (oReader.HasRows) { while (oReader.Read()) { RC_ConsultaAgregacionNumerales item = new RC_ConsultaAgregacionNumerales(); item.Id_Estructura = int.Parse(oReader["Id_Estructura"].ToString()); item.Fecha_Consulta = DateTime.Parse(oReader["Fecha_Consulta"].ToString()); item.Id_Fuente = int.Parse(oReader["Id_Fuente"].ToString()); item.Fecha_Inicial = DateTime.Parse(oReader["Fecha_Inicial"].ToString()); item.Fecha_Final = DateTime.Parse(oReader["Fecha_Final"].ToString()); item.Id_Periodicidad = int.Parse(oReader["Id_Periodicidad"].ToString()); list.Add(item); } oReader.Close(); } oReader.Dispose(); } } oSqlConnection.Close(); } catch (SqlException ex) { throw ex; } catch (IndexOutOfRangeException ex) { throw ex; } catch (TdException ex) { throw ex; } catch (FormatException ex) { throw ex; } } return(list); }