To Data Module or not Data Module, that is the question

Can anyone offer best practice advice please. Porting my access application to Delphi I have used a data module. It now has one Tdataconnection and  lots and lots of TDAOQueries and TDAOTables on it. Its now getting hard to see the wood for the trees. My question is twofold.

1) is is best to use a data module or just use the TDOQueries I need on each form?

2) is it best to use loads of TDAQueries, each with hard coded SQL or use just one or two with the sql string dynamically loaded just before I execute it.

With Qn 2 it is starting to look bettert to dynamically load the sql from a function that returns the sql as a string, as it is much clearer to see/generate the actual sql within the function, especially if parameters are involved. But thats just my opinion. what do the gurus do?
Howard
0
howard
12/22/2010 8:43:02 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

8 Replies
1319 Views

Similar Articles

[PageSpeed] 11

"howard manwaring" wrote in message news:315827@forums.embarcadero.com...
> Can anyone offer best practice advice please. Porting my access 
> application to Delphi I have used a data module. It now has one 
> Tdataconnection and  lots and lots of TDAOQueries and TDAOTables on it. 
> Its now getting hard to see the wood for the trees. My question is 
> twofold.
>
> 1) is is best to use a data module or just use the TDOQueries I need on 
> each form?

Definitely use datamodules. But you are not limited to a single datamodule. 
You can create as many as you need, so look at logically grouping the 
various query and table components on different datamodules. Datamodules can 
use other datamodules (so all can share the one with the main connection on 
it) and forms can use any number of datamodules.

> 2) is it best to use loads of TDAQueries, each with hard coded SQL or use 
> just one or two with the sql string dynamically loaded just before I 
> execute it.

This one is more of a personal preference and/or which seems to make sense 
for a given situation. There is no right or wrong answer here. One thing 
that is implicit with the latter choice is that you would not be able to use 
persistent field definitions, but I for one consider that a good thing, just 
use FieldByName to access the field values returned from a query.

-- 
Wayne Niddery (TeamB)
"Do not consider Collectivists as 'sincere but deluded idealists.' The 
proposal to enslave some men for the sake of others is not an ideal; 
brutality is not 'idealistic,' no matter what its purpose. Do not ever say 
that the desire to 'do good' by force is a good motive. Neither power-lust 
nor stupidity are good motives." -Ayn Rand"
0
Wayne
12/22/2010 10:22:58 PM
Thank you, I hadn't appreciated that you could have more than one data module. That would make it clearer to follow. 

If there is no performance issues I think I'll stick to dynamic sql. Although that raises the question of nested queries (subject of a different thread here)
Howard
0
howard
12/22/2010 10:32:11 PM
"howard manwaring" wrote in message news:315852@forums.embarcadero.com...
>
> If there is no performance issues I think I'll stick to dynamic sql. 
> Although that raises the question of nested queries (subject of a 
> different thread here)


Are you still connecting to Access, or to another database?

If you can switch to a real (SQL) database :) ... you could define your 
current subqueries as views or, if necessary, stored procedures, which can 
then be used in other queries.


-- 
Wayne Niddery (TeamB)
"Do not consider Collectivists as 'sincere but deluded idealists.' The 
proposal to enslave some men for the sake of others is not an ideal; 
brutality is not 'idealistic,' no matter what its purpose. Do not ever say 
that the desire to 'do good' by force is a good motive. Neither power-lust 
nor stupidity are good motives." -Ayn Rand"
0
Wayne
12/23/2010 12:23:27 AM
Hi,

Take a look how I handle the database using objects 
instead of hiding your queries in forms/datamodule components..

https://forums.codegear.com/thread.jspa?threadID=38359&tstart=240

greets.
0
Robert
12/23/2010 8:30:25 AM
> {quote:title=Wayne Niddery wrote:}{quote}

> Are you still connecting to Access, or to another database?

currently yes, as its a bit easier to try out queries first. The application at the moment is for a particular school where I used to work. However I want to switch to something else (mySQL?, MSServer?) for a version that can be used in many different schools. If nothing else I need to stop db bloat and have something that runs a bit faster. My issue here though is knowing which is the best/easiest db to install on a client's network that doesn't involve me with a day on site. I think all the clients will
 have MSSQL server but cannot guarantee I will have permissions to install databases, nor that any staff there will know how to give me permisions.
Howard
0
howard
12/23/2010 8:42:36 AM
> {quote:title=Robert Triest wrote:}{quote}
> Hi,
> 
> Take a look how I handle the database using objects 
> instead of hiding your queries in forms/datamodule components..
> 
> https://forums.codegear.com/thread.jspa?threadID=38359&tstart=240
> 
> greets.

That looks interesting, thank you, I shall study it further as that might ne the way I have to go.
Howard
0
howard
12/23/2010 8:47:36 AM
> 2) is it best to use loads of TDAQueries, each with hard coded SQL or use just one or two with the sql string dynamically loaded just before I execute it.
> 
> With Qn 2 it is starting to look bettert to dynamically load the sql from a function that returns the sql as a string, as it is much clearer to see/generate the actual sql within the function, especially if parameters are involved. But thats just my opinion. what do the gurus do?

Howard,

If there are a large number of queries we have evolved an approach whereby we place one of each TADO component on a form/data module and load the SQL dynamically. Having loads of components on a form/data module can end up being an administrative nightmare. Also, with some Delphi versions we have found that FieldByName works everytime, whereas the persistent component culture can be buggy (Delphi 2009, for example - our experience)

We store the SQL in a table within the db so that it can be both dynamically called to fill the TADOxxx.SQLText and also be finely tuned without having to alter the Delphi app.

Where you have nested queries it is possible to adapt this technique by running the queries in a set sequence with any parameter passing via I/O parameters.

Generally though, where we have nested queries which have frequent use we use stored procedures.

Which approach is adopted depends upon the nature of the app and the db you are dealing with. 

For clarity and ease of use and understanding, we tend to store the SQL in a db table; the table can also include notes, version nos., when last updated, etc. which aids later 'picking up the thread' of what you did weeks or months ago and have now forgotten all about it.

Brian Gooch
0
Brian
12/23/2010 10:32:37 AM
"howard manwaring" wrote in message news:315934@forums.embarcadero.com...
>
> currently yes, as its a bit easier to try out queries first. The 
> application at the moment is for a particular school where I used to work. 
> However I want to switch to something else (mySQL?, MSServer?) for a 
> version that can be used in many different schools. If nothing else I need 
> to stop db bloat and have something that runs a bit faster. My issue here 
> though is knowing which is the best/easiest db to install on a client's 
> network that doesn't involve me with a day on site. I think all the 
> clients will
> have MSSQL server but cannot guarantee I will have permissions to install 
> databases, nor that any staff there will know how to give me permisions.


There are all kinds of small footprint, easy to install databases available. 
Some have costs, some are free. They include Firebird, Nexus, Interbase, 
SQLLite, and others. Any one of these would serve your purposes and all 
support full and quite standard SQL implementations (SQL in Access is a 
strange animal).

-- 
Wayne Niddery (TeamB)
"Do not consider Collectivists as 'sincere but deluded idealists.' The 
proposal to enslave some men for the sake of others is not an ideal; 
brutality is not 'idealistic,' no matter what its purpose. Do not ever say 
that the desire to 'do good' by force is a good motive. Neither power-lust 
nor stupidity are good motives." -Ayn Rand"
0
Wayne
12/23/2010 6:07:05 PM
Reply:

Similar Artilces:

data, data, data
Hello, I need some advice on the best method to keep a database updated in this scenario. I have a local Solomon SQL Server where we keep our inventory. I want to build an application that will be hosted at a remote hosting location which provides a SQL database. I want to build the application to allow clients to access the inventory items and make requests based on the remote SQL data. The order will be sent to our fulfillment department via email; they will fiill the request and ship.  I'm not sure the method to do this while keeping the data current on the remote and the l...

Simple Question regarding passing data from Module to module in DNN 212
Hello, I am writing a custom application on top of DNN 2.1.2. I have a module on a tab with a data grid on it showing the results of a customer search. I have a double clicked event wired up so that when a record in the data grid is double clicked it will open another tab/page. I would like to know the best way to send the appropriate data from the module with the data grid to the new tab with other modules on it so that I can populate the new tab using the customer id of the double clicked row. Thanks in advance for any assistance Some options here. I assume that you are doing th...

Copy Module data to other modules
When creating a new module I would like to have the possibility of coping any other module data to the new module. Any ideas of how to do this ?? Do you mean you effectively what to be able to "clone" a module? along with it's settings? If so this'll start you off - this is an SP to do that - the front end is not here, it is intrinsically linked to our admin system CREATE PROCEDURE CloneModule ( @ModuleID int, @TargetTabID int, @TargetPaneName nvarchar(50) ) AS -- Get the new ModuleID, store in variable DECLARE @New...

Deleting module data when module instance is deleted
Hello, if a module instance is deleted from a page, how does DNN go about deleting the module data associated with that module instance? The only thread I could find on the subject suggests using database relationships and cascading deletes : http://forums.asp.net/1059952/ShowPost.aspx Are there any other ways? What if I also want to delete files and images when a module instance is deleted? Ideally I would like to execute a "cleanup" script that is executed when a module instance is deleted, but I'm not sure if it's possible to specify something like that in DNN. Thanks fo...

To module or not to module that is the question
Howdy List,=20 Bit of a quandry here. I have a script that does everything it's supposed to. It's basically like this: ---------------------- Configuration variables here: Program flow here with a single print statement after=20 it figures out what it's doing based on the configuration and user = input. ------------------ Now I need to use this same script lots of times so I already have = differen configurations saved as different modules then in the script = all I have to do is: ------------ In One.cgi: use Special::One; # this brings in all the configuar...

newbie question
hi there, i'm very new to dnn and custom modules, so please bear with me if the answer to my question is blindingly obvious to you ! what i want to be able to do is consume data from a web service and display that data in a module. i don't see an existing module that appears to fit the bill, so can anybody offer me any guidance regarding what i might need to do to achieve this please ?  do i need to develop a custom module, and if so, what is the recommended way of telling a module to use a web service instead of (?) the data provider to get the data it requires ?...

Strange occurrence with a data source in a data module? [Edit]
Hello All, I don’t understand why this is happening. I have a form that creates another form that has a data module as in: Form1 Form2 with dmModule.Create(Form2) The data module has a data source that is linked to a data aware combo box on Form2. Everything works! Now, if the first form has the same data module as in: Form1 with dmModule.Create(Form1) Form2 with dmModule.Create(Form2) The data aware combo box on Form2 no longer works. If I reassign the list source of the combo box on Form2 to the data source in the data module, it works: ...

Data Data :|
Ok, i have a very simple access database called score with just one table called high. This is just going to be used to keep the current score of a higher scorer and there name However, when the page loads i want the user to see this current high score and the persons name, but just one record i only ever want this to hold one record aswell..When the user finish’s the game there score is currently presented to them, when this happens i would like to connect to the database and check weather there score is higher than the current score (may have changed since they loaded page...

modules, modules
This site appears to be the most comprehensive list of free custom modules: www.dnnfaq.com Any other sites? I do like how Rainbow gives you a bucket of them - saves a lot of time over having to snoop around and find some of the DNN ones. Is there also a way to list modules as "certified"? Also - is there a decent repository of skins? I have recently installed version 2.0. Sure wish there were more modules ready for it! That would make it much easier to evaluate the program and give recommendations to my boss. The reason there aren't more 2.0 modules yet is becasue it h...

SQLAnywhere with Delphi Remote data modules
I know that this would be better off in the Delphi newsgroups but I figured I'd take a shot with all the SqlAnywhere experience in this group. I have been attampting to make a portable version of my three tier app using SqlAnywhere 6.0. I have built a couple of test apps to be sure that I have installed the sqlAnywhere properly and it is in fact working OK. I can connect and run queries against the database, no problem. Now, when I try to move to the three-teir model by putting the database component into a remote data module, the application freezes when it tries to ope...

Data or Data
I pronounce it "data". HTH I pronounce it dAta and data. Depends upon my mood and the context it's in. Like I preserve dAta, but i lose data. :P Matthew wrote: > I pronounce it dAta and data. Depends upon my mood and the context it's > in. Like I preserve dAta, but i lose data. :P Lt Commander D "ay" ta. Nuff said. In fact he corrected Dr Pulaski when she called him D "ah" ta. D ay ta, d ah ta, what's the difference? One is my name... the other is not. :) <-geek > > Lt Commander D "ay&quo...

Module in Module
Hi, Is there a way to place a module inside another module (e.g. A feedback module inside a Text/HTML module) ? Cheers Tassos There is a way to inject controls into a module dynamically based on some criteria like a querystring parameter - is that what you are wanting to do?Dylan Barberread my stupid blog http://codemypantsoff.com There's a commercial module "wrapper" on Snowcovered that is designed to hold other modules.  So it is possible.I don't know if you could put a module holding a module inside a module containing a module, but I wouldn't try.  The entire space-time...

accessing one modules data from another module
Hi -Is there an easy way to access the DAL of one module from another module?or, to ask a more direct question:Is it possible to get a list of files uploaded to DNN from the file uploader without going into the DB directly? I don't SEE anything that leaps out and says "do it like this" and I'm relatively new to DNN module development in general (like a month - I'm just starting to get comfy with some of this stuff)...Any thoughts or pointers?Thanks!--woody--woodyC. Woody Butler You can get the file list with the following method: DotNetNuke.Common.Globals.GetFileList(/*some params*/) HTH...

help with module name, module of abstracted regex via data objects.
Currently I've been running with this named as String::Clean but I think that it could be more descriptive. The end goal is to have a collection of things that you would need to be replaced or striped from a string. This all started because each of the developers at work had seperate lists of things that would be pulled from input forms and other common input streams. So I started building a toolkit where we could not only consolidate but also quickly show what gets modified to management. Most of this is done via YAML, arrays are striped, hashes are replaces, it allows for things ...

Web resources about - To Data Module or not Data Module, that is the question - embarcadero.delphi.ado

German question - Wikipedia, the free encyclopedia
"Kleindeutschland" redirects here. For the neighborhood in New York City, see Little Germany, Manhattan . 1820 map of Central Europe showing ...

Facebook’s Helen Crossley on the 3 Big Mobile-Commerce Questions
Mobile grabbed hold of the holiday shopping season in 2014, and there is no reason to believe that trend won’t continue this year. Helen Crossley ...

Question Of The Day: First-ever car ride?
Filed under: Automotive History The first time you were ever in a motor vehicle, what was it? Continue reading Question Of The Day: First-ever ...

‘The Walking Dead’ episode 7: A heart-pounding answer to ‘the Glenn question’
The seventh episode of The Walking Dead season 6 aired last night, and it had an interesting overall theme: people need saving. Unexpectedly, ...

Housing Rebound Didn't Lift Economy as Much as Economists Expected: Why?; Six Questions for Zandi
Home prices are nearly back to where they were before the crash. In some places, home prices are above where they were at the peak of the national ...

Real question: did Brad Pitt wear a ’90s wiglet at ‘The Big Short’ premiere?
Brad Pitt could potentially have a film at the Oscars this year. NO, not By the Sea . His real Oscar-bait film this year is The Big Short , which ...

Want a job at Apple? Here are 10 of the toughest interview questions
We alternately praise and criticize the folks at Apple depending on whether they just created the iPhone or Apple Maps, but there’s no doubting ...

Trump questions Hillary's 'stamina,' apparently unaware of her marathon Benghazi hearing
Donald Trump will find a new criticism of Hillary Clinton soon because his latest one is downright ludicrous. In an appearance on ABC’s “This ...

Lisa Vanderpump Is the Latest 'RHOBH' Star to Question Yolanda Foster's Lyme Disease
Lisa Vanderpump Is the Latest 'RHOBH' Star to Question Yolanda Foster's Lyme Disease

What's The Right Way To Ask Audiences Questions?
The key to asking questions of an audience is authenticity.

Resources last updated: 11/26/2015 7:47:15 PM