best practices for database applications

Question

I asked myself, what the community considers "best practices" when it comes to building a frontend for a database.

e.g.:

should every form has its own connection?

should all the database related stuff go into a separate layer? That is: creating a class, that handles all things database, which I then instantiate from my forms and so on?

how strict should the separation rule be followed, that is: where to put SQL strings, connection strings. In every forms code (findable), in a separate (source-)file as global variables, in a XML file or even nowhere in the sourcecode, only on the sql server?

etc.

Answer 1

Don't make these mistakes.

Answer 2

When building a Web Application you normally have 3 layers of logic.

1. Presentation layer
2. Business Logic Layer
3. Data Access Layer

The last layer does everything regarding managing the database.

The Business Logic layer should never talk with the database directly, without using the Data Access Layer.

That's my best advise

Answer 3

Read up on best practices for application architectures in your language, and read up on the popular frameworks. The answer to this question can easily fill up 3 or 4 semesters worth of application architecture classes.

Answer 4

This is a very broad question with no simple answer. If this is a small to medium project I would put the connection string in web.config (connectionStrings section) and use the built-in SqlDataSource control to access it.

Answer 5

Understand the transaction model of your database. Oracle and SQL Server both support transactions, but how they work is quiet different.

Answer 6

It's always best to separate the different layers of your project. Look into the MVC framework.

MVC on Wikipedia