I need your opinion..Please

[sanon]

I develop a MTS component (DLL). It will retrieve data from several tables (at least 5 tables in each time). I'd like to know your opinion what is the best way to make a query to a database?

[demirc]

Wow that is a very broad question.

Are you getting your data from SQL server ?

For both servers remember to :

-create late / destroy early (your objects that is)
(create objecst and connections late and destroy them soon as done with them, that goes for your DLL too when called from the clients)

-Utilize connection pooling by using module level variables ( or objects if you must, avoid using objects where possible i.e. Connection object)

-use forward only , read only cursors where all possible

- avoid using more then one object when not need.

- Make use of stored procs (big plus here), using Command object, if you are expecting a return.

-Try to follow this order : Connection obj,command obj, recordset as a last resort

-Use disconnected recordsets when appropriate (i.e. you need to pull a small recordset, but it might sit on Joe's desktop from morning till lunch)

-If you are expecting lots of traffic, put the MTS and SQL on separate machines.

In short the best way I guess depends on the situation and you 'the programmer'. Personally my self, I used lots of stored procs and a single command object with the same connection string every single time.

That is all I can think of right now. I hope this is what you were looking for.

What the heck happened to the spell checker ?