2

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();
}
Robert Koritnik
  • 103,639
  • 52
  • 277
  • 404
  • This *should* be possible to set in your framework. Are you sure it doesn't have a command or setting for that? – Pekka Jan 25 '10 at 13:32
  • @Pekka: I added an additional tag that makes it clear I'm using Asp.net MVC with Entity Framework. I thought that Entity Framework made it clear already. – Robert Koritnik Jan 25 '10 at 13:38
  • If you can't set server, can you set it on the schema(db) or table level. I believe both are possible in mysql. – MindStalker Jan 25 '10 at 14:09
  • @MindStaker: Database, table and columns level collations are all the correctly set. But MySql also applies connection collation when you connect to the DB regardless of settings on schema. So it's one more setting you have to have in mind. – Robert Koritnik Jan 25 '10 at 15:05

2 Answers2

1

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.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Good idea. I've tried doing this, please check my **EDIT 1** up on the question. – Robert Koritnik Jan 27 '10 at 13:30
  • 1
    Just replace the "System.Data.Common.DbCommand command = this.Connection.CreateCommand();" line with the "System.Data.Common.DbCommand command = (this.Connection as EntityConnection).StoreConnection.CreateCommand();" one. – Devart Jan 29 '10 at 07:35
  • This works only if I don't close the connection afterwards. If I close it (as in my upper code) I get collation error. So I'm wondering whether EF closes it afterwards? In this case I wouldn'0t worry too much. But if it doesn't... I would have to close it myself. Afterwards if at all possible. – Robert Koritnik Feb 10 '10 at 17:33
  • Closing the connection results in the collation loss (the session is lost too in this case). EF will close the connection after the query is executed. The connection will be closed on the dispose of the ObjectContext anyway. – Devart Feb 11 '10 at 16:41
0

If anyone else stumbles over this problem or wants to issue a command when opening a connection: The answer regarding OnContextCreated does no longer work as the method does no longer exist/is no longer supported.

An alternative, which I use for executing SET NAMES <character set used by the database> is to append ;initialization command=\"SET NAMES '" + CharSet + "';\" to your connection string. According to Devart's documentation this also works for PostgreSQL, MSSQL and Oracle

This property can also be set inside EntityDeveloper when accessing the properties of the database connection and clicking on the Advanced button.

Steffen Winkler
  • 2,805
  • 2
  • 35
  • 58