Using Delphi’s Database Components

August 6, 2024

Using Delphi’s Database Components

Delphi is renowned for its robust database capabilities, allowing developers to easily connect their applications to various databases. In this post, we’ll explore Delphi’s database components, how to set them up, and perform basic database operations such as creating, reading, updating, and deleting records.

Connecting to a Database

To connect your Delphi application to a database, you will primarily use the TDatabase component. This component serves as a bridge between your application and the database.

Here’s how to establish a connection:

var
  Database: TDatabase;
begin
  Database := TDatabase.Create(nil);
  try
    Database.DatabaseName := 'MyDatabase';
    Database.Params.Add('USER NAME=myUsername');
    Database.Params.Add('PASSWORD=myPassword');
    Database.Connected := True;
  finally
    Database.Free;
  end;
end;

In the example above, we create a new instance of TDatabase, set the database name, provide user credentials, and then connect to the database.

Using the TQuery Component

Once you have established a connection, you can use the TQuery component to execute SQL statements and retrieve data from the database.

Here’s how to perform a simple SELECT operation:

var
  Query: TQuery;
  Result: TStringList;
begin
  Query := TQuery.Create(nil);
  Result := TStringList.Create;
  try
    Query.DatabaseName := 'MyDatabase';
    Query.SQL.Text := 'SELECT * FROM MyTable';
    Query.Open;
    while not Query.Eof do
    begin
      Result.Add(Query.FieldByName('MyField').AsString);
      Query.Next;
    end;
  finally
    Query.Free;
    Result.Free;
  end;
end;

In this code snippet, we create a TQuery instance, set the SQL command to select all records from MyTable, and then iterate through the results, adding each field’s value to a TStringList.

Basic Database Operations

Now that we can connect to a database and query it, let’s look at the basic operations: Create, Read, Update, and Delete (CRUD).

Creating Records

To insert a new record into the database, we can use the TQuery component again:

var
  Query: TQuery;
begin
  Query := TQuery.Create(nil);
  try
    Query.DatabaseName := 'MyDatabase';
    Query.SQL.Text := 'INSERT INTO MyTable (MyField) VALUES (''NewValue'')';
    Query.ExecSQL;
  finally
    Query.Free;
  end;
end;

Reading Records

As shown earlier, you can read records using a SELECT query with the TQuery component.

Updating Records

To update existing records, you can use the following code:

var
  Query: TQuery;
begin
  Query := TQuery.Create(nil);
  try
    Query.DatabaseName := 'MyDatabase';
    Query.SQL.Text := 'UPDATE MyTable SET MyField = ''UpdatedValue'' WHERE MyField = ''OldValue''';
    Query.ExecSQL;
  finally
    Query.Free;
  end;
end;

Deleting Records

Finally, to delete records, you can use a DELETE SQL command:

var
  Query: TQuery;
begin
  Query := TQuery.Create(nil);
  try
    Query.DatabaseName := 'MyDatabase';
    Query.SQL.Text := 'DELETE FROM MyTable WHERE MyField = ''ValueToDelete''';
    Query.ExecSQL;
  finally
    Query.Free;
  end;
end;

Conclusion

In this post, we’ve covered the basics of using Delphi’s database components to connect to a database and perform CRUD operations. With these foundational skills, you can start building more complex applications that interact with data effectively. Happy coding!