CODEWITHSUNDEEP
c#.netvisual-studio
tcarter2005
tcarter2005

Reputation: 617

How to store data in a .net windows application

What would be the best way to store data in a windows forms application?

I started a project using SQL with a local .mdf file, but then soon realized that when I create an installer, and install on another system. I get an error saying SQL Server not found, because that server doesn't have SQL installed.

The target system that this app will be installed on will not have SQL installed.

So my question is: What is the norm? How is data usually stored and accessed in windows forms applications? Is it really a .mdf SQL database, or some type of sqlite DB?

If I can use a SQL database (mdf file) of some sort, without having to install SQL server on each client machine as well. How would I go about doing that?

Thanks!

Upvotes: 6

Views: 9905

Answers (9)

mythz
mythz

Reputation: 143399

For an embedded RDBMS you can't go wrong with SQLite as its the Most Widely Deployed SQL Database on the planet. It's a file-based RDBMS that just works, the database is self-contained and doesn't even need to exist as you can transparently create it on the fly.

My ServiceStack's OrmLite maintains 32bit and 64bit providers of SQLite that makes it trivial to work with, with its first-class support for POCOs: Auto-table creation and transparent support of schema-free text blobs for complex property types.

It's downloadable from NuGet in 2 flavors:

Here's is a simple web service example:

using (IDbConnection db = "~/App_Data/db.sqlite".OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand()) {

     dbCmd.DropTable<Author>();
     dbCmd.CreateTable<Author>();

     var authors = new List<Author> {
          new Author { Name = "Demis Bellot" ... },
          new Author { Name = "Angel Colmenares" ... },
     };
     dbCmd.InsertAll(authors); 

     dbCmd.Select<Author>(q => q.Birthday >= new DateTime(agesAgo, 1, 1) 
          && q.Birthday <= new DateTime(agesAgo, 12, 31));
     dbCmd.Select<Author>(q => Sql.In(q.City, "London", "Madrid", "Berlin"));
     dbCmd.Select<Author>(q => q.Name.StartsWith("A"));
     dbCmd.Select<Author>(q => q.Name.EndsWith("garzon"));
     dbCmd.Select<Author>(q => q.Name.ToUpper().EndsWith("GARZON"));
     dbCmd.Select<Author>(q => q.Name.Contains("Benedict"));
     dbCmd.Select<Author>(q => q.Eaqings <= 50);
     dbCmd.Select<Author>(q => q.Rate == 10 && q.City == "Mexico");

}

And a screenshot of the results.

Upvotes: 3

Moha Dehghan
Moha Dehghan

Reputation: 18472

This mostly depends on what kind of data you want to store.

Application settings and user configurations are a kind of data that programs store. This kind of data usually is stored in xml configuration files or in the windows registry.

Real business data is much more complex and needs a more advanced data store, like databases. For this, you have a few options:

  • A full featured database engine, like MS SQL Server (Express), MySql or PostgreSQL. These DBMS's all need a server application that should be installed on the computer that the data should be stored on.

  • An embedded database engine, like SQL Server Compact Edition, or SQLite. I personally prefer SQLite. It has a .NET implementation (System.Data.SQLite). These DBMS's don't need any installation, and they can be easily deployed with any application. Though they have some limitation, but they are enough for simple applications with no or little concurrent data access.
    The most important limitations of SQLite (that I am aware of) are that it has no row level locking (only table locking), and it has no right outer join (only left outer join). Also foreign key constraints are by default turned off. There first limitation somehow decreases the responsiveness of the application to concurrent data access (insert and updates). The other ones can be easily overcome.

Upvotes: 1

Adam Mihalcin
Adam Mihalcin

Reputation: 14478

You can also use SQLite, a free database library that stores the data in flat text files. There is a .NET library to interact with SQLite data files.

Another nice bonus is that SQLite is supported from within a bunch of different languages. SQLite was originally written for C/C++, but now Python has a built-in SQLite module. SQLite is also commonly used in Java, Ruby, and many more.

Upvotes: 1

Arek
Arek

Reputation: 1336

Besides other options already mentioned, you can consider using MS Access database (JET engine). Available out of the box and you can manage data easily with Access.

Upvotes: 0

Brannon
Brannon

Reputation: 5424

The answer to this question is nontrivial. I'll assume that you're storing actual content and not just settings or window positions. The first option is to use one of the built-in serializers to read/write XML or JSON. Or maybe use one from ServiceStack.

If you can't store all the content easily in memory, then we can consider using a database. The only time I would ever use a heavyweight database like MS SQL Server or Oracle would be where you have an enterprise-level application with full-time staffers that manage databases.

For a simple per-app file storage, you could use MS SQL Embedded or Express editions, but don't. It's slow. It has stupid limitations. You can look around online for connection string parameters to point to a local mdf file if you really want to go down that road.

Rather, you should probably use the Sqlite .NET wrapper (now managed and provided by Sqlite.org). If you want to use a shallow ORM on top of that, I recommend that too. You could look at ServiceStack or a whole slew of other options in that arena. (Just do a web search for lightweight .net orm).

Upvotes: 3

code4life
code4life

Reputation: 15794

You should be using an embedded database then. SQL Server Compact Edition is an embedded database. This wikipedia link points to other embedded databases:

http://en.wikipedia.org/wiki/Embedded_database

Note that not all are free.

Upvotes: 3

Haedrian
Haedrian

Reputation: 4328

If you really want to use SQL, you could use SQL Server Compact edition. It'll create a db file and you can use it just like a normal datbase (with some exceptions)

http://www.microsoft.com/sqlserver/en/us/editions/compact.aspx

Upvotes: 2

Harold Sota
Harold Sota

Reputation: 7566

You can insert in the install of your app the install of ms sql express or save data if they are not large in xml format or ms office acess db.

Upvotes: 0

Carl Raymond
Carl Raymond

Reputation: 4479

There is now SQL Server Compact Edition. This is compiled right into your application. The end user won't need any other version of SQL Sever to use your application. See http://www.microsoft.com/sqlserver/en/us/editions/compact.aspx

Upvotes: 9

Related Questions