Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I make a component to interact with a database table?

Component Writing

How do I make a component to interact with a database table?

by  djjd47130  Posted    (Edited  )
(Updated with new properties and better description)

The component below demonstrates the basics of how to wrap a component around a database table, creating a generic control over the table's contents. Type TCustomUsers has all the necessary functionality to interact with the data in a User table, without the end user having to know how to access the database. One property specifies the Connection String to the database. Each field in the database is mapped to a corresponding property in the component. Standard user properties such as Database ID, User Name, First Name, Last Name, Nick Name, Password, Etc. are linked through the properties in the options (TUserTableOptions). Therefore, it can be customized to interact with any database.

Note: Change the properties in 'Options' for TCustomUsers Component according to your user table in your database. Set the ConnectionString to your database. When you want to retrieve data from the component, call the procedure 'TCustomUsers.Refresh'.

Delphi 7 Unit: UserWrap

Types: TCustomUsers, TCustomUser, TUserTableOptions
Component: TCustomUsers

Code: 
[navy][i]// for automatic syntax highlighting see faq102-6487 
[/i][/navy][b]unit[/b] UserWrap;

[b]interface[/b]

[b]uses[/b]
  Variants, Classes, DB, ADODB, SysUtils, Controls, Graphics, Forms, Windows;

[b]type[/b]
  TCustomUsers = [b]class[/b];
  TCustomUser = [b]class[/b];
  TUserTableOptions = [b]class[/b];

  [navy][i]//Represents User Table Options
[/i][/navy]  TUserTableOptions = [b]class[/b](TPersistent)
  [b]private[/b]
    fUserTable: String;       [navy][i]//Database Table where User Data is Stored   
[/i][/navy]    fIDField: String;         [navy][i]//ID Field
[/i][/navy]    fUserNameField: String;   [navy][i]//User Name Field  
[/i][/navy]    fPasswordField: String;   [navy][i]//Password Field
[/i][/navy]    fFirstNameField: String;  [navy][i]//First Name Field
[/i][/navy]    fMiddleNameField: String; [navy][i]//Middle Name Field
[/i][/navy]    fLastNameField: String;   [navy][i]//Last Name Field
[/i][/navy]    fNickNameField: String;   [navy][i]//Nick Name Field
[/i][/navy]  [b]published[/b]
    [b]property[/b] UserTable: String [b]read[/b] fUserTable [b]write[/b] fUserTable;     
    [b]property[/b] IDField: String [b]read[/b] fIDField [b]write[/b] fIDField;
    [b]property[/b] UserNameField: String [b]read[/b] fUserNameField [b]write[/b] fUserNameField;  
    [b]property[/b] PasswordField: String [b]read[/b] fPasswordField [b]write[/b] fPasswordField;
    [b]property[/b] FirstNameField: String [b]read[/b] fFirstNameField [b]write[/b] fFirstNameField;
    [b]property[/b] MiddleNameField: String [b]read[/b] fMiddleNameField [b]write[/b] fMiddleNameField;
    [b]property[/b] LastNameField: String [b]read[/b] fLastNameField [b]write[/b] fLastNameField;
    [b]property[/b] NickNameField: String [b]read[/b] fNickNameField [b]write[/b] fNickNameField;
  [b]end[/b];

  [navy][i]//Represents an individual user
[/i][/navy]  TCustomUser = [b]class[/b](TObject)
  [b]private[/b]
    fConnectionString: String;
    fID: Integer;
    fUserName: String;
    fPassword: String;
    fFirstName: String;
    fMiddleName: String;
    fLastName: String;
    fNickName: String;
    fOwner: TCustomUsers;

    [b]function[/b] GetOptions: TUserTableOptions;

    [b]function[/b] GetID: Integer;
    [b]function[/b] GetUserName: String;
    [b]function[/b] GetFirstName: String;
    [b]function[/b] GetLastName: String;
    [b]procedure[/b] SetUserName(Value: String);
    [b]procedure[/b] SetFirstName(Value: String);
    [b]procedure[/b] SetLastName(Value: String);

    [b]function[/b] GetMiddleName: String;
    [b]function[/b] GetNickName: String;
    [b]function[/b] GetPassword: String;
    [b]procedure[/b] SetMiddleName(Value: String);
    [b]procedure[/b] SetNickName(Value: String);
    [b]procedure[/b] SetPassword(Value: String);

    [b]function[/b] ReadString(Field: String): String;
    [b]function[/b] ReadInteger(Field: String): Integer;
    [b]function[/b] SaveString(Field: String; Value: String): Bool;
    [b]function[/b] SaveInteger(Field: String; Value: Integer): Bool;
  [b]public[/b]
    [b]constructor[/b] Create(AOwner: TCustomUsers);
    [b]destructor[/b] Destroy; [b]override[/b];
  [b]published[/b]
    [b]property[/b] ConnectionString: String [b]read[/b] fConnectionString [b]write[/b] fConnectionString;
    [b]property[/b] ID: Integer [b]read[/b] GetID;  [navy][i]//No setting function because ID shall remain the same
[/i][/navy]    [b]property[/b] UserName: String [b]read[/b] GetUserName [b]write[/b] SetUserName;
    [b]property[/b] Password: String [b]read[/b] GetPassword [b]write[/b] SetPassword;
    [b]property[/b] FirstName: String [b]read[/b] GetFirstName [b]write[/b] SetFirstName;
    [b]property[/b] MiddleName: String [b]read[/b] GetMiddleName [b]write[/b] SetMiddleName;
    [b]property[/b] LastName: String [b]read[/b] GetLastName [b]write[/b] SetLastName;
    [b]property[/b] NickName: String [b]read[/b] GetNickName [b]write[/b] SetNickName;
    [b]property[/b] Options: TUserTableOptions [b]read[/b] GetOptions;
  [b]end[/b];

  [navy][i]//Represents a set of users (TCustomUser)
[/i][/navy]  TCustomUsers = [b]class[/b](TComponent)
  [b]private[/b]
    fUserList: TStringList;
    fConnectionString: String;
    fOptions: TUserTableOptions;

    [b]function[/b] GetUser(Index: Integer): TCustomUser;
    [b]procedure[/b] SetConnectionString(Value: String);
    [b]function[/b] GetCount: Integer;

  [b]public[/b]
    [b]constructor[/b] Create(AOwner: TComponent); [b]override[/b];
    [b]destructor[/b] Destroy; [b]override[/b];
    [b]procedure[/b] Refresh;
    [b]property[/b] Count: Integer [b]read[/b] GetCount;
    [b]property[/b] Users[Index: Integer]: TCustomUser [b]read[/b] GetUser;
  [b]published[/b]
    [b]property[/b] ConnectionString: String [b]read[/b] fConnectionString [b]write[/b] SetConnectionString;
    [b]property[/b] Options: TUserTableOptions [b]read[/b] fOptions [b]write[/b] fOptions;
  [b]end[/b];


[b]procedure[/b] Register;

[b]implementation[/b]

[b]procedure[/b] Register;
[b]begin[/b]
  RegisterComponents([teal]'JD Custom'[/teal], [TCustomUsers]);
[b]end[/b];



[b]function[/b] TCustomUser.GetID: Integer;      
[b]begin[/b]
  Self.fID:= Self.ReadInteger(Options.IDField);
  Result:= Self.fID;
[b]end[/b];

[b]function[/b] TCustomUser.GetUserName: String;    
[b]begin[/b]
  Self.fUserName:= Self.ReadString(Options.UserNameField);
  Result:= Self.fUserName;
[b]end[/b];

[b]function[/b] TCustomUser.GetFirstName: String;
[b]begin[/b]
  Self.fFirstName:= Self.ReadString(Options.FirstNameField);
  Result:= Self.fFirstName;
[b]end[/b];

[b]function[/b] TCustomUser.GetLastName: String;
[b]begin[/b]
  Self.fLastName:= Self.ReadString(Options.LastNameField);
  Result:= Self.fLastName;
[b]end[/b];

[b]function[/b] TCustomUser.GetMiddleName: String; 
[b]begin[/b]
  Self.fMiddleName:= Self.ReadString(Options.MiddleNameField);
  Result:= Self.fMiddleName;
[b]end[/b];

[b]function[/b] TCustomUser.GetNickName: String;
[b]begin[/b]
  Self.fNickName:= Self.ReadString(Options.NickNameField);
  Result:= Self.fNickName;
[b]end[/b];

[b]function[/b] TCustomUser.GetPassword: String;
[b]begin[/b]
  Self.fPassword:= Self.ReadString(Options.PasswordField);
  Result:= Self.fPassword;
[b]end[/b];

[b]procedure[/b] TCustomUser.SetMiddleName(Value: String);
[b]begin[/b]
  [b]if[/b] Self.SaveString(Options.MiddleNameField, Value) [b]then[/b]
    Self.fMiddleName:= Value;
[b]end[/b];

[b]procedure[/b] TCustomUser.SetNickName(Value: String);
[b]begin[/b]
  [b]if[/b] Self.SaveString(Options.NickNameField, Value) [b]then[/b]
    Self.fNickName:= Value;
[b]end[/b];

[b]procedure[/b] TCustomUser.SetPassword(Value: String);
[b]begin[/b]
  [b]if[/b] Self.SaveString(Options.PasswordField, Value) [b]then[/b]
    Self.fPassword:= Value;
[b]end[/b];

[b]procedure[/b] TCustomUser.SetUserName(Value: String);
[b]begin[/b]
  [b]if[/b] Self.SaveString(Options.UserNameField, Value) [b]then[/b]
    Self.fUserName:= Value;
[b]end[/b];

[b]procedure[/b] TCustomUser.SetFirstName(Value: String);
[b]begin[/b]
  [b]if[/b] Self.SaveString(Options.FirstNameField, Value) [b]then[/b]
    Self.fFirstName:= Value;
[b]end[/b];

[b]procedure[/b] TCustomUser.SetLastName(Value: String);
[b]begin[/b]
  [b]if[/b] Self.SaveString(Options.LastNameField, Value) [b]then[/b]
    Self.fLastName:= Value;
[b]end[/b];

[b]constructor[/b] TCustomUser.Create(AOwner: TCustomUsers);
[b]begin[/b]
  Self.fOwner:= AOwner;
[b]end[/b];

[b]destructor[/b] TCustomUser.Destroy;
[b]begin[/b]

  [b]inherited[/b] Destroy;
[b]end[/b];

[b]function[/b] TCustomUser.ReadString(Field: String): String;
[b]var[/b]
  Q: TADOQuery;
[b]begin[/b]
  Result:= [teal]''[/teal];
  Q:= TADOQuery.Create([b]nil[/b]);
  [b]try[/b]
    Q.ConnectionString:= Self.fConnectionString;
    Q.SQL.Text:= [teal]'select '[/teal]+Field+[teal]' from ['[/teal]+Options.UserTable+[teal]'] where ['[/teal]+
      Options.IDField+[teal]'] = '[/teal]+IntToStr(Self.fID);
    Q.Open;
      [b]if[/b] [b]not[/b] Q.IsEmpty [b]then[/b] [b]begin[/b]
        Q.First;
        Result:= Q.FieldByName(Field).AsString;
      [b]end[/b];
    Q.Close;
  [b]finally[/b]
    [b]if[/b] assigned(Q) [b]then[/b] [b]begin[/b]
      [b]if[/b] Q.Active [b]then[/b] Q.Close;
      Q.Free;
    [b]end[/b];
  [b]end[/b];
[b]end[/b];

[b]function[/b] TCustomUser.ReadInteger(Field: String): Integer;
[b]var[/b]
  Q: TADOQuery;
[b]begin[/b]
  Result:= [purple]0[/purple];
  Q:= TADOQuery.Create([b]nil[/b]);
  [b]try[/b]
    Q.ConnectionString:= Self.fConnectionString;
    Q.SQL.Text:= [teal]'select '[/teal]+Field+[teal]' from ['[/teal]+Options.UserTable+[teal]'] where ['[/teal]+
      Options.IDField+[teal]'] = '[/teal]+IntToStr(Self.fID);
    Q.Open;
      [b]if[/b] [b]not[/b] Q.IsEmpty [b]then[/b] [b]begin[/b]
        Q.First;
        Result:= Q.FieldByName(Field).AsInteger;
      [b]end[/b];
    Q.Close;
  [b]finally[/b]
    [b]if[/b] assigned(Q) [b]then[/b] [b]begin[/b]
      [b]if[/b] Q.Active [b]then[/b] Q.Close;
      Q.Free;
    [b]end[/b];
  [b]end[/b];
[b]end[/b];

[b]function[/b] TCustomUser.SaveString(Field: String; Value: String): Bool;
[b]var[/b]
  Q: TADOQuery;
[b]begin[/b]
  Result:= False;
  Q:= TADOQuery.Create([b]nil[/b]);
  [b]try[/b]
    Q.ConnectionString:= Self.fConnectionString;
    Q.SQL.Text:= [teal]'select '[/teal]+Field+[teal]' from ['[/teal]+Options.UserTable+[teal]'] where ['[/teal]+
      Options.IDField+[teal]'] = '[/teal]+IntToStr(Self.fID);
    Q.Open;
      [b]if[/b] [b]not[/b] Q.IsEmpty [b]then[/b] [b]begin[/b]
        Q.First;
        [b]try[/b]
          Q.Edit;
            Q[Field]:= Value;
          Q.Post;
          Result:= True;
        [b]except[/b]
          [b]on[/b] e: exception [b]do[/b] [b]begin[/b]
            Result:= False;
          [b]end[/b];
        [b]end[/b];
      [b]end[/b];
    Q.Close;
  [b]finally[/b]
    [b]if[/b] assigned(Q) [b]then[/b] [b]begin[/b]
      [b]if[/b] Q.Active [b]then[/b] Q.Close;
      Q.Free;
    [b]end[/b];
  [b]end[/b];
[b]end[/b];

[b]function[/b] TCustomUser.SaveInteger(Field: String; Value: Integer): Bool;
[b]var[/b]
  Q: TADOQuery;
[b]begin[/b]
  Result:= False;
  Q:= TADOQuery.Create([b]nil[/b]);
  [b]try[/b]
    Q.ConnectionString:= Self.fConnectionString;
    Q.SQL.Text:= [teal]'select '[/teal]+Field+[teal]' from ['[/teal]+Options.UserTable+[teal]'] where ['[/teal]+
      Options.IDField+[teal]'] = '[/teal]+IntToStr(Self.fID);
    Q.Open;
      [b]if[/b] [b]not[/b] Q.IsEmpty [b]then[/b] [b]begin[/b]
        Q.First;
        [b]try[/b]
          Q.Edit;
            Q[Field]:= Value;
          Q.Post;
          Result:= True;
        [b]except[/b]
          [b]on[/b] e: exception [b]do[/b] [b]begin[/b]
            Result:= False;
          [b]end[/b];
        [b]end[/b];
      [b]end[/b];
    Q.Close;
  [b]finally[/b]
    [b]if[/b] assigned(Q) [b]then[/b] [b]begin[/b]
      [b]if[/b] Q.Active [b]then[/b] Q.Close;
      Q.Free;
    [b]end[/b];
  [b]end[/b];
[b]end[/b];

[b]function[/b] TCustomUser.GetOptions: TUserTableOptions;
[b]begin[/b]
  Result:= Self.fOwner.Options;
[b]end[/b];






[b]function[/b] TCustomUsers.GetUser(Index: Integer): TCustomUser;    
[b]begin[/b]
  [b]if[/b] (Index >= [purple]0[/purple]) [b]and[/b] (Index < fUserList.Count) [b]then[/b]
    Result:= TCustomUser(Self.fUserList.Objects[Index])
  [b]else[/b] [b]begin[/b]
    Result:= [b]nil[/b];
    Raise Exception.Create([teal]'UserList Index Out Of Bounds ('[/teal]+IntToStr(Index)+[teal]')'[/teal]);
  [b]end[/b];
[b]end[/b];

[b]constructor[/b] TCustomUsers.Create(AOwner: TComponent);    
[b]begin[/b]
  [b]inherited[/b] Create(AOwner);
  Self.fUserList:= TStringList.Create;
  Self.fOptions:= TUserTableOptions.Create;
    [navy][i]//Set Defaults
[/i][/navy]    fOptions.UserTable:= [teal]'Users'[/teal]; 
    fOptions.IDField:= [teal]'ID'[/teal];
    fOptions.UserNameField:= [teal]'Login'[/teal];
    fOptions.FirstNameField:= [teal]'FName'[/teal];
    fOptions.LastNameField:= [teal]'LName'[/teal];
[b]end[/b];

[b]destructor[/b] TCustomUsers.Destroy;
[b]var[/b]
  X: Integer;
[b]begin[/b]
  [b]for[/b] X:= [purple]0[/purple] [b]to[/b] fUserList.Count - [purple]1[/purple] [b]do[/b]
    fUserList.Objects[X].Free;
  fUserList.Free;
  [b]if[/b] assigned(Self.fOptions) [b]then[/b] Self.fOptions.Free;
  [b]inherited[/b] Destroy;
[b]end[/b];

[b]procedure[/b] TCustomUsers.Refresh;
[b]var[/b]
  X: Integer;
  U: TCustomUser;
  Q: TADOQuery;
[b]begin[/b]
  [b]for[/b] X:= [purple]0[/purple] [b]to[/b] Self.fUserList.Count - [purple]1[/purple] [b]do[/b]
    Self.fUserList.Objects[X].Free;
  Self.fUserList.Clear;
  Q:= TADOQuery.Create([b]nil[/b]);
  [b]try[/b]
    Q.ConnectionString:= Self.fConnectionString;
    Q.SQL.Text:= [teal]'Select ['[/teal]+ Options.IDField +[teal]'] from ['[/teal]+ Options.UserTable +[teal]']'[/teal];
    Q.Open;
      [b]if[/b] [b]not[/b] Q.IsEmpty [b]then[/b] [b]begin[/b]
        Q.First;
        While Not Q.Eof [b]do[/b] [b]begin[/b]
          U:= TCustomUser.Create(Self);
            U.fID:= Q.FieldByName(Options.IDField).AsInteger;
            U.fConnectionString:= Self.fConnectionString;
          Self.fUserList.AddObject(IntToStr(U.ID), U);
          Q.Next;
        [b]end[/b];
      [b]end[/b];
    Q.Close;
  [b]finally[/b]
    [b]if[/b] assigned(Q) [b]then[/b] [b]begin[/b]
      [b]if[/b] Q.Active [b]then[/b] Q.Close;
      Q.Free;
    [b]end[/b];
  [b]end[/b];
[b]end[/b];

[b]procedure[/b] TCustomUsers.SetConnectionString(Value: String);
[b]var[/b]
  X: Integer;
  U: TCustomUser;
[b]begin[/b]
  Self.fConnectionString:= Value;
  [b]for[/b] X:= [purple]0[/purple] [b]to[/b] Self.fUserList.Count - [purple]1[/purple] [b]do[/b] [b]begin[/b]
    U:= TCustomUser(fUserList.Objects[X]);
    U.ConnectionString:= Value;
  [b]end[/b];
[b]end[/b];

[b]function[/b] TCustomUsers.GetCount: Integer;
[b]begin[/b]
  Result:= Self.fUserList.Count;
[b]end[/b];


[b]end[/b].

Implementation of TCustomUsers

Delphi 7 Unit: uMain
Form: Form1
Controls: ListBox1: TListBox, CustomUsers1: TCustomUsers
Methods: Form1.FormCreate

Code: 
[navy][i]// for automatic syntax highlighting see faq102-6487 
[/i][/navy]
[b]unit[/b] uMain;

[b]interface[/b]

[b]uses[/b]
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, UserWrap, StdCtrls;

[b]type[/b]
  TForm1 = [b]class[/b](TForm)
    CustomUsers1: TCustomUsers;
    ListBox1: TListBox;
    [b]procedure[/b] FormCreate(Sender: TObject);
  [b]private[/b]
    [navy][i]{ Private declarations }[/i][/navy]
  [b]public[/b]
    [navy][i]{ Public declarations }[/i][/navy]
  [b]end[/b];

[b]var[/b]
  Form1: TForm1;

[b]implementation[/b]

[navy][i]{$R *.dfm}[/i][/navy]

[b]procedure[/b] TForm1.FormCreate(Sender: TObject);
[b]var[/b]
  X: Integer;
  U: TCustomUser;
  Str: String;
[b]begin[/b]
  CustomUsers1.Refresh;
  ListBox1.Clear;
  [b]for[/b] X:= [purple]0[/purple] [b]to[/b] CustomUsers1.Count - [purple]1[/purple] [b]do[/b] [b]begin[/b]
    U:= CustomUsers1.Users[X];
    Str:= U.UserName + [teal]' - '[/teal] + U.FirstName + [teal]' '[/teal] + U.MiddleName + [teal]' '[/teal] + U.LastName;
    Str:= Str + [teal]' | '[/teal]+ U.NickName;
    ListBox1.Items.Append(Str);
  [b]end[/b];
[b]end[/b];

[b]end[/b].
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top