generic n-tier question re sql strings 5

[misterstick]

out of ignorance and hubris over the last month or so i've pulled an n-tier application out of my special magic pocket.

i thought i'd understood the general principles of it all, but a brisk reading of this microsoft article on mimicking ado in .net gives me pause for thought.

i fear that what i thought was the data access layer is, in fact, merely a wrapper for the various data access protocols.

my question, therefore, is how, and where, should sql strings be stored and handled in your bog-standard n-tier architecture?

i've got them in the "business" layer currently. should they be moved to the "data access" layer? should i create a new layer in between "business" and "data access" that actually handles the data access?

should it all be done with stored procedures, moving the actual data access into the database?

mr s. <

 

[chiph]

I typically have two "mini layers" in my data access code. One does the messy SQL operations (CRUD+), and the other sits on top of it and manages database transactions. This lets me update two tables/entities inside the same transaction.

The CRUD+ layer will have the SQL strings (using ADO.NET parameter objects, of course!) inside it. At the last company I worked at, they had a framework which retrieved the SQL from an embedded XML file. Their opinion was that it was better to have it separate from the DAL code for maintenance purposes. And for 80% of the time they were correct. But as soon as you had an "IN" clause or had to build the SQL dynamically, this argument would fall down. You picks your poison...

You'll get differing opinions on whether to use stored procedures. My own opinion is use them when there's a performance or security need. Otherwise using parameterized queries is often fast enough, and make the SQL more maintainable and versionable.

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[misterstick]

so i do need to create a data access layer, then.

would a typical request for data go like this, then?

partner form: give me my data;
businesslayer: give me the data for partners
data access layer: select * from partnerdata
dataprotocolwrapper: oracleAdapter.Fill(dataSet, "tablename")

with the data being passed between layers using a DataSet.

you mention storing sql strings in an XML file. did they have an app to update this, or was it all done via visual studio? there could potentially be rather a lot of them.

i have used only SELECT statements to do everything, and allow the commandbuilder and dataAdapter.Update(dataSet, table) to do their stuff wherever.

is this a bad idea?

mr s. <

 

[chiph]

My layers are usually something like:

GUI
Control
Business
Transaction
CRUD+

The interface between the Transaction and the CRUD+ layer is via parameter-rich method calls. The interface between the Business and Transaction layer is via DataTable/DataSet objects. The interfaces above the Business layer are all via strongly-typed objects.

You should look at the needs of your application prior to making decisions like this. For instance, if it's a web-based application, it's likely that the protocol between the Control layer and the GUI layer will have some XML in it.

----

They weren't using an app to maintain those external SQL statements. They were under source control, and the DBAs and developers would work together when changes were needed. I think part of the reason for it's existence was political, not technical. :-|

I generally don't use DataAdapters, as I usually work on large-scale systems, and they just don't perform and/or act like we want. They're fine for small-to-mid sized systems, however.

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[misterstick]

given that the database is really quite small, though, and the devloper team working on it (at the moment) is just me, did i guess right?

parameter rich method calls? what does that mean? how do you get tabular data between layers without using a data-type object? do you read the rows and columns into an array and then cast/copy it into a datatable?

too many questions, i guess i should get some tuition somewhere.

many thanks for the pointers, though.

mr s. <

 

[chiph]

parameter rich method calls? what does that mean?

It's a method call with a whole bunch of parameters.

No need (generally) for passing multiple rows at this level of database access, as a method call usually corresponds to single-row adds/updates, etc.

If I had a need to insert multiple rows at once into one table (like for imports-exports), I'd probably create a messenger object (aka DTO) and use an ArrayList to pass between the transaction layer and CRUD+ layer. Or use SQL Server DTS, depending on the volume of data.

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[misterstick]

looking at it again, it seems that my problem is one of naming.

data acces = data protocol wrapper,
rules = data access

and i haven't split out the business rules from the presentation layer. this is such a small project that there aren't that many rules anyway.

there's stuff that seems it definitely should be dealt with on the client side that could be considered a business rule. for example data typing or forced lookup validation through lists. i guess this can't be what a business rule is, though. or, since it's windows and everything's done on the client bar the database stuff, perhaps this should be done by calls to a business layer method. seems overkill. in a console app definitely on the server, in a web app definitely in the client. trying to code as generically as possible maybe isn't the best place to start.

i don't know, i guess i'll have to read around what a business rule is (as i said, ignorance and hubris all the way) and try again.

as far as the post immediately above goes, it's set lightbulbs of above my head. hmm. treating each row as an independent object. only passing the data back to the server you want to do anything with. sounds good to me. i'll put it on my tothink list.

have another star.

many thanks,


mr s. <

 

[misterstick]

chiph: sorry about the extra star, if i could give it to you i would.

further into this business rules thing.

what about populating a dropdown from a datatable. i would say the dropdown is definitely in the presentation tier, and the filling of the datatable should be in the data access tier (well duh). the control knows what data it needs, is there any reason why it shouldn't ask the data access tier directly for it? the fielding of the request by the business layer would seem only to be adding a further procedure call without any benefit. although the rule that this dropdown is enforcing definitely is a business rule, "each file must be owned by one partner", albeit a simple one. i guess this may change to be more complex, and things are always more difficult to retro-fit.

we had a consultant in and he said it took him three years to get his head round this stuff.

hmm.

thanks again,


mr s. <

 

[Guru7777]

The benefit of adding the business layer in-between your dropdown and the datasource is so that you can implement changes without affecting your data or presentation layers.

For instance, if you switch to Oracle from SQL Server, or decide to add new logic to the method, you can do so without affecting the presentation layer.

This is most effective if you have that business layer separated from the presentation layer through the use of web services or remoting.

----------------------------------------

TWljcm8kb2Z0J3MgIzEgRmFuIQ==

 

[misterstick]

For instance, if you switch to Oracle from SQL Server

the DataProtocol object two layers below the Business layer will hopefully do this.

or decide to add new logic to the method, you can do so without affecting the presentation layer.

true, but the data access layer is fielding the request for data anyway. i can't, at the moment, see why the business layer should field simple requests for data, ie straight select statements. it seems an extra function call for no gain.

i'm having a failure of intellect over this point. if the data access layer handles the actual request for data and is the only place that has database-specific things like actual table- and column-names, how does the business layer ask for such stuff?

an example. i have a generic data lookup control. this is, of course, in the presentation layer. at the moment, the constructor for the control is passed a table and two column names: the source data table, the ValueMember and the DisplayMember. would it be so bad for the control to ask the data access layer for the data directly?


mr s. <

 

[chiph]

the fielding of the request by the business layer would seem only to be adding a further procedure call without any benefit.

It would sometimes seem that way, but what it gives you is a place to put future code. For example, if you had a rule that some users couldn't see all the data (role-based permissions), then the business layer would give you a way to filter that out, based on business rules. Of course, it might be better to put such code in the WHERE clause in your SQL, but perhaps you didn't want to do that for some reason -- without a business layer, you'd have to invent one.

at the moment, the constructor for the control is passed a table and two column names: the source data table, the ValueMember and the DisplayMember. would it be so bad for the control to ask the data access layer for the data directly?

Ideally, your presentation layer would never know such details.

What you can do is create some database metadata abstraction code that hides that from the presentation layer. The presentation layer knows about this class and it's enum/constants, but that's all. It's a lot of extra work -- but ultimately it depends on what level of architectural purity you're looking for.

As a compromise, what I've seen people do is use a code generator (like CodeSmith), so when they add a new business entity, it's not a ton of work to create all the methods in the various layers. You get consistent code, but it's more work up front to set up the generator templates.

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[misterstick]

i come from a 4gl background, so effctively i've been coding in the business layer all my working life, constricted by the fact that the 4gl did all the presentation and data access stuff for me. being let loose in the big free world of oo 3gl is a bit bewildering.

and splitting out busines rules that belong in the presentation layer (type/value checking) or database (key uniqueness) from stuff that must be coded in the business layer is making my head hurt.

i like the idea of architectural purity, though. i also like the idea of not having to retro-fit layers again.

i think i've solved the dropdown problem. the control tells the business layer what kind of control it wants to be. it then binds ValueMember, DisplayMember to the first two columns in whatever is passed back.

another example. some of the processing is done in batch, and the output of that is written to text files. these are then opened in notepad for the user. notepad, presentation layer, obviously? what about the calls to the operating system to manage these files?

many thanks for your help,


mr s. <

 

[chiph]

Batch is another animal entirely from interactive usage.

There, the only criteria that you care about is performance, and so you'll make more architectural compromises in order to meet the time constraints you have.

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[misterstick]

sorry, the batch bit was a red herring. it's the output text files i was asking about. specifically the calls to open them and delete them when done.



mr s. <

 

[chiph]

I don't know enough about what you're trying to do.

I would guess, that such would be handled at either the Control or Business layer, depending.

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[bassguy]

As I see it you are fighting one of the Classic battles, How do I make OO and N-tier interact with relational data.

In general,

If you add layers,
then the application is more scalable and easily changed at a later date.

The Contrapositive of this is also true,
If I do not need to make changes at a later data I do not need a lot of layers.

(thanks freshman logic 001 ... took 20 years but I finally used it)


Google "PetShop" and D/L it for an example of how to work some layers and class factories for data access.

You are at the point where you need to do some studying because the architecture is not easily understood and not generally explained in "how to program in C#" type books. Most of them will show you how to use objects but will not tell you how to put them together in an elegant fashion.

If you are interested in Enterprise Design for all of the layers there is one accepted guru for this. Martin Fowler. Makes for interesting reading and addresses some of your concerns.... for me it made me extremely curious to see what his code would be when he has deadlines etc.

Another thing you may want to lookinto is the "Gang of Four". they have a lot of patterns that you may want to use.

"Design Patterns Explained" by Shalloway and Trott is also another Bible in my set.

HTH

Bassguy

 

[chiph]

The Martin Fowler book: Patterns of Enterprise Application Architecture is excellent. ISBN: 0321127420

It's expensive, but worth every penny.

The trick is to define a set of responsibilities for each layer:

CRUD+ : Inserts/updates/retrieves single database rows
Transaction : Manages DB transactions
Business : Enforces business rules
Control : Maps user requests to business rules
GUI : User experience

If you have a need for some code to do something, see which layer it would naturally fit, balancing your immediate need against future needs and maintainability. There's usually no "right" answer -- just ones that are "good enough".

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[chrissie1]

There's usually no "right" answer -- just ones that are "good enough".

That's what sums up these patterns for me. They are just theories and are interpretable in any way you like (a bit like the law).


Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[misterstick]

two recommendations for a book is enough: amazon have promised to have it to me before christmas.

sounds like i've got some heavy studying ahead.

one of my problems is that this is just a pilot for a project orders of magnitude bigger. so, even though this project is very small, reasonably simple, and will not need to scale or change significantly, i have to try to work out how to deal with all of that nevertheless.

i also have to sit down and work out some kind of methodology to translate my 4gl into proper business rules. writing rules is not coding, therefore not fun.

wish me luck,

mr s. <

 

[misterstick]

ok, so i've bought the book and read maybe fifty pages.

he doesn't provide answers. boo!

what he does provide is ways to think about how you go about crafting solutions. yay!

it's making my head hurt. but in a good way.

i'll read the introduction again when i've finished it, and report back.

i'm dipping into the patterns as i feel the need.

is there an internet site that provides an quick idiot's guide to decoding the pattern structure diagrams? they just look like a bunch of lines to me.

many thanks,

mr s. <