/// <summary>
/// Fetch System Parameters
/// </summary>
/// <returns>IEnumerable{SystemParametersDto}.</returns>
/// <exception cref="DataAccessException"></exception>
public IEnumerable<SystemParametersDto> FetchSystemParameters()
{
const string Sql = @"
SELECT [Id]
,[Name]
,[Documentation]
,[Type]
,[ProcessSystemName]
,[Expression]
,[GuidId]
,[LastModifiedOn]
FROM [dbo].[SystemParameters] sp
WHERE sp.IsRemoved = 0
";
var result = new List<SystemParametersDto>();
Database.GetDataReader(
Sql,
reader =>
{
if (reader == null)
{
throw new DataAccessException(Resources.FailedToRetrieveSystemOptions);
}
using (var sr = new SafeDataReader(reader))
{
while (reader.Read())
{
var parameter = new SystemParametersDto
{
ParameterId = sr.GetInt32(0),
Name = sr.GetString(1),
Documentation = sr.GetString(2),
ParameterType = !string.IsNullOrEmpty(reader.GetString(3))
? (SystemParameterType)Enum.Parse(typeof(SystemParameterType), reader.GetString(3))
: SystemParameterType.Int,
PersonProcessSystemName = sr.GetString(4),
Expression = sr.GetString(5),
Guid = sr.GetGuid(6),
LastModifiedOn = sr.GetDateTime(7)
};
result.Add(parameter);
}
}
});
return result;
}
/// <summary>
/// Update System Parameters
/// </summary>
/// <param name="parameters">The parameters.</param>
public void UpdateSystemParameters(SystemParametersDto parameters)
{
const string Sql = @"
UPDATE SystemParameters
SET
[Name] = @Name,
[Documentation] = @Documentation,
[Type] = @Type,
[ProcessSystemName] = @ProcessSystemName,
[Expression] = @Expression,
[GuidId] = @Guid,
[LastModifiedOn] = GETDATE()
WHERE [Id] = @p_Id
";
using (var cmd = new SqlCommand(Sql))
{
if (parameters != null)
{
cmd.Parameters.AddWithValue("@p_Id", parameters.ParameterId);
cmd.Parameters.AddWithValue("@Name", parameters.Name);
cmd.Parameters.AddWithValue("@Documentation", parameters.Documentation);
cmd.Parameters.AddWithValue("@Type", parameters.ParameterType.ToString());
cmd.Parameters.AddWithValue("@ProcessSystemName", parameters.PersonProcessSystemName);
cmd.Parameters.AddWithValue("@Expression", parameters.Expression);
cmd.Parameters.AddWithValue("@Guid", parameters.Guid);
}
Database.Execute(cmd);
}
}
/// <summary>
/// Insert System Parameters
/// </summary>
/// <param name="parameters">The parameters.</param>
public void InsertSystemParameters(SystemParametersDto parameters)
{
const string Sql = @"
INSERT INTO [dbo].[SystemParameters]
(
[Name],
[Documentation],
[Type],
[ProcessSystemName],
[Expression] ,
[GuidId],
[LastModifiedOn]
)
VALUES (
@Name,
@Documentation,
@Type,
@ProcessSystemName,
@Expression ,
@Guid,
GETDATE()
);
SELECT [Id] FROM [dbo].[SystemParameters] WHERE Id = SCOPE_IDENTITY()";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(Sql, cn))
{
if (parameters != null)
{
cmd.Parameters.AddWithValue("@Name", parameters.Name);
cmd.Parameters.AddWithValue("@Documentation", parameters.Documentation);
cmd.Parameters.AddWithValue("@Type", parameters.ParameterType.ToString());
cmd.Parameters.AddWithValue("@ProcessSystemName", parameters.PersonProcessSystemName);
cmd.Parameters.AddWithValue("@Expression", parameters.Expression);
cmd.Parameters.AddWithValue("@Guid", parameters.Guid);
parameters.ParameterId = (int)cmd.ExecuteScalar();
}
}
}
}