views:

1654

answers:

13

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.

A: 

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.

Joseph Daigle
+11  A: 

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?

ullmark
You never use the full version of Sql Server for a quick and dirty app unless you've got tons of cash or extra licenses laying around. You probably mean sql server express.
Will
as much as love SQLServer, its going to be easier to handle the data transfers and schema if you keep everything in a MySql instance.
StingyJack
Will, You're Correct. But I didn't specify any version of Microsoft Sql 2005. The express version is called "SQL Server 2005 Express Edition" if you want to be picky
ullmark
I'm not being picky, I'm just pointing out that there is a difference between Sql Server with and without express. You shouldn't be expecting people to understand you mean the free version, you should make that clearer. Oh, wait, now it is.
Will
+8  A: 

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.

Will
Access does indeed blow, but ironically this sort of task (standalone mini-database) is what Access blows at the least.
MusiGenesis
Never mind Access blowing or not; there are no 64-bit drivers for accessing Jet databases from code ... Access is an inherently limited model.
John Rudy
A: 

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.

Oli
The main db he's using is MySql. Edit your entry and delete this comment.
Will
A: 

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

Corey Trager
He's looking to make a quick and dirty app. A full instance of sql server is a bit of overkill. Not even considering the licensing costs.
Will
Sorry, I meant MS SQL Server Express. Free. Easy.
Corey Trager
+3  A: 

You may find this Q & A thread helpful.

Onorio Catenacci
+1  A: 

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.

Goran
Could you maybe comment on http://stackoverflow.com/questions/226681/db4o-clientserver-appears-to-only-be-able-to-process-one-query-at-a-time please, if you have pertinent experience with db4o?
Peter Mounce
A: 

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.

Joel Coehoorn
+2  A: 

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.

BFree
A: 

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.

chills42
A: 

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.

Chad Moran
A: 

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...

A: 

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.

Vivek