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