CODEWITHSUNDEEP
c#asp.netmysqlsql-server-2008entity-framework
ethem
ethem

Reputation: 2908

how to use MySQL and MSSQL both in ASp.net application with Entity framework

I've written a ASp.net C# .NET 4.0 application and I've used Ms SQL2008 DB.

It's a 2 tier application... BLL/DAL and 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)

I haven't used
- Storedprocedures
- Views
- Tsql
- no manual SQL operation
- etc.

So If you see it's very easy setup. The application uses MsSQL2008 DB

so my question is: I need to use MySQL (request of client).

What do I need to do? Since I've used Entitiy Framework I think this should be easy done right?

How can I make that the application can use MySQL / or can support both (my SQL/Mysql).

please advice?

Upvotes: 3

Views: 3630

Answers (2)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364249

EDMX file consist of three parts:

  • SSDL - description of the database. This part includes information about used database server
  • CSDL - description of your classes
  • MSL - description of mapping between SSDL and CSDL artifacts

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).

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.

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:

  • 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.
  • 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.
  • 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.

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.

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.

Upvotes: 3

ChristiaanV
ChristiaanV

Reputation: 5541

You can install the Mysql .Net connector and in your EDMX file you can select DDL Generation Template = SSDLToMySQL.tt (VS)

It will than generate the sql code based on Mysql.

Upvotes: 1

Related Questions