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 static string GetDefecthistory(int defectid)
{
string query = "select * from coebatch.tbl_defect_history where defectid = '" + defectid + "'";
TdCommand cmd = new TdCommand(query);
return(GetData(cmd).GetXml());
}
public static string GetDefectsearch(DateTime fromdate, DateTime todate)
{
string query = "Select DefectID,DefectName,Status,sDate as Opendate,sCycle as Cycles,Description,Project,Tester,TestcasesAssosiated,Module,AssignedTo,Filename,Filesize from coebatch.tbl_Defect where sDate between '" + fromdate + "' and '" + todate + "'";
TdCommand cmd = new TdCommand(query);
return(GetData(cmd).GetXml());
}
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 bool doesWorkflowExist(String folder, String workflow)
{
String folder_name = "'" + folder + "'";
String workflow_name = "'" + workflow + "'";
try
{
cn.Open();
TdCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT count(*) from edw_auv_d.INFA_SESSION_LOG where subject_area = " + folder_name + " and workflow_name =" + workflow_name + ";";
Int32 count = (Int32)cmd.ExecuteScalar();
cn.Close();
if (count > 0)
{
return(true);
}
return(false);
}catch (Exception ex)
{
cn.Close();
MessageBox.Show(ex.Message);
return(false);
}
}
private void RunTeradata(List <SQLResult> result, string cmd)
{
try
{
TdCommand toGo = this.TdConnection.CreateCommand();
toGo.CommandTimeout = 3600 * 12;
toGo.CommandText = cmd;
TdDataReader reader = toGo.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int nCol = reader.FieldCount;
SQLResult newItem = new SQLResult();
if (nCol > 0)
{
newItem.Column0 = reader.IsDBNull(0) ? String.Empty : reader.GetValue(0).ToString();
}
if (nCol > 1)
{
newItem.Column1 = reader.IsDBNull(1) ? String.Empty : reader.GetValue(1).ToString();
}
if (nCol > 2)
{
newItem.Column2 = reader.IsDBNull(2) ? String.Empty : reader.GetValue(2).ToString();
}
if (nCol > 3)
{
newItem.Column3 = reader.IsDBNull(3) ? String.Empty : reader.GetValue(3).ToString();
}
if (nCol > 4)
{
newItem.Column4 = reader.IsDBNull(4) ? String.Empty : reader.GetValue(4).ToString();
}
if (nCol > 5)
{
newItem.Column5 = reader.IsDBNull(5) ? String.Empty : reader.GetValue(5).ToString();
}
if (nCol > 6)
{
newItem.Column6 = reader.IsDBNull(6) ? String.Empty : reader.GetValue(6).ToString();
}
result.Add(newItem);
}
}
reader.Close();
toGo.Dispose();
}
catch (Exception ex)
{
throw;
}
}
public List <NumeralCambiario> Get_NumeralCambiarioDAL()
{
List <NumeralCambiario> list = new List <NumeralCambiario>();
using (TdConnection oSqlConnection = new TdConnection(Cnn))
{
try
{
oSqlConnection.Open();
using (TdCommand oSqlCmd = new TdCommand())
{
oSqlCmd.Parameters.Clear();
oSqlCmd.CommandText = "Select * from " + @instancia + ".V_D_RCNumeralCambiario ORDER BY Desc_NumeralCambiario ";
oSqlCmd.CommandTimeout = 30;
oSqlCmd.Connection = oSqlConnection;
TdDataReader oReader = oSqlCmd.ExecuteReader();
if (oReader != null)
{
if (oReader.HasRows)
{
while (oReader.Read())
{
list.Add(new NumeralCambiario()
{
Sk_RCNumeralCambiario = int.Parse(oReader["Sk_RCNumeralCambiario"].ToString()),
Desc_NumeralCambiario = oReader["Desc_NumeralCambiario"].ToString(),
});
}
oReader.Close();
}
oReader.Dispose();
}
}
oSqlConnection.Close();
}
catch (SqlException ex)
{
throw ex;
}
catch (IndexOutOfRangeException ex)
{
throw ex;
}
catch (TdException ex)
{
throw ex;
}
catch (FormatException ex)
{
throw ex;
}
}
return(list);
}
public int 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);
}
private static DataSet GetData(TdCommand cmd)
{
string strConnString = ConfigurationManager.ConnectionStrings["Connstr"].ToString();
using (TdConnection con = new TdConnection(strConnString))
{
using (TdDataAdapter sda = new TdDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
return(ds);
}
}
}
}
public override IQueryCommand GetCommand(
string sqlText,
int commandTimeout,
IEnumerable <QueryParameterInfo> parameters,
out List <Tuple <int, string> > parametersQueueForODBC
)
{
parametersQueueForODBC = new List <Tuple <int, string> >();
TdCommand cmd = new TdCommand
{
Connection = base.Connection,
CommandText = sqlText,
CommandTimeout = commandTimeout
};
return(new TeradataSqlCommand(cmd));
}
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;
}
}
}
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;
}
}
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);
}
}
}
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 <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);
}
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");
}
}
}
});
}
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>();
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;
}
}
public int DeleteConsultaAgregacionNumerales(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 = " DELETE FROM " + instancia + ".V_RC_ResultadoAgregacionNumerales " +
" WHERE Sk_Consulta= (SEL sk_consulta FROM " + instancia + ".V_RC_ConsultaAgregacionNumerales WHERE Id_Estructura= ? AND Fecha_Consulta= ? AND Id_Fuente=? AND Fecha_Inicial=? AND Fecha_Final=? " +
" AND Id_Periodicidad=?);" +
" DELETE FROM " + instancia + ".V_RC_DetalleResultadoAgregacionNumerales " +
" WHERE Sk_Consulta= (SEL sk_consulta FROM " + instancia + ".V_RC_ConsultaAgregacionNumerales WHERE Id_Estructura= ? AND Fecha_Consulta= ? AND Id_Fuente=? AND Fecha_Inicial=? AND Fecha_Final=? " +
" AND Id_Periodicidad=?);" +
"DELETE 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_Estructura = oSqlCmd.CreateParameter();
Id_Estructura.DbType = DbType.String;
Id_Estructura.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_Estructura);
Id_Estructura.Value = consulta.Id_Estructura;
TdParameter Fecha_Consulta = oSqlCmd.CreateParameter();
Fecha_Consulta.DbType = DbType.DateTime;
Fecha_Consulta.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Fecha_Consulta);
Fecha_Consulta.Value = consulta.Fecha_Consulta;
TdParameter Id_Fuente = oSqlCmd.CreateParameter();
Id_Fuente.DbType = DbType.String;
Id_Fuente.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_Fuente);
Id_Fuente.Value = consulta.Id_Fuente;
TdParameter Fecha_Inicial = oSqlCmd.CreateParameter();
Fecha_Inicial.DbType = DbType.DateTime;
Fecha_Inicial.Direction = ParameterDirection.Input;
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;
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;
oSqlCmd.Parameters.Add(Id_Periodicidad);
Id_Periodicidad.Value = consulta.Id_Periodicidad;
TdParameter Id_Estructura2 = oSqlCmd.CreateParameter();
Id_Estructura2.DbType = DbType.String;
Id_Estructura2.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_Estructura2);
Id_Estructura2.Value = consulta.Id_Estructura;
TdParameter Fecha_Consulta2 = oSqlCmd.CreateParameter();
Fecha_Consulta2.DbType = DbType.DateTime;
Fecha_Consulta2.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Fecha_Consulta2);
Fecha_Consulta2.Value = consulta.Fecha_Consulta;
TdParameter Id_Fuente2 = oSqlCmd.CreateParameter();
Id_Fuente2.DbType = DbType.String;
Id_Fuente2.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_Fuente2);
Id_Fuente2.Value = consulta.Id_Fuente;
TdParameter Fecha_Inicial2 = oSqlCmd.CreateParameter();
Fecha_Inicial2.DbType = DbType.DateTime;
Fecha_Inicial2.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Fecha_Inicial2);
Fecha_Inicial2.Value = consulta.Fecha_Inicial;
TdParameter Fecha_Final2 = oSqlCmd.CreateParameter();
Fecha_Final2.DbType = DbType.DateTime;
Fecha_Final2.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Fecha_Final2);
Fecha_Final2.Value = consulta.Fecha_Final;
TdParameter Id_Periodicidad2 = oSqlCmd.CreateParameter();
Id_Periodicidad2.DbType = DbType.String;
Id_Periodicidad2.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_Periodicidad2);
Id_Periodicidad2.Value = consulta.Id_Periodicidad;
TdParameter Id_Estructura3 = oSqlCmd.CreateParameter();
Id_Estructura3.DbType = DbType.String;
Id_Estructura3.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_Estructura3);
Id_Estructura3.Value = consulta.Id_Estructura;
TdParameter Fecha_Consulta3 = oSqlCmd.CreateParameter();
Fecha_Consulta3.DbType = DbType.DateTime;
Fecha_Consulta3.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Fecha_Consulta3);
Fecha_Consulta3.Value = consulta.Fecha_Consulta;
TdParameter Id_Fuente3 = oSqlCmd.CreateParameter();
Id_Fuente3.DbType = DbType.String;
Id_Fuente3.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_Fuente3);
Id_Fuente3.Value = consulta.Id_Fuente;
TdParameter Fecha_Inicial3 = oSqlCmd.CreateParameter();
Fecha_Inicial3.DbType = DbType.DateTime;
Fecha_Inicial3.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Fecha_Inicial3);
Fecha_Inicial3.Value = consulta.Fecha_Inicial;
TdParameter Fecha_Final3 = oSqlCmd.CreateParameter();
Fecha_Final3.DbType = DbType.DateTime;
Fecha_Final3.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Fecha_Final3);
Fecha_Final3.Value = consulta.Fecha_Final;
TdParameter Id_Periodicidad3 = oSqlCmd.CreateParameter();
Id_Periodicidad3.DbType = DbType.String;
Id_Periodicidad3.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Id_Periodicidad3);
Id_Periodicidad3.Value = consulta.Id_Periodicidad;
result = oSqlCmd.ExecuteNonQuery();
oSqlCmd.Dispose();
oSqlConnection.Close();
}
}
catch (SqlException)
{
result = -1;
}
catch (TdException e)
{
result = -1;
}
catch (FormatException)
{
result = -1;
}
catch (OverflowException)
{
result = -1;
}
}
return(result);
}
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);
}
private void ExecuteSql(string sql)
{
switch (DbType)
{
case Type.SqlServer:
using (var connection = new SqlConnection(ConnectionString))
using (var command = new SqlCommand(sql, connection))
{
ConvertToXml(connection, command);
}
break;
case Type.Access:
using (var conn = new OleDbConnection(ConnectionString))
using (var comm = new OleDbCommand(sql, conn))
{
ConvertToXml(conn, comm);
}
break;
case Type.Oracle:
using (var connection = new OracleConnection(ConnectionString))
using (var command = new OracleCommand(sql, connection))
{
ConvertToXml(connection, command);
}
break;
case Type.MySql:
using (var connection = new MySqlConnection(ConnectionString))
using (var command = new MySqlCommand(sql, connection))
{
ConvertToXml(connection, command);
}
break;
case Type.Sqlite:
using (var connection = new SQLiteConnection(ConnectionString))
using (var command = new SQLiteCommand(sql, connection))
{
ConvertToXml(connection, command);
}
break;
case Type.PostGreSql:
using (var connection = new NpgsqlConnection(ConnectionString))
using (var command = new NpgsqlCommand(sql, connection))
{
ConvertToXml(connection, command);
}
break;
case Type.Teradata:
using (var connenction = new TdConnection(ConnectionString))
using (var command = new TdCommand(sql, connenction))
{
ConvertToXml(connenction, command);
}
break;
case Type.Odbc:
using (var connenction = new OdbcConnection(ConnectionString))
using (var command = new OdbcCommand(sql, connenction))
{
ConvertToXml(connenction, command);
}
break;
}
}
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);
}
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 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);
}
private int GuardarConsultaAgregacionNumerales(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 = "INSERT INTO " + instancia + ".V_RC_ConsultaAgregacionNumerales " +
"(Sk_Consulta, Id_Estructura, Fecha_Consulta, Id_Fuente, Fecha_Inicial, Fecha_Final," +
"Id_Periodicidad, Nombre_UsuarioCreacion, Fecha_Creacion, Sk_Lote, Sk_Lote_Upd," +
"Cod_Severidad)" +
" VALUES " +
" ((SEL COALESCE(MAX(Sk_Consulta), 0) + 1 FROM " + instancia + ".V_RC_ConsultaAgregacionNumerales)," +
"?, ?, ?, ?, ?,?,?, CURRENT_DATE, ?, null, 1); ";
oSqlCmd.CommandType = CommandType.Text;
oSqlCmd.CommandTimeout = 30;
oSqlCmd.Connection = oSqlConnection;
TdParameter Id_Estructura = oSqlCmd.CreateParameter();
Id_Estructura.DbType = DbType.String;
Id_Estructura.Direction = ParameterDirection.Input;
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;
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;
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;
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;
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;
oSqlCmd.Parameters.Add(Id_Periodicidad);
Id_Periodicidad.Value = consulta.Id_Periodicidad;
TdParameter Nombre_UsuarioCreacion = oSqlCmd.CreateParameter();
Nombre_UsuarioCreacion.DbType = DbType.String;
Nombre_UsuarioCreacion.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(Nombre_UsuarioCreacion);
Nombre_UsuarioCreacion.Value = consulta.Nombre_UsuarioCreacion;
TdParameter loteP = oSqlCmd.CreateParameter();
loteP.DbType = DbType.Int32;
loteP.Direction = ParameterDirection.Input;
oSqlCmd.Parameters.Add(loteP);
loteP.Value = lote;
result = oSqlCmd.ExecuteNonQuery();
oSqlCmd.Dispose();
oSqlConnection.Close();
}
}
catch (SqlException)
{
result = -1;
}
catch (TdException)
{
result = -1;
}
catch (FormatException)
{
result = -1;
}
catch (OverflowException)
{
result = -1;
}
}
return(result);
}