disqlite3_stringlist_fmain.pas

来自「DELPHI 访问SQLITE3 数据库的VCL控件」· PAS 代码 · 共 255 行

{ DISQLite3 example project showing how to select for display, update, insert
  and delete FDatabase records with the help of TStrings / TStringLists.

  This demo was created in response to user asking how to use TStringList with
  DISQLite3. It is intentionally kept simple to allow focus on the basic
  Database operations.

  The database component is created at runtime to avoid problems if the
  DISQLite3 components are not installed into the IDE.

  The StringLists in this project manage AnsiStrings only. WideString lists
  are available for download from the Internet if required.

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de/delphi/

  Copyright (c) 2007 Ralf Junker, The Delphi Inspiration <[e-mail]>

------------------------------------------------------------------------------ }

unit DISQLite3_StringList_fMain;

interface

uses
  Classes, Controls, Forms, StdCtrls, ExtCtrls,
  DISQLite3Database;

type
  TfrmStringList = class(TForm)
    ListBox: TListBox;
    pnlLeft: TPanel;
    btnOpenDatabase: TButton;
    btnSelect: TButton;
    btnDelete: TButton;
    btnUpdate: TButton;
    btnCloseDatabase: TButton;
    btnInsert: TButton;
    procedure FormCreate(Sender: TObject);
    procedure btnOpenDatabaseClick(Sender: TObject);
    procedure btnSelectClick(Sender: TObject);
    procedure btnUpdateClick(Sender: TObject);
    procedure btnInsertClick(Sender: TObject);
    procedure btnDeleteClick(Sender: TObject);
    procedure btnCloseDatabaseClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    FDatabase: TDISQLite3Database;
  end;

const
  APP_TITLE = 'DISQLite3 Demo: StringList';

var
  frmStringList: TfrmStringList;

implementation

uses
  Dialogs,
  DISQLite3Api;

{$R *.dfm}

procedure TfrmStringList.FormCreate(Sender: TObject);
begin
  Caption := APP_TITLE;
  { Create a new database component and specify the file name. }
  FDatabase := TDISQLite3Database.Create(nil);
  FDatabase.DatabaseName := '..\World.db3';
end;

//------------------------------------------------------------------------------

procedure TfrmStringList.FormDestroy(Sender: TObject);
begin
  { Since we have created the database component in code,
    we need to free it when the form destroys. }
  FDatabase.Free;
end;

//------------------------------------------------------------------------------

procedure TfrmStringList.btnOpenDatabaseClick(Sender: TObject);
begin
  FDatabase.Open;
end;

//------------------------------------------------------------------------------

procedure TfrmStringList.btnSelectClick(Sender: TObject);
const
  { Select statement to retrieve all country names from the database.
    The ID identifies the record and is used for updates and deletes. }
  SelectSql = 'SELECT ID, Name FROM Countries ORDER BY Name COLLATE NoCase;';
var
  Stmt: TDISQLite3Statement;
  StringList: TStrings;
begin
  { Pick our StringList. }
  StringList := ListBox.Items;

  { Select -> StringList begins here. }
  StringList.BeginUpdate;
  try
    ListBox.Clear;
    { Prepare the select statement. }
    Stmt := FDatabase.Prepare16(SelectSql);
    try
      { Step through all records in the result set and
        add them to the string list. }
      while Stmt.Step = SQLITE_ROW do
        begin
          { Store country Name and ID to the StringList. }
          StringList.AddObject(
            Stmt.Column_Str16(1), // The Name.
            TObject(Stmt.Column_Int(0))); // The ID, stored as TObject.
        end;
    finally
      Stmt.Free;
    end;
  finally
    StringList.EndUpdate;
  end;
end;

//------------------------------------------------------------------------------

procedure TfrmStringList.btnUpdateClick(Sender: TObject);
const
  UpdateSql = 'UPDATE Countries SET Name=? WHERE ID=?';
var
  i, Idx: Integer;
  s: AnsiString;
  Stmt: TDISQLite3Statement;
  StringList: TStrings;
begin
  { Pick our StringList. }
  StringList := ListBox.Items;

  i := ListBox.ItemIndex;
  if i < 0 then
    begin
      ShowMessage('Please select an item first.');
      Exit;
    end;

  s := StringList[i];
  if InputQuery('Update', 'Enter new country name:', s) then
    begin
      { }
      Idx := Integer(StringList.Objects[i]);
      { Prepare the select statement. }
      Stmt := FDatabase.Prepare16(UpdateSql);
      try
        Stmt.bind_Str16(1, s);
        Stmt.bind_Int(2, Idx);
        { Step once to execute statement and update database record. }
        Stmt.Step;
        { Reflect new values in StringList. }
        StringList[i] := s;
      finally
        Stmt.Free;
      end;
    end;
end;

//------------------------------------------------------------------------------

procedure TfrmStringList.btnDeleteClick(Sender: TObject);
const
  DeleteSql = 'DELETE FROM Countries WHERE ID=?';
var
  i, Idx: Integer;
  Stmt: TDISQLite3Statement;
  StringList: TStrings;
begin
  { Pick our StringList. }
  StringList := ListBox.Items;

  i := ListBox.ItemIndex;
  if i < 0 then
    begin
      ShowMessage('Please select an item first.');
      Exit;
    end;

  if MessageDlg('Delete?', mtConfirmation, mbOkCancel, 0) = mrOK then
    begin
      Idx := Integer(StringList.Objects[i]);
      { Prepare a select statement. }
      Stmt := FDatabase.Prepare16(DeleteSql);
      try
        Stmt.bind_Int(1, Idx);
        { Step once to execute statement and delete record from database. }
        Stmt.Step;
        { Reflect deletion in StringList. }
        StringList.Delete(i);
      finally
        Stmt.Free;
      end;
    end;
end;

//------------------------------------------------------------------------------

procedure TfrmStringList.btnInsertClick(Sender: TObject);
const
  InsertSql = 'INSERT INTO Countries (Name) VALUES (?);';
var
  i, Idx: Integer;
  s: AnsiString;
  Stmt: TDISQLite3Statement;
  StringList: TStrings;
begin
  { Pick our StringList. }
  StringList := ListBox.Items;

  s := '';
  if InputQuery('Insert', 'Enter new country name:', s) then
    begin
      { Prepare a insert statement. }
      Stmt := FDatabase.Prepare16(InsertSql);
      try
        Stmt.bind_Str16(1, s);
        { Step once to execute statement and insert data. }
        Stmt.Step;
        { Retrieve the RowID of the newly inserted record. }
        Idx := FDatabase.LastInsertRowID;
        { Add the new country name to the StringList. }
        i := StringList.AddObject(s, TObject(Idx));
        ListBox.ItemIndex := i;
      finally
        Stmt.Free;
      end;
    end;
end;

//------------------------------------------------------------------------------

procedure TfrmStringList.btnCloseDatabaseClick(Sender: TObject);
var
  StringList: TStrings;
begin
  { Pick our StringList. }
  StringList := ListBox.Items;

  StringList.Clear;
  FDatabase.Close;
end;

end.