public IEnumerable<EDCEditDto> FetchEdcList()
{
const string sql = @"
SELECT [Id]
,[Name]
,[SystemName]
,[Documentation]
,[GuidId]
FROM [dbo].[ExternalDataConfiguration] edc
";
var result = new List<EDCEditDto>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var cmd = new SqlCommand(sql, cn))
{
using (var sr = new SafeDataReader(cmd.ExecuteReader()))
{
while (sr.Read())
{
var parameter = new EDCEditDto
{
Id = sr.GetInt32(0),
Name = sr.GetString(1),
SystemName = sr.GetString(2),
Documentation = sr.GetString(3),
Guid = sr.GetGuid(4)
};
result.Add(parameter);
}
}
foreach (var dto in result)
{
dto.ConnectionList = new List<EDCConnectionEditDto>();
dto.DataVariableList = new List<EDCDataVariablesEditDto>();
FetchEDCConnectionList(cn, dto);
FetchDataVariableList(cn, dto);
}
}
}
return result;
}
public EDCEditDto FetchEdcBySystemName(string systemname)
{
const string sql = @"
SELECT [Id]
,[Name]
,[SystemName]
,[Documentation]
,[GuidId]
FROM [dbo].[ExternalDataConfiguration] edc
WHERE [SystemName] = @sysName
";
var result = new EDCEditDto();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var cmd = new SqlCommand(sql, cn))
{
cmd.Parameters.AddWithValue("@sysName", systemname);
using (var sr = new SafeDataReader(cmd.ExecuteReader()))
{
while (sr.Read())
{
var parameter = new EDCEditDto
{
Id = sr.GetInt32(0),
Name = sr.GetString(1),
SystemName = sr.GetString(2),
Documentation = sr.GetString(3),
Guid = sr.GetGuid(4)
};
result = parameter;
}
}
result.ConnectionList = new List<EDCConnectionEditDto>();
result.DataVariableList = new List<EDCDataVariablesEditDto>();
FetchEDCConnectionList(cn, result);
FetchDataVariableList(cn, result);
}
}
return result;
}
private static void FetchEDCConnectionList(SqlConnection connection, EDCEditDto dto)
{
const string Sql =
@"
SELECT
[Id]
,[GuidId]
,[EDCId]
,[ConnectionSetupId]
,[FirstExpression]
FROM
[dbo].[EDCConnections]
WHERE [EDCId] = @edcId;
";
using (var cmd = new SqlCommand(Sql, connection))
{
cmd.Parameters.AddWithValue("@edcId", dto.Id);
var result = new List<EDCConnectionEditDto>();
try
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var param = new EDCConnectionEditDto
{
Id = reader.GetInt(0),
Guid = reader.GetGuid(1),
EDCId = reader.GetInt32(2),
ConnectionSetupId = reader.GetInt32(3),
FirstExpression = reader.GetString(4)
};
result.Add(param);
}
foreach (var editDto in result)
{
editDto.Connection = new ConnectionSettingsDto();
FetchConnectionSetting(connection, editDto);
}
dto.ConnectionList.AddRange(result);
}
}
catch (Exception ex)
{
throw new ArgumentException(ex.Data.ToString());
}
}
}
private static void FetchDataVariableList(SqlConnection connection, EDCEditDto dto)
{
const string Sql =
@"
SELECT
[Id]
,[GuidId]
,[EDCId]
,[VariableName]
,[VariableSystemName]
,[VariableDataType]
FROM
[dbo].[EDCDataVariables]
WHERE [EDCId] = @edcId;
";
using (var cmd = new SqlCommand(Sql, connection))
{
cmd.Parameters.AddWithValue("@edcId", dto.Id);
try
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dataVariableType = DataVariablesType.None;
var type = reader.GetString(5);
if (!string.IsNullOrWhiteSpace(type))
{
if (type == "Double")
{
dataVariableType = DataVariablesType.Decimal;
}
else
{
dataVariableType = !string.IsNullOrEmpty(reader.GetString(5))
? (DataVariablesType)Enum.Parse(typeof(DataVariablesType), reader.GetString(5))
: DataVariablesType.None;
}
}
var edcDataVariablesEditDto = new EDCDataVariablesEditDto
{
Id = reader.GetInt(0),
Guid = reader.GetGuid(1),
EDCId = reader.GetInt32(2),
VariableName = reader.GetString(3),
VariableSystemName = reader.GetString(4),
VariableDataType = dataVariableType
};
dto.DataVariableList.Add(edcDataVariablesEditDto);
}
}
}
catch (Exception ex)
{
throw new ArgumentException(ex.Data.ToString());
}
}
}
public void UpdateEdc(EDCEditDto dto)
{
if (dto == null)
{
throw new ArgumentNullException("dto");
}
const string Sql = @"
UPDATE ExternalDataConfiguration
SET
[Name] = @Name,
[SystemName] =@SystemName,
[Documentation] = @Documentation,
[GuidId] =@GuidId,
[LastModifiedOn] = GETDATE()
WHERE [Id] = @Id
";
try
{
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
using (var cmd = new SqlCommand(Sql, connection))
{
cmd.Parameters.AddWithValue("@Id", dto.Id);
cmd.Parameters.AddWithValue("@Name", dto.Name);
cmd.Parameters.AddWithValue("@SystemName", dto.SystemName);
cmd.Parameters.AddWithValue("@Documentation", dto.Documentation);
cmd.Parameters.AddWithValue("@GuidId", dto.Guid);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
if (reader.RecordsAffected == 0)
throw new DBConcurrencyException(Resources.StaleDataException);
}
}
}
catch (DBConcurrencyException ex)
{
throw new ArgumentException(ex.Data.ToString());
}
}
public void InsertEdc(EDCEditDto dto)
{
const string Sql = @"
INSERT INTO [dbo].[ExternalDataConfiguration]
(
[Name]
,[SystemName]
,[Documentation]
,[GuidId]
,[LastModifiedOn]
)
VALUES (
@Name,
@SystemName,
@Documentation,
@GuidId,
GETDATE()
);
SELECT [Id] FROM [dbo].[ExternalDataConfiguration] WHERE Id = SCOPE_IDENTITY()";
try
{
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(Sql, cn))
{
if (dto != null)
{
cmd.Parameters.AddWithValue("@Name", dto.Name);
cmd.Parameters.AddWithValue("@SystemName", dto.SystemName);
cmd.Parameters.AddWithValue("@Documentation", dto.Documentation);
cmd.Parameters.AddWithValue("@GuidId", dto.Guid);
dto.Id = (int)cmd.ExecuteScalar();
}
}
}
}
catch (Exception ex)
{
throw new DBConcurrencyException(ex.Data.ToString());
}
}