Error using client encoding in connection string

0

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();
        }
    }
}
c#
dapper
npgsql
asked on Stack Overflow Oct 24, 2018 by pospieszja • edited Oct 24, 2018 by Uwe Keim

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0