asp.net with mysql

Question

I know asp.net with mySQL is possible, but does it work well (fast and stable)? I am looking at a project for a client, they want to stay on mySQL, but they like they idea of going to asp.net from PHP. I can give them a better price by far in asp.net (more productive for me) and keep the project within their budget.

BUT, am I going to run into a whole lot of little 'gotchas' dealing with a mySQL DB instead of the SQl Server DB that I am used to?

Looking for advice from people who have actual done projects using these two together...either successfully, or unsuccessfully.

Answer 1

There is a third option, MSSQL <-> SSIS/DTS <-> MySQL.

Both parties get to stay in their respective comfort zones, and you'll be more productive instead of tearing hair out working around gotchas.

Answer 2

I would suggest using a tool like iBATIS.NET. It's a data mapping tool that works very well with .NET, and it's very easy to learn and is highly configurable.

You can configure multiple database providers (MySQL, SQL Server, Oracle, Sybase, etc.); almost everything is XML configurable, so SQL can be edited while your app is running, and if at some point they want to switch backend DBs, it's (sometimes) as easy as changing a couple of settings in an XML file.

Check it out: http://ibatis.apache.org/overview.html

Answer 3

Seriously, man, I wouldnt try to over complicate it. Write the site just like you normally would, but using the MySQL data provider instead of the mssql provider. Keep it simple. Now there are some differences in how the two DBMSs handle their SQL.

Here are items that tripped me up originally.

MSSQL: SELECT TOP 5 * FROM Table MySQL: SELECT * FROM TABLE LIMIT 0,5

MSSQL: SELECT IsNull(NumberField,0) FROM Table MySQL: SELECT IfNull(NumberField,0) FROM TABLE

MySQL: Everything is CaSe-SenSiTivE MySQL: Has stored procedures, but they are not as user friendly as MSSQL, so stick with inline sql.

MSSQL: select * from table where column1 = @col1 and column2 = @col2 MySQL: select * from table where column1 = ? and column2 = ? (remember to specify your command parameters in order)

There are a bunch of other little things that may complicate or confuse, but thats what this site is for, so you can ask