Title: TDBIniFile Class to Handle Application Settings
Question: TDBIniFile enables handling the storage and retrieval of application-specific information and settings in a database table that simulates an INI file.
Answer:
TDBIniFile Class to Handle Application Settings
=====================================================
By Marcus Neves.
Abstract
TDBIniFile enables handling the storage and retrieval of application-specific information and settings in a database table that simulates an INI file.
Introduction
It is common to database systems to have a table that stores application-specific information and settings. And it's also common to have one field in this table for each parameter. In this way it's hard to alter/delete an existing parameter or add a new one without having to drop the table and create a new one.
Why don't we use the idea of INI files in the database also? Yes! We can simply define a database table that has three simple fields and we would not have to worry anymore when altering or adding new parameters to this table.
The Delphi Way
Delphi's VCL is really very good in its design. The Borland engineers did a really great job of abstracting Windows-specific things to allow easier handling and possibly incrementing its features along the way. This is the case of TCustomINIFile. TCustomIniFile is the abstract base type for components that encapsulate access to INI files or provide similar access.
TDBIniFile enables handling the storage and retrieval of application-specific information and settings in a database table that simulates an INI file. An INI file stores information in logical groupings, called Sections.
To achieve this, we simply override some abstract methods in the base class that are responsible for reading and writing strings in the table, some methods that checks if some section exists or not and some other methods to erase a given section.
Conclusion
The TDBIniFile class is really very simple to understand thanks mainly to the efforts of the engineers at Borland who have the wisdom to abstract the handling of INI files in the TCustomINIFile base class.
{{{*************** CODE BELOW ***************}}}
{*******************************************************}
{ }
{ Simultate an INI-like file in a database table }
{ }
{ By Marcus Neves. }
{*******************************************************}
unit DBIniFile;
interface
uses Windows, Classes, SysUtils, IniFiles, DB, DBTables;
type
{ TDBIniFile }
{ Table Format:
Fields:
Section varchar(20) NOT NULL
Name varchar(20) NOT NULL
Value varchar(100) NOT NULL
PK: Section, Name (CLUSTERED) }
TDBIniFile = class(TCustomIniFile)
private
FDatabaseName: string;
FTableName: string;
FQuery: TQuery;
public
constructor Create(const DatabaseName, TableName: string);
destructor Destroy; override;
function SectionExists(const Section: string): Boolean;
function ReadString(const Section, Ident, Default: string): string; override;
procedure WriteString(const Section, Ident, Value: String); override;
procedure ReadSection(const Section: string; Strings: TStrings); override;
procedure ReadSections(Strings: TStrings); override;
procedure ReadSectionValues(const Section: string; Strings: TStrings); override;
procedure EraseSection(const Section: string); override;
procedure DeleteKey(const Section, Ident: String); override;
procedure UpdateFile; override;
property DatabaseName: string read FDatabaseName;
property TableName: string read FTableName;
end;
implementation
const { do not localize }
SqlReadString = 'SELECT Value FROM %s WHERE Section = "%s" AND Name = "%s"';
SqlSectionExists = 'SELECT COUNT(*) FROM %s WHERE Section = "%s"';
SqlDeleteKey = 'DELETE FROM %s WHERE Section = "%s" AND Name = "%s"';
SqlInsertKey = 'INSERT INTO %s (Section, Name, Value) VALUES ("%s", "%s", "%s")';
SqlReadSection = 'SELECT Name FROM %s WHERE Section = "%s"';
SqlReadSections = 'SELECT Section FROM %s';
SqlDeleteSection = 'DELETE FROM %s WHERE Section = "%s"';
{ TDBIniFile }
constructor TDBIniFile.Create(const DatabaseName, TableName: string);
begin
FDatabaseName := DatabaseName;
FTableName := TableName;
FQuery := TQuery.Create (nil);
FQuery.DatabaseName := FDatabaseName;
FQuery.RequestLive := False;
end;
procedure TDBIniFile.DeleteKey(const Section, Ident: String);
begin
FQuery.SQL.Text := Format(SqlDeleteKey, [FTableName, Section, Ident]);
FQuery.ExecSQL;
end;
destructor TDBIniFile.Destroy;
begin
if FQuery nil then
begin
FQuery.Close;
FreeAndNil (FQuery);
end;
inherited;
end;
procedure TDBIniFile.EraseSection(const Section: string);
begin
FQuery.SQL.Text := Format(SqlDeleteSection, [FTableName, Section]);
FQuery.ExecSQL;
end;
procedure TDBIniFile.ReadSection(const Section: string; Strings: TStrings);
begin
FQuery.SQL.Text := Format(SqlReadSection, [FTableName, Section]);
FQuery.Open;
try
Strings.BeginUpdate;
try
Strings.Clear;
while not FQuery.Eof do
begin
Strings.Add(FQuery.Fields[0].AsString);
FQuery.Next;
end;
finally
Strings.EndUpdate;
end;
finally
FQuery.Close;
end;
end;
procedure TDBIniFile.ReadSections(Strings: TStrings);
begin
FQuery.SQL.Text := Format(SqlReadSections, [FTableName]);
FQuery.Open;
try
Strings.BeginUpdate;
try
Strings.Clear;
while not FQuery.Eof do
begin
Strings.Add(FQuery.Fields[0].AsString);
FQuery.Next;
end;
finally
Strings.EndUpdate;
end;
finally
FQuery.Close;
end;
end;
procedure TDBIniFile.ReadSectionValues(const Section: string; Strings: TStrings);
var
I: Integer;
KeyList: TStringList;
begin
KeyList := TStringList.Create;
try
ReadSection(Section, KeyList);
Strings.BeginUpdate;
try
for I := 0 to KeyList.Count - 1 do
Strings.Values[KeyList[I]] := ReadString(Section, KeyList[I], '');
finally
Strings.EndUpdate;
end;
finally
KeyList.Free;
end;
end;
function TDBIniFile.ReadString(const Section, Ident, Default: string): string;
begin
Result := Default;
FQuery.SQL.Text := Format(SqlReadString, [FTableName, Section, Ident]);
FQuery.Open;
try
if not FQuery.Eof then Result := FQuery.Fields[0].AsString;
finally
FQuery.Close;
end;
end;
function TDBIniFile.SectionExists(const Section: string): Boolean;
begin
Result := False;
FQuery.SQL.Text := Format(SqlSectionExists, [FTableName, Section]);
FQuery.Open;
try
if not FQuery.Eof then Result := (FQuery.Fields[0].AsInteger 0);
finally
FQuery.Close;
end;
end;
procedure TDBIniFile.UpdateFile;
begin
inherited;
end;
procedure TDBIniFile.WriteString(const Section, Ident, Value: String);
begin
{ Delete entry }
DeleteKey(Section, Ident);
{ Create a new one }
FQuery.SQL.Text := Format(SqlInsertKey, [FTableName, Section, Ident, Value]);
FQuery.ExecSQL;
end;
end.