Things a .NET developer needs to know when using a MYSQL backend.

Question

With the economy the way it is my team and I have been looking into using MYSQL to reduce our licensing costs. I am wondering if there are any gotchas or issues that may crop up.

Is there anything we need ot do special to get .NET to talk to mysql as opposed to MsSQL?

When developing for it will LINQ to SQL have issues?

Any caveats we should be aware of?

Answer 1

Not a direct answer, but if you are familiar with SQL Server, then consider SQL Server Express (2005 or 2008). It is also free, and you'll be familiar with it, thus not requiring research into mySQL ;)

That said, check this out: http://dev.mysql.com/tech-resources/articles/dotnet/index.html

Answer 2

one big one is the support you can get when working with MYSQL if you dont pay the license.

Microsoft hasnt develop yet LINQ to MYSQL support, it maybe some projects out there but they are not guranteed

Answer 3

Here is a post which shows the matrices of the comparison. The database feature section is worth to look at.

http://swik.net/MySQL/MySQL+vs+MS+SQL+Server

Answer 4

You'll need the MySQL Connector for .NET, if you don't already have it, in order to get your .NET application talking to MySQL.

Then you'll have access to MySqlConnection, MySqlCommand, MySqlDataReader objects etc.

LINQ to SQL is for SQL Server only, but there are third-party LINQ providers for MySQL - here's one that looks promising, although I haven't used it myself.

Answer 5

Well, I say you're doing the right thing moving to MySQL. SQL Server's licensing cost is prohibitive and since MySQL does the job free and has no limitations like the Express edition I think using it as a backend is a solid idea.

I've used SQL Server express for more than a year now and what irks me most is that it under-uilizes the server's resources so you're not getting enough bang for the buck with express edition. If you have a website with plenty of data or one that gets plenty of visitors then you'll quickly outgrow SQL Server express, and then you'll find that you either have to somehow win a lottery to get the money for the full edition or re-write all your queries for a cheaper DB.

So better plan ahead, go with a cheap DB in the backend. MySQL is a very good choice.

Answer 6

You could just ignore the database being used an opt to use an ORM. That way you won't have to care what database you're using, and move between them (atleast in theory), effortlessly.

Answer 7

Try SQLite. I've used it and really enjoy it. There's even a free admin tool that has AutoComplete (wow). It's suitable for any site that has less than 100k page views per day. There are some downsides to be sure, but the light footprint and compatiblity with LINQ (via DBLinq, which is still in Alpha but I've used it just fine) make it a win in my book.

Answer 8

Be aware that the MyISAM engine doesn't support transactions, whereas InnoDB does.