Database connection string and collation

Robert Koritnik picture Robert Koritnik · Jan 25, 2010 · Viewed 7.4k times · Source

Is it possible to set connection collation within MySql connection string and how, since there's a default setting on the server that's used for new connections.

Two things I can't do:

  1. Can't call SET COLLATION_CONNECTION after I open a connection, because I'm using Entity Framework that does all the calls for me not entirely true as you may see in the edit
  2. Can't change server default connection collation because of other databases and their respected applications that use them.

All I'd like to specify is a certain connection string parameter in my web.config file like:

"User id=dbuser;Password=dbpass;Host=dbserver;Database=testung;Collation=utf8_general_ci"

but Collation setting/variable isn't recognised.

Technologies used

  • Asp.net MVC 2
  • IIS 7
  • Entity Framework 1
  • DevArt dotConnect MySql connector
  • MySql 5.1

EDIT 1

I've tried this code as @Devart suggested but to no avail:

partial void OnContextCreated()
{
    System.Data.Common.DbCommand command = this.Connection.CreateCommand();
    command.CommandText = "set collation_connection = utf8_slovenian_ci;";
    command.CommandType = System.Data.CommandType.Text;
    this.Connection.Open();
    command.ExecuteNonQuery();
    // this.Connection.Close();
}

Answer

Devart picture Devart · Jan 27, 2010

We recommend you to implement the OnContextCreated partial method.
You have access to the store connection in it and you can execute ADO.NET command "SET COLLATION = ..." using this connection.