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 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);
}