Count records and return total records number in real time then display it as text in a widget

Question

I'm trying to get total number of records sqlite database table called 'persons'. After getting the total number of records I want to show this number in a Container widget inside a Text() widget. I have tried different ways to make this work but not any method I've used is working.

Below is db_helper.dart file. The funtion that needs to return total number of records is being defined as countPeople.

import 'dart:async';
import 'dart:io' as io;
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

/// models

import 'package:path/path.dart';

class DBHelper {
  static Database db_instance;

  Future<Database> get db async {
    if (db_instance == null) {
      db_instance = await initDB();
    }
    return db_instance;
  }

  initDB() async {
    var databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'development.db');
    var db = await openDatabase(path, version: 1, onCreate: onCreateFunc);
    return db;
  }

  void onCreateFunc(Database db, int version) async {
    // create table
    await db.execute('''CREATE TABLE persons(
      id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, nickname TEXT, country_id INTEGER, role TEXT, created DATETIME, updated DATETIME
    );''');
  }

  /**
   * crud functions
   */

  void countPeople() async {
    var db_connection = await db;
    final count = Sqflite.firstIntValue(
        await db_connection.rawQuery('SELECT COUNT(*) FROM persons'));
    // assert(count == 2);
    print('people count: $count');
  }
  Future<List<Person>> getAllPersons() async {
    final db_connection = await db;
    var response = await db_connection
        .rawQuery("SELECT * FROM persons ORDER BY created DESC");
    // print(response);
    List<Person> list = response.map((c) => Person.fromJson(c)).toList();
    return list;
  }

  // add new person
  void addNewPerson(Person person) async {
    var db_connection = await db;
    String query = """
        INSERT INTO persons(name,nickname,role,created,updated) VALUES('${person.name}','${person.nickname}', '${person.role}','${person.created}', '${person.updated}')
        """;
    await db_connection.transaction((transaction) async {
      // print(query);
      return await transaction.rawInsert(query);
    });
  }

Below is people_count.dart file. This is where I want the total record count number to be shown inside the Text() widget.

import 'package:flutter/material.dart';
import 'package:com.example.simple_app/utils/db_helper.dart';
import 'package:com.example.simple_app/models/person.dart';
import 'package:com.example.simple_app/pages/main_user_registration/create_account.dart';

class MainUserRegistration extends StatefulWidget {
  MainUserRegistration({Key key}) : super(key: key);

  @override
  _MainUserRegistrationState createState() => _MainUserRegistrationState();
}

var dbHelper = DBHelper();

class _MainUserRegistrationState extends State<MainUserRegistration> {
  var result = dbHelper.countPeople();

  @override
  Widget build(BuildContext context) {
    return new Scaffold(
      appBar: new AppBar(
        title: Text('Loans'),
      ),
      body: new Container(
        child: Row(
          children: <Widget>[
            Text('all persons: ${dbHelper.countPeople()}'), // the total number of records should appear here
          ],
        ),
      ),
    );
  }
}

Answer 1

You should probably try using setState() to change async data on initState, here is what the people_count.dart would look like:

import 'package:flutter/material.dart';
import 'package:com.example.simple_app/utils/db_helper.dart';
import 'package:com.example.simple_app/models/person.dart';
import 'package:com.example.simple_app/pages/main_user_registration/create_account.dart';

class MainUserRegistration extends StatefulWidget {
  MainUserRegistration({Key key}) : super(key: key);

  @override
  _MainUserRegistrationState createState() => _MainUserRegistrationState();
}

var dbHelper = DBHelper();

class _MainUserRegistrationState extends State<MainUserRegistration> {
  int number = -1;

  void countPeople() async {
    int count = await dbHelper.countPeople();
    setState(() => number = count);

  @override
  void initState() {
    super.initState();
    countPeople();
  }

  @override
  Widget build(BuildContext context) {
    return new Scaffold(
      appBar: new AppBar(
        title: Text('Money Lender'),
      ),
      body: new Container(
        child: Row(
          children: <Widget>[
            Text('all persons: ${number}'), // the total number of records should appear here
          ],
        ),
      ),
    );
  }
}

And the count people function in the db_helper.dart should return the number of people:

Future<int>countPeople() async {
    var db_connection = await db;
    final count = Sqflite.firstIntValue(
        await db_connection.rawQuery('SELECT COUNT(*) FROM persons'));
    // assert(count == 2);
    print('people count: $count');
    return count;
  }