/// <summary>
/// The insert checklist step.
/// </summary>
/// <param name="dto">The DTO object.</param>
/// <exception cref="System.ArgumentNullException">The input DTO is null.</exception>
public void InsertChecklistStep(ChecklistStepDto dto)
{
if (dto == null) throw new ArgumentNullException(string.Format(CultureInfo.InvariantCulture, Resources.NullArguementException, "dto"));
const string CommandText =
@"
INSERT INTO stepChecklist
( FieldId ,
QuestionProcessSystemName ,
AnswerProcessSystemName ,
AnswerProcessDefaultStateGuid ,
CommentsFieldSystemName ,
IsSingleQuestionView ,
ListDisplayFieldSystemName ,
AllowAdhocQuestions ,
FilterDefinition ,
FilterGuid ,
CanChangeItemState,
ShowLinkedItems,
SortFieldSystemName,
SortDirection,
HideValidationIcon
)
VALUES ( @fieldId ,
@questionProcessSystemName ,
@answerProcessSystemName ,
@answerProcessDefaultStateGuid ,
@commentsFieldSystemName ,
@isSingleQuestionView ,
@listDisplayFieldSystemName ,
@allowAdhocQuestions ,
@filterDefinition ,
@filterGuid ,
@canChangeItemState,
@showLinkedItems,
@sortFieldSystemName,
@sortDirection,
@hideValidationIcon
)";
using (var cmd = new SqlCommand(CommandText))
{
cmd.Parameters.AddWithValue("@fieldId", dto.FieldId);
cmd.Parameters.AddWithValue("@questionProcessSystemName", dto.QuestionProcessSystemName);
cmd.Parameters.AddWithValue("@answerProcessSystemName", dto.AnswerProcessSystemName);
cmd.Parameters.AddWithValue("@answerProcessDefaultStateGuid", dto.AnswerProcessDefaultStateGuid);
cmd.Parameters.AddWithValue("@commentsFieldSystemName", dto.CommentsFieldSystemName);
cmd.Parameters.AddWithValue("@isSingleQuestionView", dto.IsSingleQuestionView);
cmd.Parameters.AddWithValue("@listDisplayFieldSystemName", dto.ListDisplayFieldSystemName);
cmd.Parameters.AddWithValue("@allowAdhocQuestions", dto.AllowAdhocQuestions);
cmd.Parameters.AddWithValue("@filterDefinition", AdoHelper.NullCheck(dto.FilterDefinition));
cmd.Parameters.AddWithValue("@filterGuid", AdoHelper.NullCheck(dto.FilterGuid));
cmd.Parameters.AddWithValue("@canChangeItemState", dto.CanChangeItemState);
cmd.Parameters.AddWithValue("@showLinkedItems", dto.ShowLinkedItems);
cmd.Parameters.AddWithValue("@sortFieldSystemName", dto.SortFieldSystemName);
cmd.Parameters.AddWithValue("@sortDirection", dto.SortDirection.ToString());
cmd.Parameters.AddWithValue("@hideValidationIcon", dto.HideValidationIcon);
Database.Execute(cmd);
}
}
/// <summary>
/// Retrieves checklist step.
/// </summary>
/// <param name="processName">The process name.</param>
/// <param name="fieldName">The field name.</param>
/// <param name="isPublishedCopy">The is published copy.</param>
/// <returns>The <see cref="ChecklistStepDto" />.</returns>
public ChecklistStepDto FetchChecklistStep(string processName, string fieldName, bool isPublishedCopy = false)
{
const string CommandText = @"
DECLARE @fieldId AS INT
SELECT @fieldId = f.Id
FROM
[dbo].[Processes] p
INNER JOIN [dbo].[Sections] s ON s.ProcessId = p.Id
INNER JOIN [dbo].[Fields] f ON f.SectionId = s.Id
WHERE p.[SystemName] = @processName AND p.IsRemoved = 0 AND p.IsPublishedCopy = @isPublishedCopy AND f.SystemName = @fieldName;
EXEC [dbo].[GetChecklistStep] @FieldId = @fieldId;";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
using (var cmd = new SqlCommand(CommandText, ctx.Connection))
{
cmd.Parameters.AddWithValue("@processName", processName);
cmd.Parameters.AddWithValue("@fieldName", fieldName);
cmd.Parameters.AddWithValue("@isPublishedCopy", isPublishedCopy);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
var dto = new ChecklistStepDto
{
QuestionProcessSystemName = reader.GetString(0),
AnswerProcessSystemName = reader.GetString(1),
AnswerProcessDefaultStateGuid = reader.GetGuid(2),
CommentsFieldSystemName = reader.GetString(3),
IsSingleQuestionView = reader.GetBool(4),
ListDisplayFieldSystemName = reader.GetString(5),
AllowAdhocQuestions = reader.GetBool(6),
FilterDefinition = reader.GetString(7),
FilterGuid = reader.IsDBNull(8) ? null : (Guid?)reader.GetGuid(8),
CanChangeItemState = reader.GetBool(9),
ShowLinkedItems = reader.GetBool(10),
SortFieldSystemName = reader.GetString(11),
SortDirection = reader.GetEnum(12, SortDirection.Ascending),
HideValidationIcon = reader.GetBool(13)
};
return dto;
}
}
}
return null;
}
/// <summary>
/// Deletes checklist step.
/// </summary>
/// <param name="dto">The DTO object.</param>
/// <exception cref="System.ArgumentNullException">The input DTO is null.</exception>
public void DeleteChecklistStep(ChecklistStepDto dto)
{
if (dto == null) throw new ArgumentNullException(string.Format(CultureInfo.InvariantCulture, Resources.NullArguementException, "dto"));
const string CommandText = @"DELETE FROM stepChecklist WHERE FieldId = @fieldId";
using (var cmd = new SqlCommand(CommandText))
{
cmd.Parameters.AddWithValue("@fieldId", dto.FieldId);
Database.Execute(cmd);
cmd.CommandText = "DELETE FROM stepChecklistQAMap WHERE FieldId = @fieldId";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@fieldId", dto.FieldId);
Database.Execute(cmd);
}
}
