Database for an Inventory System in C#

Question

I'm upgrading an inventory system for a jewllery store that was originally built with MS Access. The application stores items, customers, vendors, and displays data about items stored. Reporting tool should be considered to generate reports for items in a printable format.

The system should be built in C#, but I've not made the decision yet for the database engine I should use for this application. What do you suggest?. I thought of SQLite, but I need to keep in mind that migration items from the old database to the new updated one is a must. And since it was originally built with MS Access. Should I just stick with MS Access and build my application with it?.

Edit: This is a single user system.

Answer 1

since you're using C#, Sql Server would seem to be a logical candidate; MySql is also a good choice.

I would not consider using MS-Access or SqlLite in a production application; too many drawbacks/limitations - see all this stuff for examples.

Answer 2

Sql Server, then you can add reporting services, and the migration path from access should be easy. You could even start with the Express edition.

Answer 3

This decision depends on many factors. Like how many concurrent users will be accessing this application (Access does not allow more than 5).

• How big is the database?
• How much growth do you expect?
• What features do you need besides basic data storage?
• What is the budget?

The answers to these questions will help you to select the right product. There are many different ones out there. So far i've been very pleased with SQL server and MySQL (which is free). I'd steer clear of Oracle though I found it it be a hassle to work with and generally a heap of junk.

[Edit]

Based on your last comment I would go with SQL Server Express with advanced services http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=b448b0d0-ee79-48f6-b50a-7c4f028c2e3d. This product is still free. The only limitation is the database can't be larger than 4 gigabytes or something like that. I hope this helps.

Answer 4

SQL Server Express is a good choice if you want to stick with MS Technologies. Since your conversion is going to be one off, you can make it in various ways - either looking up for a DB Conversion tool (seems like there are some of them around), or even write your own, using either DTS, SSIS or handcrafted code, if you need to.

Answer 5

Take a look at PostgreSQL, http://www.postgresql.org/

It's free, fast and has providers for .NET

Answer 6

For single-user desktop apps in a Microsoft ecosystem, consider SQL Server Compact Edition rather than the full-fledged server product. Here's the relevant part of Microsoft's overview:

Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) is designed for developers who need light weight, in process relational database solution for their applications that can be developed and deployed on desktop as well as on mobile devices. SQL Server Compact Edition Runtime can be used to develop and deploy applications on desktop.

SQL Server Compact Edition is a powerful, yet lightweight relational database engine that makes it easy to develop desktop applications by supporting familiar Structured Query Language (SQL) syntax and providing a development model and API consistent with SQL Server.

Some of its limitations (no nested transactions, for example) are described in its Wikipedia article and comparisons to other embedded databases abound (as far as I can tell, there isn't a consensus about a best option).

There's at least one SO question addressing migration from Access and a poster here links to a list of SQL Server CE tools, including various kinds of migration software.