private static void FetchComputerConnectionList(SqlConnection connection, ConnectionSettingsDto dto) { const string Sql = @" SELECT [Id] ,[GuidId] ,[ConnectionId] ,[ComputerConnection] ,[ConnectionType] ,[PortName] FROM [dbo].[MultiplexorComputerConnections] WHERE [ConnectionId] = @connectionId; "; using (var cmd = new SqlCommand(Sql, connection)) { cmd.Parameters.AddWithValue("@connectionId", dto.ConnectionSetupId); try { using (var reader = new SafeDataReader(cmd.ExecuteReader())) { while (reader.Read()) { var connectionSettingsDto = new ComputerConnectionEditDto { Id = reader.GetInt(0), Guid = reader.GetGuid(1), ConnectionId = reader.GetInt32(2), ComputerConnection = reader.GetString(3), ConnectionType = !string.IsNullOrEmpty(reader.GetString(4)) ? (ConnectionType)Enum.Parse(typeof(ConnectionType), reader.GetString(4)) : ConnectionType.SerialPort, PortName = reader.GetString(5) }; dto.ComputerConectionList.Add(connectionSettingsDto); } } } catch (Exception ex) { throw new DBConcurrencyException(ex.Data.ToString()); } } }
public IEnumerable<ConnectionSettingsDto> FetchConnectionSetupList() { const string sql = @" SELECT [ConnectionSetupId] ,[Name] ,[SystemName] ,[Documentation] ,[ConnectionType] ,[PortName] ,[BaudRate] ,[Parity] ,[Dtr] ,[FlowControlHighSignal] ,[ParityErrorChecking] ,[FlowControl] ,[Rts] ,[DataBits] ,[StopBits] ,[GuidId] ,[Vid] ,[Pid] ,[ParentIdPrefix] FROM [dbo].[MultiplexorConnections] cs WHERE cs.IsRemoved = 0 "; var result = new List<ConnectionSettingsDto>(); 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 ConnectionSettingsDto { ConnectionSetupId = sr.GetInt32(0), Name = sr.GetString(1), SystemName = sr.GetString(2), Documentation = sr.GetString(3), ConnectionType = !string.IsNullOrEmpty(sr.GetString(4)) ? (ConnectionType)Enum.Parse(typeof(ConnectionType), sr.GetString(4)) : ConnectionType.SerialPort, PortName = sr.GetString(5), BaudRate = sr.GetString(6), Parity = !string.IsNullOrEmpty(sr.GetString(7)) ? (SerialPortParity)Enum.Parse(typeof(SerialPortParity), sr.GetString(7)) : SerialPortParity.None, Dtr = sr.GetBoolean(8), FlowControlHighSignal = sr.GetInt32(9), ParityErrorChecking = sr.GetBoolean(10), FlowControl = !string.IsNullOrEmpty(sr.GetString(11)) ? (FlowControl)Enum.Parse(typeof(FlowControl), sr.GetString(11)) : FlowControl.None, Rts = sr.GetBoolean(12), DataBits = sr.GetInt32(13), StopBits = !string.IsNullOrEmpty(sr.GetString(14)) ? (StopBits)Enum.Parse(typeof(StopBits), sr.GetString(14)) : StopBits.One, Guid = sr.GetGuid(15), Vid = sr.GetString(16), Pid = sr.GetString(17), ParentIdPrefix = sr.GetString(18) }; result.Add(parameter); } } foreach (var dto in result) { dto.ComputerConectionList = new List<ComputerConnectionEditDto>(); FetchComputerConnectionList(cn, dto); } } } return result; }
public void UpdateConnectionSetup(ConnectionSettingsDto parameters) { if (parameters == null) { throw new ArgumentNullException("parameters"); } const string Sql = @" UPDATE MultiplexorConnections SET [Name] = @Name, [SystemName] =@SystemName, [Documentation] = @Documentation, [ConnectionType] =@ConnectionType, [PortName] =@PortName, [BaudRate] =@BaudRate, [Parity]=@Parity, [Dtr]=@Dtr, [FlowControlHighSignal]=@FlowControlHighSignal, [ParityErrorChecking]=@ParityErrorChecking, [FlowControl]=@FlowControl, [Rts]=@Rts, [DataBits]=@DataBits, [StopBits]=@StopBits, [GuidId] =@GuidId, [LastModifiedOn] = GETDATE(), [Vid] = @vid, [Pid] = @pid, [ParentIdPrefix] = @ParentIdPrefix WHERE [ConnectionSetupId] = @ConnectionSetupId "; try { using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false)) { var connection = ctx.Connection; using (var cmd = new SqlCommand(Sql, connection)) { cmd.Parameters.AddWithValue("@ConnectionSetupId", parameters.ConnectionSetupId); cmd.Parameters.AddWithValue("@Name", parameters.Name); cmd.Parameters.AddWithValue("@SystemName", parameters.SystemName); cmd.Parameters.AddWithValue("@Documentation", parameters.Documentation); cmd.Parameters.AddWithValue("@ConnectionType", parameters.ConnectionType.ToString()); cmd.Parameters.AddWithValue("@PortName", parameters.PortName); cmd.Parameters.AddWithValue("@BaudRate", parameters.BaudRate); cmd.Parameters.AddWithValue("@Parity", parameters.Parity.ToString()); cmd.Parameters.AddWithValue("@Dtr", parameters.Dtr); cmd.Parameters.AddWithValue("@FlowControlHighSignal", parameters.FlowControlHighSignal); cmd.Parameters.AddWithValue("@ParityErrorChecking", parameters.ParityErrorChecking); cmd.Parameters.AddWithValue("@FlowControl", parameters.FlowControl.ToString()); cmd.Parameters.AddWithValue("@Rts", parameters.Rts); cmd.Parameters.AddWithValue("@DataBits", parameters.DataBits); cmd.Parameters.AddWithValue("@StopBits", parameters.StopBits.ToString()); cmd.Parameters.AddWithValue("@GuidId", parameters.Guid); cmd.Parameters.AddWithValue("@Vid", parameters.Vid); cmd.Parameters.AddWithValue("@Pid", parameters.Pid); cmd.Parameters.AddWithValue("@ParentIdPrefix", parameters.ParentIdPrefix); using (var reader = new SafeDataReader(cmd.ExecuteReader())) if (reader.RecordsAffected == 0) throw new DBConcurrencyException(Resources.StaleDataException); } } } catch (Exception ex) { throw new DBConcurrencyException(ex.Data.ToString()); } }
public void InsertConnectionSetup(ConnectionSettingsDto parameters) { const string Sql = @" INSERT INTO [dbo].[MultiplexorConnections] ( [Name] ,[SystemName] ,[Documentation] ,[ConnectionType] ,[PortName] ,[BaudRate] ,[Parity] ,[Dtr] ,[FlowControlHighSignal] ,[ParityErrorChecking] ,[FlowControl] ,[Rts] ,[DataBits] ,[StopBits] ,[GuidId] ,[LastModifiedOn] ,[Vid] ,[Pid] ,[ParentIdPrefix] ) VALUES ( @Name, @SystemName, @Documentation, @ConnectionType, @PortName, @BaudRate, @Parity, @Dtr, @FlowControlHighSignal, @ParityErrorChecking, @FlowControl, @Rts, @DataBits, @StopBits, @GuidId, GETDATE(), @vid, @pid, @ParentIdPrefix ); SELECT [ConnectionSetupId] FROM [dbo].[MultiplexorConnections] WHERE ConnectionSetupId = SCOPE_IDENTITY()"; try { 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("@SystemName", parameters.SystemName); cmd.Parameters.AddWithValue("@Documentation", parameters.Documentation); cmd.Parameters.AddWithValue("@ConnectionType", parameters.ConnectionType.ToString()); cmd.Parameters.AddWithValue("@PortName", parameters.PortName); cmd.Parameters.AddWithValue("@BaudRate", parameters.BaudRate); cmd.Parameters.AddWithValue("@Parity", parameters.Parity.ToString()); cmd.Parameters.AddWithValue("@Dtr", parameters.Dtr); cmd.Parameters.AddWithValue("@FlowControlHighSignal", parameters.FlowControlHighSignal); cmd.Parameters.AddWithValue("@ParityErrorChecking", parameters.ParityErrorChecking); cmd.Parameters.AddWithValue("@FlowControl", parameters.FlowControl.ToString()); cmd.Parameters.AddWithValue("@Rts", parameters.Rts); cmd.Parameters.AddWithValue("@DataBits", parameters.DataBits); cmd.Parameters.AddWithValue("@StopBits", parameters.StopBits.ToString()); cmd.Parameters.AddWithValue("@GuidId", parameters.Guid); cmd.Parameters.AddWithValue("@vid", parameters.Vid); cmd.Parameters.AddWithValue("@pid", parameters.Pid); cmd.Parameters.AddWithValue("@ParentIdPrefix", parameters.ParentIdPrefix); parameters.ConnectionSetupId = (int)cmd.ExecuteScalar(); } } } } catch (Exception ex) { throw new DBConcurrencyException(ex.Data.ToString()); } }
public static ConnectionSettingsDto FetchConnectionSetup(SqlConnection connection, int Id) { const string sql = @" SELECT [ConnectionSetupId] ,[Name] ,[SystemName] ,[Documentation] ,[ConnectionType] ,[PortName] ,[BaudRate] ,[Parity] ,[Dtr] ,[FlowControlHighSignal] ,[ParityErrorChecking] ,[FlowControl] ,[Rts] ,[DataBits] ,[StopBits] ,[GuidId] ,[Vid] ,[Pid] ,[ParentIdPrefix] FROM [dbo].[MultiplexorConnections] mpxc WHERE mpxc.IsRemoved = 0 AND mpxc.[ConnectionSetupId] = @id "; var result = new ConnectionSettingsDto(); using (var cmd = new SqlCommand(sql, connection)) { cmd.Parameters.AddWithValue("@id", Id); using (var sr = new SafeDataReader(cmd.ExecuteReader())) { while (sr.Read()) { var parameter = new ConnectionSettingsDto { ConnectionSetupId = sr.GetInt32(0), Name = sr.GetString(1), SystemName = sr.GetString(2), Documentation = sr.GetString(3), ConnectionType = !string.IsNullOrEmpty(sr.GetString(4)) ? (ConnectionType)Enum.Parse(typeof(ConnectionType), sr.GetString(4)) : ConnectionType.SerialPort, PortName = sr.GetString(5), BaudRate = sr.GetString(6), Parity = !string.IsNullOrEmpty(sr.GetString(7)) ? (SerialPortParity)Enum.Parse(typeof(SerialPortParity), sr.GetString(7)) : SerialPortParity.None, Dtr = sr.GetBoolean(8), FlowControlHighSignal = sr.GetInt32(9), ParityErrorChecking = sr.GetBoolean(10), FlowControl = !string.IsNullOrEmpty(sr.GetString(11)) ? (FlowControl)Enum.Parse(typeof(FlowControl), sr.GetString(11)) : FlowControl.None, Rts = sr.GetBoolean(12), DataBits = sr.GetInt32(13), StopBits = !string.IsNullOrEmpty(sr.GetString(14)) ? (StopBits)Enum.Parse(typeof(StopBits), sr.GetString(14)) : StopBits.One, Guid = sr.GetGuid(15), Vid = sr.GetString(16), Pid = sr.GetString(17), ParentIdPrefix = sr.GetString(18) }; result = parameter; } } result.ComputerConectionList = new List<ComputerConnectionEditDto>(); FetchComputerConnectionList(connection, result); } return result; }