I am beginning to create my first Delphi application that connects for an SQL database (MySQL) while using ADO database components. I wondered whether there is any easiest way of storing what they are called from the fields within the database for simple reference when designing SQL queries afterwards.

To begin with I built them into an easy constant e.g. c_UserTable_Username, c_UserTable_Password, however made the decision which was not really a particularly great way of doing things and so i am now storing these questions constant record e.g.:

type
   TUserTable = record
     TableName : String;
     Username : String;
     Password : String;
 end;

const
UserTable : TUserTable =
    (
      TableName : 'users';
      Username : 'Username';
      Password : 'Password';
    );

this enables me to produce a statement like:

query.SQL.Add('SELECT ' + UserTable.Username + ' FROM ' + UserTable.TableName);

without having to be worried about hard coding the area names etc.

I have now encounter the issue however where if I wish to cycle with the table fields (for instance if you will find 20 approximately fields), I can not. I must by hand type the record reference for each area.

I you know what Let me know is whether or not there's a method to iterate though all area names at the same time, or singularly or shall we be held carrying out this the wrong manner? Possibly I should not be storing them such as this whatsoever?

Also, I have designed a “Database” class which essentially holds techniques for a lot of different SQL claims, for instance GetAllUsers, GetAllProducts, etc. Does that seem correct? I have reviewed lots of Delphi/SQL lessons, however they don't appear to visit much past demonstrating how you can run queries.

I guess I am somewhat lost and then any assistance is very welcome. Thanks :)

You might store your queries as RESOURCESTRING which may allow editing of these afterwards utilizing a resource editor (if required).

RESOURCESTRING
  rsSelectFromUsers = 'SELECT USERNAME FROM USERS ';

Your approach of the database class is effective. I've done exactly that in a number of of my projects, coming back an interface for an object which consists of the dataset...the benefit of this is where the came back interface variable is out of scope, the dataset could be closed and removed.

Well, you're hard coding area names you simply hardcode them within the const rather than within the query itself. I am unsure that really enhances anything. So far as iterating with the fields goes, do this:

var
  Field: TField;
begin
  for Field in query.Fields do begin
     // do stuff with Field
  end;
end;

Instead of creating a "Database" class, I'd most likely make use of a TDataModule. This may almost exactly the same factor as the class, with the exception that it enables you to definitely interactively design queries at design time. Place the any techniques you'll need around the DataModule.

This causes it to be quite simple to instantiate persistent TFields (see help on that subject), which you might discover the solution more the way you like than using consts to keep area names.