Delphi 資料庫連接SQL Server 2008 R2 (新增,修改,刪除,查詢)

雖然Delphi在DB Control裡有工具可以直接拉出對資料庫作新增修改刪除的動作
但介面上要比較好看且功能較強大的話還是要自己寫出IUDS的動作
所以寫了小小的程式當作練習
我是使用ADO做資料庫連線
先設定ADOQuery的ConnectionString,連線成功
再設定一個DataSource使其可以連上資料庫
這邊我是用一個DBGrid呈現資料表中的資料

表單配置如下圖

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, DB, ADODB, StdCtrls, Mask, DBCtrls;

type
  TForm1 = class(TForm)
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    btnAZ: TButton;
    btnZA: TButton;
    Label1: TLabel;
    ediID: TEdit;
    btnSea: TButton;
    Label2: TLabel;
    DBID: TDBEdit;
    DBPW: TDBEdit;
    Label3: TLabel;
    Label4: TLabel;
    btnUpdate: TButton;
    btnDelect: TButton;
    btnUp: TButton;
    btnDown: TButton;
    btnFirst: TButton;
    btnLast: TButton;
    Label5: TLabel;
    ID: TEdit;
    PW: TEdit;
    Label6: TLabel;
    Label7: TLabel;
    btnIsert: TButton;
    btnClose: TButton;
    Label8: TLabel;
    procedure btnAZClick(Sender: TObject);
    procedure btnZAClick(Sender: TObject);
    procedure btnSeaClick(Sender: TObject);
    procedure btnUpdateClick(Sender: TObject);
    procedure btnDelectClick(Sender: TObject);
    procedure btnUpClick(Sender: TObject);
    procedure btnDownClick(Sender: TObject);
    procedure btnFirstClick(Sender: TObject);
    procedure btnLastClick(Sender: TObject);
    procedure btnIsertClick(Sender: TObject);
    procedure btnCloseClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btnAZClick(Sender: TObject);
begin
 ADOQuery1.Sort := 'ID ASC'    //升冪排序
end;

procedure TForm1.btnZAClick(Sender: TObject);
begin
 ADOQuery1.Sort := 'ID DESC'   //降冪排序
end;
procedure TForm1.btnSeaClick(Sender: TObject);
 var
  F : Boolean;
 begin
   F := ADOQuery1.Locate('ID',ediID.Text,[loPartialKey]); //搜尋特定字串
  if F = False then
   begin
    showMessage('No Data Exist!');   //沒有相符資料
   end;
 end;

procedure TForm1.btnUpdateClick(Sender: TObject);  //修改
 var
  i : Integer;
 begin
   i := Application.MessageBox('確定要修改這筆資料?','修改確認',MB_OKCANCEL);
  if i = 1 then
  begin
   ADOQuery1.Edit;
   ADOQuery1['ID'] := DBID.Text;
   ADOQuery1['PW'] := DBPW.Text;
   ADOQuery1.Post;
  end
  else
   exit;
 end;

procedure TForm1.btnDelectClick(Sender: TObject);   //刪除
 var
  i : Integer;
 begin
   i := Application.MessageBox('確定要刪除這筆資料?','刪除確認',MB_OKCANCEL);
  if i = 1 then
    ADOQuery1.Delete
  else
    exit;
 end;

procedure TForm1.btnUpClick(Sender: TObject);  //上一筆功能
 begin
  if ADOQuery1.Bof then
    ADOQuery1.First
  else
    ADOQuery1.Prior;
 end;

procedure TForm1.btnDownClick(Sender: TObject);   //下一筆功能
 begin
  if ADOQuery1.Eof then
   ADOQuery1.Last
  else
   ADOQuery1.Next;
 end;

procedure TForm1.btnFirstClick(Sender: TObject);    //第一筆
 begin
  ADOQuery1.First();
 end;

procedure TForm1.btnLastClick(Sender: TObject);     //最後一筆
 begin
  ADOQuery1.Last();
 end;

procedure TForm1.btnIsertClick(Sender: TObject);   //新增
 begin
  if ID.Text = '' then
   begin
     showMessage('請輸入資料');
     exit;
   end;
  if Length(ID.Text) > 5 then
   begin
     showMessage('輸入字串長度要小於5');
     ID.Clear;
     exit;
   end;
  ADOQuery1.Insert;
  ADOQuery1['ID'] := ID.Text;
  ADOQuery1['PW'] := PW.Text;
  ADOQuery1.Post;
  ID.Clear;
  PW.Clear;
 end;
 
procedure TForm1.btnCloseClick(Sender: TObject);
begin
 Close();
end;

end.