How to Choose a Data Access Strategy while developing Application
Choosing a proper data access strategy for your application may be one of the most important and far reaching decisions you have to make. It is strongly recommended that you put a considerable amount of thought into this decision.
I strongly recommend that you make use of an ORM tool (Object to Relational Mapping) to greatly simplify your data access code.
These tools take care of 'mapping' your business objects (often referred to as 'Entities') to your relational data structures.
For example, the ORM framework would understand how to fetch/update/insert/delete your .NET 'Customer' object with its underlying database table(s).
Most ORM tools allow you to take advantage of this type of functionality without writing any database specific code.
This gets you out of the job of writing mundane, error prone data access code and in turn allows you to spend more time on solving the business problem at hand.Recommended ORM tools:
Microsoft LINQ to SQL (SQL Server) / Devart LINQConnect (Oracle, fromerly called LINQ to Oracle)
Microsoft Entity Framework (SQL Server and Oracle via Devart's EF provider)
NHibernate (SQL Server & Oracle)The above ORM tools are listed in order of ease of use and feature set.
LINQ to SQL & LINQConnect are the most basic and easy to use tools. They support a type per table mapping scenario which works well if your domain model matches that of your relational model. These two tools have a small learning curve but lack the features and flexibility that may be required by some applications. Mapping for these technologies is typically done through the use of a GUI designer that integrates into Visual Studio, overall the LINQ to SQL designer works well initially but becomes very problematic when you need to make changes to the underlying data structures as the recommended option is to delete from the GUI and re-add after the changes.
Entity Framework is Microsoft's provider driven, enterprise ORM solution.
The Entity Framework will support a much wider range of mapping scenarios than LINQ To SQL does (view mapping options here) and as of EF 4.1, the Entity Framework also handles the typical LINQ to SQL scenarios as well with a relatively small learning curve. Entity Framework also supports true persistence ingnorance where you can create your domain model free of any EF related base classes, this is otherwise known as support for POCO's (Plain Old CLR Objects). The current release of EF suppports three different entity modeling options: Database First, Model First & Code First. Of these, code first follows a convention over configuration setup that can allow you to map your POCO objects to your database with little or no configuration (xml or code). When using Entity Framework you can additionaly leverage LINQ to Entities which provides a familiar LINQ style query syntax over your entity objects.
Entity Framework documentation can be found at http://msdn.microsoft.com/en-us/librarybb399572.aspx
NHibernate is a mature, flexible and powerful open source ORM tool for the .NET framework.
NHibernate is the most advanced ORM tool we have to choose from, and with that it also represents the most significant learning curve. With version 3.x of NHibernate there is now a built-in LINQ provider that enables you to formule LINQ style queries similar to that of Entity Framework. NHibernate allows you to setup your mapping via standardized xml files (hbm's) or through code, when using code you can also use a framework that supports a more fluent style of mapping such as FluentNHibernate. NHibernate supports true persistence ignorance and more flexible lazy loading options than that of any of the other tools.
NHibernate documentation can be found at http://nhforge.org/
Do not underestimate the time it will take to learn a sophisticated ORM framework like NHibernate, a short project with a very tight timeline is likely not a good candidate for NHibernate if there is no one with any experience using it.
Inline SQL vs. Stored Procedures
The use of an ORM framework will largely remove the need to create stored procedures, in fact if you use one of these frameworks it is possible you won't have to write any SQL for your entire application. This is not to say that there isn't a place for stored procedures, below is a list of reasons that a stored procedure may be necessary:
Security - Using a stored procedure allows you to lock down the underlying tables and only grant execute permissions on the procedure, some security sensitive operations may require this additional level of security (similar levels of security could be accomplished without stored procedures through the use of views).
Data intensive operations - In general using an ORM tool for 'normal' data access will result in performance that is equal to that of a stored procedure; however, if you need to manipulate a lot of data it may be wise to have the code that performs the manipulation closer to the data. This is not a common scenario so generally performance is not an issue.
Complexity - Some queries can become extremely complex and trying to accomplish them in an ORM-friendly manner may result in a solution that is less optimal than using a stored procedure.
ORM frameworks allow for the use of stored procedures alongside with their normal dynamic SQL mode so it is a perfectly acceptable solution to build most of your application without the use of stored procedures and them use them when it becomes appropriate.
Other data related items to consider when designing your application:
Cuncurrency - this is when data might be being worked on simultaneously by different users. There are several ways to handle this and you will need to determine what is right for your application. The simplest approach is a "last wins" type of approach where the last person to update the record/entity is the one that ends up in the database. This option may be a perfectly acceptable approach for your application, just make sure that you know that this is what you are accepting by effectively ignoring concurrency issues. Most ORM frameworks have some built in support for concurrency management. For more information from Microsoft on the topic it is located on MSDN here.
Transaction management - ORM tools typically manage transactions internally and abstract you from it on normal operations (i.e. persisting a hierarchy of objects that all lead up to a single parent) but also support advanced scenarios and the unit of work pattern when you need to persist multiple objects that are not part of the same object hierarchy. If your application requires a transaction to span more than the database in which you are dealing with (i.e. it needs to have a service call commit at the same time) you will need to make use of a DTC (Distributed Transaction Coordinator) - this is not a common scenario but it is something that needs to be considered if you need to make commits to multiple resources within a transaction.