/// <summary>
/// Inserts schedule.
/// </summary>
/// <param name="dto">The DTO object.</param>
/// <exception cref="System.ArgumentException">The input DTO is null.</exception>
public void InsertShedule(ProcessScheduleEditDto dto)
{
if (dto == null) throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Resources.NullArguementException, "dto"));
const string CommandText =
@"
INSERT INTO [dbo].ProcessSchedule
(
ProcessId,
NumberOfItemsScheduled,
ScheduleFrequencyPatternId,
ScheduleKeyField,
ScheduleDisplayDateField,
ScheduleStartStateGuid,
ScheduleDateField,
ScheduleFilterGuid
)
VALUES
(
@p_ProcessId,
@p_NumberOfItemsScheduled,
@p_ScheduleFrequencyPatternId,
@p_ScheduleKeyField,
@p_ScheduleDisplayDateField,
@p_ScheduleStartStateGuid,
@p_ScheduleDateField,
@p_ScheduleFilterGuid
)";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
using (var command = new SqlCommand(CommandText, connection))
{
command.Parameters.AddWithValue("@p_ProcessId", dto.ProcessId);
command.Parameters.AddWithValue("@p_NumberOfItemsScheduled", dto.NumberOfItemsScheduled);
command.Parameters.AddWithValue("@p_ScheduleFrequencyPatternId", dto.ScheduleFrequencyPatternId);
command.Parameters.AddWithValue("@p_ScheduleKeyField", dto.ScheduleKeyField);
command.Parameters.AddWithValue("@p_ScheduleDisplayDateField", dto.ScheduleDisplayDateField);
command.Parameters.AddWithValue("@p_ScheduleStartStateGuid", dto.ScheduleStartStateGuid);
command.Parameters.AddWithValue("@p_ScheduleDateField", dto.ScheduleBasedOnDateField);
command.Parameters.AddWithValue("@p_ScheduleFilterGuid", dto.ScheduleFilterGuid);
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Updates schedule.
/// </summary>
/// <param name="dto">The DTO object.</param>
/// <exception cref="System.ArgumentNullException">The input DTO is null.</exception>
public void UpdateSchedule(ProcessScheduleEditDto dto)
{
if (dto == null) throw new ArgumentNullException(string.Format(CultureInfo.InvariantCulture, Resources.NullArguementException, "dto"));
const string CommandText =
@"
DECLARE @ScheduleId INT
SELECT TOP 1
@ScheduleId = Id
FROM [dbo].[ProcessSchedule]
WHERE [ProcessId] = @p_ProcessId
IF ISNULL(@ScheduleId, 0) > 0
BEGIN
UPDATE [dbo].[ProcessSchedule]
SET [NumberOfItemsScheduled] = @p_NumberOfItemsScheduled,
[ScheduleFrequencyPatternId] = @p_ScheduleFrequencyPatternId,
[ScheduleKeyField] = @p_ScheduleKeyField,
[ScheduleDisplayDateField] = @p_ScheduleDisplayDateField,
[ScheduleStartStateGuid] = @p_ScheduleStartStateGuid,
[ScheduleDateField] = @p_ScheduleDateField,
[ScheduleFilterGuid] = @p_ScheduleFilterGuid
WHERE [ProcessId] = @p_ProcessId
SELECT @ScheduleId
END
ELSE
BEGIN
INSERT INTO [dbo].ProcessSchedule
( ProcessId,
NumberOfItemsScheduled,
ScheduleFrequencyPatternId,
ScheduleKeyField,
ScheduleDisplayDateField,
ScheduleStartStateGuid,
ScheduleDateField,
ScheduleFilterGuid
)
VALUES ( @p_ProcessId,
@p_NumberOfItemsScheduled,
@p_ScheduleFrequencyPatternId,
@p_ScheduleKeyField,
@p_ScheduleDisplayDateField,
@p_ScheduleStartStateGuid,
@p_ScheduleDateField,
@p_ScheduleFilterGuid
)
SELECT [Id]
FROM [dbo].[ProcessSchedule]
WHERE [Id] = SCOPE_IDENTITY()
END
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var paramsUpdate = new SqlCommand(CommandText, cn))
{
paramsUpdate.Parameters.AddWithValue("@p_ProcessId", dto.ProcessId);
paramsUpdate.Parameters.AddWithValue("@p_NumberOfItemsScheduled", dto.NumberOfItemsScheduled);
paramsUpdate.Parameters.AddWithValue("@p_ScheduleFrequencyPatternId", dto.ScheduleFrequencyPatternId);
paramsUpdate.Parameters.AddWithValue("@p_ScheduleKeyField", dto.ScheduleKeyField);
paramsUpdate.Parameters.AddWithValue("@p_ScheduleDisplayDateField", dto.ScheduleDisplayDateField);
paramsUpdate.Parameters.AddWithValue("@p_ScheduleStartStateGuid", dto.ScheduleStartStateGuid);
paramsUpdate.Parameters.AddWithValue("@p_ScheduleDateField", dto.ScheduleBasedOnDateField);
paramsUpdate.Parameters.AddWithValue("@p_ScheduleFilterGuid", dto.ScheduleFilterGuid);
dto.Id = (int)paramsUpdate.ExecuteScalar();
}
}
}
/// <summary>
/// Retrieves schedule.
/// </summary>
/// <param name="id">The Process Schedule id.</param>
/// <returns>The <see cref="ProcessScheduleEditDto" />.</returns>
public ProcessScheduleEditDto FetchSchedule(int id)
{
var result = new ProcessScheduleEditDto();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
const string CommandText =
@"
SELECT [s].[Id]
, [s].[ProcessId]
, [s].[NumberOfItemsScheduled]
, [s].[ScheduleFrequencyPatternId]
, [s].[ScheduleKeyField]
, [s].[ScheduleDisplayDateField]
, [s].[ScheduleStartStateGuid]
, [s].[ScheduleDateField]
, [s].[ScheduleFilterGuid]
FROM [dbo].[ProcessSchedule] s
WHERE s.ProcessId = @p_Id";
using (var command = new SqlCommand(CommandText, connection))
{
command.Parameters.AddWithValue("@p_Id", id);
using (var reader = new SafeDataReader(command.ExecuteReader()))
{
if (reader.Read())
{
result.Id = reader.GetInt32(0);
result.ProcessId = reader.GetInt32(1);
result.NumberOfItemsScheduled = reader.GetInt32(2);
result.ScheduleFrequencyPatternId = reader.GetInt32(3);
result.ScheduleKeyField = reader.GetString(4);
result.ScheduleDisplayDateField = reader.GetString(5);
result.ScheduleStartStateGuid = reader.GetString(6);
result.ScheduleBasedOnDateField = reader.GetString(7);
result.ScheduleFilterGuid = reader.GetNullableGuid(8) ?? Guid.Empty;
}
else
{
return null;
}
}
}
}
return result;
}
