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();
}
}
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);
}
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;
}
}
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);
}
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 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);
}
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>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
// Build Xml
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("SqlServer_{0:yyyy-MM-dd-HH-mm-ss-fff}.xml", DateTime.Now));
var xdoc = new XDocument();
var xobjects = new XElement("Records");
while (reader.Read())
{
var xobject = new XElement("Record");
foreach (var column in columns)
{
//xobject.Add(new XElement("Cell", new XAttribute("column", SecurityElement.Escape(column) , new XAttribute("value", SecurityElement.Escape(reader[column].ToString())))));
}
xobjects.Add(xobject);
}
xdoc.Add(xobjects);
xdoc.Save(destPath);
Files.Add(new FileInf(destPath, Id));
InfoFormat("XML 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>();
if (reader != null)
{
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
}
// Build Xml
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Access_{0:yyyy-MM-dd-HH-mm-ss-fff}.xml", DateTime.Now));
var xdoc = new XDocument();
var xobjects = new XElement("Records");
while (reader != null && reader.Read())
{
var xobject = new XElement("Record");
foreach (var column in columns)
{
xobject.Add(new XElement("Cell", new XAttribute("column", SecurityElement.Escape(column)), new XAttribute("value", SecurityElement.Escape(reader[column].ToString()))));
}
xobjects.Add(xobject);
}
xdoc.Add(xobjects);
xdoc.Save(destPath);
Files.Add(new FileInf(destPath, Id));
InfoFormat("XML 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>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
// Build Xml
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Oracle_{0:yyyy-MM-dd-HH-mm-ss-fff}.xml", DateTime.Now));
var xdoc = new XDocument();
var xobjects = new XElement("Records");
while (reader.Read())
{
var xobject = new XElement("Record");
foreach (var column in columns)
{
xobject.Add(new XElement("Cell", new XAttribute("column", SecurityElement.Escape(column)), new XAttribute("value", SecurityElement.Escape(reader[column].ToString()))));
}
xobjects.Add(xobject);
}
xdoc.Add(xobjects);
xdoc.Save(destPath);
Files.Add(new FileInf(destPath, Id));
InfoFormat("XML 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>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
// Build Xml
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("MySql_{0:yyyy-MM-dd-HH-mm-ss-fff}.xml", DateTime.Now));
var xdoc = new XDocument();
var xobjects = new XElement("Records");
while (reader.Read())
{
var xobject = new XElement("Record");
foreach (var column in columns)
{
xobject.Add(new XElement("Cell", new XAttribute("column", SecurityElement.Escape(column)), new XAttribute("value", SecurityElement.Escape(reader[column].ToString()))));
}
xobjects.Add(xobject);
}
xdoc.Add(xobjects);
xdoc.Save(destPath);
Files.Add(new FileInf(destPath, Id));
InfoFormat("XML 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>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
// Build Xml
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Sqlite_{0:yyyy-MM-dd-HH-mm-ss-fff}.xml", DateTime.Now));
var xdoc = new XDocument();
var xobjects = new XElement("Records");
while (reader.Read())
{
var xobject = new XElement("Record");
foreach (var column in columns)
{
xobject.Add(new XElement("Cell", new XAttribute("column", SecurityElement.Escape(column)), new XAttribute("value", SecurityElement.Escape(reader[column].ToString()))));
}
xobjects.Add(xobject);
}
xdoc.Add(xobjects);
xdoc.Save(destPath);
Files.Add(new FileInf(destPath, Id));
InfoFormat("XML 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>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
// Build Xml
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("PostGreSql_{0:yyyy-MM-dd-HH-mm-ss-fff}.xml", DateTime.Now));
var xdoc = new XDocument();
var xobjects = new XElement("Records");
while (reader.Read())
{
var xobject = new XElement("Record");
foreach (var column in columns)
{
xobject.Add(new XElement("Cell", new XAttribute("column", SecurityElement.Escape(column)), new XAttribute("value", SecurityElement.Escape(reader[column].ToString()))));
}
xobjects.Add(xobject);
}
xdoc.Add(xobjects);
xdoc.Save(destPath);
Files.Add(new FileInf(destPath, Id));
InfoFormat("XML 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>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
// Build Xml
string destPath = Path.Combine(Workflow.WorkflowTempFolder
, string.Format("Teradata_{0:yyyy-MM-dd-HH-mm-ss-fff}.xml", DateTime.Now));
var xdoc = new XDocument();
var xobjects = new XElement("Records");
while (reader.Read())
{
var xobject = new XElement("Record");
foreach (var column in columns)
{
xobject.Add(new XElement("Cell", new XAttribute("column", SecurityElement.Escape(column)), new XAttribute("value", SecurityElement.Escape(reader[column].ToString()))));
}
xobjects.Add(xobject);
}
xdoc.Add(xobjects);
xdoc.Save(destPath);
Files.Add(new FileInf(destPath, Id));
InfoFormat("XML file generated: {0}", destPath);
}
break;
}
}
static void Main(string[] args)
{
// Loosely based on https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples
var user = Environment.GetEnvironmentVariable("USERNAME");
Console.Write($"User [{user}]: ");
var userIn = Console.ReadLine();
user = String.IsNullOrWhiteSpace(userIn) ? user : userIn;
Console.Write("Password: "******"server",
UserId = user,
Password = pass,
// Optional
AuthenticationMechanism = "LDAP",
ConnectionPooling = true,
DataEncryption = true,
IntegratedSecurity = false,
ResponseBufferSize = 7340000
};
Console.WriteLine("Connection string:");
Console.WriteLine(connStrbBuilder.ConnectionString);
Console.WriteLine();
Console.WriteLine("Result:");
// "Data Encryption=True;Authentication Mechanism=LDAP;Response Buffer Size=7340000;User Id=xxx;Data Source=xxx;Password=xxxx;Connection Pooling=True;Integrated Security=False"
// Provide the query string with a parameter placeholder.
string queryString =
"SELECT * FROM DBC.TablesV "
+ "WHERE TableKind = ? "
+ "SAMPLE 10;";
// Specify the parameter value.
var param = new TdParameter("", "V");
// Create and open the connection in a using block. This
// ensures that all resources will be closed and disposed
// when the code exits.
using (var connection = new TdConnection(connStrbBuilder.ConnectionString))
{
// Create the Command and Parameter objects.
var command = new TdCommand(queryString, connection);
command.Parameters.Add(param);
// Open the connection in a try/catch block.
// Create and execute the DataReader, writing the result
// set to the console window.
try
{
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}\t{2}",
reader[0], reader[1], reader[2]);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
}
protected void BtnSearch_Click(object sender, EventArgs e)
{
DateTime Sysdate = DateTime.Now;
fromdate = Calendarfrom.SelectedDate;
todate = CalendartoDate.SelectedDate;
int defect = 0;
if (fromdate > todate || fromdate > Sysdate || todate > Sysdate)
{
LbMessage.Visible = true;
LbMessage.Text = "Please enter the valid date range";
}
else
{
if
(DDLAssignedto.SelectedValue == "" && DDLEnvironment.SelectedValue == "" &&
DDLModule.SelectedValue == "" && DDLProject.SelectedValue == "" && DDLProjectname.SelectedValue == "" &&
DDlStatus.SelectedValue == "" &&
DDLTester.SelectedValue == "" && txtfromdate.Text == "" && txtTodate.Text == "" && TxtDefectname.Text == "" && DDLCycle.SelectedValue == "")
{
LbMessage.Visible = true;
LbMessage.Text = "Please select any of the search items";
}
else
{
try
{
string trteradataconnection = "Data Source='204.99.34.21';User ID='coebatch';Password='******';";
if (txtfromdate.Text != "" && txtTodate.Text != "")
{
Datefrom = Convert.ToDateTime(txtfromdate.Text);
DateTo = Convert.ToDateTime(txtTodate.Text);
Datefrom1 = new TdTimestamp(Datefrom);
DateTo1 = new TdTimestamp(DateTo);
}
else
{
DateTime timetora = DateTime.Now;
string text = timetora.ToString("MM/dd/yyyy HH:mm:ss");
DateTime Test1 = Convert.ToDateTime(text);
DateTo1 = new TdTimestamp(Test1);
}
Teradata.Client.Provider.TdConnection tereconnection = new Teradata.Client.Provider.TdConnection(trteradataconnection);
//TdCommand Teracomd = new TdCommand("select * from coebatch.tbl_Defect", tereconnection);
//TdDataAdapter AdvancedDefectSearch =
// new TdDataAdapter(@"Select DefectID,DefectName,Status,sDate,sCycle,Description,Project,Tester,TestcasesAssociated,Module,AssignedTo,Filenames,Filesize from coebatch.tbl_Defect where sDate between '" + fromdate + "' and '" + todate + "'", tereconnection);
TdCommand cmd = new TdCommand("COEBATCH.ADVANCEDDEFECTSEARCH1", tereconnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("VDefectname", TdType.VarChar).Value = TxtDefectname.Text;
cmd.Parameters.Add("VStatus", TdType.VarChar).Value = DDlStatus.SelectedValue;
cmd.Parameters.Add("VAssignedto", TdType.VarChar).Value = DDLAssignedto.SelectedValue;
cmd.Parameters.Add("VTester", TdType.VarChar).Value = DDLTester.SelectedValue;
cmd.Parameters.Add("VOpendate", TdType.Timestamp).Value = Datefrom1;
cmd.Parameters.Add("VOpendate1", TdType.Timestamp).Value = DateTo1;
cmd.Parameters.Add("VProject", TdType.VarChar).Value = DDLProject.SelectedValue;
cmd.Parameters.Add("VModule", TdType.VarChar).Value = DDLModule.SelectedValue;
cmd.Parameters.Add("VEnvironment", TdType.VarChar).Value = DDLEnvironment.SelectedValue;
cmd.Parameters.Add("VRelease", TdType.VarChar).Value = DDLProjectname.SelectedValue;
cmd.Parameters.Add("VCycle", TdType.VarChar).Value = DDLCycle.SelectedValue;
//cmd.Parameters.Add("cur1", TdType.AnyType).Direction = ParameterDirection.Output;
tereconnection.Open();
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
// defect = cmd.ExecuteNonQuery();
// TdDataAdapter AdvancedDefectSearch =
// new TdDataAdapter();
//AdvancedDefectSearch= new TdDataAdapter(cmd);
//// IDataSource dt1 = new IDataSource ;
//tereconnection.Open();
//DataSet ds = new DataSet();
//AdvancedDefectSearch.Fill(ds);
GridAdvancedSearch.DataSource = dt;
GridAdvancedSearch.DataBind();
//this.GridAdvancedSearch.DataSource = ds.Tables[0].DataSet;
//GridAdvancedSearch.DataKeyNames = new string[] { "Defectid" };
//this.GridAdvancedSearch.DataBind();
//this.GridAdvancedSearch.Visible = true;
if (GridAdvancedSearch.Rows.Count > 0)
{
rowcount = GridAdvancedSearch.Rows.Count;
BtnExport.Enabled = true;
}
else
{
BtnExport.Enabled = false;
}
}
catch (Teradata.Client.Provider.TdException ex)
{
//Throw the exception to calling environment
throw ex;
}
finally
{
//Close the Connection from the Database
}
//GridAdvancedSearch.DataSource = dt;
// AdvancedDefectSearch.Fill(dt);
}
}
}