public async Task <pg_result> SqlQueryAsync(string query,
CancellationToken cancellationToken = default)
{
if (_user == null)
{
throw new NullReferenceException("Not connected to any data source.");
}
pg_result res = PQexec(_user.Connection, query);
return(await Task.FromResult(res));
}
public string ResultAsGeoJson(pg_result res,
string typeName,
bool isLineString = true)
{
int nFields = PQnfields(res);
int nRows = PQntuples(res);
DataStructures.GeoJsonSchema.GeoJson geoJson = new DataStructures.GeoJsonSchema.GeoJson
{
Type = GeoJsonKind.FeatureCollection, Features = new List <FeatureType?>(nRows)
};
string colName;
int surfaceLongitudeIndex = -1;
int surfaceLatitudeIndex = -1;
int bottomLongitudeIndex = -1;
int bottomLatitudeIndex = -1;
if (isLineString)
{
for (int i = 0; i < nFields; ++i)
{
colName = PQfname(res, i);
if (colName.Contains("Surface") && colName.Contains("Longitude"))
{
surfaceLongitudeIndex = i;
}
else if (colName.Contains("Surface") && colName.Contains("Latitude"))
{
surfaceLatitudeIndex = i;
}
else if (colName.Contains("Bottom") && colName.Contains("Longitude"))
{
bottomLongitudeIndex = i;
}
else if (colName.Contains("Bottom") && colName.Contains("Latitude"))
{
bottomLatitudeIndex = i;
}
}
}
else
{
for (int i = 0; i < nFields; ++i)
{
colName = PQfname(res, i);
if (colName.Contains("Surface") && colName.Contains("Longitude"))
{
surfaceLongitudeIndex = i;
}
else if (colName.Contains("Surface") && colName.Contains("Latitude"))
{
surfaceLatitudeIndex = i;
}
}
}
if (-1 == surfaceLongitudeIndex || -1 == surfaceLatitudeIndex)
{
throw new NotSupportedException("-1 == surfaceLongitudeIndex ||-1 == surfaceLatitudeIndex ||-1 == bottomLongitudeIndex ||-1 == bottomLatitudeIndex");
}
if (isLineString)
{
if (-1 == bottomLongitudeIndex || -1 == bottomLatitudeIndex)
{
throw new NotSupportedException("-1 == surfaceLongitudeIndex ||-1 == surfaceLatitudeIndex ||-1 == bottomLongitudeIndex ||-1 == bottomLatitudeIndex");
}
}
if (isLineString)
{
Parallel.ForEach(Partitioner.Create(0, nRows, Math.Max(nRows / Environment.ProcessorCount, 1)),
row =>
{
string columnName;
OidKind colKind;
bool is_null;
double?surfaceLongitude;
double?surfaceLatitude;
double?bottomLongitude;
double?bottomLatitude;
Dictionary <string, object?> properties;
for (int i = row.Item1; i < row.Item2; i++)
{
properties = new Dictionary <string, object?>();
surfaceLongitude = null;
surfaceLatitude = null;
bottomLongitude = null;
bottomLatitude = null;
for (int j = 0; j < nFields; ++j)
{
columnName = PQfname(res, j);
colKind = OidTypes[PQftype(res, j)];
is_null = PQgetisnull(res, i, j) != 0;
if (j == surfaceLongitudeIndex && !is_null)
{
surfaceLongitude = double.Parse(PQgetvalue(res, i, j));
}
else if (j == surfaceLatitudeIndex && !is_null)
{
surfaceLatitude = double.Parse(PQgetvalue(res, i, j));
}
else if (j == bottomLongitudeIndex && !is_null)
{
bottomLongitude = double.Parse(PQgetvalue(res, i, j));
}
else if (j == bottomLatitudeIndex && !is_null)
{
bottomLatitude = double.Parse(PQgetvalue(res, i, j));
}
switch (colKind)
{
case OidKind.BOOLOID:
{
if (!is_null)
{
properties.Add(columnName, PQgetvalue(res, i, j) == "true");
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.BYTEAOID:
{
if (!is_null)
{
properties.Add(columnName, sbyte.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.INT2OID:
{
if (!is_null)
{
properties.Add(columnName, short.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.INT4OID:
{
if (!is_null)
{
properties.Add(columnName, int.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.INT8OID:
{
if (!is_null)
{
properties.Add(columnName, long.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.FLOAT4OID:
{
if (!is_null)
{
properties.Add(columnName, float.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.FLOAT8OID:
{
if (!is_null)
{
properties.Add(columnName, double.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.CHAROID:
{
if (!is_null)
{
properties.Add(columnName, char.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.VARCHAROID:
case OidKind.TEXTOID:
{
if (!is_null)
{
properties.Add(columnName, PQgetvalue(res, i, j).ToString());
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.TIMEOID:
case OidKind.TIMESTAMPOID:
case OidKind.DATEOID:
{
if (!is_null)
{
properties.Add(columnName, DateTime.Parse(PQgetvalue(res, i, j).ToString()));
}
else
{
properties.Add(columnName, null);
}
break;
}
default:
{
throw new NotSupportedException(colKind.ToString());
}
}
}
geoJson.Features.Add(new Feature
{
Id = i,
Geometry = new Geometry
{
Type = GeometryKind.LineString,
Coordinates = new List <CoordinateElement?>
{
new List <double>
{
surfaceLongitude ?? 0.0, surfaceLatitude ?? 0.0
},
new List <double>
{
bottomLongitude ?? 0.0, bottomLatitude ?? 0.0
}
}
},
Properties = properties
});
}
});
}
else
{
Parallel.ForEach(Partitioner.Create(0, nRows, Math.Max(nRows / Environment.ProcessorCount, 1)),
row =>
{
string columnName;
OidKind colKind;
bool is_null;
double?surfaceLongitude;
double?surfaceLatitude;
Dictionary <string, object?> properties;
for (int i = row.Item1; i < row.Item2; i++)
{
properties = new Dictionary <string, object?>();
surfaceLongitude = null;
surfaceLatitude = null;
for (int j = 0; j < nFields; ++j)
{
columnName = PQfname(res, j);
colKind = OidTypes[PQftype(res, j)];
is_null = PQgetisnull(res, i, j) != 0;
if (j == surfaceLongitudeIndex && !is_null)
{
surfaceLongitude = double.Parse(PQgetvalue(res, i, j));
}
else if (j == surfaceLatitudeIndex && !is_null)
{
surfaceLatitude = double.Parse(PQgetvalue(res, i, j));
}
switch (colKind)
{
case OidKind.BOOLOID:
{
if (!is_null)
{
properties.Add(columnName, PQgetvalue(res, i, j) == "true");
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.BYTEAOID:
{
if (!is_null)
{
properties.Add(columnName, sbyte.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.INT2OID:
{
if (!is_null)
{
properties.Add(columnName, short.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.INT4OID:
{
if (!is_null)
{
properties.Add(columnName, int.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.INT8OID:
{
if (!is_null)
{
properties.Add(columnName, long.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.FLOAT4OID:
{
if (!is_null)
{
properties.Add(columnName, float.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.FLOAT8OID:
{
if (!is_null)
{
properties.Add(columnName, double.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.CHAROID:
{
if (!is_null)
{
properties.Add(columnName, char.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.VARCHAROID:
case OidKind.TEXTOID:
{
if (!is_null)
{
properties.Add(columnName, PQgetvalue(res, i, j));
}
else
{
properties.Add(columnName, null);
}
break;
}
case OidKind.TIMEOID:
case OidKind.TIMESTAMPOID:
case OidKind.DATEOID:
{
if (!is_null)
{
properties.Add(columnName, DateTime.Parse(PQgetvalue(res, i, j)));
}
else
{
properties.Add(columnName, null);
}
break;
}
default:
{
throw new NotSupportedException(colKind.ToString());
}
}
}
geoJson.Features.Add(new Feature
{
Id = i,
Geometry = new Geometry
{
Type = GeometryKind.Point,
Coordinates = new List <CoordinateElement?>
{
surfaceLongitude ?? 0.0, surfaceLatitude ?? 0.0
}
},
Properties = properties
});
}
});
}
// DataManager.Instance[(mapboxData.Location, MediaTypes.Json)] = locationGeoJson.ToJson();
return(geoJson.ToJson());
}
public string ResultAsJson(pg_result res,
string typeName)
{
int nFields = PQnfields(res);
DbTypeBuilder dbTypeBuilder = new DbTypeBuilder("SqlQueryAssembly", "SqlQuery", typeName);
string colName;
Type col_type;
for (int i = 0; i < nFields; ++i)
{
colName = PQfname(res, i);
col_type = DbTypeBuilder.GetClrType(OidTypes[PQftype(res, i)]);
dbTypeBuilder.AddProperty(colName, col_type);
}
Type resultType = dbTypeBuilder.CreateType();
List <object> result = new List <object>(PQntuples(res));
for (int i = 0; i < PQntuples(res); ++i)
{
result.Add(dbTypeBuilder.CreateNew(resultType));
}
OidKind colKind;
for (int j = 0; j < PQntuples(res); j++)
{
bool is_null;
for (int i = 0; i < nFields; ++i)
{
//col_text = raw.sqlite3_column_text(stmt, i).utf8_to_string();
colKind = OidTypes[PQftype(res, i)];
is_null = PQgetisnull(res, j, i) != 0;
//Console.Write($"{PQgetvalue(res, j, i)} ");
switch (colKind)
{
case OidKind.BOOLOID:
{
if (!is_null)
{
DbTypeBuilder.SetProperty <bool?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], PQgetvalue(res, j, i) == "true");
}
else
{
DbTypeBuilder.SetProperty <bool?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], null);
}
break;
}
case OidKind.BYTEAOID:
{
if (!is_null)
{
DbTypeBuilder.SetProperty <sbyte?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], sbyte.Parse(PQgetvalue(res, j, i)));
}
else
{
DbTypeBuilder.SetProperty <sbyte?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], null);
}
break;
}
case OidKind.INT2OID:
{
if (!is_null)
{
DbTypeBuilder.SetProperty <short?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], short.Parse(PQgetvalue(res, j, i)));
}
else
{
DbTypeBuilder.SetProperty <short?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], null);
}
break;
}
case OidKind.INT4OID:
{
if (!is_null)
{
DbTypeBuilder.SetProperty <int?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], int.Parse(PQgetvalue(res, j, i)));
}
else
{
DbTypeBuilder.SetProperty <int?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], null);
}
break;
}
case OidKind.INT8OID:
{
if (!is_null)
{
DbTypeBuilder.SetProperty <long?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], long.Parse(PQgetvalue(res, j, i)));
}
else
{
DbTypeBuilder.SetProperty <long?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], null);
}
break;
}
case OidKind.FLOAT4OID:
{
if (!is_null)
{
DbTypeBuilder.SetProperty <float?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], float.Parse(PQgetvalue(res, j, i)));
}
else
{
DbTypeBuilder.SetProperty <float?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], null);
}
break;
}
case OidKind.FLOAT8OID:
{
if (!is_null)
{
DbTypeBuilder.SetProperty <double?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], double.Parse(PQgetvalue(res, j, i)));
}
else
{
DbTypeBuilder.SetProperty <double?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], null);
}
break;
}
case OidKind.CHAROID:
{
if (!is_null)
{
DbTypeBuilder.SetProperty <char?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], char.Parse(PQgetvalue(res, j, i)));
}
else
{
DbTypeBuilder.SetProperty <char?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], null);
}
break;
}
case OidKind.VARCHAROID:
case OidKind.TEXTOID:
{
if (!is_null)
{
DbTypeBuilder.SetProperty <string?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], PQgetvalue(res, j, i));
}
else
{
DbTypeBuilder.SetProperty <string?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], null);
}
break;
}
case OidKind.TIMEOID:
case OidKind.TIMESTAMPOID:
case OidKind.DATEOID:
{
if (!is_null)
{
DbTypeBuilder.SetProperty <DateTime?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], DateTime.Parse(PQgetvalue(res, j, i)));
}
else
{
DbTypeBuilder.SetProperty <DateTime?>(resultType, result[j], dbTypeBuilder.PropertyNames[i], null);
}
break;
}
default:
{
throw new NotSupportedException(colKind.ToString());
}
}
}
//Logger.LogInformation();
}
return(JsonSerializer.Serialize(result));
}
