/// <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());
}
static NpgsqlDbType GetColumnNpgsqlDbType(NpgsqlDbColumn c)
{
return(c.PostgresType.NpgsqlDbType ?? NpgsqlDbType.Text);
}
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);
}