ASP.NET MVC open source application. MySQL or SQL Server?

Question

Hi, I have around 4 years experience of ASP.NET & SQL Server (only web forms). I have a little knowledge of Ruby on Rails and have developed one very small 2-3 pages application in ASP.NET MVC in my job. Now I and one of my friend ( same experience like me) are willing to gain more experience of ASP.NET MVC. For that purpose we have thought of developing an open source application in ASP.NET MVC. It can be a a blog system or forum or anything else so that community can also take benefit of it. But we are not able to decide the back-end database for the application between MySQL and SQL Server. What will you suggest in terms of following -

1. Which will be more supported or required by community?
2. If we use MySQL, will it be a tough road ahead?
3. Is there any chance of supporting both by using ORM (I have no experience of working with any ORM)
4. Any other suggestion ?

Thanks in advance.

Answer 1

If you can't decide which to use as a backend, then one suggestion I have it define an interface for the data layer, use the IRepository pattern against this interface, and simply pick one and write an implementation of the data layer for that choice. In a (successful) open source project, it's unlikely that everyone who wants to use it will have the same ideas about what database will work for them. In that case, designing it so that it can use any database, given a suitable implementation of the interface is a wise move. Using something like nHibernate, which already supports many different databases may be the route you want to go if you don't mind having dependencies on other libraries.

Answer 2

• MS SQL is easier to use with .NET. .NET includes LINQ to SQL ORM out-of-box, so nothing has to be installed.

• MS SQL has a free Express edition but it's up to you to decide whether its capabilities (4Gb for DB, 1 CPU only and so on) will be enough for your application (IMHO they will be =))

• There'are ORMs that allows to switch between DBMS's: NHibernate, SubSonic, DbLinq. NHibernate is the strongest beast out there, and it has an active record implementation built if you are looking for familiar concepts from RoR.

Answer 3

tvanfosson and elder_george had given good comments.

ASP.NET MVC aside, it's more of MySQL vs MSSQL. I've developed web projects using both and find them relatively on-par from a development point of view. ORM I've used is NHibernate.

From deployment/production perspective, you will be bounded by MSSQL Express edition's limitation, assuming you are not going to pay for it. You might want to spend some thoughts in this aspect.

Answer 4

Use ORM -> NHibernate

https://www.hibernate.org/361.html

Answer 5

Which will be more supported or required by community?

For most business users, and most dedicated ASP hosting providers, it will be Microsoft SQL Server IMHO. With a surprising spread across version numbers. I see quite many SQL Server 2008, a lot of 2005, and quite surprisingly many 2000's too.

For small "we're cheap, is it gratis?" businesses and individuals, I do see some MySQL on Windows too. I guess in my little circle of companies that I've worked with, it is 2/3 MSSQL and 1/3 MySQL of the two databases you mention.

If we use MySQL, will it be a tough road ahead?

In terms of more difficult development? Yes and no; LINQ to SQL is more or less only available for MSSQL, and many find it to be a simple and natural technology to work with. So "yes" if your plan was to use LINQ to SQL as your ORM of choice. Other good ORM's like nHibernate work equally well with both databases, as elder_george says.

Is there any chance of supporting both by using ORM (I have no experience of working with any ORM)

Sure, f.x. with nHibernate, and a bit of extra XML for nHibernate configuration, and some more extensive install docs, SQL scripts et cetera for the users.

Any other suggestion ?

Since this is your first application, play around with the design first using mocksups & prototyping, UML or something else of your choice. See if you can get some experienced MVC developers to critique your design, but don't take their critique as gospel, there is more than one way of doing things right.

Definitely consider tvanfosson's suggestion to use an IRepository to provide flexibility on the storage layer.

Consider using unit testing and dependency injection from the start too; both are IMHO very important and helpful in the longer run. On the downside, that would be a lot of new technology if you don't have prior experience with these.

Answer 6

Hello,

I was in a similar situation a couple of months ago. Now my newest project on ASP.Net MVC is almost complete. Here's the set I chose

--ASP.Net MVC + MySQL. No ORM.--

I decided to go with MySQL instead of MS SQL because MySQL is free, and MS SQL is very expensive. If you're making an open source application, you gotta think about that seriously. This is going to be a major problem if you are targeting widespread adoption of your application.

At first I tried to use MySQL through an ORM -- Enterprise Framework (EF). It was the worst decision ever. Enterprise Framework totally killed the performance. It was the pits of the pits. I believe nHibernate is better, but I don't know how much better.

With my own SQL code things are fast enough.

The first iteration of my site will be in public beta this week and then there will be several iterations.

It went rather well so I car recommend the set I chose with ease.