public void SQLMappingTest()
{
var sqlJob = new SqlJob
{
Comments = "blabla",
CreatedBy = "sdsd",
DatabaseName = "CGControlPanel",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 1",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa",
JobTypeEnum = 1,
Password = "******",
Triggers = new List<JobTrigger>(),
ServerName = "SQL",
UserName = "******",
ExecProcedure = "sarlanga"
};
var unitOfWork = new UnitOfWork();
unitOfWork.JobsRepository.Insert(sqlJob);
unitOfWork.Save();
}
public void Execute(IJobExecutionContext context)
{
var unitofWork = new UnitOfWork();
var triggerId = context.JobDetail.JobDataMap["TriggerId"];
var trigger = (SqlJobTrigger)unitofWork.JobTriggerRepository.GetByID(triggerId);
var job = (SqlJob) trigger.Job;
SqlConnection conn = null;
try
{
//var connectionString = @"data source=.\SQLEXPRESS;Integrated Security=false;user id=sa;password=123456;User Instance=false;Initial Catalog=cgControlPanel2";
var connectionString = @"data source=" + job.ServerName + ";Integrated Security=false;user id=" +
job.UserName +
";password="******";User Instance=false;Initial Catalog=" +
job.DatabaseName;
conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand(
job.ExecProcedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("@xInput", SqlDbType.Xml).Value =
new SqlXml(new XmlTextReader(trigger.XmlFormInputValues, XmlNodeType.Document, null));
cmd.Parameters.Add("@xOutput", SqlDbType.Xml).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@xResultado", SqlDbType.Xml).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@xLogEjecucion", SqlDbType.Xml).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@nRowsAffected", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@nRowsTotal", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
trigger.XmlTableOutput = cmd.Parameters["@xOutput"].Value.ToString();
trigger.XmlResult = cmd.Parameters["@xResultado"].Value.ToString();
trigger.XmlLog = cmd.Parameters["@xLogEjecucion"].Value.ToString();
trigger.RecordsAffected = int.Parse(cmd.Parameters["@nRowsAffected"].Value.ToString());
trigger.RecordsProcessed = int.Parse(cmd.Parameters["@nRowsTotal"].Value.ToString());
job.JobStatusEnum = (int)JobStatus.Success;
//unitofWork.JobsRepository.Update(job);
unitofWork.JobTriggerRepository.Update(trigger);
unitofWork.Save();
}
catch(Exception e)
{
var ex = e.Message;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void SQLMappingTest()
{
var sqlJob = new SqlJob
{
Comments = "blabla",
CreatedBy = "sdsd",
DatabaseName = "CGControlPanel",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
ExecutionDays = 1,
Group = "Replicación",
Name = "Job Replicación 1",
JobType = JobType.Automatico,
InputSchemaProcedure = "wdwdsadsa",
JOB_GROUP = "wwww",
JOB_NAME = "adasas",
JobStatus = JobStatus.Scheduled,
JobStatusEnum = 1,
JobTypeEnum = 1,
Password = "******",
Logs = new List<JobLog>(),
Triggers = new List<JobTrigger>(),
SCHED_NAME = "asdsdsa",
LastExecutionStatus = LastExecutionStatus.Success,
LastExecutionStatusEnum = 1,
ServerName = "SQL",
UserName = "******",
ExecProcedure = "sarlanga"
};
var unitOfWork = new UnitOfWork();
unitOfWork.JobsRepository.Insert(sqlJob);
unitOfWork.Save();
}
public void Execute(IJobExecutionContext context)
{
UnitOfWork = new UnitOfWork();
Helper = new Helper();
var triggerId = context.JobDetail.JobDataMap["TriggerId"];
if (log.IsInfoEnabled)
{
log.Info("Info en TriggerID: " + triggerId + " - /********** INICIO DE LLAMADA AL STORED PROCEDURE *****************/");
}
var trigger = (SqlJobTrigger)UnitOfWork.JobTriggerRepository.GetByID(int.Parse(triggerId.ToString()));
try
{
trigger.StartExecutionDate = DateTime.Now;
trigger.JobTriggerStatus = JobTriggerStatus.Ejecutando;
UnitOfWork.JobTriggerRepository.Update(trigger);
UnitOfWork.Save();
if (log.IsInfoEnabled)
log.Info("Info en TriggerID: " + trigger.JobTriggerId + " - Llamada al proceso Asinc. (Model.SqlScheduledJob.Execute).");
Helper.ExecuteAsyncNonQueryForTrigger(trigger);
}
catch (SqlException ex)
{
var sqlErrorMessage = Helper.BuildSqlErrorMessage(ex);
var fullError = "Error en TriggerID: " + trigger.JobTriggerId + "<br />Servidor: " + ex.Server + "<br />Procedure: " + ex.Procedure + "<br />Línea: " + ex.LineNumber + "<br />Errores Sql: " + sqlErrorMessage;
if (log.IsErrorEnabled)
log.Error(fullError);
trigger.OutputExecutionLog += fullError;
trigger.JobTriggerStatus = JobTriggerStatus.Error;
trigger.EndExecutionDate = DateTime.Now;
UnitOfWork.JobTriggerRepository.Update(trigger);
UnitOfWork.Save();
}
catch (DbEntityValidationException ex)
{
var entityValidationErrorMessage = Helper.BuildEntityValidationErrorMessage(ex);
var fullError = "Error en TriggerID: " + context.JobDetail.JobDataMap["TriggerId"] + @"<br />Entity Framework: " + entityValidationErrorMessage;
if (log.IsErrorEnabled)
log.Error(fullError);
trigger.OutputExecutionLog += fullError;
trigger.JobTriggerStatus = JobTriggerStatus.Error;
trigger.EndExecutionDate = DateTime.Now;
UnitOfWork.JobTriggerRepository.Update(trigger);
UnitOfWork.Save();
}
catch (Exception ex)
{
var standardErrorMessage = Helper.BuildRecursiveErrorMessage(ex);
var fullError = "Error en TriggerID: " + context.JobDetail.JobDataMap["TriggerId"] + @"<br />Standard: " + standardErrorMessage;
if (log.IsErrorEnabled)
log.Error(fullError);
trigger.OutputExecutionLog += fullError;
trigger.JobTriggerStatus = JobTriggerStatus.Error;
trigger.EndExecutionDate = DateTime.Now;
UnitOfWork.JobTriggerRepository.Update(trigger);
UnitOfWork.Save();
}
}
public void Add_Automatic_Trigger_to_Scheduler()
{
_schedulerService.AddJob(_sqlJob);
_sqlJobTrigger.Job = _sqlJob;
_sqlJobTrigger.ScheduledStartExecutionDate = DateTime.Now.AddSeconds(20);
_schedulerService.AddTrigger(_sqlJob, _sqlJobTrigger);
Thread.Sleep(65000*3);
var uw = new UnitOfWork();
var tr = uw.JobTriggerRepository.GetAll().First();
Assert.IsTrue(tr.JobTriggerStatus == JobTriggerStatus.Ejecutado);
}
public void TestMultipleTriggersWithoutThread()
{
_schedulerService.AddJob(_sqlJob);
_sqlJobTrigger.Job = _sqlJob;
_sqlJobTrigger.ScheduledStartExecutionDate = DateTime.Now;
_schedulerService.AddTrigger(_sqlJob, _sqlJobTrigger);
_sqlJobTrigger2.Job = _sqlJob;
_sqlJobTrigger.ScheduledStartExecutionDate = DateTime.Now;
_schedulerService.AddTrigger(_sqlJob, _sqlJobTrigger2);
_sqlJobTrigger3.Job = _sqlJob;
_sqlJobTrigger.ScheduledStartExecutionDate = DateTime.Now;
_schedulerService.AddTrigger(_sqlJob, _sqlJobTrigger3);
_sqlJobTrigger4.Job = _sqlJob;
_sqlJobTrigger.ScheduledStartExecutionDate = DateTime.Now;
_schedulerService.AddTrigger(_sqlJob, _sqlJobTrigger4);
_sqlJobTrigger5.Job = _sqlJob;
_sqlJobTrigger.ScheduledStartExecutionDate = DateTime.Now;
_schedulerService.AddTrigger(_sqlJob, _sqlJobTrigger5);
_sqlJobTrigger6.Job = _sqlJob;
_sqlJobTrigger.ScheduledStartExecutionDate = DateTime.Now;
_schedulerService.AddTrigger(_sqlJob, _sqlJobTrigger6);
Thread.Sleep(65000*6);
var uw2 = new UnitOfWork();
var tr = uw2.JobTriggerRepository.GetAll().First();
Assert.IsTrue(tr.JobTriggerStatus == JobTriggerStatus.Ejecutado);
}
public void Setup()
{
stream = new FileStream(@"C:\test.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
ISchedulerFactory sf = new StdSchedulerFactory();
if (_schedulerService == null)
{
_schedulerService = new DefaultJobSchedulerService();
((DefaultJobSchedulerService)_schedulerService).Scheduler = sf.GetScheduler();
}
_unitOfWork = new UnitOfWork();
_schedulerService.UnitOfWork = _unitOfWork;
//_schedulerService.Resume();
_sqlJobTrigger = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJob = new SqlJob
{
Comments = "blabla",
CreatedBy = "sdsd",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 1",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC",
};
_sqlJobTrigger2 = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba2</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJobTrigger3 = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba2</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJobTrigger4 = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba2</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJobTrigger5 = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba2</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJobTrigger6 = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba2</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJob2 = new SqlJob
{
Comments = "blabla2",
CreatedBy = "sdsd2",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 2",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa2",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC2",
};
_sqlJob3 = new SqlJob
{
Comments = "blabla3",
CreatedBy = "sdsd3",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 3",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa3",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC3",
};
_sqlJob4 = new SqlJob
{
Comments = "blabla4",
CreatedBy = "sdsd4",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 4",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa4",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC4",
};
_sqlJob4 = new SqlJob
{
Comments = "blabla5",
CreatedBy = "sdsd5",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 5",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa5",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC5",
};
_sqlJob5 = new SqlJob
{
Comments = "blabla6",
CreatedBy = "sdsd6",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 6",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa6",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC6",
};
}
public void Process_Daily_Automatic_Triggers_Should_Return_Quartz_Scheduled()
{
_sqlJob.JobType = JobType.Automático;
_sqlJob.Weekdays = 127;
_schedulerService.AddJob(_sqlJob);
_schedulerService.ProcessDailyJobs();
Thread.Sleep(65000 * 2);
var uw = new UnitOfWork();
var tr = uw.JobTriggerRepository.GetAll().First();
Assert.IsTrue(tr.JobTriggerStatus == JobTriggerStatus.Ejecutado);
}
public void Manual_Fire_Trigger_With_Scheduling()
{
_sqlJobTrigger.Job = _sqlJob;
_sqlJob.Triggers.Add(_sqlJobTrigger);
var uw = new UnitOfWork();
uw.JobsRepository.Insert(_sqlJob);
uw.Save();
_schedulerService.ExecuteManualJob(_sqlJobTrigger);
Thread.Sleep(65000*3);
var uw2 = new UnitOfWork();
var tr = uw2.JobTriggerRepository.GetAll().First();
Assert.IsTrue(tr.JobTriggerStatus == JobTriggerStatus.Ejecutado);
}
public void Delete_A_Trigger_Sould_Return_The_Trigger_Deleted()
{
_schedulerService.AddJob(_sqlJob);
_sqlJobTrigger.Job = _sqlJob;
_sqlJobTrigger.ScheduledStartExecutionDate = DateTime.Now;
_schedulerService.AddTrigger(_sqlJob, _sqlJobTrigger);
_sqlJobTrigger.DeletedBy = "Pepe";
_schedulerService.DeleteTrigger(_sqlJobTrigger);
Thread.Sleep(65000*3);
var uw2 = new UnitOfWork();
var triggers = uw2.JobTriggerRepository.GetAll();
Assert.IsTrue(!triggers.Contains(_sqlJobTrigger));
}
public void Delete_A_Job_Sould_The_Return_The_Job_Deleted()
{
_schedulerService.AddJob(_sqlJob);
_sqlJobTrigger.Job = _sqlJob;
_sqlJobTrigger.ScheduledStartExecutionDate = DateTime.Now.AddMinutes(5);
_schedulerService.AddTrigger(_sqlJob, _sqlJobTrigger);
_sqlJobTrigger2.Job = _sqlJob;
_sqlJobTrigger.ScheduledStartExecutionDate = DateTime.Now.AddMinutes(5);
_schedulerService.AddTrigger(_sqlJob, _sqlJobTrigger2);
_sqlJob.DeletedBy = "Pepe";
_sqlJob.InputXmlFixedParameters = "<test>probando</test>";
_schedulerService.DeleteJob(_sqlJob);
Thread.Sleep(40000);
var uw2 = new UnitOfWork();
var jobs = uw2.JobsRepository.GetAll();
Assert.IsTrue(!jobs.Contains(_sqlJob));
}
public void Add_Manual_Trigger_to_Scheduler()
{
_schedulerService.AddJob(_sqlJob);
_sqlJobTrigger.Job = _sqlJob;
_schedulerService.AddTrigger(_sqlJob, _sqlJobTrigger);
Thread.Sleep(60000*3);
var uw = new UnitOfWork();
var tr = uw.JobTriggerRepository.GetAll().First();
Assert.IsTrue(tr.JobTriggerStatus == JobTriggerStatus.Ejecutado);
}
public bool execute(Object data)
{
UserData user = (UserData)data;
using (UserProvider provider = new UserProvider(new databaseDataContext(ConnectionProvider.ConnectionString)))
{
UnitOfWork uof = new UnitOfWork(provider);
if (user.UserID == -1)
user.UserID = getUserID(user);
uof.Insert(user);
uof.Commit();
}
return true;
}
private int getUserID(Object data)
{
int id = 0;
using (UserProvider provider = new UserProvider(new databaseDataContext(ConnectionProvider.ConnectionString)))
{
UnitOfWork uof = new UnitOfWork(provider);
//var users = provider.GetUsers();
var users = uof.getNumberOfUsers() + 1;
id = uof.getNumberOfUsers() + 1;
}
return id;
}
private PaymentsProcessor(PWDatabase db, int processingPause)
{
this.database = new UnitOfWork(db);
this.processingPause = processingPause;
}
