Today I maintain project that has really messy DB that need a lot of refactor and publish on clients machines.
今天我维护的项目非常混乱，需要大量的重构并在客户端机器上发布。
I know that I could add a SQL Server Database project that contains just scripts of the database and creates a .dacpac file that allows me to change clients databases automatically.
我知道我可以添加一个只包含数据库脚本的SQL Server数据库项目，并创建一个允许我自动更改客户端数据库的.dacpac文件。
Also I know that I could just add an .mdf file to the App_Data or even to Solution_Data folder and have my database there. I suppose that localDb that already exists allows me to startup my solution without SQL Server
另外我知道我可以将.mdf文件添加到App_Data甚至是Solution_Data文件夹并在那里添加我的数据库。我认为已经存在的localDb允许我在没有SQL Server的情况下启动我的解决方案
And atlast i know that Entity Framework exist with it's own migrations. But i don't want to use it, besouse i can't add and change indexes with it's migrations and i don't have anought flexibility when i need to describe difficult migrations scenarios.
至此我知道实体框架存在于它自己的迁移中。但我不想使用它，besouse我无法添加和更改索引的迁移，当我需要描述困难的迁移场景时，我没有应有的灵活性。
My goals:
我的目标：
- Generate migration scripts to clients DB's automaticaly.
- 自动生成迁移脚本到客户端DB。
- Make my solution self-contained, that any new Programmer that came to project don't even need to install SQL Server on his machine.
- 让我的解决方案自成一体，任何来到项目的新程序员甚至不需要在他的机器上安装SQL Server。
- Be able to update local (development) base in 1-2 clicks.
- 能够在1-2次点击中更新本地（开发）基础。
- Be able to move back in history of db changes (I have TFS server)
- 能够在db更改的历史记录中返回（我有TFS服务器）
- Be able to have clean (only with dictionaries or lookup tables) db in solution with up to date DB scheme.
- 能够在解决方案中使用最新的DB方案清理（仅限字典或查找表）db。
- Additionally i want to be able to update my DB model (EF or
.dbml) automatically or very easy way. - 此外，我希望能够自动或非常简单地更新我的数据库模型（EF或.dbml）。
So what I what to ask:
那么我要问的是：
-
What's a strengths and weaknesses of using this 2 approaches if I want to achive my goals?
如果我想实现目标，使用这两种方法有什么优点和缺点？
-
Can be that I should use sort of combination of this tools?
可以说我应该使用这种工具的组合吗？
-
Or don't I know about other existing tool from MS?
或者我不知道MS的其他现有工具？
-
Is there a way to update my DAL model from this DB?
有没有办法从这个DB更新我的DAL模型？
2 个解决方案
#1
10
What's a strengths and weaknesses of using this 2 approaches if I want to achive my goals?
如果我想实现目标，使用这两种方法有什么优点和缺点？
Using a database project allows you to version control all of the database objects. You can publish to various database instances and roll out changes incrementally, rather than having to drop and recreate the database, thus preserving data. These changes can be in the form of a dacpac, a SQL script, or done right through the VS interface. You gain a lot of control over deployments using pre- and post-deployment scripts and publishing profiles. Developers will be required to install SQL Server (the developer/express edition is usually good enough).
使用数据库项目允许您对所有数据库对象进行版本控制。您可以发布到各种数据库实例并逐步推出更改，而不必删除并重新创建数据库，从而保留数据。这些更改可以是dacpac，SQL脚本的形式，也可以通过VS接口完成。使用部署前和部署后脚本以及发布配置文件，您可以获得对部署的大量控制。开发人员将被要求安装SQL Server（开发人员/快递版通常足够好）。
LocalDB is a little easier to work with -- you can make your changes directly in the database without having to publish. LocalDB doesn't have a built-in publish process for pushing changes to other instances. No SQL Server installation required.
LocalDB更容易使用 - 您可以直接在数据库中进行更改而无需发布。 LocalDB没有用于将更改推送到其他实例的内置发布过程。无需安装SQL Server。
Use a database project if you need version control for your database objects, if you have multiple users concurrently making changes, or if you have multiple applications that use the same database. Use LocalDB if none of those conditions apply or for small apps that require their own standalone database.
如果需要对数据库对象进行版本控制，如果有多个用户同时进行更改，或者有多个应用程序使用同一数据库，请使用数据库项目。如果这些条件都不适用，则使用LocalDB，或者对需要自己的独立数据库的小型应用程序使用LocalDB。
Can be that I should use sort of combination of this tools?
可以说我应该使用这种工具的组合吗？
Yes. According to Kevin's comment below, "If the Database Project is set as your startup project, hitting F5 will automatically deploy it to LocalDB. You don't even need a publish profile in this case."
是。根据Kevin在下面的评论，“如果将数据库项目设置为您的启动项目，点击F5将自动将其部署到LocalDB。在这种情况下，您甚至不需要发布配置文件。”
Or don't I know about other existing tool from MS?
或者我不知道MS的其他现有工具？
Entity Framework's Code First approach comes close.
实体框架的Code First方法非常接近。
Is there a way to update my DAL model from this DB?
有没有办法从这个DB更新我的DAL模型？
Entity Framework's POCO generator works well unless you make changes to your DAL classes, then those changes get lost the next time you run the generator.
实体框架的POCO生成器运行良好，除非您对DAL类进行更改，然后在下次运行生成器时这些更改会丢失。
There is a new tool called SqlSharpener which can generate classes from the SQL files in a database project. I have not used it so I cannot vouch for it but it looks promising.
有一个名为SqlSharpener的新工具，它可以从数据库项目中的SQL文件生成类。我没有使用它所以我不能保证它，但它看起来很有希望。
#2
1
One way for generating client script for DB changes is to use database modeling tool like ERWin Which have a free community edition. The best way to meet your database version control requirement and easy script generation is Redgate SQL Source Control. Using Redgate tool you will meet the first five goals mentioned. Moreover, you can now update EF Model by single click after changing DB schema (i.e. Database first approach) as required in goal 6.
为数据库更改生成客户端脚本的一种方法是使用像ERWin这样的数据库建模工具，它具有免费的社区版本。满足数据库版本控制要求和轻松生成脚本的最佳方法是Redgate SQL Source Control。使用Redgate工具，您将满足前面提到的五个目标。此外，您现在可以在更改目标6中所需的数据库架构（即数据库第一种方法）后单击更新EF模型。
I do not recommend using LocalDB at all. It always make issues with source control like "DB File is in use and can't commit...” In addition, the developer in the project will not have common set of updated data to work on unless a developer add test data to the database and ask others to get latest version and overwrite their own database Or generate update script by the previous mentioned tool and ask every developer to run it on his localDB. The best way in your situation is to use SQL Server on network. A master version that all the developers use. Since you have version control on the database using previously mentioned tool, you can rollback any buggy change in the database server.
我建议根本不建议使用LocalDB。它始终存在源代码控制问题，例如“数据库文件正在使用且无法提交......”此外，除非开发人员将测试数据添加到项目中，否则项目中的开发人员将无法使用常用的更新数据集。数据库并要求其他人获取最新版本并覆盖他们自己的数据库或者通过前面提到的工具生成更新脚本，并要求每个开发人员在他的localDB上运行它。最好的方法是在网络上使用SQL Server。主版本所有开发人员都使用。由于您使用前面提到的工具对数据库进行版本控制，因此您可以回滚数据库服务器中的任何错误更改。
If you think that RedGate tool is expensive for the budget of your project. A second approach is to generate single SQL file from your database that has all database object and the other developers update the SQL file in source control per their changes. This can be done easily by using schema compare tool in visual studio and appending the generated script to SQL file in the source control. With EF DB First approach, you will not have to add many migration classes as in EF Code first.
如果您认为RedGate工具对于项目的预算来说是昂贵的。第二种方法是从数据库生成具有所有数据库对象的单个SQL文件，其他开发人员根据其更改在源代码管理中更新SQL文件。这可以通过在visual studio中使用模式比较工具并将生成的脚本附加到源代码管理中的SQL文件来轻松完成。使用EF DB First方法，您不必像EF代码中那样添加许多迁移类。
#1
10
What's a strengths and weaknesses of using this 2 approaches if I want to achive my goals?
如果我想实现目标，使用这两种方法有什么优点和缺点？
Using a database project allows you to version control all of the database objects. You can publish to various database instances and roll out changes incrementally, rather than having to drop and recreate the database, thus preserving data. These changes can be in the form of a dacpac, a SQL script, or done right through the VS interface. You gain a lot of control over deployments using pre- and post-deployment scripts and publishing profiles. Developers will be required to install SQL Server (the developer/express edition is usually good enough).
使用数据库项目允许您对所有数据库对象进行版本控制。您可以发布到各种数据库实例并逐步推出更改，而不必删除并重新创建数据库，从而保留数据。这些更改可以是dacpac，SQL脚本的形式，也可以通过VS接口完成。使用部署前和部署后脚本以及发布配置文件，您可以获得对部署的大量控制。开发人员将被要求安装SQL Server（开发人员/快递版通常足够好）。
LocalDB is a little easier to work with -- you can make your changes directly in the database without having to publish. LocalDB doesn't have a built-in publish process for pushing changes to other instances. No SQL Server installation required.
LocalDB更容易使用 - 您可以直接在数据库中进行更改而无需发布。 LocalDB没有用于将更改推送到其他实例的内置发布过程。无需安装SQL Server。
Use a database project if you need version control for your database objects, if you have multiple users concurrently making changes, or if you have multiple applications that use the same database. Use LocalDB if none of those conditions apply or for small apps that require their own standalone database.
如果需要对数据库对象进行版本控制，如果有多个用户同时进行更改，或者有多个应用程序使用同一数据库，请使用数据库项目。如果这些条件都不适用，则使用LocalDB，或者对需要自己的独立数据库的小型应用程序使用LocalDB。
Can be that I should use sort of combination of this tools?
可以说我应该使用这种工具的组合吗？
Yes. According to Kevin's comment below, "If the Database Project is set as your startup project, hitting F5 will automatically deploy it to LocalDB. You don't even need a publish profile in this case."
是。根据Kevin在下面的评论，“如果将数据库项目设置为您的启动项目，点击F5将自动将其部署到LocalDB。在这种情况下，您甚至不需要发布配置文件。”
Or don't I know about other existing tool from MS?
或者我不知道MS的其他现有工具？
Entity Framework's Code First approach comes close.
实体框架的Code First方法非常接近。
Is there a way to update my DAL model from this DB?
有没有办法从这个DB更新我的DAL模型？
Entity Framework's POCO generator works well unless you make changes to your DAL classes, then those changes get lost the next time you run the generator.
实体框架的POCO生成器运行良好，除非您对DAL类进行更改，然后在下次运行生成器时这些更改会丢失。
There is a new tool called SqlSharpener which can generate classes from the SQL files in a database project. I have not used it so I cannot vouch for it but it looks promising.
有一个名为SqlSharpener的新工具，它可以从数据库项目中的SQL文件生成类。我没有使用它所以我不能保证它，但它看起来很有希望。
#2
1
One way for generating client script for DB changes is to use database modeling tool like ERWin Which have a free community edition. The best way to meet your database version control requirement and easy script generation is Redgate SQL Source Control. Using Redgate tool you will meet the first five goals mentioned. Moreover, you can now update EF Model by single click after changing DB schema (i.e. Database first approach) as required in goal 6.
为数据库更改生成客户端脚本的一种方法是使用像ERWin这样的数据库建模工具，它具有免费的社区版本。满足数据库版本控制要求和轻松生成脚本的最佳方法是Redgate SQL Source Control。使用Redgate工具，您将满足前面提到的五个目标。此外，您现在可以在更改目标6中所需的数据库架构（即数据库第一种方法）后单击更新EF模型。
I do not recommend using LocalDB at all. It always make issues with source control like "DB File is in use and can't commit...” In addition, the developer in the project will not have common set of updated data to work on unless a developer add test data to the database and ask others to get latest version and overwrite their own database Or generate update script by the previous mentioned tool and ask every developer to run it on his localDB. The best way in your situation is to use SQL Server on network. A master version that all the developers use. Since you have version control on the database using previously mentioned tool, you can rollback any buggy change in the database server.
我建议根本不建议使用LocalDB。它始终存在源代码控制问题，例如“数据库文件正在使用且无法提交......”此外，除非开发人员将测试数据添加到项目中，否则项目中的开发人员将无法使用常用的更新数据集。数据库并要求其他人获取最新版本并覆盖他们自己的数据库或者通过前面提到的工具生成更新脚本，并要求每个开发人员在他的localDB上运行它。最好的方法是在网络上使用SQL Server。主版本所有开发人员都使用。由于您使用前面提到的工具对数据库进行版本控制，因此您可以回滚数据库服务器中的任何错误更改。
If you think that RedGate tool is expensive for the budget of your project. A second approach is to generate single SQL file from your database that has all database object and the other developers update the SQL file in source control per their changes. This can be done easily by using schema compare tool in visual studio and appending the generated script to SQL file in the source control. With EF DB First approach, you will not have to add many migration classes as in EF Code first.
如果您认为RedGate工具对于项目的预算来说是昂贵的。第二种方法是从数据库生成具有所有数据库对象的单个SQL文件，其他开发人员根据其更改在源代码管理中更新SQL文件。这可以通过在visual studio中使用模式比较工具并将生成的脚本附加到源代码管理中的SQL文件来轻松完成。使用EF DB First方法，您不必像EF代码中那样添加许多迁移类。