public DataTable returnResult(string query, out string mensaje)
{
try
{
using (TdConnection cn = new TdConnection(conn.StringConnection))
{
cn.Open();
TdCommand cmd = cn.CreateCommand();
cmd.CommandText = @query;
cmd.CommandTimeout = 100000;
TdDataAdapter adapter = new TdDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
var dt = ds.Tables[0];
mensaje = "Se consultaron " + dt.Rows.Count.ToString() + " registros.";
return(dt);
}
}
catch (Exception e)
{
mensaje = e.Message;
return(null);
}
}
public bool tryConnect()
{
try
{
cn.ConnectionString = connectionStringBuilder.ConnectionString;
cn.Open();
cn.Close();
return(true);
}catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
}
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 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();
}
}
public void UpdateDefect(string Comments, string LoginUser, int DefectID, String DefectName, TdTimestamp Date, String Status, String Cycle, String Description, String Project, String Tester, String TestcasesAssociated, String Module, String Assignedto, TdTimestamp sysdate, String filenames, String filesize, Byte Attachments, int histid, String environment, String Severity, String Projectname, String Rootcause)
{
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdCommand cmdUpdateDefect =
new Teradata.Client.Provider.TdCommand(@"Update coebatch.tbl_Defect set DefectName='" + DefectName + "' ,Status='" + Status + "',SCycle ='" + Cycle + "',Description='" + Description + "',Project='" + Project + "',Tester='" + Tester + "',TestcasesAssociated='" + TestcasesAssociated + "',Module='" + Module + "',AssignedTo='" + Assignedto + "',CloseDate='" + sysdate + "', Environment = '" + environment + "' , Severity = '" + Severity + "', Releases = '" + Projectname + "',Rootcause = '" + Rootcause + "' where DefectID= '" + DefectID + "'", con);
Teradata.Client.Provider.TdCommand cmdInsertComments =
new Teradata.Client.Provider.TdCommand(@"Insert into coebatch.tbl_Comments Values ('" + DefectID + "','" + Comments + "','" + sysdate + "', '" + LoginUser + "' )", con);
DateTime Datetimestamp = DateTime.Now;
Teradata.Client.Provider.TdCommand cmdUpdateDefectHistory =
new Teradata.Client.Provider.TdCommand(@"Insert into coebatch.tbl_Defect_History Values('" + DefectID + "','" + DefectName + "' ,'" + Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','" + sysdate + "','" + histid + "','" + LoginUser + "', '" + environment + "' , '" + Severity + "','" + Projectname + "','" + Rootcause + "') ", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdUpdateDefect.ExecuteNonQuery();
cmdInsertComments.ExecuteNonQuery();
cmdUpdateDefectHistory.ExecuteNonQuery();
}
catch (TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
}
public int InsertLogin(string Loginuser, string Password)
{
TdConnection con = new TdConnection(strConnString);
int id = 0;
//Define the Command Object
Teradata.Client.Provider.TdCommand cmdAddLogin = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Login Values('" + Loginuser + "','"
+ Password + "')", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdAddLogin.ExecuteNonQuery();
id = 1;
return(id);
}
catch (Teradata.Client.Provider.TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(id);
}
public void InsertDefect(string Loginuser, int DefectID, String DefectName, TdTimestamp Date, String Status, String Cycle, String Description, String Project, String Tester, String TestcasesAssociated, String Module, String Assignedto, TdTimestamp date1, String filenames, String filesize, Byte Attachments, String environment, String severity, String Projectname, String Rootcause)
{
TdConnection con = new TdConnection(strConnString);
// Date = CAST(Date AS FORMAT 'YYYY-MM-DD');
// TdTimestamp dt = new TdTimestamp(Date);
// string dts = dt.ToString("MM/dd/yyyy HH:mm:ss");
//Define the Command Object
Teradata.Client.Provider.TdCommand cmdAddDefect = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Defect Values('" + DefectID + "','"
+ DefectName + "','"
+ Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','"
+ date1 + "','" + filenames + "','" + filesize + "',,'" + environment + "','" + severity + "','" + Projectname + "','" + Rootcause + "')", con);
int DefectIDD = (int)GetNextDefectID();
int histid = 1;
//DefectIDD = DefectIDD - 1;
Teradata.Client.Provider.TdCommand cmdAddDefectHistory = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Defect_History Values('"
+ DefectIDD + "','"
+ DefectName + "','"
+ Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','"
+ date1 + "','" + histid + "','" + Loginuser + "','" + environment + "','" + severity + "', '" + Projectname + "','" + Rootcause + "')", con);
int intLengt = 0;
byte[] objDat;
objDat = new byte[intLengt];
string DataOb = Convert.ToString(objDat);
// cmdAddDefect.Parameters.Add(new Teradata.Client.Provider.TdParameter("@Attachments", (object)objDat));
// cmdAddDefectHistory.Parameters.Add(new Teradata.Client.Provider.TdParameter("@Attachments", (object)objDat));
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdAddDefect.ExecuteNonQuery();
cmdAddDefectHistory.ExecuteNonQuery();
}
catch (Teradata.Client.Provider.TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
}
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 TdDataAdapter GetAttachments(int Defectid)
{
//Define the Command Object
int ID = 0;
string defect1 = "";
//IDDefect = Convert.ToInt32(DefectID);
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdDataAdapter AdvancedDefectSearch =
new Teradata.Client.Provider.TdDataAdapter("Select Path from coebatch.tbl_Attachments where DefectID = " + Defectid + ";", con);
try
{
//Open the Connection to connect to the database
con.Open();
return(AdvancedDefectSearch);
//Execute the Query
//AdvancedDefectSearch.ExecuteNonQuery();
//TdDataReader r = AdvancedDefectSearch.ExecuteReader();
//if (r.Read())
//{
// defect1 = r["DefectID"].ToString();
//}
//r.Close();
//if (defect1 == "")
//{
// con.Close();
// return ID;
//}
//else
//{
// ID = 1;
//}
// nextDefectId = (int)cmdNewDefect.ExecuteScalar();
}
catch (TdException ex)
{
// return ID;
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(AdvancedDefectSearch);
}
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();
}
}
static void Main(string[] args)
{
TdConnection cn = new TdConnection();
TdConnectionStringBuilder conStrBuilder = new TdConnectionStringBuilder();
conStrBuilder.DataSource = "DSN";
// conStrBuilder.Database = "optional";
conStrBuilder.UserId = "user"; conStrBuilder.Password = "******";
cn.ConnectionString = conStrBuilder.ConnectionString;
cn.Open();
Console.WriteLine("connection successfull");
}
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 bool testConnection()
{
TdConnection connect = new TdConnection();
connect.ConnectionString = conn.StringConnection;
try
{
connect.Open();
connect.Close();
return(true);
}
catch (Exception e)
{
return(false);
}
}
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 void Connect()
{
if (this.MyDriver == UseDriver.ODBC)
{
OdbcConnection connection = new OdbcConnection(this.ConnectString);
connection.Open();
this.ODBCConnection = connection;
}
else if (this.MyDriver == UseDriver.ORACLE)
{
OracleConnection connection = new OracleConnection(this.ConnectString);
connection.Open();
this.OleDbConnection = connection;
}
else if (this.MyDriver == UseDriver.TERADATA)
{
TdConnection connection = new TdConnection(this.ConnectString);
connection.Open();
this.TdConnection = connection;
}
}
public void Connect()
{
Logger.Log(String.Format("Connecting to server using {0} driver.", MyDriver.ToString()));
if (this.MyDriver == UseDriver.ODBC)
{
OdbcConnection connection = new OdbcConnection(this.ConnectString);
connection.Open();
this.ODBCConnection = connection;
}
else if (this.MyDriver == UseDriver.ORACLE)
{
OracleConnection connection = new OracleConnection(this.ConnectString);
connection.Open();
this.OleDbConnection = connection;
}
else if (this.MyDriver == UseDriver.TERADATA)
{
TdConnection connection = new TdConnection(this.ConnectString);
connection.Open();
this.TdConnection = connection;
}
else if (this.MyDriver == UseDriver.POSTGRESQL)
{
NpgsqlConnection connection = new NpgsqlConnection(this.ConnectString);
connection.Open();
this.NpgsqlConnection = connection;
}
else if (this.MyDriver == UseDriver.SNOWFLAKE)
{
SnowflakeDbConnection connection = new SnowflakeDbConnection();
connection.ConnectionString = ConnectString;
connection.Open();
this.SnowflakeConnection = connection;
}
Logger.Log("Connection succesfully established");
}
public int AddAttachments(string text, string filename, int Defectid1, int id1)
{
TdConnection con = new TdConnection(strConnString);
int id = 0;
//Define the Command Object
Teradata.Client.Provider.TdCommand cmdAddAttach = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Attachments Values('" + text + "','"
+ filename + "',"
+ Defectid1 + ","
+ id1 + " )", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdAddAttach.ExecuteNonQuery();
id = 1;
return(id);
}
catch (Teradata.Client.Provider.TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(id);
}
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 int EjecutarConsultaAgregacionNumerales(RC_ConsultaAgregacionNumerales consulta)
{
var result = 0;
DateTime centuryBegin = new DateTime(2001, 1, 1);
DateTime currentDate = DateTime.Now;
long elapsedTicks = currentDate.Ticks - centuryBegin.Ticks;
TimeSpan elapsedSpan = new TimeSpan(elapsedTicks);
int lote = Convert.ToInt32(elapsedSpan.TotalSeconds);
using (TdConnection oSqlConnection = new TdConnection(Cnn))
{
try
{
oSqlConnection.Open();
using (TdCommand oSqlCmd = new TdCommand())
{
oSqlCmd.Parameters.Clear();
oSqlCmd.CommandText = $"{instanciaStage}.{SpAgregacionNumerales}";
oSqlCmd.CommandType = CommandType.StoredProcedure;
oSqlCmd.CommandTimeout = int.MaxValue;
oSqlCmd.Connection = oSqlConnection;
TdParameter Id_Estructura = oSqlCmd.CreateParameter();
Id_Estructura.DbType = DbType.String;
Id_Estructura.Direction = ParameterDirection.Input;
Id_Estructura.ParameterName = "Id_Estructura";
oSqlCmd.Parameters.Add(Id_Estructura);
Id_Estructura.Value = consulta.Id_Estructura;
TdParameter Fecha_Consulta = oSqlCmd.CreateParameter();
Fecha_Consulta.DbType = DbType.DateTime;
Id_Estructura.Direction = ParameterDirection.Input;
Id_Estructura.ParameterName = "Fecha_Consulta";
oSqlCmd.Parameters.Add(Fecha_Consulta);
Fecha_Consulta.Value = consulta.Fecha_Consulta;
TdParameter Id_Fuente = oSqlCmd.CreateParameter();
Id_Fuente.DbType = DbType.String;
Id_Estructura.Direction = ParameterDirection.Input;
Id_Estructura.ParameterName = "Id_Fuente";
oSqlCmd.Parameters.Add(Id_Fuente);
Id_Fuente.Value = consulta.Id_Fuente;
TdParameter Fecha_Inicial = oSqlCmd.CreateParameter();
Fecha_Inicial.DbType = DbType.DateTime;
Id_Estructura.Direction = ParameterDirection.Input;
Id_Estructura.ParameterName = "Fecha_Inicial";
oSqlCmd.Parameters.Add(Fecha_Inicial);
Fecha_Inicial.Value = consulta.Fecha_Inicial;
TdParameter Fecha_Final = oSqlCmd.CreateParameter();
Fecha_Final.DbType = DbType.DateTime;
Fecha_Final.Direction = ParameterDirection.Input;
Id_Estructura.ParameterName = "Fecha_Final";
oSqlCmd.Parameters.Add(Fecha_Final);
Fecha_Final.Value = consulta.Fecha_Final;
TdParameter Id_Periodicidad = oSqlCmd.CreateParameter();
Id_Periodicidad.DbType = DbType.String;
Id_Periodicidad.Direction = ParameterDirection.Input;
Id_Estructura.ParameterName = "Id_Periodicidad";
oSqlCmd.Parameters.Add(Id_Periodicidad);
Id_Periodicidad.Value = consulta.Id_Periodicidad;
result = oSqlCmd.ExecuteNonQuery();
result = 1;
oSqlCmd.Dispose();
oSqlConnection.Close();
}
}
catch (SqlException e)
{
DeleteConsultaAgregacionNumerales(consulta);
result = -1;
}
catch (TdException e)
{
DeleteConsultaAgregacionNumerales(consulta);
result = -1;
}
catch (FormatException e)
{
DeleteConsultaAgregacionNumerales(consulta);
result = -1;
}
catch (OverflowException e)
{
DeleteConsultaAgregacionNumerales(consulta);
result = -1;
}
}
return(result);
}
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_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);
}
static void Main(string[] args)
{
Parser.Default.ParseArguments <Options>(args)
.WithParsed <Options>(o =>
{
Utils utils = new Utils();
DataTable excel = utils.ImportExceltoDatatable(o.inExcel, "Queries");
DirectoryInfo od = new DirectoryInfo(o.outDir);
od.Create();
DataTable dtSummary = new DataTable();
dtSummary.Columns.Add(new DataColumn("SSASModel", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TableName", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TestResults", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TDQueryStartTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("TDQueryEndTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("TDQueryExecutionTime(minutes)", typeof(System.Double)));
dtSummary.Columns.Add(new DataColumn("SSASQueryStartTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("SSASQueryEndTime", typeof(DateTime)));
dtSummary.Columns.Add(new DataColumn("SSASQueryExecutionTime(minutes)", typeof(System.Double)));
dtSummary.Columns.Add(new DataColumn("Exception", typeof(String)));
dtSummary.Columns.Add(new DataColumn("TDQuery", typeof(String)));
dtSummary.Columns.Add(new DataColumn("DAX", typeof(String)));
foreach (DataRow row in excel.Rows)
{
string ssasModel = row["SSAS_MODEL"].ToString();
string tableName = row["TABLE_NAME"].ToString();
string tdQuery = row["TD_QUERY"].ToString();
string daxQuery = row["DAX"].ToString();
DateTime tdStartTime = DateTime.Now, tdEndTime = DateTime.Now, SSASStartTime = DateTime.Now, SSASEndTime = DateTime.Now;
if (tableName.ToString().Equals(""))
{
continue;
}
DataRow drSummary = dtSummary.NewRow();
drSummary["SSASModel"] = ssasModel;
drSummary["TableName"] = tableName;
drSummary["TDQuery"] = tdQuery;
drSummary["DAX"] = daxQuery;
drSummary["TestResults"] = "Failed";
Console.WriteLine("---------------------------------------------------------------");
Console.WriteLine(ssasModel + " -> " + tableName);
using (var workbook = new XLWorkbook())
{
try
{
Console.WriteLine(tdQuery);
TdConnection cn = new TdConnection(o.Server);
cn.Open();
TdCommand cmd = null;
TdDataAdapter adapter = null;
DataTable dtt = new DataTable();
tdStartTime = DateTime.Now;
try
{
cmd = new TdCommand(tdQuery, cn);
cmd.CommandTimeout = 1200;
adapter = new TdDataAdapter(cmd);
tdStartTime = DateTime.Now;
adapter.Fill(dtt);
tdEndTime = DateTime.Now;
workbook.Worksheets.Add(dtt, "TD");
cmd.Connection.Close();
cmd.Dispose();
adapter.Dispose();
}
catch (Exception e)
{
tdEndTime = DateTime.Now;
cmd.Connection.Close();
cmd.Dispose();
adapter.Dispose();
throw e;
}
drSummary["TDQueryStartTime"] = tdStartTime;
drSummary["TDQueryEndTime"] = tdEndTime;
drSummary["TDQueryExecutionTime(minutes)"] = (double)tdEndTime.Subtract(tdStartTime).Seconds / 60;
//ADOTabularConnection conx = new ADOTabularConnection(@"Data Source = .; Catalog = AW;", AdomdType.AnalysisServices);
System.Data.DataTable dt = new DataTable();
ADOTabularConnection conx = null;
try
{
conx = new ADOTabularConnection(o.SSAS, ADOTabular.Enums.AdomdType.AnalysisServices);
if (!ssasModel.Trim().Equals(""))
{
conx.ChangeDatabase(ssasModel);
}
Console.WriteLine("--------------------DAX---------------------------");
Console.WriteLine(daxQuery);
SSASStartTime = DateTime.Now;
drSummary["SSASQueryStartTime"] = SSASStartTime;
dt = conx.ExecuteDaxQueryDataTable(daxQuery);
SSASEndTime = DateTime.Now;
conx.Close();
conx.Dispose();
}
catch (Exception e)
{
SSASEndTime = DateTime.Now;
conx.Close();
conx.Dispose();
throw e;
}
drSummary["SSASQueryEndTime"] = SSASEndTime;
drSummary["SSASQueryExecutionTime(minutes)"] = (double)SSASEndTime.Subtract(SSASStartTime).Seconds / 60;
int noOfRows = dt.Rows.Count;
if (noOfRows > MAX_ROWS)
{
dt = dt.Rows.Cast <DataRow>().Take(MAX_ROWS).CopyToDataTable();
}
workbook.Worksheets.Add(dt, "SSAS");
DataTable ret = utils.getDifferentRecords(dtt, dt);
DataTable dtOutput = new DataTable();
dtOutput.Columns.Add(new DataColumn("SSASModel", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TableName", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TDQuery", typeof(String)));
dtOutput.Columns.Add(new DataColumn("DAX", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TestResults", typeof(String)));
dtOutput.Columns.Add(new DataColumn("TDQueryStartTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("TDQueryEndTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("TDQueryExecutionTime(minutes)", typeof(System.Double)));
dtOutput.Columns.Add(new DataColumn("SSASQueryStartTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("SSASQueryEndTime", typeof(DateTime)));
dtOutput.Columns.Add(new DataColumn("SSASQueryExecutionTime(minutes)", typeof(System.Double)));
DataRow dr = dtOutput.NewRow();
dr["SSASModel"] = ssasModel;
dr["TableName"] = tableName;
dr["TDQuery"] = tdQuery;
dr["DAX"] = daxQuery;
dr["TDQueryStartTime"] = tdStartTime;
dr["TDQueryEndTime"] = tdEndTime;
dr["TDQueryExecutionTime(minutes)"] = (double)tdEndTime.Subtract(tdStartTime).Seconds / 60;
dr["SSASQueryStartTime"] = SSASStartTime;
dr["SSASQueryEndTime"] = SSASEndTime;
dr["SSASQueryExecutionTime(minutes)"] = (double)SSASEndTime.Subtract(SSASStartTime).Seconds / 60;
if (noOfRows == 0)
{
//tableName = tableName + "_NODATA";
dr["TestResults"] = "No data";
drSummary["TestResults"] = "No data";
}
else if (ret.Rows.Count > 0)
{
workbook.Worksheets.Add(ret, "ERRORS");
//tableName = tableName + "_FAILED";
dr["TestResults"] = "Failed";
drSummary["TestResults"] = "Failed";
}
else
{
//tableName = tableName + "_PASSED";
dr["TestResults"] = "Passed";
drSummary["TestResults"] = "Passed";
}
dtOutput.Rows.Add(dr);
workbook.Worksheets.Add(dtOutput, "TestOutput");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
tableName = tableName + "_Exception";
DataTable ex = new DataTable("Exception");
ex.Columns.AddRange(new DataColumn[2] {
new DataColumn("ERROR1"), new DataColumn("ERROR2")
});
drSummary["Exception"] = e.Message;
ex.Rows.Add("Failed", e.Message);
ex.Rows.Add("", e.StackTrace);
workbook.Worksheets.Add(ex, "Exception");
}
finally
{
dtSummary.Rows.Add(drSummary);
workbook.SaveAs(od.FullName + "\\" + tableName + ".xlsx");
XLWorkbook summaryworkbook = new XLWorkbook();
summaryworkbook.Worksheets.Add(dtSummary, "TestSummary");
summaryworkbook.SaveAs(od.FullName + "\\TestSummary.xlsx");
}
}
}
});
}
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_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);
}
/// <summary><see cref="Edrych.DataAccess.DataAccessBase.GetDbConnection"/></summary>
protected override System.Data.IDbConnection GetDbConnection()
{
TdConnection conn = new TdConnection(this.ConnectionString);
conn.Open();
return conn;
}
void ExecuteSql(string sql)
{
switch (DbType)
{
case Type.SqlServer:
using (var conn = new SqlConnection(ConnectionString))
{
var comm = new SqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Access:
using (var conn = new OleDbConnection(ConnectionString))
{
var comm = new OleDbCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Oracle:
using (var conn = new OracleConnection(ConnectionString))
{
var comm = new OracleCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.MySql:
using (var conn = new MySqlConnection(ConnectionString))
{
var comm = new MySqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Sqlite:
using (var conn = new SQLiteConnection(ConnectionString))
{
var comm = new SQLiteCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.PostGreSql:
using (var conn = new NpgsqlConnection(ConnectionString))
{
var comm = new NpgsqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Teradata:
using (var conn = new TdConnection(ConnectionString))
{
var comm = new TdCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
}
}
void ExecuteSql(string sql)
{
switch (DbType)
{
case Type.SqlServer:
using (var conn = new SqlConnection(ConnectionString))
{
var comm = new SqlCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("SqlServer_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.Access:
using (var conn = new OleDbConnection(ConnectionString))
{
var comm = new OleDbCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
if (reader != null)
{
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Access_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader != null && reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.Oracle:
using (var conn = new OracleConnection(ConnectionString))
{
var comm = new OracleCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Oracle_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.MySql:
using (var conn = new MySqlConnection(ConnectionString))
{
var comm = new MySqlCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("MySql_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.Sqlite:
using (var conn = new SQLiteConnection(ConnectionString))
{
var comm = new SQLiteCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Sqlite_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.PostGreSql:
using (var conn = new NpgsqlConnection(ConnectionString))
{
var comm = new NpgsqlCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("PostGreSql_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
case Type.Teradata:
using (var conn = new TdConnection(ConnectionString))
{
var comm = new TdCommand(sql, conn);
conn.Open();
var reader = comm.ExecuteReader();
// Get column names
var columns = new List <string>();
StringBuilder builder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
builder.Append(reader.GetName(i)).Append(Separator);
}
builder.Append("\r\n");
// Build Csv
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Teradata_{0:yyyy-MM-dd-HH-mm-ss-fff}.csv", DateTime.Now));
while (reader.Read())
{
foreach (var column in columns)
{
builder.Append(reader[column]).Append(Separator);
}
builder.Append("\r\n");
}
File.WriteAllText(destPath, builder.ToString());
Files.Add(new FileInf(destPath, Id));
InfoFormat("CSV file generated: {0}", destPath);
}
break;
}
}
private void ExecuteSql(string sql)
{
switch (this.DbType)
{
case Type.SqlServer:
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Access:
using (OleDbConnection conn = new OleDbConnection(this.ConnectionString))
{
OleDbCommand comm = new OleDbCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Oracle:
using (OracleConnection conn = new OracleConnection(this.ConnectionString))
{
OracleCommand comm = new OracleCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.MySql:
using (MySqlConnection conn = new MySqlConnection(this.ConnectionString))
{
MySqlCommand comm = new MySqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Sqlite:
using (SQLiteConnection conn = new SQLiteConnection(this.ConnectionString))
{
SQLiteCommand comm = new SQLiteCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.PostGreSql:
using (NpgsqlConnection conn = new NpgsqlConnection(this.ConnectionString))
{
NpgsqlCommand comm = new NpgsqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
case Type.Teradata:
using (TdConnection conn = new TdConnection(this.ConnectionString))
{
TdCommand comm = new TdCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
}
break;
}
}
static void readTeradata()
{
string db_name = @"DPRD_SSL_MDM_V"; // @"LabBICC_Test";LabBICC_FIN_DYI
string queryTemplate = @"
SELECT TableName, tablekind, case tablekind when 'V' then RequestText else null end as RequestText
FROM dbc.tablesv
WHERE tablekind in ('V', 'T') AND databasename IN ('{0}')
AND TableName = 'vD_GeoSite'
--AND TableName LIKE 'vD_Equipment_%'
";
//and (TableName like 'MSBI_vF_Fixed%' or TableName like 'MSBI_vD_Report%')
// AND TableName like 'MSBI_%'
//in ('MSBI_vD_KeyFigureGroup', 'vD_KeyFigureGroup', 'vD_KeyFigureGroupCateg')
DbObjectMaster objMaster = new DbObjectMaster();
objMaster.DefaultDatabase = db_name;
string queryString = String.Format(queryTemplate, db_name);
TdConnection cn = new TdConnection();
string connectionString = @"Data Source=maersk6;Database=LabBICC_Test;User Id=UADL_BICC_LOADUSER;Password=Lab@BICC123;Connection Timeout=300;";
string obj_text = "", obj_name = "", obj_type = "";
using (TdConnection connection = new TdConnection(connectionString))
{
//connection.ConnectionTimeout = 300; //covered by connection string
TdCommand cmd = new TdCommand(queryString, connection);
cmd.CommandTimeout = 180;
//cmd.Parameters.Add(new TdParameter("@viewname", "MSBI_vD_Company"));
//cmd.CommandText = queryString;
Console.WriteLine("Acquiring the connection....");
connection.Open();
Console.WriteLine("Getting database object list....");
TdDataReader reader = cmd.ExecuteReader();
//Console.WriteLine("{0} tables found.", reader.RecordsAffected);
while (reader.Read())
{
obj_name = reader["TableName"].ToString().Trim();
obj_type = reader["tablekind"].ToString().Trim().ToUpper();
obj_text = reader["RequestText"].ToString().Trim();
//str = Convert.ToString(cmd.ExecuteScalar());
//str = (string)cmd.ExecuteScalar();
//obj_text = compressQueryText(obj_text);
//str = "[" + str + "]";
obj_name = obj_name.IndexOf(".") >= 0 ? obj_name : db_name + "." + obj_name;
//DbObject obj = new DbObject(obj_name, obj_type, objMaster, db_name, obj_text);
DbObject obj = objMaster.AddNew(obj_name, obj_type, db_name, obj_text);
Console.WriteLine(obj_name);
//Console.WriteLine(obj_text);
Console.WriteLine("::: source objects :::");
foreach (DbObject src in obj.Sources.Values)
{
Console.WriteLine(src.Name);
}
Console.WriteLine("*******************************************");
//objMaster.Add(obj);
}
cmd.Dispose();
connection.Close();
}
objMaster.BuildReferences();
//var json = ApiResponse
//var json = JsonConvert.SerializeObject(objMaster);
//this gets all objects and user drills down to their sources (if any)
//this way some objects may appear in different branches of the tree
DbObjectTree tree = objMaster.getDbObjectTree();
//this starts from the objects that have no targets (no one is sourced from them)
//and user drills down to the sources, nvigating to the other objects this way
//DbObjectTree tree = objMaster.getDbObjectTreeFlowEnd();
tree.GroupChildrenBySchema();
tree.SortTree();
tree.AddIcons();
//tree.CleanParents();
var json = JsonConvert.SerializeObject(tree);
//Console.WriteLine(json);
File.WriteAllText(@"C:\TEMP\views.json", json);
//File.WriteAllText(@"\\SCRBADLDK003868\db\views.json", json);
}
public void Connect()
{
if (this.MyDriver == UseDriver.ODBC)
{
OdbcConnection connection = new OdbcConnection(this.ConnectString);
connection.Open();
this.ODBCConnection = connection;
}
else if (this.MyDriver == UseDriver.OLEDB)
{
OracleConnection connection = new OracleConnection(this.ConnectString);
connection.Open();
this.OleDbConnection = connection;
}
else if (this.MyDriver == UseDriver.TERADATA)
{
TdConnection connection = new TdConnection(this.ConnectString);
connection.Open();
this.TdConnection = connection;
}
}
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);
}
