Is this a good application Design?

[Eliyahu Goldin]

Sometimes it is the best practice. You can change from one platform to
another without affecting the business logic implemented in stored
procedures.

Eliyahu

 

[bradley]

Look out! The barbarians are at the gate.

With SQL Server 2005 you can write stored procedures etc. in c#.

Eliyahu

 

[Chad Z. Hower aka Kudzu]

It can be if the business logic is implemented as meta data tables
rather than hard coded conditional branching and looping.

Yes, in that case it could be. In such cases I would put in the middle tier, not the database.

 

[Chad Z. Hower aka Kudzu]

Problem with that approach is our application will have to support
ACCESS as well as SQL Server, in future we might have to deal with
Oracle.

Then you should do it as my article says.

We program exclusively on interfaces and not on actual SQL or OLE
objects. By using simple Views we can mask the changes in underlying
tables if needed.

Thats very good, but views wont do complex transforms. And the matter of views vs sps is a SQL
server one, SQL Server is not efficient with views, use SP "as views" when using SQL server.

 

[Eliyahu Goldin]

With SQL Server 2005 you can write stored procedures etc. in c#.

Eliyahu

 

[Cor Ligthert]

Steve,

T-SQL is an excellent language for implementing business logic that is
tied
closely to the database model or database processing. For example,
imposing
data constraints or referencing a set of parameters against a lookup table
and returning a value. Of course, it helps is the developer is at least an
intermediate level SQL programmer and familiar with sub-queries, complex
joins, conditional grouping and where clauses, etc.

Not better than English, what is called by non native English speakers often
a very poor language when it comes to symantics. Than it is in my opinion
even English with USA slang and therefore even worse.

Just my thought about it.

Cor

 

[Eliyahu Goldin]

Make your mind. Horrible language vs not terribly portable.

Eliyahu

 

[Nick Malik [Microsoft]]

Hi Jay,

1. Presentation Layer (Asp.Net / Win Forms/ Pocket PC UI.)
This predominantly contains User Controls, Custom Controls and Win/Web
Forms.
I have one base form and I inherit from that form.
All validation is done in this tier. There is zero Data Access code in
this tier. This tier exclusively gets DataViews/ Arays/Lists as input.

All this looks pretty good... but...

Outputs are string objects with SQL Statements or just paremeters with
dataconnection key.

This is a pretty bad idea. Your presentation objects should pass data
views, data sets, lists of business objects, etc, to the business layer.
The presentation layer must not know anything about the persistence
mechanism. Otherwise, you end up with expensive code to maintain.

2. Business Layer.
Currently this is just a Library residing in the same machine as
Presentation layer. Eventually I plan to use WebServices if application
needs physical seperation.
Most of Business Logic are implemented in this layer. There is absolutely
no UI code here.
These are all bunch of static methods which perform CRUD functionality.
All methods are atomic in nature. I dont rely on Static Variables. There
is just one DatabaseGateway class which does all the functionality.

This is a structured programming approach. It is not really my style. I
fail to see how you can truly move business logic through a "God Class." (A
God Class is an antipattern. The God Class sees everything, knows
everything, and does everything. . This nearly always violates the
Open-Closed principle and produces fragile code (usually in other layers).
Handing off the difficult part to other layers because it is hard to do it
in the business layer is not my first recommendation.

Note that CRUD functionality is not usually the same as a business rule. In
fact, I would suggest that 90% of the real business rules in your app are
not related at all to CRUD functions. These are database operations.

If your system is truly partitioned this way, then your business logic is in
the presentation layer.

3. Data Layer. (SQL Server)
I dont use stored procedures. predominantly use Views.

I read your subsequent posts about why you don't use stored procs. I
understand and sympathize. However, typically in code, we have a Data
Access Layer that hides the fact that your underlying system is a particular
database. This data access layer does many of the connection and CRUD
operations.

If you truly want to reconsider how to partition the functionality of your
system, create an idea in your head of the purpose for each layer. Write
that purpose in large letters on a white board, divided into sections for
each layer, and then break our the activities of your system into methods
that you will implement in each layer. Write these methods under the
heading. Make sure that the purpose matches the method. If the method
doesn't support, defend, and make sense in light of the purpose, move it or
redefine it.

Your answer is your own. It is much easier to work with that way, and if it
works for you, use it. Don't let an author decide for you... or a geek on a
newsgroup .

In my experience, I have found that CRUD is a feature of the data access
layer. Business objects enforce business rules and nothing else.
Presentation layers deal with presentation and format validation. That
allows me to seperate the functions in a way that is easier to maintain.

Consider reading about the "Layers" pattern in the Buschmann book on
patterns.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--

 

[Steve Walker]

T-SQL is an excellent language for implementing business logic that is tied
closely to the database model or database processing. For example, imposing
data constraints or referencing a set of parameters against a lookup table
and returning a value. Of course, it helps is the developer is at least an
intermediate level SQL programmer and familiar with sub-queries, complex
joins, conditional grouping and where clauses, etc.

Speaking personally, I'm familiar with all of those constructs. The fact
that I can program competently in a language doesn't change my opinion
of whether or not it is horrible. TSQL is horrible. It's BASIC with
support for set-based operations and a slightly uglier procedural
syntax.

I'm not saying it isn't useful. It is immensely so. I'm saying I dislike
it, and were it not an absolute necessity of my professional life, I'd
be happy never to write another line of it.

While I'm being rude about other people's pet languages, by the way, the
same goes for XSLT. And yes, I'm reasonably competent in that too.

 

[Steve Walker]

Make your mind. Horrible language vs not terribly portable.

Using C# from within the database is not terribly portable between
databases. Implementing a tiered design with simple stored procedures
gives you more options and allows you to do what SQL is good at in SQL
and what object oriented languages are good at in object orientated
languages. If presentation, domain model, data access and database logic
(i.e. stored procedures)are all distinct tiers it's relatively easy to
substitute a different platform for any one of them. If the stored
procedures are relatively simple CRUD affairs, you will even be able to
migrate to a platform which doesn't support stored procedures with
relative ease.

 

[Chad Z. Hower aka Kudzu]

Using C# from within the database is not terribly portable between
databases. Implementing a tiered design with simple stored procedures
gives you more options and allows you to do what SQL is good at in SQL
and what object oriented languages are good at in object orientated
languages. If presentation, domain model, data access and database
logic (i.e. stored procedures)are all distinct tiers it's relatively
easy to substitute a different platform for any one of them. If the
stored procedures are relatively simple CRUD affairs, you will even be
able to migrate to a platform which doesn't support stored procedures
with relative ease.

Spot on.

In fact, you can even see performance benefits from this approach, as well as more scalable
deployments.

 

[Michael Rodriguez]

There are of course many schools of thought, but personally I would never
recommend that. If
you want my views as to why, you can read the link that I just posted.

Chad,

I have to respectively disagree with you. There are several instances where
the business logic should be in the stored procedures. Suppose you have a
posting procedure that needs to compute the average cost of items based on
thousands of invoices, then post those costs to another table. In order to
do that logic in the business tier, you would have to retrieve all of the
relevant data locally, crunch it, then send it back to the DB server in
another form. This defeats the whole purpose of having a database server to
begin with. Now you're back to the FoxPro days where the network bandwith
was sucked up by thousands and thousands of rows of data being sent back and
forth across the network for processing. One of the main advantages of
having a database server is to let it crunch (read - perform logic) all the
data and return only the much smaller result set.

I'm not saying your approach is necessarily incorrect. That would work
great if you were dealing with small chunks of data. But when you have to
insert values into tables based on numbers computed from thousands of rows
of data in other tables, I think it's best to let the DB server do all of
the work for you. That way nothing has to travel across a wire.

My $.02

Mike Rodriguez

 

[Chad Z. Hower aka Kudzu]

I have to respectively disagree with you. There are several instances
where the business logic should be in the stored procedures. Suppose
you have a posting procedure that needs to compute the average cost of
items based on thousands of invoices, then post those costs to another

These exceptions are rare, and if you read the URL I posted, I even covered this. These account for <
1% of business logic, yet most systems implement often half of their business logic in the
datbase.

Even in this case, its a simle matter of running a grouping query, dividing and reposting. There are
complex cases though beyond your example that definitely need to be in an SP, but again they are
rare.

 

[Michael Rodriguez]

These exceptions are rare, and if you read the URL I posted, I even
covered this. These account for <
1% of business logic, yet most systems implement often half of their
business logic in the
datbase.

I have to admit I have a rare application! We do accounting for restaurants
and everything is based on theoretical food costs, which involves a lot of
data to compute. Many of my posting stored procedures are contain over 500+
lines of T-SQL code. As much as I would like that work done in the business
layer, it just isn't practical in our case.

I did like your article, though. Nice work.

Mike

 

[Chad Z. Hower aka Kudzu]

I have to admit I have a rare application! We do accounting for
restaurants and everything is based on theoretical food costs, which
involves a lot of data to compute. Many of my posting stored
procedures are contain over 500+ lines of T-SQL code. As much as I
would like that work done in the business layer, it just isn't
practical in our case.

Exceptional cases do exist. But in most business applications such calculations that can be done
with measureable diference in performance in the SP's versus the business layer are a small
amount.

I dont envy the large SP's you have to maintain. I once designed a system to track trades, which we
thousands per second. We had to build big calcuation and aggregation servers becuase not even the
DB could keep up.

I did like your article, though. Nice work.

Thanks. I hope you found something useful. Please feel free to pass it around and dont forget to
vote in the bottom right.

Its actually the first in a series of articles, so keep your eyes open.

 

[Michael Rodriguez]

Exceptional cases do exist. But in most business applications such
calculations that can be done
with measureable diference in performance in the SP's versus the business
layer are a small
amount.

I dont envy the large SP's you have to maintain. I once designed a system
to track trades, which we
thousands per second. We had to build big calcuation and aggregation
servers becuase not even the
DB could keep up.

It's really a very fine line. If you have a system where hundreds of users
are crunching loads of data, which poison do you pick? Do you do all the
work in stored procedures (= database bottleneck), or do you send everything
back to the clients and distribute the processing (= network bottleneck).
We're still trying to figure it out...

Mike

 

[Guest]

For good architectures read on:
http://msdn.microsoft.com/practices/ArchDes/default.aspx

Kurt

 

[Joerg Jooss]

Your SQL should be in the Business Layer. The presentation layer
should not make or provide decisions on how data is collected ( that
what tables / view or columns are used ) as that is a business rule.

SQL belongs in the Data Access Layer, not in the Business Layer. Why
tie the business logic to any specific persistence mechanism?


Cheers,

 

[William \(Bill\) Vaughn]

What? I would get your facts straight before advising people to simply
depend on C# for Stored Procedure coding. While it's entirely possible to
write stored procedures and other server-side executables in CLR languages
(including C# and VB.NET), it's not always (and not even usually) a good
idea to do so. CLR code execution should be reserved for a few specific
cases where CPU-intensive operations or those jobs that TSQL was never
designed to handle.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Chad Z. Hower aka Kudzu]

What? I would get your facts straight before advising people to simply
depend on C# for Stored Procedure coding. While it's entirely possible
to write stored procedures and other server-side executables in CLR
languages (including C# and VB.NET), it's not always (and not even
usually) a good idea to do so. CLR code execution should be reserved
for a few specific cases where CPU-intensive operations or those jobs
that TSQL was never designed to handle.

For code that exists on the database server, I agree. Im excited about where MS is going with this, but the
first generation of CLR on the DB I think is going to cause more problems as its not "complete" yet as
to where it will eventually go and be really useful, and in the interim users wont know the proper
roles and will abuse it.