In an standalone app'., where the business logic must reside?

I read Tom Kyte's "Effective Oracle by Design". There he says to write most of the code in the DB itself to reduce application code. It is good in a distributed environment, but is it advantageous in an standalone applic开发者_StackOverflowation also?

My app. is in .NET.

Yes there are lots of advantages to putting the business logic in the Oracle database even for a standalone application:

• Performance and scalability, where the business logic involves accessing or updating the database
• Dependency tracking: references to tables and other objects in your code are easily found
• PL/SQL is designed for writing code that accesses the database, very simply, and without the need for building lots of dynamic SQL prepared statements, or using an obfuscating layer like Hibernate to do it for you.

I would actually steal some of Josh's answer and turn it 180 degrees:

IF your database will only ever support a single application, and IF you never expect the same data to have different use cases depending who is using it... then sure put business logic in the...

application.

I mean, do you seriously want two different applications to access the same rows of data but apply different rules to changes to that data? What's the point of having a "rule" if you can break it just by using a different access path?

Note that I omitted this part of Josh's answer:

...IF you are never ever planning to use a different storage mechanism...

Of course, if you are planning to support multiple databases, or to throw away Oracle and start using SQL Server or something else entirely to store your data, then you won't want to use PL/SQL to write your code. But in many, many cases that is not going to happen and you would be ill-advised to pursue database independence for its own sake.

IF your database will only ever support a single application, and IF you never expect the same data to have different use cases depending who is using it, and IF you are never ever planning to use a different storage mechanism... then sure put business logic in the databse.

However, if any of those are true (as I suspect). Then I, and probably most people on this site, would strongly advise you against going down this path.

Putting logic in the DB can be seductive as you think "I can change this on the fly!" but it is a maintenance road you don't want to walk down. Listen to the voice of experience and keep your concerns separate.

My app. is an standalone Windows app. It is an Inventory and Invoicing solution. At present, I wrote classes containing methods to perform various DB operations like Insert, Update and Delete. I wonder, if I could write stored procedures in the DB itself and only pass parameters via .NET front-end, what advantage it might give?

This doesn't actually sound like business logic to me at all, unless of course you are doing something more than just CRUD operations. In that case, then whether or not you use stored procedures, an ORM, or manually generated queries in code is an implementation detail.

The data access layer should care about connecting to the data storage mechanism, but the rest of your application shouldn't care. I'll illustrate with an example:

public class MyDomainObject
{
    public String Name {get; set;}

    public Boolean IsValid()
    {
        return !String.IsNullOrWhitespace(Name) &&
           DoesNotContainInvalidCharacters(Name);
    }
}

public class MyDataAccess
{
    public List<MyDomainObject> GetAll()
    {
        //Some data access logic here
    }

    public MyDomainObject GetByName(String name)
    {
        //Some data access logic here
    }

    public void Save(MyDomainObject)
    {
        if(MyDomainObject.IsValid)
        {
            //Some data access logic here
        }
    }
}

So now you have a domain object and a data access object. The domain object enforces some trivial business logic that can be checked via the IsValid property, and the data access layer uses that to determine if it should be saved or not. However, the details of how data is stored don't really matter to the domain/business layer.

If you want to refactor to use stored procedures, then your application shouldn't really care.