Char encoding and SQL in C#

Question

I have this sql:

select productid from products where productcode = @code

and @code is a parameter, it's value was ABCÊ but it matched ABC debugging it in visual studio showed � in the quickwatch. the database has Latin1_General_CI_AS as collation. The field type in the database is an nvarchar(50)

So why is the datarow containing productcode = 'ABC' returned when I compare it to ABCÊ? I entered this in my smss select 1 where 'ABC' = 'ABCÊ' and it did not return 1, this did: select 1 where 'ABC' = 'ABC' So is it in my code? something with utf or encoding?

edit c# code:

SqlCommand comGetProd = new SqlCommand(@"SELECT ProductID FROM PRODUCTS WHERE (ProductCode = @name)");
comGetProd.Parameters.AddWithValue("name", "ABCÊ");

edit 15-6-2015

narrowed the problem down as @Oskar Sjöberg suggested. It is not database related! Because this returns true:

using (StreamReader sr = new StreamReader("test.csv"))
{
    while (!sr.EndOfStream)
    {                  
        Console.WriteLine("D1103SL".Equals(sr.ReadLine()));
    }
}

test.csv contains: D1103SLÊ

notepad++:

so it is http://www.codetable.net/hex/ca I still do not see how it is equal.

Answer 1

Apparently, "\xCA" is a non-breaking space on the Mac — the same as "\xA0" on Windows and in ISO-Latin-1, and " " in HTML.

http://use.perl.org/use.perl.org/commentsf6b4-3.html?sid=18447&op=reply&threshold=0&commentsort=0&mode=thread

So when using UTF-8 encoding it just knows that this is a nonsense char and can be skipped, so the strings are equal

using (StreamReader sr = new StreamReader("test.csv", ASCIIEncoding.UTF8))
{
    while (!sr.EndOfStream)
    {
        Console.WriteLine("D1103SL".Equals(sr.ReadLine()));
    }
}

and when using ascii, it 'sees' the Ê and when compared, it returns false.

Answer 2

Use

COLLATE DATABASE_DEFAULT

Like below

select productid from products where productcode COLLATE DATABASE_DEFAULT = @code

Answer 3

Try using SQL_Latin1_General_CP1_CI_AS instead of COLLATE Latin1_General_CI_AS. source

Answer 4

If you're using LINQ, you could use culture comparison.

var result = from a in collection.Products
             select a.Productid
             where string.Equals(a.ProductCode, 'ABCÊ', StringComparison.Ordinal);

Answer 5

I tried to setup the same scenario with the same collation, table and example data and ran this code:

var connection = new SqlConnection("connectionstringgoeshere");
connection.Open();
var command = new SqlCommand(@"SELECT ProductID FROM PRODUCTS WHERE (ProductCode = @name)", connection);
command.Parameters.AddWithValue("name", "ABCÊ");
var dataTable = new DataTable();
dataTable.Load(command.ExecuteReader());
Console.WriteLine(dataTable.Rows);  

It returns zero rows as expected and changing "ABCÊ" in the code to "ABC" returns one row with the correct data.

I think there is another issue at play here, but you need to include a more complete but still minimal example of your code, database schema and database data.