public async Task <ActionResult <List <Salesperson> > > Test()
{
var models = new List <Salesperson>();
var connectionString = _config[SystemConstants.ConnectionStringKey];
using (var connection = new Npgsql.NpgsqlConnection(connectionString))
{
await connection.OpenAsync();
var command = new Npgsql.NpgsqlCommand(@"SELECT v.businessentityid, v.emailaddress, v.firstname, v.lastname, v.phonenumber, v.territoryid FROM sales.vsalesperson AS v", connection);
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var model = new Salesperson();
model.BusinessEntityId = await reader.GetFieldValueAsync <int>(reader.GetOrdinal("businessentityid"));
model.FirstName = await reader.GetFieldValueAsync <string>(reader.GetOrdinal("firstname"));
model.TerritoryId = await reader.GetFieldValueAsync <int?>(reader.GetOrdinal("territoryid"));
models.Add(model);
}
}
}
return(models);
}
protected async Task <List <T> > DeserializerAsync <T>(Npgsql.NpgsqlCommand command, CancellationToken canToken) where T : new()
{
using (var dataRead = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult, canToken))
{
var func = DeserializerManager.GetInstance().GetFuncForType <T>(dataRead);
return(func(dataRead));
}
}
/// <summary>
/// Run query and convert return to int
/// </summary>
/// <param name="command">Command</param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <object> ConvertToIntegerAsync(Npgsql.NpgsqlCommand command, CancellationToken cancellationToken = default(CancellationToken))
{
using (var dr = await command.ExecuteReaderAsync(System.Data.CommandBehavior.SingleResult))
{
await dr.ReadAsync(cancellationToken);
return(dr.GetInt32(0));
}
}
/// <summary>
/// Run query and transform return to given Models. Important: Model needs to have a constructor which takes a NpgsqlDataReader
/// </summary>
/// <typeparam name="TModel">Model with correct constructor</typeparam>
/// <param name="command">Command</param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <object> ConvertToModelAsync <TModel>(Npgsql.NpgsqlCommand command, CancellationToken cancellationToken = default(CancellationToken))
where TModel : new()
{
using (var dr = await command.ExecuteReaderAsync(System.Data.CommandBehavior.SingleRow))
{
await dr.ReadAsync(cancellationToken);
return(CreateModel <TModel>(dr));
}
}
/// <summary>
/// Run query and transform return to List of given Models. Important: Model needs to have a constructor which takes a NpgsqlDataReader
/// </summary>
/// <typeparam name="TModel">Model with correct constructor</typeparam>
/// <param name="command"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <object> ConvertToListAsync <TModel>(Npgsql.NpgsqlCommand command, CancellationToken cancellationToken = default(CancellationToken))
where TModel : new()
{
using (var dr = await command.ExecuteReaderAsync(System.Data.CommandBehavior.Default))
{
var list = new List <TModel>();
while (await dr.ReadAsync(cancellationToken))
{
list.Add(CreateModel <TModel>(dr));
}
return(list);
}
}
/// <summary>
/// Returns usable and unusable IP addresses for call.route messages
/// </summary>
/// <returns></returns>
private async Task <List <Customer> > GetIPs()
{
var list = new List <Customer>();
using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(this._connectionString))
{
using (Npgsql.NpgsqlCommand com = new Npgsql.NpgsqlCommand(@"SELECT HOST(address) AS ip, prefix FROM domain.customer_ip
INNER JOIN customer ON customer.id = customer_ip.customer_id
WHERE customer.enabled = TRUE AND customer_ip.enabled = TRUE
AND customer_id IN (SELECT DISTINCT customer_id FROM customer_price WHERE NOW()
BETWEEN valid_from AND valid_to);", con))
{
try
{
com.CommandType = System.Data.CommandType.Text;
await con.OpenAsync();
using (var reader = await com.ExecuteReaderAsync(System.Data.CommandBehavior.SingleResult))
{
while (await reader.ReadAsync())
{
list.Add(new Customer()
{
IP = reader.GetString(0),
Prefix = reader.IsDBNull(1) ? string.Empty : reader.GetString(1)
});
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
finally
{
con.Close();
}
}
}
return(list);
}
public async Task <int> QtdRequestsDia(string Usuario)
{
int Requests = 0;
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = "SELECT COUNT(1) AS Requests FROM LogRequest WHERE Usuario = '" + Usuario + "' and cast(datarequest as date) = '" + DateTime.Now.ToString("yyyy-MM-dd") + "'";
conn.Open();
Npgsql.NpgsqlDataReader dr = await comm.ExecuteReaderAsync();
while (dr.Read())
{
Requests = Convert.ToInt32(dr["Requests"]);
}
}
return(Requests);
}