代码第一个定制数据库初始化程序

代码第一个定制数据库初始化程序

本文介绍了实体框架CTP 4 - 代码第一个定制数据库初始化程序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想实现自定义数据库初始化策略,以便我可以生成数据库模式,并使用提供的用户ID和密码将其应用于EXISTING EMPTY SQL数据库。



不幸的是,内置策略不能提供我正在寻找的内容:

  //默认策略仅在不存在的情况下创建数据库 - 但是
//存在,因此不执行任何操作
Database.SetInitializer(new CreateDatabaseOnlyIfNotExists< DataContext> ));

//删除并重新创建数据库,但这会破坏我的安全映射,
//仅在使用可信连接时有效
Database.SetInitializer(new RecreateDatabaseIfModelChanges< ; DataContext>());

//每次运行应用程序总是重新创建数据库的策略 - 对于
没有好处//我想要的
Database.SetInitializer (新的AlwaysRecreateDatabase< DataContext>());

我已经制定了以下内容,但这不会创建ModelHash,因此我无法使用context.Database.ModelMatchesDatabase()来验证数据库模式是否已创建,并阻止多次初始化:


$ b $ public class Initializer:IDatabaseInitializer< DataContext>
{
public void InitializeDatabase(DataContext context)
{
//这会生成我的POCO类的脚本
var sql = context.ObjectC ontext.CreateDatabaseScript();

//如预期的那样,当第二次运行时,它会在数据库
context.ObjectContext.ExecuteStoreCommand中已经有
//名为xxxxx的对象 (SQL);

this.seed(context)
context.SaveChanges();
}
}

问题:



有谁知道如何获取/创建模型哈希? (这是一个EdmMetadata实体)



-Or -



是否有更好的方式这样做使用Code First CTP?

解决方案

我遇到同样的问题。我没有真正解决它,但是我设法得到一个令人讨厌的解决方法运行,所以我可以部署我的解决方案到AppHarbor;)



它的一个IDatabaseInitializer实现,不删除数据库,但只是nukes所有的约束和表,然后使用ObjectContext.CreateDatabaseScript()方法生成sql,然后我执行它作为一个store命令。非常像上述问题的实现。



但是,我还添加了从模型创建哈希值并将其保存在数据库中的功能,当它再次运行时,它会检查如果当前的模型哈希匹配一个i db。就像真正的代码优先实现一样。



我无法使它在上下文中的构建工作.Database.CompatibleWithModel(true) - 但这应该也是一样的,并且看到它是一个临时的解决方法应该很好。

  using System; 
使用System.Data.Entity;
使用System.Data.Entity.Database;
使用System.Data.Entity.Design;
使用System.Data.Entity.Infrastructure;
使用System.Data.Metadata.Edm;
使用System.Data.Objects;
使用System.Globalization;
使用System.Security.Cryptography;
使用System.Text;
使用System.Xml;
使用System.Linq;

命名空间Devtalk
{
public class DontDropDbJustCreateTablesIfModelChanged< T> :IDatabaseInitializer< T>其中T:DbContext
{
private EdmMetadata _edmMetaData;

public void InitializeDatabase(T context)
{
ObjectContext objectContext =((IObjectContextAdapter)context).ObjectContext;
string modelHash = GetModelHash(objectContext);

if(CompatibleWithModel(modelHash,context,objectContext))返回;

DeleteExistingTables(objectContext);
CreateTables(objectContext);

SaveModelHashToDatabase(context,modelHash,objectContext);


private void SaveModelHashToDatabase(T context,string modelHash,ObjectContext objectContext)
{
if(_edmMetaData!= null)objectContext.Detach(_edmMetaData);

_edmMetaData = new EdmMetadata();
context.Set& EdmMetadata>()。Add(_edmMetaData);

_edmMetaData.ModelHash = modelHash;
context.SaveChanges();
}

private void CreateTables(ObjectContext objectContext)
{
string dataBaseCreateScript = objectContext.CreateDatabaseScript();
objectContext.ExecuteStoreCommand(dataBaseCreateScript);
}

private void DeleteExistingTables(ObjectContext objectContext)
{
objectContext.ExecuteStoreCommand(Dropallconstraintsscript);
objectContext.ExecuteStoreCommand(Deletealltablesscript);
}

私有字符串GetModelHash(ObjectContext context)
{
var csdlXmlString = GetCsdlXmlString(context).ToString();
返回ComputeSha256Hash(csdlXmlString);
}

private bool CompatibleWithModel(string modelHash,DbContext context,ObjectContext objectContext)
{
var isEdmMetaDataInStore = objectContext.ExecuteStoreQuery< int>(LookupEdmMetaDataTable).FirstOrDefault );
if(isEdmMetaDataInStore == 1)
{
_edmMetaData = context.Set& EdmMetadata>()。FirstOrDefault();
if(_edmMetaData!= null)
{
return modelHash == _edmMetaData.ModelHash;
}
}
return false;
}

私有字符串GetCsdlXmlString(ObjectContext context)
{
if(context!= null)
{
var entityContainerList = context。 MetadataWorkspace.GetItems< EntityContainer相关>(DataSpace.SSpace);
if(entityContainerList!= null)
{
EntityContainer entityContainer = entityContainerList.FirstOrDefault();
var generator = new EntityModelSchemaGenerator(entityContainer);
var stringBuilder = new StringBuilder();
var xmlWRiter = XmlWriter.Create(stringBuilder);
generator.GenerateMetadata();
generator.WriteModelSchema(xmlWRiter);
xmlWRiter.Flush();
return stringBuilder.ToString();
}
}
return string.Empty;
}

private static string ComputeSha256Hash(string input)
{
byte [] buffer = new SHA256Managed()。ComputeHash(Encoding.ASCII.GetBytes(input) );
var builder = new StringBuilder(buffer.Length * 2);
foreach(缓冲区中的字节数)
{
builder.Append(num.ToString(X2,CultureInfo.InvariantCulture));
}
return builder.ToString();
}

private const string Dropallconstraintsscript =
@select
'ALTER TABLE'+ so.table_name +'DROP CONSTRAINT'+ so.constraint_name
来自INFORMATION_SCHEMA.TABLE_CONSTRAINTS所以;

private const string Deletealltablesscript =
@declare @cmd varchar(4000)
declare cmds cursor for
选择
'drop table ['+ Table_Name +']'

INFORMATION_SCHEMA.TABLES

打开cmds
而1 = 1
开始
将cmds导入@cmd
if @@ fetch_status!= 0 break
print @cmd
exec(@cmd)
end
close cmds
deallocate cmds;

private const string LookupEdmMetaDataTable =
@选择COUNT(*)
FROM INFORMATION_SCHEMA.TABLES T
其中T.TABLE_NAME ='EdmMetaData';
}
}


I would like to implement a custom database initialization strategy so that I can generate the database schema and apply it to an EXISTING EMPTY SQL database using a supplied User ID and Password.

Unfortunately the built-in strategies don’t provide what I’m looking for:

// The default strategy creates the DB only if it doesn't exist - but it does
// exist so this does nothing
Database.SetInitializer(new CreateDatabaseOnlyIfNotExists<DataContext>());

// Drops and re-creates the database but then this breaks my security mapping and
// only works if using a "Trusted" connection
Database.SetInitializer(new RecreateDatabaseIfModelChanges<DataContext>());

// Strategy for always recreating the DB every time the app is run. – no good for
// what I want
Database.SetInitializer(new AlwaysRecreateDatabase<DataContext>());

I have worked out the following but this does not create the ModelHash so I’m unable to use "context.Database.ModelMatchesDatabase()" to validate that the database schema has been created and prevent multiple initializations:

public class Initializer : IDatabaseInitializer<DataContext>
{
    Public void InitializeDatabase(DataContext context)
    {
         // this generates the SQL script from my POCO Classes
         var sql = context.ObjectContext.CreateDatabaseScript();

         // As expected - when run the second time it bombs out here with "there is already an
         // object named xxxxx in the database"
         context.ObjectContext.ExecuteStoreCommand(sql);

         this.seed(context)
         context.SaveChanges();
    }
}

Questions:

Does anyone know how I can get/create the model hash? (which is an EdmMetadata Entity)

-Or-

Is there a better way of doing this in general using the Code First CTP?

解决方案

I ran into the same problem. I didn't really solve it, but I managed to get a little nasty workaround running, so i can deploy my solution to AppHarbor ;)

Its a IDatabaseInitializer implementation, that doesn't delete the db, but just nukes all the constraints and tables, and then uses the ObjectContext.CreateDatabaseScript() method to generate the sql, and then I execute it as a storecommand. A lot like the above implementation in the question.

But i also added functionality to create a hash from the model and save it in db, and when it runs again it checks if the current model-hash matches the one i db. Just like the real code-first implementation.

I couldn't make it work with the build in context.Database.CompatibleWithModel(true) - but this should work just as well, and seeing as its a temporary workaround it should be fine.

using System;
using System.Data.Entity;
using System.Data.Entity.Database;
using System.Data.Entity.Design;
using System.Data.Entity.Infrastructure;
using System.Data.Metadata.Edm;
using System.Data.Objects;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using System.Xml;
using System.Linq;

namespace Devtalk
{
    public class DontDropDbJustCreateTablesIfModelChanged<T> : IDatabaseInitializer<T> where T : DbContext
    {
        private EdmMetadata _edmMetaData;

        public void InitializeDatabase(T context)
        {
            ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
            string modelHash = GetModelHash(objectContext);

            if (CompatibleWithModel(modelHash, context, objectContext)) return;

            DeleteExistingTables(objectContext);
            CreateTables(objectContext);

            SaveModelHashToDatabase(context, modelHash, objectContext);
        }

        private void SaveModelHashToDatabase(T context, string modelHash, ObjectContext objectContext)
        {
            if (_edmMetaData != null) objectContext.Detach(_edmMetaData);

            _edmMetaData = new EdmMetadata();
            context.Set<EdmMetadata>().Add(_edmMetaData);

            _edmMetaData.ModelHash = modelHash;
            context.SaveChanges();
        }

        private void CreateTables(ObjectContext objectContext)
        {
            string dataBaseCreateScript = objectContext.CreateDatabaseScript();
            objectContext.ExecuteStoreCommand(dataBaseCreateScript);
        }

        private void DeleteExistingTables(ObjectContext objectContext)
        {
            objectContext.ExecuteStoreCommand(Dropallconstraintsscript);
            objectContext.ExecuteStoreCommand(Deletealltablesscript);
        }

        private string GetModelHash(ObjectContext context)
        {
            var csdlXmlString = GetCsdlXmlString(context).ToString();
            return ComputeSha256Hash(csdlXmlString);
        }

        private bool CompatibleWithModel(string modelHash, DbContext context, ObjectContext objectContext)
        {
            var isEdmMetaDataInStore = objectContext.ExecuteStoreQuery<int>(LookupEdmMetaDataTable).FirstOrDefault();
            if (isEdmMetaDataInStore == 1)
            {
                _edmMetaData = context.Set<EdmMetadata>().FirstOrDefault();
                if (_edmMetaData != null)
                {
                    return modelHash == _edmMetaData.ModelHash;
                }
            }
            return false;
        }

        private string GetCsdlXmlString(ObjectContext context)
        {
            if (context != null)
            {
                var entityContainerList = context.MetadataWorkspace.GetItems<EntityContainer>(DataSpace.SSpace);
                if (entityContainerList != null)
                {
                    EntityContainer entityContainer = entityContainerList.FirstOrDefault();
                    var generator = new EntityModelSchemaGenerator(entityContainer);
                    var stringBuilder = new StringBuilder();
                    var xmlWRiter = XmlWriter.Create(stringBuilder);
                    generator.GenerateMetadata();
                    generator.WriteModelSchema(xmlWRiter);
                    xmlWRiter.Flush();
                    return stringBuilder.ToString();
                }
            }
            return string.Empty;
        }

        private static string ComputeSha256Hash(string input)
        {
            byte[] buffer = new SHA256Managed().ComputeHash(Encoding.ASCII.GetBytes(input));
            var builder = new StringBuilder(buffer.Length * 2);
            foreach (byte num in buffer)
            {
                builder.Append(num.ToString("X2", CultureInfo.InvariantCulture));
            }
            return builder.ToString();
        }

        private const string Dropallconstraintsscript =
            @"select
                'ALTER TABLE ' + so.table_name + ' DROP CONSTRAINT ' + so.constraint_name
                from INFORMATION_SCHEMA.TABLE_CONSTRAINTS so";

        private const string Deletealltablesscript =
            @"declare @cmd varchar(4000)
                declare cmds cursor for
                Select
                    'drop table [' + Table_Name + ']'
                From
                    INFORMATION_SCHEMA.TABLES

                open cmds
                while 1=1
                begin
                    fetch cmds into @cmd
                    if @@fetch_status != 0 break
                    print @cmd
                    exec(@cmd)
                end
                close cmds
                deallocate cmds";

        private const string LookupEdmMetaDataTable =
            @"Select COUNT(*)
              FROM INFORMATION_SCHEMA.TABLES T
              Where T.TABLE_NAME = 'EdmMetaData'";
    }
}

这篇关于实体框架CTP 4 - 代码第一个定制数据库初始化程序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-15 22:00