Query input parameters of type char, text and varchar should use collation set to connected database by default

[mjaric]

With the addition of Tds.Encoding , which supports other codepages, parameters of type char, varchar, and text should be encoded into the codepage set to the database by default. The database collation is received from the SQL server on the Login7 response. Since language and collation can be defined during database creation, we need to add more tests where additional databases are created before tests are run. The session will pick up these settings and apply them to the connection, so TDS should respect these settings on login and keep them in the connection state.

When decoding results, MSSQL returns ColumnInfo before rows. Column info, in almost all cases, contains collation, so this value should have higher priority than the one received during login.

However, it's difficult to use different collations for column and database settings. As a workaround for inserting, you can manually encode varchar and binary data sent as varbinary parameters during insertion. The column that should store this value must be in the used collation (CODEPAGE) to save it properly. While this workaround will allow you to read such columns, attempting to compare them (parameter and column) will likely fail, or the query will be over 10 times slower. The execution plan will generate undesired reads since the SQL server will convert the varchar column to varbinary to compare it, and this action will spill into tempdb.