Which database would you recommend to use with C# (.NET) application?

Question

Hey,

I'm developing a little project plan and I came to a point when I need to decide what local databse system to use.

The input data is going to be stored on webserver (hosting - MySQL DB). The idea is to build a process to download all necessary data (for example at midnight) and process them. However, there are going to be many inputs and stages of processing, so I need to use some kind of local database to store the semi-product of the application

What local database system would you recommend to work with C# (.NET) application?

edit: The final product (information) should be easily being exported back to Hosting MySQL DB.

As Will mentioned in his answer - yes, I'm for a performance AND comfort of use.

Answer 1

MS SQL Server support comes out of the box without any other drivers or setup required. Also, MS SQL Server Express is free.

You can generate scripts that will export the data to/from MySQL.

Answer 2

I want to say Microsoft Sql 2005 Express, as it (almost) comes as the obvious choice when developing in .NET.

But it all depends on what previous db skills you have. If you already know MySql and as you already said, the data should be exported back to MySql. Why not use MySql all the way?

Answer 3

For quick and dirty I'd go with Sql Server Compact Edition. Its an in-process implementation of Sql Server, so it doesn't require you install any other applications.

Back in the day, you'd use an Access database for this kind of thing. But Access databases kinda blow.

It wouldn't take much to upload your finished data back to the production server. If you're looking for a solution that automates that process, you'll probably need to look at hosting an instance of MySql locally and use whatever replication services it provides.

Answer 4

The "obvious" choice would be MS SQL Server Express. VS and .net both support it natively and if you've experience with it already (for the main DB), I'd certainly be tempted to stick with it (or its express version).

But that's certainly not the end of your options. I use SQLite a lot for cross-platform applications and web-apps. It's eye-wateringly-fast and it does integrate pretty well through System.Data.SQLite -- albeit not as tightly as MS SQL Server.

There's also a Compact edition of SQL Server that compares quite well to SQLite.

Answer 5

For what you described, definitely MS SQL Server. Good performance, good tools. Free.

Answer 6

You may find this Q & A thread helpful.

Answer 7

How about using db4o? It's an OODB you can embed in your application. Also supports replication. Edit: As a side note - In my current pet project using db4o I have a line (C# 3.5):

IList<Users> list = Persistence.Database.Query<Users>(u => u.Name == "Admin");

Using strong typed lambda expression to get a (lazy) list of objects from the database. It also uses indexes to retrieve the list quickly.

Answer 8

I don't know of a good in-process database that is fully syntax and type compatible with MySQL. With that in mind, you have three options:

1. Choose something like SQLlite, Access, or SQL Server Compact. The problem is that you'll end up writing some complex conversion logic with any of those and have to write all your queries twice.
2. Install MySQL locally. Then you have to put up with having a full database server running on your local system. You definitely want to avoid this for anything you'll ship to a customer, but for your own use it might be ok. Fortunately, MySQL doesn't use as many resources as some other modern database servers, but this is still less than ideal.
3. Switch to SQL Server Express edition at the server and use SQL Server Compact at the client. It's just as cheap as MySQL (maybe even cheaper, since you're supposed purchase MySQL for any commercial use). Considering that you're using C# at the client end, you might want to use it with ASP.Net at the server side as well. And if you're using ASP.Net server side, then it's not difficult to find a host that offers SQL Server Express. Now your databases are type compatible and any query you write for you client is guaranteed to work for your server as well.

IMO, one of the big strengths of the MS database stack (excluding access) is that they have a compatible solution for whatever you're doing all the way from the desktop up to multi-datacenter clusters. If your app's scale changes or you need to ship data between two different classes of app, your database layer is taken care of.

Answer 9

I say go with Sql Server Compact Edition. It's real similar to the full blown version of SQL Server, and VS2008 has built in support for designing tables, querying etc. (Management Studio 2008 also has support for it). The biggest downside is that you lose out on stored procedures, but the upside is great as there's no need to install anything on the local users machine, and it works real fast for selecting data. Even cooler, is that with SQL Metal, you can create a DBML file and use LINQ just as you would with Sql Server.

Answer 10

Pick anything that's available, but code against interfaces only, that way you can easily switch between them.

For production, I'd say either MS SQL for large projects, (or express for mid level) simply because of the close integration with VS and Sqlite for smaller projects.

Given the description, I would think that Sqlite would be a good choice, since it's the simplest/lowest overhead.

Answer 11

I've been doing some testing as of late and though I'd also recommend SQL Server 2005 (Express if needed) because it works out of the box though SQL Server 2008 is new and RTM'd now.

Answer 12

SQL Server as most have mentioned... My reason would be cos you can use the source control to integrate the test cases from C# to database....

Team foundation (TFS) is one such from Microsoft with GUI...

Answer 13

Since this is already answered. I have to mention when working with CLR languages, CLR/.NET Framework Integration sets MS SQL Server 2005/2008 apart from the rest. Following excerpt from here.

By using languages such as Visual Basic .NET and C#, you can capitalize on CLR integration to write code that has more complex logic and is more suited for computation tasks. Additionally, Visual Basic .NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. You can easily organize related code into classes and namespaces, which means that you can more easily organize and maintain your code investments when you are working with large amounts of code. The ability to logically and physically organize code into assemblies and namespaces is a huge benefit that enables you to better find and relate different pieces of code in a large database implementation.