Data Service Tier Dll

[RobDog888]

I want to write a dll for connecting to a SQL server so I can
re-use it on my programs. I think its correct to do it this way for
better performance. Right now I have eveything in the exe. How
can I get started making it so its generic for any server or db?

Also, would running an exe from a network share be detrimental
to performance because of all the network traffic? Better to install
the exe on every pc locally?

Thanks for any help.

[NeedSomeAnswers]

Are you just wanting the dll to be generic for any SQL Server DB on Any Server ??

If so then the main thing you would need to do would be to pass in the Connection string as an arguement to the dll.

By doing this you could have all the logic for Connecting inside the Dll, but would pass in a connection string to it which would mean that it would be re-usable as you would be passing in the IP Address of the Server & Name of the DB each time it was used!

I take it that you know a fair amount about dll's and the like you seem pretty experienced looking at some of your other posts ??

[RobDog888]

Thanks, but I just wonder if its correct logic to do it like this? I want
to be able to pass the connection string props and return an ADO
connection object. Also, where would it be located, on the server,
or ws' for best performance and maintenance?

[Dave Sell]

Ever heard of a Factory? This is a great case to use one.

In fact you are starting to want one, and do not know it yet. However I would recommend a slight change in your imagination.

Currently, you want this:


(Client) --asksfor--> Factory.GiveMeAnObject("Databse specific crapola")


This forces your client to contain unneeded "baggage" - specifically the "way" to get the "type" of database.

What you should be wanting is your client NOT to know the "way", but simply the type of database.


(Client) --asksfor--> Factory.GiveMeAnMSSQLObject("less database crapola, like username - you cannot escape this crapola")
(Client) --asksfor--> Factory.GiveMeAnMYSQLObject("less database crapola")

This is the whole problem with databases - they simply refuse to be completely encasulated inside a dumb (read generic) object.
There is unfortunately always some bagagge you will have to burden your client with.

[RobDog888]

Thats fine Dave. I actually only want to pass the server name, db
name, userid, and pwd. This will be specifically for SQL servers so
I dont need to write it to handle anything other than that.

What about where should it be located, on the server,
or ws' for best performance and maintenance?

And - running an exe from a network share be detrimental
to performance because of all the network traffic? Better to install
the exe on every pc locally?

[Dave Sell]

Are you making an in-process DLL, or an out-of-process EXE?

[RobDog888]

Whichever is most suitable for the application's enviroment. Its a
network with a dozen or so users, but if this works well in this
app I want to use it on another program where the database is
intense.

If the apps exe is located on a network share then should I
change it to local installs? Then depending on that answer would
tell me which type of ActiveX to create, right?

[Dave Sell]

er, I think the situation will be the same in those 2 cases:

1) The Client app (exe) runs on a network share
2) The Client app (exe) runs on the local PC disk

The question regarding network clogging may rest in the diff between 3 things:

1) In-process DLLs
2) out-of-process LOCAL COM EXEs
3) out-of-process REMOTE DCOM EXEs

I think those 3 will have different affects on the network. What kind of performance metrics are you most concerned about?

[RobDog888]

As you can tell, this is my first attempt at creating an ActiveX process.
So, if there is no difference between hosting the exe on a
network share vs. a local exe then I only need to know if it
should be dll or exe? I want the app to have fast db access and
GUI response. I dont think I need to use DCOM?

Thanks.

[Dave Sell]

How many client EXEs are we talking about?

I believe your best approach is an in-process ActiveX DLL. The only thing to watch out for is "how many connections are open at 1 time?"

This resource management problem could be harmful in the case of an in-process DLL and an out-of-process LOCAL COM EXE.

But it can be regulated globally with respect to the DB server if you use an out-of-process REMOTE DCOM EXE.

As far as speed, I thought your object's main job is to return an ADO object? I dont see any performance difference in the LOCAL cases.

Again, what specific thing do you want to work the fastest? The time it takes to return an object, or the performance of the object once its been instantiated?

I am not sure if a Connection object will have any difference in performance characteristics once it has been instantiated, regardles of LOCAL or REMOTE location.

[RobDog888]

I guess I want performance of the object once its been instantiated.
I'm getting a little confused here. I thought that by creating an
activex object I would be able to increase the performance of my
app?

[Dave Sell]

Originally posted by RobDog888
I guess I want performance of the object once its been instantiated.
I'm getting a little confused here. I thought that by creating an
activex object I would be able to increase the performance of my
app?

Possibly. However my strongest motivation for involving pre-compiled (binary) components is the reduction in complexity.

This gives me an increase in performance when designing, testing, debugging, optimizing, and refactoring a SYSTEM.

Are you having problems with your run-time performance? We can definately examine the system and look for bottlenecks.

But in my opinion, your course of action will benefit you at design time, perhaps not at run-time. I've been known to be wrong, though.

If you want a substantial increase in performance, you will be better served moving to C++.

[RobDog888]

Its just not possible to port it over to C++. The app has over 50
forms, 5 modules, and 5 classes or so and it was written in such
a way using Public ADO Recordsets populated with unprepared
sql statements that its so slooooow slow. I know using sp's will
be better for performance, but that would require a complete
rewrite because of the public rs'. Its been driving me crazy the
crappy way the other programmers wrote it years ago. I am
slowly making improvements, but I wanted to refocus my efforts
in a more beneficial area.

So to get this app using a dll or two should help if I do it right.

Thanks for letting me vent my frustrations. I just wish the other
programmers knew what they were doing back then. Its just
common sense!!! They have the entire program in the exe! No
Data service tier. No business Rules tier. Nothing. :rolleyes:

There I go again. Help!!!

[Dave Sell]

Originally posted by RobDog888
Its just common sense!!!

True Story:

When I was about 12, my Grandpa (rest his soul) was tuning up an engine. He says: "DaviT (thick German accent) when you remove the head, you must turn each bolt a quarter turn at a time, and slowly back each bolt off in that way. If you remove one bolt all the way, before loosening another bolt, you will warp the head. IT'S JUST COMMON SENSE!"

Those words ring in my ears to this day; I had almost no clue what he was talking about at the time, I was just a kid!

Anyway, what is common sense to you comes from thousands of hours of experience, and is always a strange concept to the nOOb.

I inherited a 10-year old VB 3 project that evolved to VB4 then VB5 and finally VB6. You should see me pull my hair out looking at the crap-f-ing-ola these nOObs put in there. No concept of standard OO practices whatsoever. One "programmer" was a secretary!!!! AAAAAHHHHHHAAAAAAAHHHHHAAAAAAAAH!!!!!!!!!

You are not alone!

Anyway, concerning your current "project", I would stay down the path you are on - not for the benefit of run-time performance. That will come later. But instead, if you can gut the crap-ola out of the source, things always become easier to refactor, and thus, optimize.

[NeedSomeAnswers]

Condsidering it is an old app, is it using Disconnected Recordsets ?, this often speeds things up !

[NeedSomeAnswers]

I suppose the point i was trying to make, with my last post was that, just shoving something into a dll or activeX object won't necessarily improve preformance, although it can if it is done right

Before you do that i would look at all other ways of improving the way the App interacts with it's database, and try to rationalize the dodgy code that you have been lumbered with when you take on old projects that other people have written.

Cant you stop it using Public Recordset's for a start!, or are there alot of them ??

[RobDog888]

There are about a dozen public rs' and they are used throughout
the 50 forms in the program. I want to change that too. They are
not disconnected either, but when ever they load a form from a rs
they reopen the rs with another call. Dont make much sense to
have it public if you are reopening the rs everytime? Might as well
use private rs' then. Same difference.

[Dave Sell]

Agreed.

Also wanted to point out that you want to keep you connections to a minimum.

That means ideally you will only have 1 ADODB.Connection object instantiated and open at a time.

However, I beleive having multiple ADODB.Recordset objects open is OK and should not be too resource intensive per se.

That said, of course everything should be kept to a minimum where possible.

[RobDog888]

See, I want to use the data service dll to pass sp's and parameters
to eliminate the unprepared public rs'. I have only one open
persistant connection, but in order to see the add, deletes, and
edits in the rs' the rs needs to be opened with adOpenDynamic. Then there need to be a way to know if there is a new record
added or deleted so I can update the data on the form?

[Dave Sell]

Originally posted by RobDog888
Then there need to be a way to know if there is a new record added or deleted so I can update the data on the form?

Can an ADODB.Recordset raise an event when a record is added?

[Dave Sell]

I am thinking no, but you could make your own mechanism into your data-tier, however that will be quite expensive at run-time...

[Calibra]

Only go for an DLL when youre app will have 50+ users, otherwise the performance gain wil not outweigh the extra effort you will have to put in.

[Dave Sell]

Originally posted by RobDog888
Then there need to be a way to know if there is a new record added or deleted so I can update the data on the form?

This would require creating new database objects, namely Triggers. I dunno if there is a COM equivalent, I doubt it.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_t_3nsk.asp

Exerpt:

SQL Server supports using triggers as a kind of stored procedure. Triggers are executed when a specified data modification, such as an attempt to delete a row, is attempted on the table on which the trigger is defined.

[RobDog888]

Ah!, I never thought about using a trigger to tell the app that a
record was either added, updated, or deleted. So would quering
the db to get a temp table listing of current adds, updates, and
deletes be the wrong way to do it?

[Dave Sell]

Unfortunately, I can't see any other way. I do not believe a Trigger can notify an external COM entity of an event. I have been known to be wrong though.

It seems to me you are going to have to "poll" some new table you make just for the purpose of being notified of events occuring in the database.

[Dave Sell]

Man, if this is really your best route then I feel like we are in the friggen stone age of computing :mad:

[RobDog888]

Wait, since I will be opening the rs' with the adOpenDynamic
the rs will always have the updated record contents? The issue is
how to know when to reload the combos textboxes and other
controls to include the updated data?

[Dave Sell]

Originally posted by RobDog888
[color=darkblue]Wait, since I will be opening the rs' with the adOpenDynamic
the rs will always have the updated record contents?color]

Is this true? I wasn't able to find documentation to support this claim. Do you have some that does? That would be pretty cool.

[RobDog888]

Dave, in the documentation for adOpenDynamic from MSDN:

adOpenDynamic:
Reflects any new additions, changes, and deletions made by
other users, and allows all types of movement through the
Recordset object that don't rely on bookmarks; allows bookmarks
if the provider supports them. This cursor type isn't supported by
the Microsoft Jet 4.0 OLE DB Provider.

adOpenKeyset:
Behaves like a Dynamic cursor, except that it doesn't contain any
new or deleted records added by other users. Any data changes
made by other users to the records available when the Recordset
object was opened will still be visible. A Keyset cursor always
supports bookmarks and therefore allows all types of movement
through the Recordset object.

But I never actually tested the difference if you open a rs with
adOpenDynamic and you load some cbos. Then another user
adds a new record, the rs should reflect the addition, but how do
I know that I need to reload the cbo to reflect the addition?

Thanks.

[Dave Sell]

Right. How do you know? More specifically WHEN do you know that a change has occurred? The ADODB.Recordset object does not advertise Events AFAIK.

This is where a Trigger must be used; to find out WHEN something happens. It looks from the docs you provided that the Dynamic Recordset will reflect outside changes WHEN POLLED, but not alert you to changes at the time of the event.

A way around this is to tie a Trigger to your table in question, and force some data into another table. Then, you could make a kind of Custom Class to detect changes in the second table and alert you via a true VB Event.

I personally hope you find a better way than this - I am interested in this problem.

[RobDog888]

First: what does "AFAIK" stand for. I have seen it before can not
figure it out.

Second: If I use a trigger on the table in question, how do I get
the trigger to relate to a vb event? Oh wait there is the
RaiseEvent in SQL. In the triggers (ADD, UPDATE, and DELETE) I
can raise a custom event telling my app that there was a change
to a recordset. But then what if it comes from another instance of
the app?

Third: Oakland Raiders are going to win this years superbowl!
They are going to spread that Green Bay cheese over a cracker
and eat it to celebrate!

:D

[Dave Sell]

Originally posted by RobDog888
First: what does "AFAIK" stand for. I have seen it before can not
figure it out.

Second: If I use a trigger on the table in question, how do I get
the trigger to relate to a vb event? Oh wait there is the
RaiseEvent in SQL. In the triggers (ADD, UPDATE, and DELETE) I
can raise a custom event telling my app that there was a change
to a recordset. But then what if it comes from another instance of
the app?

Third: Oakland Raiders are going to win this years superbowl!
They are going to spread that Green Bay cheese over a cracker
and eat it to celebrate!

:D

1) As Far As I Know

2) If SQL calls its RaiseEvent, how can that get communicated to your Client app? I'm not saying it can't I'm just saying I have no clue how that would work. I am not sure you can translate a SQL event (Trigger) into a VB event. Maybe this should be the subject of a new thread in the Database Section.

3) LOL Packers RULE!

[RobDog888]

Thanks. I agree that maybe this new subject should be a new
thread in the databases forum.

Direct quote from nfl.com front page story
Two costly interceptions hurt Brett Favre and the Packers last
week in a surprising loss to the Bears, a team Green Bay had
dominated in recent years.They got beat by the BEARS! The Bears are one of the worst
teams in the NFL and your Packers lost to them IN Green Bay.

:lol:


Just giving you a hard time!

[Dave Sell]

Originally posted by RobDog888
the BEARS! The Bears are one of the worst
teams in the NFL and your Packers lost to them IN Green Bay.
:lol:


Hey, we were getting dead sick of always beating them... They lost to us the last 7 games in a row!

[Calibra]

Originally posted by RobDog888
Second: If I use a trigger on the table in question, how do I get
the trigger to relate to a vb event? Oh wait there is the
RaiseEvent in SQL. In the triggers (ADD, UPDATE, and DELETE) I
can raise a custom event telling my app that there was a change
to a recordset. But then what if it comes from another instance of
the app?
:D

Actually triggers aren't meant for that, but it's is possible, cuz it's possible to 'enhance' SQL server with new commands (thru custom DLL's).
But this is an big security risk and not really advised to do.

But why would you want realtime updates of changing data?

And finally, if you use such an kind of warning and the clients will an mass start updating the data when an event , youre SQL server and Network aren't gonna like that.

Triggers are meant to keep youre database integrety good, not for noticing clients.

[Dave Sell]

Originally posted by Calibra

But why would you want realtime updates of changing data?


He said it was because, when any user updates a certain table, he wants that change to reflect in a ComboBox on a Form.

As from Calibra's comments describe, you will not be successful doing this. Perhaps you could re-evaluate the behaviour of your entire system and refactor that aspect out of it?

I would spend 20 hours refactoring before I'd spend 10 hours going down the "wrong" path, Robdog.

[Calibra]

Originally posted by Dave Sell
He said it was because, when any user updates a certain table, he wants that change to reflect in a ComboBox on a Form.


Then you could use an other trick, how about an Com component on the SQL server that monitors the table and fires an event to the clients when the data changes.

This could be realised by using the event/source sink technology

[RobDog888]

Could you elaborate on this?

Also, Its not really a real-time program but rather I just want to
know when populating the form (not Form_Load) if I need to
requery the recordset to obtain the latest data.

Thanks.

[Calibra]

They are called connection points and it allows COM objects to fire events to the client or whatever you want.
Coulcn't remember the name (SHAME), had to get my MOC book on COM development out of the bookcase (shame deeply :( )

read these :
COM tech overview

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncomser/html/complus4vb.asp

More on connection points

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnguion/html/drgui082399.asp


don't know if it's possible in VB (never had to use it ther, actually i have never written an COM in VB, just in C++ :-( )

[RobDog888]

Thanks for the links. I will read up on them. Don't worry I havent
written any COM either. How about when to know when
populating a form to know if I should requery the db to refresh
the rs for the latest recordset info?

[Calibra]

Originally posted by RobDog888
Thanks for the links. I will read up on them. Don't worry I havent
written any COM either. How about when to know when
populating a form to know if I should requery the db to refresh
the rs for the latest recordset info?

that's an easy quistion, but it has an hard answer meaning:
The more realtime critical youre app, the more resources it wil eat to keep data up to date.
so you have to make decisions about how time critical your app is going to be, some examples :

An Stock apllication (for shares/stock etc etc) is really time critical, and every millisec of delay could cost money, so you'll want cont. updates.
This wil eat youre network bandwith/CPU power etc etc

But an app that's used for sales, meaning an salesman is selling an product an when an customer wants to buy X products, the sales person can ask how many products are still in stock and he could answer the customer, these are not so time critical.

So first you have to decide how (real)time critical youre app is, and then you hasve to decide how time critical youre data for youre combobox is.

And depending on youre needs you should pick an tech.

[RobDog888]

Its a Client/Project/Service Call application that our company wrote
for our own use. It is of medium importance to have all current
service calls on a form when the form is brought up for display.
Either I keep the recordset as is and populate the form as usual
(or just show the form if its already been populated) or requery
the db to get any new service calls and re-populate the form.

This is the part I am having the most trouble with because if I
repopulate the rs and the form every time it slows the program
down to an unacceptable rate.

Thanks.

[Calibra]

which tech do you use to connect to the DB?

[RobDog888]

ADO

[Calibra]

If youre using ADO, why not execute an sProc that checks if the tabel has changed on the server thru the ado connection, and if it returns that it has changed, then reget the Recordset.

[RobDog888]

It looks like making a separate call to the db is the only way then? :(

[Calibra]

Originally posted by RobDog888
It looks like making a separate call to the db is the only way then? :(

it's the most easy way indeed, there is an way thru implementing an external command in SQL, which could be called by an trigger, but due to security risks this feature has been disabled by MS and MS doesn't advice to use it.

I might suggest using Disconnected recordset, and polling the database with an sProc to see if there have been changes.

If you want to do this just to prevent, 2 users making changes to 1 record, this is no problem, cuz SQL keeps track of who changed first and will allert the second user if the data has been changed before he changed it.

[RobDog888]

I think I am going to use a separate call to the db but not 100%
sure how to determine if there have been chages.

[shunt]

Database connections:
You are basically trying to rewrite ADO. If you are using SQL Server only, then just use ADO.

There is no other generic way to handle your database. I have tried to write that over and over again and came to the conclusion that ADO is the GENERIC DB ACCESS! And that is exactly what it is! As for the ADO connection object, it is an Adapter! Why do you think you have to use a connection string with "Provider=????". Its telling the ADO connection object to use that providers connection implementation. But as a consumer, you are protected from that.

The way you should really be doing this is by creating an ActiveX DLL, and using this as your applications data service. Note! This is your APPLICATION's service layer, not a generic do it all service layer. There are ways that you can create generic helper classes, but at the end of the day, the data service layer should know how to load, save and find all the objects for your application only. Your EXE should not deal with the database in any way, shape or form. Not even for login credentials. This way you will be able to write classes to load and save your objects from any database, any database scheme, any file or where ever you choose without your application having to deal with those problems! Even better, you will be able to swap these out at any time without making a single change to the client! Investigate the Adapter pattern!

Concurrency:
This is always a problem, even in a local EXE. I found a realy nice article on this subject!
Search for "Dealing with Concurrency: Designing Interaction Between Services and Thier Agents" on MSDN.

When you start talking about updating forms when objects are added, you start looking at Orb's. Its the only way you can manage what objects exist and message clients of changes. Another good thing to look at here is the Observer pattern.

You cannot use a database trigger to message the vb app. The best way is to get the orb to message the clients when a new object is saved. There are many ways to handle concurrency. I would suggest that you keep it to a "refresh" button for now and just check concurrency when you save objects. Otherwise it just gets too complex and you will never get anything done.

ActiveX:
The application will not run any faster by using activeX components alone. In actual fact, it will run slower if its just for 1 or 2 users. The benefit will only be seen if you make an ActiveX DLL hosted on a server for access by more than 2 users and making use of COM+. By the way, return primitive datatypes from the DLL! Do not return objects! Returning objects will result in your application marshalling calls to and from the server and that will KILL your network! Translation: do not return the recordset directly from the dll, rather save the recordset as xml, then send the xml text over the network and load a recordset from the xml on the other side.

Stored Procedures:
The stored procedures will give you marginal performance bonuses. The server caches all sql statements and execution paths in any case. The only reason you should use sp's is for security reasons. You MUST however use parametarized queries. If you dont do this, your servers cache will become flooded with statements that look similar, but arent actually the same.

Conclusion:
Write a proper data service for your application. No short cuts! The benefit will be that your application will be structured better, it will be able to be distributed, it will be able to be upgraded to a web service providing data to both client exe's and web browsers, and just for you... will hopefully be faster!

[Dave Sell]

Shunt,

very well-put and insightful. What do you think about creating a design pattern that encapsulates an ADO Recordset object in combination with a cross application communications layer. Let's call it an N-ADO Object (N for notification).

ie..

All applications instantiate an NADO object. The NADO object instantiates and exposes a normal recordset object and also instantiates a server-type Notification object, running on the server as a service (using GetObject).

All the clients in the model which mutate the database would be required to do so via thier own "handle" to the server object. In turn, the server object would throw an Event all NADO clients could catch.

In the Form which has a ComboBox which needs current data from the database, that Form could have its own NADO listener, and catch its Events.

When the service-type ActiveX EXE server receives notification that some random client mutates the database, it Raises an event, which could be caught by the Form in question.

When the Form catches this Event, it could re-query the database and re-populate the ComboBox.

Have you tried such a scenario? Have I made myself clear? Does it sound too fragile to be considered a robust solution?

[shunt]

Creating a design pattern:
Firstly, design patterns are methods of solving particular problems. They are not technology dependant, so it would not be a design pattern in any case. I have seen a name for this type of thing before, just cant remember it right now.

It is pointless trying to encapsulate ADO. Ask yourself: Why do I want to encapsulate ADO?
Actually tell me why you want to encapsulate ADO and I will convince you not to. ADO is the perfect adapter for database access... If anyone could make it any better, M$ would have payed them out ages ago. USE ADO, AS IS!

Your solution:
Dave, you are close. I think you are still exposing too much to your application. Remember, you do not want object references passed across your network. So, you will not want to make the recordset object available outside your data service. The only thing you want to make available is the data itself. In any case, to encapsulate the data access correctly, you should not be exposing a recordset object.

My Solution:
Have a look at the .NET framework and the remoting infrastructure. The .NET framework will serialize the dataset (basically convert it to an xml string) and send it accross the network (send the string). So, I decided to learn from this and send all my data objects in the same way. Serialize them, send them, deserialize on the other side. This works like magic! Performance hit? Who cares the ease in loading, saving and searching for objects far outweighs the negligible performance hit.

[Dave Sell]

Originally posted by shunt
Creating a design pattern:
Firstly, design patterns are methods of solving particular problems. They are not technology dependant, so it would not be a design pattern in any case. I have seen a name for this type of thing before, just cant remember it right now.

I think its an Observer.

It is pointless trying to encapsulate ADO. Ask yourself: Why do I want to encapsulate ADO?
Actually tell me why you want to encapsulate ADO and I will convince you not to. ADO is the perfect adapter for database access... If anyone could make it any better, M$ would have payed them out ages ago. USE ADO, AS IS!

I was being considerate to the consumers of an NADO object. Rather than saddle a NADO consumer with 2 objects (a Notifier/Observer and ADO), you could create the NADO to epsose the identical ADO interface (so the consumer believes he is using ADO), plus "hide" a notification layer inside of it. That way, a NADO consumer would "not care" about dealing with sending notifications - it would just use NADO as it would use ADO.

Your solution:
Dave, you are close. I think you are still exposing too much to your application. Remember, you do not want object references passed across your network. So, you will not want to make the recordset object available outside your data service. The only thing you want to make available is the data itself. In any case, to encapsulate the data access correctly, you should not be exposing a recordset object.

Yes, I agree, but this is an advanced topic to the point where no one thinks like this. This may be the "Holy Grail" of VB6 and I'm not sure we're ready for that yet ;)

My Solution:
Have a look at the .NET framework and the remoting infrastructure. The .NET framework will serialize the dataset (basically convert it to an xml string) and send it accross the network (send the string). So, I decided to learn from this and send all my data objects in the same way. Serialize them, send them, deserialize on the other side. This works like magic! Performance hit? Who cares the ease in loading, saving and searching for objects far outweighs the negligible performance hit.
You mean to use ADO's built-in Method to serialize itself in the form of an XML file, then send that file across application space, then reload that file?

[shunt]

Your last comment is about right.

[Dave Sell]

Check this out shunt:

http://www.vbforums.com/showthread.php?s=&threadid=307794

[RobDog888]

I see you guys have been busy since I was last here.

So, then to solve one on my apps problems, slow data retrieval,
what would be the solution? Create a specific ActiveX dll for
connecting to and executing sp's or adhoc queries. I see Shunt
suggests to send the data as xml across the network. Sounds
interesting, but I want to keep on the simplest level possible for
now.

GUI <-> Business Rules <-> Data Service

With the data service dll using ADO as is, I would have a
standard module for starting point. Then it would create a top
level class which would create a few more classes at that level.
Maybe at the top level class it would have the connection object
as a property.

Then when the data class is destroyed it would cascade down
destroying all the sub level classes ensuring that there is proper
cleanup.

Comments anyone?

[shunt]

Rob Dog...

I think the idea should be that you NEVER expose the connection object. The connection should be controlled by the data service. There should be nothing that is able to get a reference to that connection from outside the dll. This could also be a security threat if you expose your connection object.

Also, the code required to send xml accross the network is minimal... The recordset object actually has a method to save to xml and open from xml.

The thing to remember here is, you will not get better performance for a single user. You will only see the benefits with more users and as long as the dll is installed on a server and running within COM+ with appropriate settings. Basically, you will only realise the performance benefit when all the clients are connecting to the same instance of the component.

If higher DB performance is what youre looking at, rather check your queries. Make sure your queries are using the appropriate indexes. Also, make sure you are using parameters in your calls to the DB. This will make a huge affect on your db performance.

Last thing... GUI <-> Business Rules <-> Data Service ... I think is 100%!

[Dave Sell]

Originally posted by shunt
Rob Dog...
Basically, you will only realise the performance benefit when all the clients are connecting to the same instance of the component.


I was under the impression that this technology is not available when working in VB6.

Each Client app would have to use GetObject to instantiate the server object.

This would imply the server object (ActiveX EXE) is running as a service and loaded into the ROT.

VB6 does not have the means to create an ActiveX EXE service.

Do you know something I don't know?

[RobDog888]

Guys, basically I am preparing to re-write our entire program
because its soo bad. I'm tired of placing bandaids on top of
bandaids. This is also my first real attempt at multi-tier. I assumed
that starting at the db tier is the place to start.

DB Dll:
Everyone will use the same object with GetObject, then how is it
to be instanciated if someone is the first to try to connect?

Or, just have a small exe to create the object first?

In this dll I should have the code to execute sp's and addhoc
queries. The sp's will be parameterized for the exec procedure
and the adhoc queries will be parameters for another procedure
to invoke.

What else should or will the data tier be required to do or need?

Thanks.

[Dave Sell]

Originally posted by RobDog888

DB Dll:
Everyone will use the same object with GetObject, then how is it
to be instanciated if someone is the first to try to connect?


Here is my point from above. In order for a VB6 client to instantiate an Object via GetObject, that Object must be compiled from a C++ project (using the ATL library).

From how I understand VB6, No VB6 project can be compiled into a binary object that can then be instantiated via the GetObject directive.

I have a book that claims to have a way around this, but I am still looking into it.

[RobDog888]

So how about my other questions?

Also, if everyone is creating a new data tier object then there is no
performance benefits?

[Dave Sell]

Originally posted by RobDog888
Also, if everyone is creating a new data tier object then there is no
performance benefits?

I hate double-negatives, but to answer this question, no.

In other words, yes, there will be a performance increase from making a server-side object to be an adapter between your client tier and the server tier, if:

You do not simply toss ADO Recordset Objects around your network. Rather, as shunt is suggesting, the client receives an XML representation of a Recordset (created on the server), and then the client can transform that XML representation back to an ADO Recordset after it has received it.

Will all this perform even better if there is only 1 object doing all this server-side work instead of 1 for each client?

Maybe. But I think the lion's share of your optimization should come from XML transformations. Let me know if I've made myself crystal clear. If not, I'd be happy to go into more detail.

[RobDog888]

Thanks Dave. Clear as the smog filled air here in LA on a good day. ;)

I will try creating the dll then and let you know how it goes.

Thanks.

[Dave Sell]

Cool, please keep us updated.

Is this DLL going to be a DCOM object?

Ie.. That would imply that the client apps are on different machines than the server hosting the DLL. Just asking. It shouldn't change anything we've discussed so far.

[RobDog888]

I dont know that much at all about DCOM, but the client exe would
need to be on another server network share separate from the
database server because we willl have users connecting to our
Terminal Server to run the program as well as local network users.

[Dave Sell]

Originally posted by RobDog888
I dont know that much at all about DCOM, but the client exe would
need to be on another server network share separate from the
database server because we willl have users connecting to our
Terminal Server to run the program as well as local network users.

At this point, a better description of your topology is in order.

If the client apps are being executed on a different machine than the server, then you have a case where DCOM is required.

If, however, the clients connect to the server where the database is running, via Terminal Services, then the client apps are actually being run on the same machine as the server. There will be no need for DCOM in this case.

Do you know which is the case?

[RobDog888]

Basic layout:

Database server - DB + proposed data service dll.
Application server - location of executable.
Terminal Server - some remote user will run shortcut to App Server from here.
Local Network Workstations - most users will run program internally from local shortcut to App Server..

:confused:

[Dave Sell]

Originally posted by RobDog888
Basic layout:

Database server - DB + proposed data service dll.
Application server - location of executable.
Terminal Server - some remote user will run shortcut to App Server from here.
:confused:

Are these 3 seperate machines? I guess that wasnt clear.

[RobDog888]

Yes, three separate physical server machines.

Application Server = Windows Server 2003
Terminal Server = Windows 2000 Terminal Server
Database Server = Windows 2000 Server

:D

[shunt]

Sorry guys... Was out job hunting! :(

Anyways...
You do not have to use GetObject to connect to a single instance of a component or class. You can create a standard dll, host it in COM+, and set COM+ accordingly. This will make sure that only one instance of your component is created regardless of the number of clients connecting to it. You will need to make sure that your exposed classes are stateless however... Otherwise you are going to encounter some nasty bugs. But that should be the case with your data services in any case. In effect, COM+ will act as a ActiveX EXE that wraps the ActiveX DLL, turning it into a fully fledged ActiveX Server!

There are also programming techniques in which you can return a single instance of a class to all clients. Investigate the singleton pattern for this one.

[Dave Sell]

Originally posted by shunt
Sorry guys... Was out job hunting! :(

Anyways...
You do not have to use GetObject to connect to a single instance of a component or class. You can create a standard dll, host it in COM+, and set COM+ accordingly. This will make sure that only one instance of your component is created regardless of the number of clients connecting to it. You will need to make sure that your exposed classes are stateless however... Otherwise you are going to encounter some nasty bugs. But that should be the case with your data services in any case. In effect, COM+ will act as a ActiveX EXE that wraps the ActiveX DLL, turning it into a fully fledged ActiveX Server!

There are also programming techniques in which you can return a single instance of a class to all clients. Investigate the singleton pattern for this one.

Well, I dunno about RobDog, but this would be very useful to know how to do. I can make my own ActiveX DLL. Would you consider making a short tutorial on how to connect 2 clients to a remote COM+ DLL?

I would do some of the work if I could. It should be its own new thread in the DCOM section.

[Dave Sell]

Originally posted by RobDog888
Basic layout:

Database server - DB + proposed data service dll.
Application server - location of executable.
Terminal Server - some remote user will run shortcut to App Server from here.
Local Network Workstations - most users will run program internally from local shortcut to App Server..

:confused:

You have a case where multiple clients connect to the Terminal server. Presumably they will all run their client app from that machine.

The amount of data traffic between the Term server (TS) and the App Server (AS) should be minimized. The best way to accomplish this would be to have the client app be totally thin. The thinnest app I can think of is a web browser like IE, or maybe a little heavier - a VB app with an Internet webbrowser COM control.

Then your application server could be an ASP app running in IIS. Since ASP can instantiate COM objects, you have a link from ASP back to the COM+ idea.

There should then be a pair of COM+ Objects - call them rsSender and rsReceiver. rsReceiver would reside with the web server in the middle and ask the rsSender for a Recordset. rsSender lives on the same machine as the database and queries it for an ADO recordset, but stops short of sending the ADO object across the network to the rsReceiver.

Instead, it serializes the Recordset into an XML file (1 line of code) and sends that file across the network to the rsReceiver.

The rsReceiver then transforms the XML file back into an ADO Recordset and give that back to your ASP application running on IIS.

ASP is more than happy to work with ADO Recordsets. Then it sends either HTML or XHTML (your choice) back to each thin client.

So your NW traffic is like this:

Many Clients connect to TS. Many thin apps on TS connect to web server (App server). App server makes many COM objects. Many COM objects talk to single COM+ rsReceiver object on same machine. rsReceiver object (on app machine) talks to single COM+ rsSender object on DB server.

[RobDog888]

Orininally posted by Dave Sell
Presumably they will all run their client app from that machine.Terminal Server users would run a shortcut to the app residing
on the Application Server.

Also, we have another Windows 2003 server, which is our PDC
and Web server, running IIS so I doubt it that the owners would
allow me to install IIS on the Application Server. :(

I really appreciate all the time and effort by you guys to help me
understand and figure out how this app should be
designed. :thumb:

I understand the rsSender and rsReceiver transmitting the xml
string thing and it sounds like a good idea. We are currently
passing the entire ado recordsets. They are being populated by
passing the SQL string as the rs source when its opened. Allot of
network traffic.

I am a beginner at this (n-tier) so please bear with me.

How should I create the Data Service dll? Props, Methods, etc.

Thanks.

[Dave Sell]

Originally posted by RobDog888
Terminal Server users would run a shortcut to the app residing
on the Application Server.


This is no different than running the app from the same machine. This renders your app server useless.

Lets say you and I are on a LAN. You have 2 apps. 1 is on your disk and 1 is on my disk.

Lets say you run your app. Your CPU loads it off disk and plugs in into the execution stack of your CPU. You use your CPU and your RAM.

Lets say you run my app via a shortcut. Your CPU loads it off the network and plugs in into the execution stack of your CPU. You use your CPU and your RAM.

Same thing. Your App Server is nothing more than a File Server.. This is fine, but do you really need another machine for that? This is what I meant when I said the client app should reside on the Terminal Server.

[Dave Sell]

Originally posted by RobDog888
Also, we have another Windows 2003 server, which is our PDC
and Web server, running IIS so I doubt it that the owners would
allow me to install IIS on the Application Server. :(


2 IIS servers can reside peacefully on the same LAN. Your main IIS can listen to port 80. You could set up another IIS to be your App server and listen to port 8080 or whatever.

You will need installation rights to any web server where you are developing resident COM objects like we are proposing.

[Dave Sell]

Originally posted by RobDog888

I really appreciate all the time and effort by you guys to help me
understand and figure out how this app should be
designed. :thumb:


I'm doing this as much for me as for you! This is solid gold!

[Dave Sell]

Originally posted by RobDog888

How should I create the Data Service dll? Props, Methods, etc.


I think that may depend on how you choose the rest of the suggestions above, but in my opinion, you can consider the rsSender to be half of it, and stored procedures on the RDBMS to be the other half. Since they both reside on the same machine, they are considered in the same tier.

I'll give more thought to the kinds of methods that would be helpful, but alot of that may hinge on the App server.

[RobDog888]

IIS is still no possibility of installing it on the App server because
the app server also runs our Exchange mail.

I have Global Admin rights on the network. So no worries about
that.

[RobDog888]

Originally posted by Dave Sell
This is no different than running the app from the same machine. This renders your app server useless.

Lets say you and I are on a LAN. You have 2 apps. 1 is on your disk and 1 is on my disk.

Lets say you run your app. Your CPU loads it off disk and plugs in into the execution stack of your CPU. You use your CPU and your RAM.

Lets say you run my app via a shortcut. Your CPU loads it off the network and plugs in into the execution stack of your CPU. You use your CPU and your RAM.

Same thing. Your App Server is nothing more than a File Server.. This is fine, but do you really need another machine for that? This is what I meant when I said the client app should reside on the Terminal Server. Makes allot of sense now. If I can talk the Network Admin into
this then I will move the client exe to the Terminal Server.

[Dave Sell]

Originally posted by RobDog888
Makes allot of sense now. If I can talk the Network Admin into
this then I will move the client exe to the Terminal Server.

You will more likely win your argument if you stop calling it an App server and call it what it really is planned to be, a File Server. $0.02

[RobDog888]

So the Data Service dll would have hidden methods for
connecting to the database. A generic procedure to Execute the
stored procedures and return the rsSender xml strings.

I am a little confused about the connecting part. If we want to
make the most of this, then we want only one object and have
clients using the Getobject method? So the first step is to set this
up?

[RobDog888]

Ok,
Application Server = File Server.
Application Server = File Server.
Application Server = File Server.
'...
'...
'...
Got it now.

:D

[Dave Sell]

Originally posted by RobDog888
So the Data Service dll would have hidden methods for
connecting to the database. A generic procedure to Execute the
stored procedures and return the rsSender xml strings.

I am a little confused about the connecting part. If we want to
make the most of this, then we want only one object and have
clients using the Getobject method? So the first step is to set this
up?

I believe the first step is to decide on your topology. These decisions should not be made until you know the thin-ness of the client app. It sounds like your are not leaning towards the web-browser-type thin client?

[RobDog888]

Correct. The owners dont like web browser apps.
Neither does the Network Admin.

[RobDog888]

Would there be a benefit of writting this in .NET? I dont know if there
will be problems with distributing the framework. Could be a big
issue. Would be nice since I'm going to all this trouble of re-writting
it.

[Dave Sell]

Originally posted by RobDog888
Would there be a benefit of writting this in .NET? I dont know if there
will be problems with distributing the framework. Could be a big
issue. Would be nice since I'm going to all this trouble of re-writting
it.

Personally I am sticking with VB6 for now (I am in an almost identical situation as you). I could go .NET for all this inter-process, inter-machine communications, but I already know how to do everything in VB6 and I don't understand a lick of VB.NET/ASP.NET.

These are very advanced programming techniques, and I daresay the crux of the language.

How long did it take you to get to this point in skills in VB6?

How long will it take to master VB.NET to do the same stuff?

I am using VB.NET now for easy stuff like GUIs and crap. But for all this domain design complexity, I'll stick with VB6, but that doesn't mean anyone else has to.

As far as your question, I don't see .NET bringing anything extra to the table that can't get done in VB6 in the same amount of time with the same resources. The only benefit is you could spare your future the drudgery of supporting a legacy app in VB6...

[Dave Sell]

Originally posted by RobDog888
I want to write a dll for connecting to a SQL server so I can
re-use it on my programs. I think its correct to do it this way for
better performance. Right now I have eveything in the exe. How
can I get started making it so its generic for any server or db?

Also, would running an exe from a network share be detrimental
to performance because of all the network traffic? Better to install
the exe on every pc locally?

Thanks for any help.

Here is your original post, with your original intent. You clearly state you have everything in the EXE. This is the very definition of a Thick client (as opposed to a Thin client).

Yet you just got done saying that your bosses or whatever won't let you make a thin client.

They can't have it both ways. Either you go thin client, and spare yourself a ton of pain, or go thick, and you will basically be left with exactly what you got right now. A big ugly mess. Sorry to sound crude, but that's the way I see it.

[RobDog888]

I'll stick with VB6 for this re-write.

They just dont want a web browser based program for this. We
cant have it a thin client exe without going to browser based?

:(

[Dave Sell]

Originally posted by RobDog888
I'll stick with VB6 for this re-write.

They just dont want a web browser based program for this. We
cant have it a thin client exe without going to browser based?

:(

Of course you can, but then you end up writing an app very much like a web-browser and a server very much like a web-server.

Do you really want to re-write a client-type-browser and also a server?

Do ya? huh? huh? :confused:

[RobDog888]

:confused: Do I?

[Dave Sell]

Originally posted by RobDog888
:confused: Do I?

I sure the hell don't.

[Dave Sell]

I'll say this. Why don't you do the whole thing with the thin web client (IE) and "real" app server (IIS) and use it as a kind of prototype.

Once everything is up and running and working you can give it to your bosses and let them evaluate it.

If they still hate it, at that point you could re-write the thin client and the web server. Let it be their choice (to spend the extra time on it). You never know, they might just take what you give them and love it. I sincerely believe you will double your design time if you don't go thin client.

[RobDog888]

I always thought that to do n-tier you didn't need to write a browser
based app?

:confused:

[Dave Sell]

Originally posted by RobDog888
I always thought that to do n-tier you didn't need to write a browser based app?
:confused:

You don't need to write one if you use one that's already made, like netscape or IE. But, if you don't use one that's already made, then you do have to write your own thin client (as well as a corresponding server).

Of course, that client does not have to be an HTML interpreter (web browser), but it will behave in very much the same way regardless how it is written.

As soon as you start placing intelligence into a thin client, you no longer have a thin client, but instead have a thick client which defeats the whole purpose of n-tier design.

[RobDog888]

Ok, now I'm completely :confused:

[Dave Sell]

Ok, now I'm completely :confused:
Originally posted by RobDog888
I always thought that to do n-tier you didn't need to write a browser based app?

:confused:

OK, then sorry to not speak so clearly. Let me re-phrase. The above assumption is correct.

But... You 1 need to write something as the thin-client front end. You will also have to 2 write some kind of server (service) that talks to and controls that thin client. You also have to write 3 an application-type intelligence to control that server.

The thing I'm trying to impart to you, is: if you 1 use a browser as your thin client, then you don't have to write it. Nor do you have to write 2 the server - use IIS. All you have to write is 3 the intelligence to control the server (like an ASP application).

Crap I think I just said the same exact thing...:blush:

[shunt]

I dont agree with Dave on his views about using the Web Browser as a thin client. You are still going to have to write client side logic to control the web page interaction and server side logic to control the data access. It just makes things more difficult because you have to write everything as a stateless application, meaning you will most likely have to get rid of your business logic component and try and write this stateless... Also, you must remember that web pages are not type safe and are more difficult to debug. In fact, every aspect about writing the applications for a web browser is much more difficult.

I wrote a production planning system that used a web browser as a front end. Trust me! It is much harder! And a real mission to maintain!

Writing the app in VB6, you will not have to re-write a web server nor a web browser... Not even close. Those things only have the plumming work for server-browser communication, not the data transfer, business rules etc. that you require. In any case, you will have DCOM and COM+ that will handle your communication plumming! Thats so much better!

In terms of the tutorial, I'll get started with a simple one right now.

I think you should stick with your client->business logic->data services idea!

[shunt]

Here is my view on your topology.

[Dave Sell]

Originally posted by shunt
Here is my view on your topology.

How did you make those diagrams?

[shunt]

I drew them... with a program...
Why?

[Dave Sell]

Originally posted by shunt
I drew them... with a program...
Why?

Well I want to make a rebuttle, but I just can't do it with words anymore. I'd like to make some pictoral diagrams like you made. What program did you use?

[shunt]

:)

I used Visio. You can use paint if you like. I think it would be good to see some diagrams out here. I think its something that is not used enough on VB forums.

In any case, I would like to see a) how other people draw the diagrams, b) how other people model the applications.

Please, state your case! I am eager to see the knowledge and views of others out there! (0 sarcasm!)

[Dave Sell]

Well, I have Visio at home, so I will install it tonight, but I won't get around to making a full diagram of my ideas until next week most likely...

I understand your disagreement, and you make valid points. But to say its more difficult to make web-controlled apps as opposed to VB6 apps deserves debate.

I agree if: the front end is unreasonably complicated.

I disagree if: the front end is elegantly simple.

I guess that will all depend on the requirements of your customer... They get the ultimate decision in the complexity of their GUI.

It is that decision that can have reprecussions throughout the rest of the entire design.

It is totally true that a web GUI is stateless. But I contend that this is a good thing. Keeping track of state creates more complexity.

[RobDog888]

It only works for the view size combo box. It looks like there
should be a navigator or some other kind of animation? Maybe its
not working on my system?

[Dave Sell]

Originally posted by RobDog888
It only works for the view size combo box. It looks like there
should be a navigator or some other kind of animation? Maybe its
not working on my system?

What?

[RobDog888]

From shunt's example. If you look in the support folder you will see
what appears to be rollover images and some other stuff.

[Dave Sell]

Ya I saw alot of JS but nothing really happenned. All I did was look at the main images. I wasn't sure if there was something wrong with my system too.

[RobDog888]

Ok, I like the design but I wanted to know about the users that will
just be running the app from their workstation (internal use). Will
they be taking a performance hit any different than what it was
originally? They will not want to run the program internally
through Terminal Server.

http://www.vbforums.com/attachment.php?s=&amp;postid=1812793

:)

[shunt]

Question: Why not run the internal users on Terminal Server too?

Anyway, the terminal server in the diagram can be a workstation too. To an end user, the terminal server is a workstation.

In my opinion:
Your business objects should be statefull, providing your rules as to how objects relate, data validation etc. If you use web pages for your front end, you will not be able to use objects and classes. You will have to use Java applets or ActiveX components to accomplish that, and then...you may as well be coding a vb front end. You can try and write objects using javascript...it can be done to an extent, but it is such a mission! I've done that too. I also found that coding the rules of an object into each page of the site was also a real headache! Even if it was a copy/paste affair. One little change takes you hours upon hours to code, debug and test... That equates to $$$$$$$$$$$$!

Your data services component should be stateless, providing access to the data of the system.

On the other hand:
What does your application do? If it is a simple read data, display to user, let user edit data, save data; with very simple business rules and/or data rules, then sure...a web application will do it. In fact, chuck all design out the window and use recordsets in the asp pages to do everything. Also databind the controls to the recordset. Using Visual Studio 6 with its RAD tools, you will have the application banged out in no time!

[Dave Sell]

If you use web pages for your front end, you will not be able to use objects and classes. You will have to use Java applets or ActiveX components to accomplish that, and then...you may as well be coding a vb front end.

No. You can make dynamically generated HTML "forms" without anything special running on the client (javascript, java applet, or client-side ActiveX control).

The pages are generated via code running on the server very easily. Given the simplicity of the GUI, server-side HTML generation can be much much easier than a client-side VB app.

Granted, the GUI will have limits.

[Dave Sell]

Oh, I forgot - yes, you can make Classes and Objects and use them in your web app. That's the real saving grace of crappy ASP 3.0 - the fact that it makes the server infinately extensible through the use of your own custom classes.

I'm talking about ASP code like:


Set myObject = Server.CreateObject("myProject.myClassThatCanDoAnything")

[RobDog888]

Our program manages client, project, task, and employee information.
So yes, there will be a bit more transactions going on than
searches.

[shunt]

I know you can use server.createobject, but that is server side. Your client application is not going to be able to use that object. Basically, while the user is viewing and interacting with the web page, you will not be able to use objects, unless they are java applets or vb activex's. Basically, your user is chained to simple request data, edit data, submit data.

From RobDog's description here, it looks like thats what they will be doing in any case. If there is no real process for the application then just use recordsets with data bound controls. Its a real simple solution. But if you want to do a really elegant solution, you are seriously going to battle with pure HTML/ASP!

[shunt]

Here is a nice article I think you guys would be interested in:
http://msdn.microsoft.com/architecture/default.aspx?pull=/library/en-us/dnbda/html/CRUD-afford.asp

[Dave Sell]

Originally posted by shunt
I know you can use server.createobject, but that is server side. Your client application is not going to be able to use that object. Basically, while the user is viewing and interacting with the web page, you will not be able to use objects, unless they are java applets or vb activex's. Basically, your user is chained to simple request data, edit data, submit data.


Agreed. That is the nature of a truly thin client. It not a glamorous GUI.

[RobDog888]

I read the CRUD article and maybe I'm tired but I didn't quite take
it all in. I think I'm getting lost. I need a client/server app design
that will be scallable for a dozen users or so with decent
performance. I would like to stay away from a browser based app
simple because we have had too many issues with them from
before.

Thanks.

[shunt]

Basically, the article is saying that you dont often have pure CRUD operations in the application. So, reading and writing recordsets is actually not good enough. You need to define the operations of the application and implement your database interaction behind that.

I need a client/server app design
that will be scallable for a dozen users or so with decent
performance.

I feel that my topology, you would be able to accomplish this. I think you will get the most performance using a single instance server component with connection pooling, parameterised sql, good database design with appropriate indexes.

[Dave Sell]

Originally posted by RobDog888
I read the CRUD article and maybe I'm tired but I didn't quite take
it all in. I think I'm getting lost. I need a client/server app design
that will be scallable for a dozen users or so with decent
performance. I would like to stay away from a browser based app
simple because we have had too many issues with them from
before.
Thanks.

Try a DCOM Singleton:
http://www.vbforums.com/showthread.php?s=&threadid=310205

[shunt]

Note: The singleton pattern solves a very different problem to the one discussed in the CRUD article. But I suppose it is very relevant cos it is most likely your service layer to the db...

[Dave Sell]

With the Observer-Broadcaster-Singleton pattern implementation I have been discussing, any client "hooked" into the Event chain can raise an event that an update has been made. All clients will get the message as a Raised Event.

In that scenario, your clients could then re-load the combo-box only when exactly necessary.

[jhermiz]

I cannot believe I read this entire thread.

All I have to say is ... WHAT THE HELL ARE YOU GUYS SMOKING ? :D :D :D

Ok whats so hard about this:

3 tiers:

Business Tier
Database Tier
Validation Tier

The client (exe) has no access to the db...so I can't understand how hard that it to understand? Your business logic simply accesses the db tier. Your db tier returns data back to the business tier which in turn returns it back to the exe. You probably don't need the validation tier, but you'd be amazed how helpful this can become if you have a lot of business logic rules.
The validation tier validates on your client application before sending anything to your business tier (hope that makes sense)

From a design perspective you are looking at:

[Client (EXE) ] ----> [Validate tier (check it before you even bother)]-----> [business logic tier (ok so the validation worked...now i need to perform some logic and send it to the server)] <----------------> [database tier (ill store this data and return back)]

db tier cannot touch exe, database tier has nothing to do with validation tier, db tier talks back to business logic tier vice versa. Business logic tier only gets validated but talks back to the client application via some records from methods within itself.

Rob, dont make this too difficult man, even if its your first time, draw it out on paper before you start getting too deep into it. Mind you, this is a LOT of code, however this design will pay off in the end when it comes time to maintenance.

Also Rob, the business logic dll should be very versatile. In fact you should be able to pass stored procedure names, etc when retrieving data from the database tier level. Your database doesnt have to worry about the client application at all.

In this entire scenerio you can expect that you have at least the following:

DB SERVER (SQL)
Client Machine (local host to run the application)
Application Server (this is even optional but you can register your validation and business logic tiers here).

Real simple, Woka's got some good examples, else I can write you a small employee type tier application some time soon. I've just been very busy lately.

Jon