How to store objects data into database from OOP application efficiently and easily?

Question

Hello I design an objected oriented application using C# with database back end, for some time I was building same application using procedural way in which I feel storing data to database is little bit easier using simple SQL statement. I my application I want to store the objects attribute to database, for example for an item object

    Class Item {
 private string itemId;

private string itemname;

private Invoice inv;  //assume Invoice object

//method

public bool storetodb() {

//..something like this
}
}

The problem is I feel a little bit complex when storing and retrieving object data to database. When I searched on some resource also they use like collection class to map between object on the application and relational database. Even I have searched SO but I couldn't find any related information. One source I got was

• http://www.codeproject.com/KB/database/AONETOOP.aspx ..... The author uses collection to store and retrieve data
• I hear also about ORM technology although I have never tried it.

So what is good practice for designing these types of applications? can you give any links, article, recommended book or response for tackling these types of problem.

Thanks

Update

It seems ORM tools mainly nhibernate is most promising from your suggestion. Thanks all of for your help, I will consider ORM

Answer 1

Buy: Scott Ambler_s "Building Object Applications That Work". old, but true.

Read up on ORM (Object Rlelational Mappers).

Read up on NHibernate and Microsoft Entity Framework - the later being clearly inferior.

Answer 2

"google"? :-)

[n]hibernate seems popular. LINQtoSQL and EF (Entity Framework) are also ORMs and are "in the Microsoft stack" -- e.g. come standard with VS. There are many other ORMs as well, such as Subsonic.

In general, due to the "impedance mismatch" from traditional SQL RDBMs systems to "OO", most of the above systems work best with schemas that follow certain conventions specific to each model. (Some are more 'accepting' of different schema views than others.)

Answer 3

Best practice for the object oriented vs relational database impedance mismatch is to use an ORM.

This is not perfect, but will help with most uses. As @SquareCog comments, you still have to remember that there is a relational database under the hood, as most ORMs do end up exposing this to one extent or another (see leaky abstraction).

ORM tools are there to assist with the mismatch. Popular ones in the .NET world are:

• nHibernate (oldest of them all, fully featured)
• Linq to Sql (deprecated by MS)
• Entity Framework (new by MS)

Answer 4

I'll recommend LINQ to SQL from Microsoft for Windows development. Really easy to learn and to get started.

LINQ to SQL: .NET Language-Integrated Query for Relational Data

ADO.NET Entity Data Model Tools (for easy design within Visual Studio)

I'll recommend Hibernate for Java development.

Relational Persistence for Java and .NET

Read this article on Wikipedia too:

Object-relational mapping

Answer 5

Hi

You should invest some time to investigate one or two ORMs. If you're working with .NET 4.0, you should give the new implementation of Entity Framework a try. Another widely used ORM is NHibernate, which is a .NET version of Java's famous Hibernate ORM. However, from my own standing, ensure that you encapsulate the whole ORM into your Data Access Layer (DAL) so you can use native ADO.NET if the ORM ever hits the wall.

Another option are EAV CR databases. However, it's not the easiest way to learn how to live with. (I did that more than five years.)

Answer 6

My 2c - db4o - simple and natural...

Answer 7

As Daniel pointed out: db4o is really a natural choice as you don't have any ORM involved, you simply define your domain model and save these object do the database! However, if you are constrained to relational databases then I would go with Entity framework in version 4.

Appart from that I would recommend the repository pattern to hide the database related stuff behind it.