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)); }