I've written a ASp.net C# .NET 4.0 application and I've used Ms SQL2008 DB.
我已经编写了一个ASp.net c# .net 4.0应用程序，并使用了Ms SQL2008 DB。
It's a 2 tier application... BLL/DAL and UI
这是一个2层的应用程序……BLL /木豆和UI
I have used
- tables (with multiple index to make the record unique)
- relationals between tables (1 to m relation)
- Entity Framework for Datamodel
- LINQ (update/insert/select/delete)
我使用了-表(有多个索引使记录唯一)-表之间的关系(1到m关系)-数据模型的实体框架- LINQ(更新/插入/选择/删除)
I haven't used
- Storedprocedures
- Views
- Tsql
- no manual SQL operation
- etc.
我没有使用-存储过程-视图- Tsql -没有手动SQL操作-等等。
So If you see it's very easy setup. The application uses MsSQL2008 DB
如果你看到这是很简单的设置。该应用程序使用MsSQL2008 DB。
so my question is: I need to use MySQL (request of client).
我的问题是:我需要使用MySQL(请求客户端)。
What do I need to do? Since I've used Entitiy Framework I think this should be easy done right?
我需要做什么?既然我已经使用了Entitiy框架，我认为这应该很容易做对吧?
How can I make that the application can use MySQL / or can support both (my SQL/Mysql).
如何使应用程序能够使用MySQL /或同时支持这两种(我的SQL/ MySQL) ?
please advice?
请建议吗?
2 个解决方案
#1
3
EDMX file consist of three parts:
EDMX文件由三部分组成:
- SSDL - description of the database. This part includes information about used database server
- 数据库的描述。本部分包含有关已使用数据库服务器的信息
- CSDL - description of your classes
- CSDL -类的描述
- MSL - description of mapping between SSDL and CSDL artifacts
- 描述SSDL和CSDL构件之间的映射
If you want to use multiple database servers you must have at least separate SSDL for each of them (if you are going to use different table or column names per database you must also have separate MSL).
如果您希望使用多个数据库服务器，那么您必须为每个服务器至少拥有单独的SSDL(如果您要对每个数据库使用不同的表名或列名，那么您还必须拥有单独的MSL)。
When your library is compiled those three parts are extracted into separate files and included as resources to your compiled dll. That are those strange things referenced from EF connection string. You can switch the generation in EF designer and those files will be deployed to your bin directory instead.
当您的库被编译时，这三个部分被提取到单独的文件中并作为资源包含到您编译的dll中。这些奇怪的东西是从EF连接字符串中引用的。您可以在EF designer中切换生成，这些文件将被部署到bin目录中。
The problem with supporting multiple database server is that you need multiple SSDL files. VS and EF designer allows you working with SSDL only within EDMX and each EDMX can have only single SSDL. So your options for supporting multiple DBs are:
支持多个数据库服务器的问题是需要多个SSDL文件。VS和EF设计器只允许在EDMX中使用SSDL，而每个EDMX只能有一个SSDL。因此，您支持多个DBs的选项是:
- Separate EDMX for each server. That makes really big problems because you must synchronize them and you must ensure that CSDL part will be exactly the same.
- 为每个服务器分离EDMX。这会产生很大的问题，因为您必须同步它们，并且您必须确保CSDL部分将完全相同。
- One CSDL, one MSL, two SSDLs in your project. Again this is really problematic because it means having single main EDMX and separate SSDL (it is XML file) where you will maintain all DB description again.
- 一个CSDL，一个MSL，两个SSDLs。同样，这也有问题，因为这意味着只有一个主EDMX和单独的SSDL(它是XML文件)，您将再次维护所有DB描述。
- Only one EDMX for MSSQL server with artifacts deployed to bin directory + separate postprocessing which will take SSDL and creates second one for MySQL automatically. This is imho best option. You need to detect differences in used types between MySQL and MSSQL (you can use trick described by @ChristiaanV to find those differences but make sure you backup your EDMX because you will have to revert it) and write either custom console application, script or XSLT transformation which will convert SSDL for MsSQL to SSDL for MySQL. You will end with one CSDL, one MSL and two SSDLs where the second is autogenerated from the first one.
- 只有一个MSSQL服务器的EDMX被部署到bin目录+单独的postprocessing，它将使用SSDL并自动为MySQL创建第二个。这是我的最佳选择。你需要检测使用MySQL类型之间的差异和该软件(你可以使用技巧被@ChristiaanV找到这些差异,但确保你备份EDMX因为你要恢复)和编写自定义控制台应用程序,脚本或XSLT转换,将为该转换SSDL SSDL MySQL。您将以一个CSDL、一个MSL和两个SSDLs结尾，第二个SSDLs将从第一个CSDL自动生成。
To make it work at runtime you need MySQL provider for EF - for example the connector mentioned by @ChristaanV and you must reference correct SSDL in connection string for MSSQL and MySQL.
要使其在运行时工作，需要为EF提供MySQL提供程序——例如@ChristaanV提到的连接器，并且必须在MSSQL和MySQL的连接字符串中引用正确的SSDL。
Btw. next time learn which DB customer requires before you develop the application. It is real analysis / requirement failure to develop application for platform customer don't support.
顺便说一句。下次在开发应用程序之前，要了解哪个DB客户需要。开发平台客户不支持的应用是真正的分析/需求失败。
#2
1
You can install the Mysql .Net connector and in your EDMX file you can select DDL Generation Template = SSDLToMySQL.tt (VS)
可以安装Mysql . net连接器，在EDMX文件中可以选择DDL生成模板= SSDLToMySQL。tt(VS)
It will than generate the sql code based on Mysql.
它将生成基于Mysql的sql代码。
#1
3
EDMX file consist of three parts:
EDMX文件由三部分组成:
- SSDL - description of the database. This part includes information about used database server
- 数据库的描述。本部分包含有关已使用数据库服务器的信息
- CSDL - description of your classes
- CSDL -类的描述
- MSL - description of mapping between SSDL and CSDL artifacts
- 描述SSDL和CSDL构件之间的映射
If you want to use multiple database servers you must have at least separate SSDL for each of them (if you are going to use different table or column names per database you must also have separate MSL).
如果您希望使用多个数据库服务器，那么您必须为每个服务器至少拥有单独的SSDL(如果您要对每个数据库使用不同的表名或列名，那么您还必须拥有单独的MSL)。
When your library is compiled those three parts are extracted into separate files and included as resources to your compiled dll. That are those strange things referenced from EF connection string. You can switch the generation in EF designer and those files will be deployed to your bin directory instead.
当您的库被编译时，这三个部分被提取到单独的文件中并作为资源包含到您编译的dll中。这些奇怪的东西是从EF连接字符串中引用的。您可以在EF designer中切换生成，这些文件将被部署到bin目录中。
The problem with supporting multiple database server is that you need multiple SSDL files. VS and EF designer allows you working with SSDL only within EDMX and each EDMX can have only single SSDL. So your options for supporting multiple DBs are:
支持多个数据库服务器的问题是需要多个SSDL文件。VS和EF设计器只允许在EDMX中使用SSDL，而每个EDMX只能有一个SSDL。因此，您支持多个DBs的选项是:
- Separate EDMX for each server. That makes really big problems because you must synchronize them and you must ensure that CSDL part will be exactly the same.
- 为每个服务器分离EDMX。这会产生很大的问题，因为您必须同步它们，并且您必须确保CSDL部分将完全相同。
- One CSDL, one MSL, two SSDLs in your project. Again this is really problematic because it means having single main EDMX and separate SSDL (it is XML file) where you will maintain all DB description again.
- 一个CSDL，一个MSL，两个SSDLs。同样，这也有问题，因为这意味着只有一个主EDMX和单独的SSDL(它是XML文件)，您将再次维护所有DB描述。
- Only one EDMX for MSSQL server with artifacts deployed to bin directory + separate postprocessing which will take SSDL and creates second one for MySQL automatically. This is imho best option. You need to detect differences in used types between MySQL and MSSQL (you can use trick described by @ChristiaanV to find those differences but make sure you backup your EDMX because you will have to revert it) and write either custom console application, script or XSLT transformation which will convert SSDL for MsSQL to SSDL for MySQL. You will end with one CSDL, one MSL and two SSDLs where the second is autogenerated from the first one.
- 只有一个MSSQL服务器的EDMX被部署到bin目录+单独的postprocessing，它将使用SSDL并自动为MySQL创建第二个。这是我的最佳选择。你需要检测使用MySQL类型之间的差异和该软件(你可以使用技巧被@ChristiaanV找到这些差异,但确保你备份EDMX因为你要恢复)和编写自定义控制台应用程序,脚本或XSLT转换,将为该转换SSDL SSDL MySQL。您将以一个CSDL、一个MSL和两个SSDLs结尾，第二个SSDLs将从第一个CSDL自动生成。
To make it work at runtime you need MySQL provider for EF - for example the connector mentioned by @ChristaanV and you must reference correct SSDL in connection string for MSSQL and MySQL.
要使其在运行时工作，需要为EF提供MySQL提供程序——例如@ChristaanV提到的连接器，并且必须在MSSQL和MySQL的连接字符串中引用正确的SSDL。
Btw. next time learn which DB customer requires before you develop the application. It is real analysis / requirement failure to develop application for platform customer don't support.
顺便说一句。下次在开发应用程序之前，要了解哪个DB客户需要。开发平台客户不支持的应用是真正的分析/需求失败。
#2
1
You can install the Mysql .Net connector and in your EDMX file you can select DDL Generation Template = SSDLToMySQL.tt (VS)
可以安装Mysql . net连接器，在EDMX文件中可以选择DDL生成模板= SSDLToMySQL。tt(VS)
It will than generate the sql code based on Mysql.
它将生成基于Mysql的sql代码。