Data Service Tier Dll

Quote:

---

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.

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.

which tech do you use to connect to the DB?

ADO

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.

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

Quote:

---

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.

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.

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!

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?

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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 ;)

Quote:

---

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?

Your last comment is about right.

Check this out shunt:

http://www.vbforums.com/showthread.p...hreadid=307794

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?

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%!

Quote:

---

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?

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.

Quote:

---

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.

So how about my other questions?

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

Quote:

---

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.

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.

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.

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.

Quote:

---

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?

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:

Quote:

---

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.

Yes, three separate physical server machines.

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

:D

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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!

Quote:

---

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.

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.

Quote:

---

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.

Quote:

---

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

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?