I see a lot of asp.net projects coupled with mssql2005 and mssql2008. Most of them don’t use MySQL for some reason.This is even true when an ORM like nhibernate is being used.
Is this a pattern only to me or are there quite a lot of asp.net projects with MySQL as the db?
If this is true, what might be the reason for not choosing MySQL?
SQL Server is noticeably faster in a typical setup, has better graphical design tools, and is easier to administer. All Windows machines have a SQL Server driver preinstalled.
MySQL is free and also works on Unix servers. It requires a client library to be used in ASP.NET (the MySQL connector) which has to be installed on the machine running the IIS web server.
So, even though ASP.NET plays well with MySQL, it's both slower and more difficult. But if you can't afford SQL Server, or if licensing is an issue, MySQL is the way to go.
The last time I checked (and it's been a few years), the only way to access MySQL from .NET, using only out-of-the-box tools in Visual Studio, was using .NET's ODBC provider. I know there are better tools for MySQL and .NET, but the only one's I looked at, at the time, were not free. By contrast, .NET's SQL Server provider is fast, easy to set up, and very reliable.
Second, there are free versions of SQL Server (express editions), so cost isn't a factor unless the database exceeds 4GB in size. That's a limitation in the express editions.
Third, SQL server express edition comes with paid versions of Visual Studio, so it's already there for most developers. If you have an MSDN subscription, you get the developer edition of SQL server, which is the whole enterprise edition with restricted licensing. Again, there's not much incentive to go looking for another database when you've already got a good one.
Fourth, most versions of Visual Studio have excellent integration with SQL Server, so that you can edit tables as well as data, and design and test queries, in the Visual Studio environment. Good tools are always a win.
As long as you have a .NET connector for your database system, you can use . There is no special limitations that will prevent a database system from working with a specific language. The database system is a separate software system in its own. A connect creates a bridge between your software component/project/system to the database software.
This allows you to connect to (from .NET using the connectors): Oracle, PostGreSQL, MS-SQL, MySQL, SqlLite, ..... without much problem.
Database systems are designed to be implementation independent so that the data can be segregated from the users, and application and to be able to retain the main principles of database. (Atomic, Consistency, Isolation, and Durability)
SQL Server is often a default for most .Net developers as that is what they are used to and the integration is good.
This doesn't mean that MySql doesn't feature. In fact there is a version of MySql Connector for ADO.Net. As for ORMs, Nhibernate comes with a Dialect for MySql and works well, I used this recently for a small project. In fact, if you let Nhibernate generate your schema you're free to choose between; MS SQL, MySql, Oracle or even, erm, Access.
ASP.net connects to SQL Server Database with the Tabular Data Stream Protocol (TDS), which is different from the OleDB protocol it uses to connect to MySQL. This is a "closer to the metal" approach to connecting to the database, and as a result, is markedly faster.
You can see an ADO.NET diagram here which outlines the different paths.
This question is also covered here on Stack Overflow.