Printable View
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.
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 ??
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?
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:
This forces your client to contain unneeded "baggage" - specifically the "way" to get the "type" of database.Code:(Client) --asksfor--> Factory.GiveMeAnObject("Databse specific crapola")
What you should be wanting is your client NOT to know the "way", but simply the type of database.
This is the whole problem with databases - they simply refuse to be completely encasulated inside a dumb (read generic) object.Code:(Client) --asksfor--> Factory.GiveMeAnMSSQLObject("less database crapola, like username - you cannot escape this crapola")
(Client) --asksfor--> Factory.GiveMeAnMYSQLObject("less database crapola")
There is unfortunately always some bagagge you will have to burden your client with.
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?
Are you making an in-process DLL, or an out-of-process EXE?
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?
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?
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.
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.
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.Quote:
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?
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++.
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!!!
True Story:Quote:
Originally posted by RobDog888
Its just common sense!!!
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.
Condsidering it is an old app, is it using Disconnected Recordsets ?, this often speeds things up !
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 ??
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.
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.
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?
Can an ADODB.Recordset raise an event when a record is added?Quote:
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?
I am thinking no, but you could make your own mechanism into your data-tier, however that will be quite expensive at run-time...
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.
This would require creating new database objects, namely Triggers. I dunno if there is a COM equivalent, I doubt it.Quote:
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?
http://msdn.microsoft.com/library/de..._ob_t_3nsk.asp
Exerpt:
Quote:
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.
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?
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.
Man, if this is really your best route then I feel like we are in the friggen stone age of computing :mad:
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?
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.Quote:
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]
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.
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.
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 KnowQuote:
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
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!
Thanks. I agree that maybe this new subject should be a new
thread in the databases forum.
They got beat by the BEARS! The Bears are one of the worstQuote:
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.
teams in the NFL and your Packers lost to them IN Green Bay.
:lol:
Just giving you a hard time!
Hey, we were getting dead sick of always beating them... They lost to us the last 7 games in a row!Quote:
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:
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).Quote:
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
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.
He said it was because, when any user updates a certain table, he wants that change to reflect in a ComboBox on a Form.Quote:
Originally posted by Calibra
But why would you want realtime updates of changing data?
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.
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.Quote:
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.
This could be realised by using the event/source sink technology
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.
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++ :-( )
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?