disqlite3_stringgrid_fmain.pas

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

{ DISQLite3 example project showing how to select for display, update, insert
  and delete Database records with the help of a standard string grid.

  This demo was created in response to user asking how to use a standard
  string grid with DISQLite3. It is meant to help understand basic Database
  operations and therefore intentionally kept simple.

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

  Even though this project uses WideStrings as much as possible, the Delphi VCL
  (TStringGrid mostly) limits text display to AnsiStrings only. WideString VCL
  extensions and grids 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_StringGrid_fMain;

interface

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

type
  TfrmStringGrid = class(TForm)
    pnlLeft: TPanel;
    btnOpenDatabase: TButton;
    btnSelect: TButton;
    btnDelete: TButton;
    btnUpdate: TButton;
    btnCloseDatabase: TButton;
    btnInsert: TButton;
    StringGrid: TStringGrid;
    procedure FormCreate(Sender: TObject);
    procedure btnOpenDatabase_Click(Sender: TObject);
    procedure btnSelect_Click(Sender: TObject);
    procedure btnUpdate_Click(Sender: TObject);
    procedure btnInsert_Click(Sender: TObject);
    procedure btnDelete_Click(Sender: TObject);
    procedure btnCloseDatabase_Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    FDatabase: TDISQLite3Database;
    function CheckCanEdit(const ACol, ARow: Integer): Boolean;
    procedure SetGridRow(
      const ARowIdx: Integer;
      const AID: Integer = -1;
      const AName: WideString = '';
      const AArea: WideString = '';
      const APopulation: WideString = '');
  end;

const
  APP_TITLE = 'DISQLite3 Demo: StringGrid';

var
  frmStringGrid: TfrmStringGrid;

implementation

uses
  SysUtils, Dialogs,
  DISQLite3Api;

{$R *.dfm}

const
  ColumnNames: array[0..2] of WideString = ('Name', 'Population', 'Birth Rate');

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

  { Helper routine to set one row in the StringGrid. }
procedure TfrmStringGrid.SetGridRow(
  const ARowIdx: Integer;
  const AID: Integer = -1;
  const AName: WideString = '';
  const AArea: WideString = '';
  const APopulation: WideString = '');
var
  Row: TStrings;
begin
  Row := StringGrid.Rows[ARowIdx];
  Row[0] := AName; Row.Objects[0] := TObject(AID);
  Row[1] := AArea; Row.Objects[1] := TObject(AID);
  Row[2] := APopulation; Row.Objects[2] := TObject(AID);
end;

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

{ Helper routine to check if a cell is editable. }
function TfrmStringGrid.CheckCanEdit(const ACol, ARow: Integer): Boolean;
begin
  Result := (ACol >= 0) and (ARow >= 1) and (Integer(StringGrid.Rows[ARow].Objects[ACol]) > 0);
  if not Result then
    begin
      ShowMessage('Can not edit cell.');
    end;
end;

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

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

  { Set up the string grid. }
  StringGrid.ColCount := 3;
  StringGrid.DefaultColWidth := 106;
  StringGrid.defaultrowheight := 19;
  StringGrid.Options := StringGrid.Options + [goColSizing, goThumbTracking];
  StringGrid.FixedCols := 0;
  StringGrid.RowCount := 2;
  SetGridRow(0, -1, ColumnNames[0], ColumnNames[1], ColumnNames[2]);
  SetGridRow(1);
end;

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

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

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

procedure TfrmStringGrid.btnOpenDatabase_Click(Sender: TObject);
begin
  FDatabase.Open;
end;

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

procedure TfrmStringGrid.btnSelect_Click(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, Population, "Birth Rate" FROM Countries ORDER BY Name COLLATE NoCase;';
var
  ID, RowCount: Integer;
  Stmt: TDISQLite3Statement;
begin
  { Prepare the select statement. }
  Stmt := FDatabase.Prepare16(SelectSql);
  try
    RowCount := 1;
    while Stmt.Step = SQLITE_ROW do
      begin
        ID := Stmt.Column_Int(0);
        SetGridRow(
          RowCount,
          ID,
          { Column "Name" is stored as TEXT, so we can retrieve it as a string. }
          Stmt.Column_Str16(1),
          { Column "Population" is stored as INTEGER, but DISQLite3 will
            automatically convert the integer value to a string. }
          Stmt.Column_Str16(2),
          { Column "Birth Rate" is stored as FLOAT, but DISQLite3 will
            automatically convert the float value to a string. }
          Stmt.Column_Str16(3));
        Inc(RowCount);
      end;
    StringGrid.RowCount := RowCount;
  finally
    Stmt.Free;
  end;
end;

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

procedure TfrmStringGrid.btnUpdate_Click(Sender: TObject);
const
  { Notice the quotation marks around around "%s% in the following line. They
    ensure that column names with spaces are properly quoted. }
  UpdateSql = 'UPDATE Countries SET "%s"=? WHERE ID=?;';
var
  c, r: Integer;
  ID: Integer;
  s: AnsiString;
  SQL: WideString;
  Stmt: TDISQLite3Statement;
begin
  c := StringGrid.Col; r := StringGrid.Row;
  if not CheckCanEdit(c, r) then
    Exit;

  s := StringGrid.Cells[c, r];
  if InputQuery('Update', Format('Enter new %s:', [ColumnNames[c]]), s) then
    begin
      { Prepare a select statement, dynamically choosing the column. }
      SQL := Format(UpdateSql, [ColumnNames[c]]);
      Stmt := FDatabase.Prepare16(SQL);
      try
        Stmt.bind_Str16(1, s);
        { Retrieve and bind the RowID which uniquely identifies this record. }
        ID := Integer(StringGrid.Objects[c, r]);
        Stmt.bind_Int(2, ID);
        { Step once to execute statement and update database record. }
        Stmt.Step;
        { Reflect changes to StringGrid. }
        StringGrid.Cells[c, r] := s;
      finally
        Stmt.Free;
      end;
    end;
end;

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

procedure TfrmStringGrid.btnInsert_Click(Sender: TObject);
const
  InsertSql = 'INSERT INTO Countries (Name) VALUES (?);';
var
  ID, r: Integer;
  NewCountryName: AnsiString;
  Stmt: TDISQLite3Statement;
begin
  NewCountryName := '';
  if InputQuery('Insert', 'Enter new country name:', NewCountryName) then
    begin
      { Prepare a insert statement. }
      Stmt := FDatabase.Prepare16(InsertSql);
      try
        { We bind all columns as strings. DISQLite3 will convert them to
          integers or floats as appropriate. }
        Stmt.bind_Str16(1, NewCountryName);
        { Step once to execute statement and insert data. }
        Stmt.Step;
        { Retrieve the RowID of the newly inserted record. }
        ID := FDatabase.LastInsertRowID;

        { Add the new country to the StringGrid. Set the name only and
          leave other columns empty. }
        r := StringGrid.RowCount;
        StringGrid.RowCount := r + 1;
        SetGridRow(r, ID, NewCountryName);
        StringGrid.Row := r;
      finally
        Stmt.Free;
      end;
    end;
end;

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

procedure TfrmStringGrid.btnDelete_Click(Sender: TObject);
const
  DeleteSql = 'DELETE FROM Countries WHERE ID=?;';
var
  ID, r: Integer;
  Stmt: TDISQLite3Statement;
begin
  r := StringGrid.Row;
  if not CheckCanEdit(0, r) then
    Exit;

  if MessageDlg('Delete?', mtConfirmation, mbOkCancel, 0) = mrOK then
    begin
      { Prepare a select statement. }
      Stmt := FDatabase.Prepare16(DeleteSql);
      try
        { Retrieve and bind the RowID which uniquely identifies this record. }
        ID := Integer(StringGrid.Rows[r].Objects[0]);
        Stmt.bind_Int(1, ID);
        { Step once to execute statement and delete record from database. }
        Stmt.Step;
        { Reflect changes to the StringGrid. This is a bit of a problem since
          TStringGrid does not have a method to delete a row. Hence just
          reload the entire grid. }
        btnSelect_Click(nil);
      finally
        Stmt.Free;
      end;
    end;
end;

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

procedure TfrmStringGrid.btnCloseDatabase_Click(Sender: TObject);
begin
  StringGrid.RowCount := 2;
  SetGridRow(1);
  FDatabase.Close;
end;

end.