I already posted about the problems I ran into using UTF-8 encoding on MySQL and PHP. Unfortunately, MySQL still seems to not be completely happy about Unicode characters, even if you work with ASP.NET and NHibernate (which both have complete Unicode support by default).
Therefore, in order to get your UTF strings stored in the database and get them back correctly in .NET, you'll first have to specify the charset you're using for the database connection in your connection string. For instance:
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<reflection-optimizer use="false"/>
<session-factory>
<property name="dialect">NHibernate.Dialect.MySQL5Dialect</property>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
<property name="connection.connection_string">
Server=localhost;Database=db_name;User ID=user_id;Password=pwd;charset=utf8
</property>
<!-- other properties -->
</session-factory>
</hibernate-configuration>As far as I know, this should be equal to sending the "SET NAMES utf8;" command I used to send in PHP.
Then, you must ensure that your database tables actually use the Unicode encoding to store strings. This is especially true if your table creation SQL is automatically generated by NHibernate, through the GenerateSchemaCreationScript() method, because NHibernate will just use the default encoding (not Unicode). On Microsoft SQL Server it will instead use the Unicode column types like "nvarchar" and "ntext", preventing any problem.
Hence, when you create a new database, explicitly define the charset to use or MySQL will default to latin1:
CREATE DATABASE db_name
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_unicode_ci;Then you can normally create all your tables and start using them. Test the database using some uncommon characters, like the "famous" dotless turkish ı, and try to do some upper/lowercase conversion on the database.
