C# - XML vs MySQL

Question

In this program I'm writing, it would need frequent database communication, and at the moment I'm using just XML files. Is there really a benefit from using MySQL or SQL in general over XML. Just note that I'm using C# so MySQL is not very fun to deal with in it (from what little experience I have).

Answer 1

If there's truly relational data...you'll almost always benefit from using a RDBMS. Retrieving data will be faster with the backing of a query engine rather than tying together XML nodes. You'll also get referential integrity when inserting data into the structure.

There is an ADO.NET provider for MySQL, so you shouldn't have any more difficulty dealing with a MySQL database than MS SQL Server.

You could even download DbLinq and give their LINQ to MySQL functionality a shot. Could make things even easier (or you could use Entity Framework with the MySQL ADO.NET provider).

Answer 2

The size of XML documents can be a large factor. In XML you either produce large and complicated text files with a huge amount of additional data or your data is split up accross several files. Managing these files can be a headache. Using a SQL database will allow you waste less disk space.

SQL is faster than using XML.

Any SQL database will give you access to a whole set of permissions and role capabilities that may be difficult to enforce using XML.

Answer 3

If you have relational data, a database would work. As an alternative to MySQL, if you aren't looking for a centralized solution, you can use SQLite. SQLite runs in-process (meaning the program running it is it's own "database server") and requires no installation other than distributing the DLL file containing it.

Robert Simpson has written System.Data.SQLite, a SQLite Data Provider for the .Net framework. It's free and open source (like SQLite) and works and feels as native as System.Data.SqlClient does. It supports standard ADO.Net conventions, Linq, and the Entity Framework.

I've used System.Data.SQLite for projects at work for applications that need to run fast and cache data locally for comparison between multiple runs (data processing and job scheduling). Firefox is a good example of an application using SQLite, Firefox 3 uses SQLite for it's Cookies, the Downloads history, Form autocomplete, and most importantly your web browsing history.

Again SQLite is meant for direct application use and lacks features like user authentication and schema permissions. It has issues if multiple programs try to write to the same database (those can be worked around but nothing like what a real RDBMS can do). It's biggest advantage is it doesn't need to be installed and set up to work like MySQL does. In the C# case all you have to do is reference System.Data.SQLite and copy the .dll file along with your program and it'll work.

Answer 4

In terms of maintaining data stored in XML files vs. a relational database (Mysql, in your case), the database is far more robust than simple XML files. But this is simply an exercise in determining the needs of your application.

MySql, like many other RDBMSs, will provide much more than just a place to park your data. The biggest advantage to using a modern db such as MySql is ACID support. This means you get all-or-nothing transactions, ensuring consistency through your data.

You also get referential integrity to ensure that related records stay intact and don't leave you with abandoned references to other data records. We could go on and on to discuss the value of locking or the power of stored procedures.

But really, you should consider the needs of your application. If you do significant gymnastics to keep your data in order or you care about shared access and file locks while trying to read and write data, you need to punt on your XML file basis. No need trying to find ways around these issues when a basic mysql database will solve those issues.