public List <ColumnDto> GetColumnList(string tableName, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = $"SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '{tableName}'";
using NpgsqlDataReader rdr = cmd.ExecuteReader();
var columns = new List <ColumnDto>();
while (rdr.Read())
{
columns.Add(new ColumnDto()
{
ColumnName = rdr.GetString(0),
Value = rdr.GetString(1)
});
}
con.Close();
return(columns);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List <string> GetListOfTables(string schema, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = $"SELECT TABLE_NAME FROM {connDto.Database}.information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = '{schema}'";
using NpgsqlDataReader rdr = cmd.ExecuteReader();
var tableNames = new List <string>();
while (rdr.Read())
{
tableNames.Add(rdr.GetString(0));
}
con.Close();
return(tableNames);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List <string> GetSchemasForDatabase(ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT schema_name FROM information_schema.schemata;";
using NpgsqlDataReader rdr = cmd.ExecuteReader();
var schemas = new List <string>();
while (rdr.Read())
{
var name = rdr.GetString(0);
if (!name.Contains("pg_") && !name.Equals("information_schema") && !name.Equals("pgagent"))
{
schemas.Add(name);
}
}
con.Close();
return(schemas);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List <string> GetConstraints(string tableName, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = $"SELECT c.conname AS constraint_name FROM pg_constraint c JOIN pg_class t ON c.conrelid = t.oid JOIN pg_namespace n ON t.relnamespace = n.oid WHERE t.relname = '{tableName}';";
using NpgsqlDataReader rdr = cmd.ExecuteReader();
var constraints = new List <string>();
while (rdr.Read())
{
constraints.Add(rdr.GetString(0));
}
con.Close();
return(constraints);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// return the list of users whome the logedin user is following.
/// </summary>
/// <param name="uId">The u identifier.</param>
/// <returns></returns>
public IList <ConnectionDto> GetFollowee(int uId)
{
// var list = glitterDb.Users.Include(followee => followee.Connections.Where(i => i.Follower_id == uId)).ToList();
var list = glitterDb.Connections.Where(i => i.Follower_id == uId).Include(i => i.User).ToList();
ConnectionDto user;
if (list.Count > 0)
{
foreach (var followee in list)
{
user = new ConnectionDto();
user.id = followee.Followee_id;
user.Firstname = followee.User.Firstname;
user.Lastname = followee.User.Lastname;
user.image = followee.User.Image;
connectionList.Add(user);
}
}
else
{
list = null;
}
return(connectionList);
}
public List <string> GetDatabasesInServer(ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT datname FROM pg_database WHERE datistemplate = false;";
using NpgsqlDataReader rdr = cmd.ExecuteReader();
var dbs = new List <string>();
while (rdr.Read())
{
dbs.Add(rdr.GetString(0));
}
con.Close();
return(dbs);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public async Task add_existing_connection_returns_expected_status_code()
{
var slackFacadeSpy = new SlackFacadeSpy();
var logger = new LoggerFactory().CreateLogger <SlackService>();
var clientId = Guid.NewGuid();
var connection = new ConnectionDto()
{
ClientId = Guid.NewGuid().ToString(),
ClientName = "MyClient",
ClientType = "capability",
ChannelId = Guid.NewGuid().ToString(),
ChannelName = "MyChannel",
ChannelType = "slack"
};
using (var builder = new HttpClientBuilder())
{
var client = builder
.WithService <ICapabilityRepository>(new StubCapabilityRepository(new List <Guid> {
clientId
}))
.WithService <ISlackFacade>(new SlackFacadeStub(simulateFailOnSendMessage: false))
.WithService <ISlackService>(new SlackService(slackFacadeSpy, logger))
.Build();
var payload = new ObjectContent(connection.GetType(), connection, new JsonMediaTypeFormatter());
var response = await client.PostAsync($"api/v1/connections", payload);
var response2 = await client.PostAsync($"api/v1/connections", payload);
Assert.Equal(HttpStatusCode.Accepted, response.StatusCode);
Assert.Equal(HttpStatusCode.Accepted, response2.StatusCode);
}
}
public async Task <RefreshQueueInformationDto> GetQueueInfomation(ConnectionDto dto)
{
//1. get count of users before me, if == 0 : && total online customers < max count, redirectable = true
//2. get count of users before me for display and predicted total minutes
var result = new RefreshQueueInformationDto();
var predictedMinutesForOneUser = await _queueHistoryManager.GetAveragePredictedMinutes();
var countBeforeMe = await _queueSystemManager.GetTotalUsersCountBeforeMe(dto.ConnectionId);
var totalOnlineCustomers = await this.GetOnlineCustomersCount();
bool redirctable = false;
var canAccessSystem = await this.CanAccessSystem(new UserIdDto { NtId = string.Empty });
if (countBeforeMe == 0 && canAccessSystem)
{
redirctable = true;
}
result.Redirectable = redirctable;
result.UsersCountBeforeMe = countBeforeMe;
result.PredictedMinutes = predictedMinutesForOneUser * countBeforeMe;
result.UsersInQueueCountForFastToken = usersInQueueCountForFastToken;
return(result);
}
public void CreateDatabaseTable(AddNewTableDto tableDto, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = $"DROP TABLE IF EXISTS {tableDto.TableName}";
cmd.ExecuteNonQuery();
cmd.CommandText = $@"CREATE TABLE {tableDto.TableName}(id SERIAL PRIMARY KEY";
foreach (var column in tableDto.Columns)
{
cmd.CommandText += $", {column.ColumnName} {column.Value}";
}
cmd.CommandText += ")";
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void AddColumnToTable(AddNewTableDto tableDto, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = $"ALTER TABLE IF EXISTS {tableDto.TableName}";
foreach (var column in tableDto.Columns)
{
cmd.CommandText += $" ADD COLUMN {column.ColumnName} {column.Value},";
}
cmd.CommandText = cmd.CommandText.TrimEnd(',');
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public ConnectionDto GetConnectionInfo(string nickname)
{
con = new SQLiteConnection("Data Source=connDb.sqlite;Version=3;");
cmd = new SQLiteCommand($"SELECT Host, Username, Password, Port, Nickname, Database FROM Connections WHERE Nickname = '{nickname}'", con);
con.Open();
dr = cmd.ExecuteReader();
var conn = new ConnectionDto();
while (dr.Read())
{
if (dr.FieldCount != 6)
{
throw new Exception("Could not find connection info.");
}
conn.Host = dr.GetString(0);
conn.Username = dr.GetString(1);
conn.Password = dr.GetString(2);
conn.Port = dr.GetString(3);
conn.Nickname = dr.GetString(4);
conn.Database = dr.GetString(5);
}
con.Close();
return(conn);
}
public void Unregister(Connection connection)
{
ConnectionDto connectionDto = new ConnectionDto(
connection.Name,
connection.HostName,
connection.Port);
this.cacheRegistrationService.Unregister(connectionDto);
}
public void Initialize()
{
ConnectionDto connectionDto = new ConnectionDto(
this.Session.Connection.Name,
this.Session.Connection.HostName,
this.Session.Connection.Port);
SlabsDto slabContainer = this.cacheService.GetSlabs(connectionDto);
}
public async Task <ActionResult <ConnectionDto> > Get(string origin, string destination, int weight, int length, int height, int depth, string deliveryTypes)
{
if (weight > 40 || weight < 0 || string.IsNullOrEmpty(origin) || string.IsNullOrEmpty(destination))
{
return(NotFound());
}
var sentTypes = new List <DeliveryType>();
if (deliveryTypes != null)
{
var deliveryTypesArray = deliveryTypes.Split(',');
foreach (var deliveryType in deliveryTypesArray)
{
var knownDeliveryType = _deliveryTypes.FirstOrDefault(x => x == deliveryType);
if (knownDeliveryType == null)
{
return(NotFound());
}
if (!_supportedDeliveryTypes.Contains(knownDeliveryType))
{
return(NotFound());
}
var dbType = _context.DeliveryTypes.First(x => x.Name == knownDeliveryType);
sentTypes.Add(dbType);
}
}
var fromCityInvariant = origin.ToLower();
var toCityInvariant = destination.ToLower();
var connection = await _context.Connections
.Include(x => x.StartCity)
.Include(x => x.EndCity)
.FirstOrDefaultAsync(x =>
x.StartCity.CityName.ToLower() == fromCityInvariant &&
x.EndCity.CityName.ToLower() == toCityInvariant &&
x.Company == "Telstar");
if (connection == null)
{
return(NotFound());
}
var totalPriceAddition = sentTypes.Sum(x => x.Price);
var conn = new ConnectionDto
{
Price = connection.Price.Value + (connection.Price.Value * (totalPriceAddition / 100)),
Time = connection.Time.Value
};
return(Ok(conn));
}
public async Task <ConnectionDto> AddConnection(ConnectionDto connectionDto)
{
var connection = _mapper.Map <Connection>(connectionDto);
await _unitOfWork.Connections.AddAsync(connection);
await _unitOfWork.CommitAsync();
return(connectionDto);
}
public async Task <ConnectionDto> RemoveConnection(ConnectionDto connectionDto)
{
var connection = _mapper.Map <Connection>(connectionDto);
_unitOfWork.Connections.Delete(connection);
await _unitOfWork.CommitAsync();
return(connectionDto);
}
public int AddRowToTable(AddNewRowDto tableDto, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = $"INSERT INTO {tableDto.TableName} (";
for (int i = 1; i < tableDto.Columns.Count; i++)
{
cmd.CommandText += tableDto.Columns[i].ColumnName.ToString();
cmd.CommandText += ",";
}
cmd.CommandText = cmd.CommandText.TrimEnd(',');
cmd.CommandText += ") VALUES (";
for (int i = 0; i < tableDto.Rows.Count; i++)
{
if (RequiresQuotes(tableDto.Columns[i + 1].Value))
{
cmd.CommandText += $"'{tableDto.Rows[i]}'";
}
else
{
cmd.CommandText += tableDto.Rows[i];
}
cmd.CommandText += ",";
}
cmd.CommandText = cmd.CommandText.TrimEnd(',');
cmd.CommandText += ") RETURNING ID";
NpgsqlDataReader dr = cmd.ExecuteReader();
int id = 0;
while (dr.Read())
{
id = dr.GetInt32(0);
}
con.Close();
return(id);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void AddData(ConnectionDto dto)
{
con = new SQLiteConnection("Data Source=connDb.sqlite;Version=3;");
cmd = new SQLiteCommand();
con.Open();
cmd.Connection = con;
cmd.CommandText = $"INSERT INTO Connections(Host, Username, Password, Port, Nickname, Database) VALUES ('{dto.Host}', '{dto.Username}', '{dto.Password}', '{dto.Port}', '{dto.Nickname}', '{dto.Database}')";
cmd.ExecuteNonQuery();
con.Close();
}
public Collection <ItemDto> GetItems(ConnectionDto connection, SlabDto slab, bool mustRetrieveValues)
{
Collection <ItemDto> items = null;
Cache cache = new Cache(connection);
cache.Connect();
items = cache.GetItems(slab);
cache.Disconnect();
return(items);
}
public ActionResult <Connection> CreateConnection(ConnectionDto connectionCreateDto)
{
if (connectionCreateDto == null)
{
throw new ArgumentException(nameof(connectionCreateDto));
}
_connectionsRepository.CreateConnection(_mapper.Map <Connection>(connectionCreateDto));
_connectionsRepository.SaveChanges();
return(Created("", connectionCreateDto));
}
public SlabsDto GetSlabs(ConnectionDto connection)
{
SlabsDto slabs = null;
Cache cache = new Cache(connection);
cache.Connect();
slabs = cache.GetSlabs();
cache.Disconnect();
return(slabs);
}
public void TestStoreRetrieve()
{
ICacheService service = this.GetService();
ConnectionDto connection = new ConnectionDto(".", "localhost", 11211);
const string Key = "test";
const string Value = "data";
service.Set(connection, Key, Value);
string resultValue = service.Get(connection, Key) as string;
Assert.AreEqual(Value, resultValue);
}
public int UpdateRowFromTable(FormUpdateDto dto, ConnectionDto connDto)
{
try
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
using var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = $"UPDATE {dto.TableName} SET";
for (int i = 0; i < dto.Columns.Count; i++)
{
cmd.CommandText += $" {dto.Columns[i].ColumnName} = ";
if (RequiresQuotes(dto.Columns[i].Value))
{
cmd.CommandText += $"'{dto.Values[i]}'";
}
else
{
cmd.CommandText += $"{dto.Values[i]}";
}
cmd.CommandText += ",";
}
cmd.CommandText = cmd.CommandText.TrimEnd(',');
cmd.CommandText += $" WHERE {dto.WhereColumn.ColumnName} {dto.WhereOperator} ";
if (RequiresQuotes(dto.WhereColumn.Value))
{
cmd.CommandText += $"'{dto.WhereValue}'";
}
else
{
cmd.CommandText += $"{dto.WhereValue}";
}
NpgsqlDataReader dr = cmd.ExecuteReader();
con.Close();
return(dr.RecordsAffected);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public async Task <PersonDto> DeleteConnection(int id, ConnectionDto connectionDto)
{
var personEntity = await _unitOfWork.PersonRepository.GetPersonWithDetailsAsync(id);
if (personEntity == null)
{
throw new Exception();
}
var connectionEntity = await _unitOfWork.ConnecttionRepository.GetConnectionByConnectedPersonId(connectionDto.ConnectedPersonId);
_unitOfWork.ConnecttionRepository.Delete(connectionEntity);
await _unitOfWork.Commit();
return(_mapper.Map <Person, PersonDto>(personEntity));
}
public string CheckDatabaseVersion(ConnectionDto connDto)
{
using var con = new NpgsqlConnection(ConnectionString(connDto));
con.Open();
var sql = "SELECT VERSION()";
using var cmd = new NpgsqlCommand(sql, con);
var version = cmd.ExecuteScalar().ToString();
con.Close();
return(version);
}
public void handleConnectRequest(HttpListenerContext context)
{
try
{
ConnectionDto connParams = readResponseAsJson <ConnectionDto>(context.Request);
MsSqlRequestService msSqlRequestService = new MsSqlRequestService(connParams.server, connParams.database);
sendAnswerWithCode(context.Response, 200);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
sendStaticResourceWithCode(context.Response, JObject.FromObject(new { errorMessage = ex.Message }).ToString(), "application/json", 406);
return;
}
}
public void handleDatabaseListRequest(HttpListenerContext context)
{
try
{
ConnectionDto connParams = readResponseAsJson <ConnectionDto>(context.Request);
MsSqlRequestService msSqlRequestService = new MsSqlRequestService(connParams.server, "master");
List <string> databaseList = msSqlRequestService.requestDatabaseList(string.Format(Resources.connectionStringTemplate, connParams.server, "master"));
sendStaticResource(context.Response, JArray.FromObject(databaseList).ToString(), "application/javascript");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
sendStaticResourceWithCode(context.Response, JObject.FromObject(new { errorMessage = ex.Message }).ToString(), "application/json", 406);
}
}
public void handleServerObjectList(HttpListenerContext context)
{
try
{
ConnectionDto connParams = readResponseAsJson <ConnectionDto>(context.Request);
MsSqlRequestService msSqlRequestService = new MsSqlRequestService(connParams.server.ToString(), connParams.database.ToString());
string result = JArray.FromObject(msSqlRequestService.requestAllServerObjectList()).ToString();
sendStaticResource(context.Response, result, "application/javascript");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
sendStaticResourceWithCode(context.Response, JObject.FromObject(new { errorMessage = ex.Message }).ToString(), "application/json", 406);
return;
}
}
public void Register(ConnectionDto connection)
{
Cache cache = new Cache(connection);
try
{
cache.Connect();
cache.Disconnect();
CacheRegistrationService.Connections.Add(connection);
}
catch (Exception ex)
{
throw new CacheConnectionException(ex.Message, ex);
}
}
public async Task <ConnectionDto> AddConnection(int id, ConnectionDto connectionDto)
{
var connectionEntity = _mapper.Map <ConnectionDto, PersonConnection>(connectionDto);
connectionEntity.PersonId = id;
var personEntity = await _unitOfWork.PersonRepository.GetPersonWithDetailsAsync(id);
if (personEntity == null)
{
throw new Exception();
}
_unitOfWork.ConnecttionRepository.CreateConnection(connectionEntity);
await _unitOfWork.Commit();
return(_mapper.Map <PersonConnection, ConnectionDto>(connectionEntity));
}
