在Entity Framework Code-First Initializer中设置数据库整理 [英] Set database collation in Entity Framework Code-First Initializer

问题描述

我尝试过以下操作：

$ b我想要设置数据库的默认排序规则
$ b

  public class TestInitializer< T> ：DropCreateDatabaseAlways< T>其中T：DbContext 
 {
 protected override void Seed（T context）
 {
 context.Database.ExecuteSqlCommand（ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE）; 
 context.Database.ExecuteSqlCommand（ALTER DATABASE [Test] COLLATE Latin1_General_CI_AS）; 
 context.Database.ExecuteSqlCommand（ALTER DATABASE [Test] SET MULTI_USER）; 
} 
} 
  

当SQL Server为已经设置为相同的默认排序规则Latin1_General_CI_AS。

但是，如果我指定了不同的排序规则，则说SQL_Latin1_General_CP1_CI_AS失败，错误，

  System.Data.SqlClient.SqlException：重置连接会导致与初始登录不同的
状态。登录失败。 
  

任何人都可以建议我如何设置排序规则？

解决方案

使用命令拦截器解决方案

绝对有可能，虽然这是一个黑客。您可以使用命令拦截器更改CREATE DATABASE命令。 Il将拦截发送到数据库的所有命令，基于正则表达式识别数据库创建命令，并使用归类更改命令文本。

创建数据之前

  DbInterception.Add（new CreateDatabaseCollat​​ionInterceptor SQL_Romanian_Cp1250_CI_AS_KI_WI））; 
  

拦截器

 code> public class CreateDatabaseCollat​​ionInterceptor：IDbCommandInterceptor 
 {
 private readonly string _collat​​ion; 
 
 public CreateDatabaseCollat​​ionInterceptor（string collat​​ion）
 {
 _collat​​ion = collat​​ion; 
} 
 
 public void NonQueryExecuted（DbCommand命令，DbCommandInterceptionContext< int> interceptionContext）{} 
 public void NonQueryExecuting（DbCommand命令，DbCommandInterceptionContext< int> interceptionContext）
 { 
 //适用于SQL Server 
 if（Regex.IsMatch（command.CommandText，@^ create database \ [。*] $））
 {
命令。 CommandText + =COLLATE+ _collat​​ion; 
} 
} 
 public void ReaderExecuted（DbCommand命令，DbCommandInterceptionContext< DbDataReader> interceptionContext）{} 
 public void ReaderExecuting（DbCommand命令，DbCommandInterceptionContext< DbDataReader> interceptionContext）{} 
 public void ScalarExecuted（DbCommand命令，DbCommandInterceptionContext< object> interceptionContext）{} 
 public void ScalarExecuting（DbCommand命令，DbCommandInterceptionContext< object> interceptionContext）{} 
} 
  

备注

由于从一开始就使用正确的排序规则创建数据库，所有列将自动继承该排序规则，然后您不必再ALTER。

请注意，它将影响应用程序域内发生的任何后续数据库创建。因此，您可能希望在创建数据库后删除拦截器。

I want to set the default collation for a database, when Entity Framework Code First creates it.

I've tried the following:

public class TestInitializer<T> : DropCreateDatabaseAlways<T> where T: DbContext
{
    protected override void Seed(T context)
    {
        context.Database.ExecuteSqlCommand("ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
        context.Database.ExecuteSqlCommand("ALTER DATABASE [Test] COLLATE Latin1_General_CI_AS");
        context.Database.ExecuteSqlCommand("ALTER DATABASE [Test] SET MULTI_USER");
    }
}

This appears to run OK when SQL Server is already set to the same default collation Latin1_General_CI_AS.

But if I specify a different collation, say SQL_Latin1_General_CP1_CI_AS this fails with the error,

System.Data.SqlClient.SqlException: Resetting the connection results in a different 
state than the initial login. The login fails.

Can anyone advise how I can set the collation please?

解决方案

Solution with a command interceptor

It is definitely possible, though it's a bit of a hack. You can alter the CREATE DATABASE command with a command interceptor. Il will intercept all the commands sent to the database, recognize the database creation command based on a regex expression, and alter the command text with your collation.

Before database creation

DbInterception.Add(new CreateDatabaseCollationInterceptor("SQL_Romanian_Cp1250_CI_AS_KI_WI"));

The interceptor

public class CreateDatabaseCollationInterceptor : IDbCommandInterceptor
{
    private readonly string _collation;

    public CreateDatabaseCollationInterceptor(string collation)
    {
        _collation = collation;
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { }
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        // Works for SQL Server
        if (Regex.IsMatch(command.CommandText, @"^create database \[.*]$"))
        {
            command.CommandText += " COLLATE " + _collation;
        }
    }
    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { }
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { }
    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { }
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { }
}

Remarks

Since the database is created with the right collation from the start, all the columns will automatically inherit that collation and you wan't have to ALTER them afterwards.

Be aware that it will impact any later database creation occurring inside the application domain. So you might want to remove the interceptor after the database is created.

这篇关于在Entity Framework Code-First Initializer中设置数据库整理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！