Database app good practice

[Tntman 14]

Hi people. 

I wanna connect vcl app to sql express server but i have some questions for you about application design pattern ..

I was doing simple things until now ( click on button perform queries and display results )..

Basically i was not dividing my code and now a lot of things are in main.pas file..

I would like to implement some MVC pattern and divide my code because its better practice, more maintainable etc..

Can somebody give me some simple example how this is done ?

[Stano 143]

I would also like to know how MVC is implemented in Delphi in practice.
General discussions on the issue did not help me. I couldn't find an example.

[bobD 6]

From the description of where you are ("click on button perform queries and display results"), this is an immense topic.

Probably the classic intro to object oriented design is Object-Oriented Software Construction by Bertrand Meyer

For a simpler approach (and with the benefit of being written for Delphi), try NIck Hodges' books on coding in Delphi. They're straightforward and very approachable.

Bit more detail in Hands-On Design Patterns with Delphi by Primoz Gabrijelcic

These  will get you started with OO thinking--the prerequisite to architectural layering.

 

In the mean time, how to refactor your current code to make it more maintainable? I'd suggest trying this:

New rule: no data access objects on forms. Those belong in data modules. Organize the modules into groups of logically related queries. That will start to separate out your code by 'topic of concern' so you can start grouping logically related code.

 

bobD

[Guest]

You can start by tring to...

 

- Create a TDataModule and drop some TDataSets (you DACs queries) on it.

- Put TDataSoures on a TFrame and link them to the TDataModule.

- Put the TFrame on a form and link the forms buttons to the frames TActions.

 

...and see what happens (but create a small test project).

 

The frame has the datamodule in interface uses, the form has the frame in interface section uses.

When the number of datamodules and frames increase, a lot of component suites has "Link" components.

You can, for example, if it suits your needs put a ConnectionLink component on the second... third... TDatamodule and link it to a singular Connection component.

 

...

Primoz's books are great!!

There's also "MVVM in Delphi- Architecting and Building Model View ViewModel Applications" but it will not speak of the TDataset based "model". They differ too much IMHO.

[Javier Tarí 23]

There are no MVC or MVVM frameworks on Delphi, other than the DMVCframework, wich I believe is not thought for Windows UI apps

And anyway, that kind of frameworks are not oriented to let a form use a TDataset or a TDatasource

So IMHO, as of today, RAD and MVC/MVVM are not friends

[Wloochacz 2]

5 hours ago, Javier Tarí said:

There are no MVC or MVVM frameworks on Delph

I'm sorry, do you know this project?
https://github.com/grijjy/MvvmStarterKit

It is not pure MVC, but MVVM 

 

5 hours ago, Javier Tarí said:

other than the DMVCframework, wich I believe is not thought for Windows UI apps

Here full approval, DMVC (https://github.com/danieleteti/delphimvcframework) is not a project for VCL/FMX applications.

 

5 hours ago, Javier Tarí said:

And anyway, that kind of frameworks are not oriented to let a form use a TDataset or a TDatasource

 So IMHO, as of today, RAD and MVC/MVVM are not friends

And yes and no.
Please note that FMX is also not TDataSource oriented, it does not have DBAware controls known from VCL (e.g. TDBEdit) and instead LiveBinding mechanism is used.

And in case of MVVM it can work very similarly.

 

In my opinion, RAD is no one's friend and this friendship turns into hatred when the project is bigger and bigger... And it gets older and older, and it needs to be developed.

After all, for decades we have been convinced that RAD is great! For everything!

Just, it's bullshit.

[aehimself 396]

I started actively using databases in the past couple of years in my applications, and these are the things I wished to know from the beginning:

 

- Every SQL thing should be in a separate thread. If connection takes 20 seconds or you are downloading a very large dataset over a slow connection, your application will be frozen. Publish important data or the complete dataset objects as properties. Just make sure you disconnect all datasources before any operation and reconnect them after, as data events will start to fire off in an inconsistent state causing AVs most probably.

 

- When it comes to threads, a golden rule is that each thread should have it's own connection to the database. You also want to make sure that threads are working with different tables or you should have correct transaction isolation set up.

 

- For service applications I wrote my own "ORM", which is basically a wrapper for a TQuery. Each field the query returns are published as typed properties. So instead of

query.Edit;
query.FieldByName('field').AsString := 'Hello, world';
query.Post;

I simply can say:

myTable.Field := 'Hello, world';

and myTable takes care of everything else. I took this one step further after a couple of DB changes and wrote my own planner. I tell it what kind of tables and fields I want, and it generates MySQL and MSSQL create and delta scripts AND all the myTable classes as Delphi source files. I make a change, I have all the SQL scripts ready to ship with the update and I already can use the new fields in all of MyTable objects... you get the point.

 

- Depending on the component you use (and how they access the databases) client libraries might not be thread safe or requiring special parameters upon establishing the connection to be thread safe! I found it as a best practice for example to have a critical section when connecting as I had strange access violations when 7 worker threads tried to connect using the same library at the same time.

 

- If performance is critical, do not use TQuery.FieldByName.

 

That's all I can think of for now but I'm sure I missed a few. If anything else pops up, I'll try to remember to update this thread.

[Tntman 14]

All answers helped me, i will google more things based in your answers, thank you for sharing your knowledge with people here 

[mvanrijnen 123]

Take a look here (noi do not have grijjy stock 🙂 )
 

MVVM Starter Kit (Part 1 of 3) – grijjy blog

 

Good explanation and good examples.

And a framework ready to go

Edited December 4, 2020 by mvanrijnen

[Javier Tarí 23]

1 hour ago, mvanrijnen said:

And a framework ready to go

Very far from it; just an appetizer, an starting point to get a feeling on how MVVM could work

 

If you are looking for a true MVVM framework, this is not for you

 

[Guest]

I do not think you can achieve this in delphi. Grijjy uses interposer classes for the visual controls to inject an interface.

I use interposers only to add what my 3rd party vendor refuses to do, and it requires it's management (of uses clauses mostly).

In a growing project that could prove rather messy.

I have a production system built with vanilla JS + knockout.js. In the browser, the form is the document model, so it's like already prepared for MVVM, MVC...

But it contains only two "gridlike "controls"" (the js client project, that is), my delphi clients contains 100's of grids with specific settings for each and every one.

Why would a solution using MVVM/MVC be (much?) better that a solution using the DB-aware pattern?

Is'nt the "vision" of having one set of controls that can do a bit much?

And using MVVM - in knockout.js there's an extention for building "controls". I did not use it, but it feels like going around back to where we are already.

When MVVM becomes convoluted (due to real-world needs) then MVVM "components" are needed... Round it goes.

Edited December 4, 2020 by Guest

[Javier Tarí 23]

1 hour ago, Dany Marmur said:

Why would a solution using MVVM/MVC be (much?) better that a solution using the DB-aware pattern?

Testing

Greater Platform independency

Long term, evolutive applications

 

1 hour ago, Dany Marmur said:

When MVVM becomes convoluted (due to real-world needs) then MVVM "components" are needed

Same happens with DB-aware; you just have some DBaware components

[Guest]

57 minutes ago, Javier Tarí said:

Same happens with DB-aware; you just have some DBaware components

That was my point. We already have them. Good ones (aware, they are).

 

Re future-proofing, in my experience, re-writing a project using the same but newer renders a better result.

Re-writing using a completely different pattern will be a... "brand" new project with all that entails.

What i mean to say is the first project may keep you above the waterline until you can muster up recourses to enter new domains (second project).

But then, if you are on to the second one, why use Delphi at all?

[Javier Tarí 23]

14 minutes ago, Dany Marmur said:

re-writing a project using the same but newer renders a better result

If you can, sure. I work everyday on the same project, for more than 20 years, and it can't stop evolving

So no chance of rewriting projects here