예제 #1
0
        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);
        }
예제 #2
0
 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));
     }
 }
예제 #3
0
        /// <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));
            }
        }
예제 #4
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));
            }
        }
예제 #5
0
        /// <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);
            }
        }
예제 #6
0
        /// <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);
        }
예제 #7
0
        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);
        }