Example #1
0
        /// <summary>
        /// Executes a Query asynchronously
        /// </summary>
        /// <param name="context">Database Context</param>
        /// <param name="query">SQL</param>
        /// <param name="parameters">Parameters</param>
        /// <returns>awaitable Task</returns>
        public static async Task <IReadOnlyDictionary <string, object>[]> NpgsqlQueryAsync(this DatabaseContext context, string query, NpgsqlParameter[] parameters = null)
        {
            //Place to store Results
            List <IReadOnlyDictionary <string, object> > results = new();
            //Create and Open Connection
            NpgsqlConnection connection = new(context.GetNpgsqlConnectionString());
            await connection.OpenAsync();

            //Create command with `query` Command Text
            NpgsqlCommand cmd = connection.CreateCommand();

            cmd.CommandText = query;
            //Add Parameters if present
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
            }
            //Execute Data Reader
            await using NpgsqlDataReader reader = await cmd.ExecuteReaderAsync();

            //Read Columns
            ReadOnlyCollection <NpgsqlDbColumn> columns = await reader.GetColumnSchemaAsync();

            //Read Results
            while (await reader.ReadAsync())
            {
                //Place to store Result
                Dictionary <string, object> result = new();
                //Create Object Array with the size of the amount of columns
                object[] values = new object[columns.Count];
                //Get Values
                reader.GetValues(values);
                //Iterate over each column
                for (int i = 0; i < columns.Count; i++)
                {
                    //Get Column
                    NpgsqlDbColumn column = columns[i];
                    //Add Result
                    result.Add(column.ColumnName, values[i]);
                }
                //Add Result to Results
                results.Add(result);
            }
            //Close Connection
            await connection.CloseAsync();

            //Return Results
            return(results.ToArray());
        }
Example #2
0
 static NpgsqlDbType GetColumnNpgsqlDbType(NpgsqlDbColumn c)
 {
     return(c.PostgresType.NpgsqlDbType ?? NpgsqlDbType.Text);
 }
Example #3
0
        public static JToken ConvertFromDB(object data, NpgsqlDbColumn column)
        {
            if (data == null)
            {
                return(null);
            }

            if (data == DBNull.Value)
            {
                return(null);
            }

            var postgresType = column.DataTypeName;

            if (column.DataType == typeof(Array))
            {
                postgresType = $"{postgresType}[]";
            }

            var type       = PgSqlTypeManager.GetDotNetType(postgresType);
            var npgsqlType = PgSqlTypeManager.GetNpgsqlDbType(postgresType);


            JToken newObject = null;

            switch (npgsqlType)
            {
            case NpgsqlDbType.Json:
            case NpgsqlDbType.Jsonb:
            {
                if (type.IsListType())
                {
                    var list       = new JArray();
                    var enumerable = data as IEnumerable;
                    foreach (var o in enumerable)
                    {
                        var jt = JToken.Parse(o.ToString());
                        //var jo = jt.ToBasicDotNetObject();
                        list.Add(jt);
                    }
                    newObject = list;
                }
                else
                {
                    newObject = JToken.Parse(data.ToString());
                }
                break;
            }

            case NpgsqlDbType.Json | NpgsqlDbType.Array:
            case NpgsqlDbType.Jsonb | NpgsqlDbType.Array:
            {
                var list       = new JArray();
                var enumerable = data as IEnumerable;
                foreach (var o in enumerable)
                {
                    var jt = JToken.Parse(o.ToString());

                    list.Add(jt);
                }
                newObject = list;
                break;
            }

            default:
                newObject = JToken.FromObject(data);
                break;
            }

            return(newObject);
        }