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