I'm using npgsql (ver 4.0.3) and Dapper (ver 1.50.5) in my .NET Core application (.NET Standard 2.0).
I query data from PostgreSQL. Output text had some problems with polish characters, so in connection string I force to use client encoding set to latin2. But with option this I get below error:
System.Data.DataException
HResult=0x80131501
Message=Error parsing column 0 (name=<null>)
Source=Dapper
StackTrace:
at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in C:\projects\dapper\Dapper\SqlMapper.cs:line 3609
at Dapper.SqlMapper.<QueryImpl>d__138`1.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.cs:line 1100
at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 723
Inner Exception 1:
DecoderFallbackException: Unable to translate bytes [A3] at index 2 from specified code page to Unicode.
Some information about database PostgreSQL 9.4.3 (on Debian) Database encoding set to LATIN2 Collation: pl_PL
Connection string:
User ID=postgres;Host=192.168.1.26;Port=5432;Client Encoding = latin2;Database=testdb;Pooling=true;
If I delete Client Encoding from connection string, db.Query() is working properly return values whithout errors. Buuut with wrong charactrer.
Return: MĽKA Should return: MĄKA
Below you can find my repository class. GetAllItems method return list of items. Field in database which may contain polish characters is called "nazwa_indeksu". db.Query() function is throwing exception. And exception is throwing only when "nazwa_indeksu" contain polish characters.
public class CatalogRepository : ICatalogRepository
{
private readonly IConfiguration _config;
public CatalogRepository(IConfiguration config)
{
_config = config;
}
internal IDbConnection Connection
{
get
{
return new NpgsqlConnection(_config.GetConnectionString("DBConnection"));
}
}
public IEnumerable<CatalogItem> GetAllItems(int pageIndex, int pageSize)
{
var sql = "select nazwa_indeksu as name, TRIM(indeks) as code, ilosc as quantity from g.gm_kartoteki where id_magazynu = 1 and ilosc > 0 order by nazwa_indeksu offset @Offset limit 10;";
int offset = (pageIndex - 1) * pageSize;
int limit = pageSize;
using (IDbConnection db = Connection)
{
db.Open();
return db.Query<CatalogItem>(sql, new { Offset = offset });
}
}
}
I also wrote simple console app which could help with reproduce the problem.
using Dapper;
using Npgsql;
using System;
using System.Data;
namespace DapperError
{
class Program
{
internal static IDbConnection Connection
{
get
{
return new NpgsqlConnection("User ID=postgres;Host=192.168.1.26;Port=5432;Client Encoding = latin2; Database=dbtest;Pooling=true;");
}
}
static void Main(string[] args)
{
var sql = "select nazwa_indeksu as name from g.gm_kartoteki;";
using (IDbConnection db = Connection)
{
db.Open();
var result = db.Query<string>(sql);
foreach (var item in result)
{
Console.WriteLine(item);
}
}
Console.ReadKey();
}
}
}
User contributions licensed under CC BY-SA 3.0