How to bind data from SQLite to the Flutter Cartesian chart (SfCartesianChart) ?
The following steps explain how to add data to the SQLite database and bind that data to the Flutter SfCartesianChart widget.
Step 1: Add the following dependencies in the pubspec.yaml file and get the packages.
dependencies:
flutter:
sdk: flutter
// To process with the Syncfusion charts.
syncfusion_flutter_charts: ^19.1.59
// To process with the sqlite db.
sqflite: ^2.0.0+3
// To provide the path to store the data in the device.
path_provider: ^2.0.1
Step 2: Create the new file model.dart and create the SalesData model class to process the chart’s x and y values and define the table name of SQLite and the column names to be processed with the SQLite in the separate variables.
class SalesData {
//SQLite table name.
static const tblSales = 'salestable';
// x and y columns of the table.
static const salesXValue = 'xValue';
static const salesYValue = 'yValue';
SalesData({this.xValue, this.yValue});
num? xValue;
num? yValue;
}
Step 3: Since in SQLite database records are stored as a collection of map objects, declare the named constructor fromMap for initializing an instance from a given map object. And with toMap function, we will convert sales object to the corresponding map object.
SalesData.fromMap(Map<String, dynamic> map) {
xValue = map[salesXValue];
yValue = map[salesYValue];
}
Map<String, dynamic> toMap() {
var map = <String, dynamic>{salesXValue: xValue, salesYValue: yValue};
return map;
}
Step 4: Create the new file helper to handle the SQLite database-related operations by creating the class DataBaseHelper class.
class DataBaseHelper {
static const _databaseName = 'Sales.db';
static const _databaseVersion = 1;
//Singleton class
DataBaseHelper._();
static final DataBaseHelper instance = DataBaseHelper._();
DataBase? _database;
Future<DataBase> get database async {
if (_database != null) return _database!;
_database = await _initDataBase();
return _database!;
}
}
Define the database name, database version and declare a final property instance of the type DataBaseHelper. Also declare a public property named _database, which returns the DataBase instance. If _database is not initialized _initDataBase function will be called.
Step 5: Define the _initDataBase method as like the below in which the data directory is initialized with getApplicationDocumentsDirectory function, which is from path_provider package. This function returns the location where the database is stored, which is handled by the path_provider package. To connect and return the database reference openDataBase function is called.
_initDataBase() async {
Directory dataDirectory = await getApplicationDocumentsDirectory();
String dbPath = join(dataDirectory.path, _databaseName);
return await openDataBase(dbPath,
version: _databaseVersion, onCreate: _onCreateDB);
}
Step 6: If there is no database with the given database path, we have to create the database. That’s what we have done with onCreate property.
Future _onCreateDB(DataBase db, int version) async {
//create table query
await db.execute('''
CREATE TABLE ${SalesData.tblSales} (
${SalesData.salesXValue} REAL NOT NULL,
${SalesData.salesYValue} REAL NOT NULL
)
''');
}
Step 7: Now, the table is created from the above, so define the methods to add and fetch the data from the SQLite by defining the add(), getSales() methods as below.
// To insert data into the SQLite.
void add(SalesData sales) async {
var dbClient = await database;
// Insert query to insert data into the database
dbClient.insert(SalesData.tblSales, sales.toMap());
}
// To fetch data from the SQLite.
Future<List<SalesData>> getSales() async {
var dbClient = await database;
List<Map> maps = await dbClient.query(SalesData.tblSales,
columns: ['${SalesData.salesXValue}', '${SalesData.salesYValue}']);
// Adding the fetched data to the list to bind to the chart.
List<SalesData> students = [];
if (maps.length > 0) {
for (int i = 0; i < maps.length; i++) {
students.add(SalesData.fromMap(maps[i] as Map<String, dynamic>));
}
}
return students;
}
// To delete data from the database table which is in the given id.
void delete(int id) async {
var dbClient = await database;
dbClient.execute('delete from salestable where xValue = $id');
}
Now combing whole in our main file to process the operations with the SQLite database with SfCartesianChart widget.
Step 8: Declare the chart key and chart data as below,
final chartKey = GlobalKey<ChartState>();
List<SalesData> salesData = <SalesData>[];
Step 9: Define the SfCartesianChart widget with the required properties as below.
SfCartesianChart(series: <SplineSeries<SalesData, num>>[
SplineSeries<SalesData, num>(
animationDuration: 0,
dataSource: salesData,
xValueMapper: (SalesData sales, _) => sales.xValue,
yValueMapper: (SalesData sales, _) => sales.yValue,
name: 'Sales')
]
)
Step 10: Define the two buttons for performing the adding and deleting the data in the onPressed callback from the SQLite and the same in the Cartesian chart.
ElevatedButton(
onPressed: () async {
// Adding data to the database
dbHelper.add(
SalesData(xValue: count, yValue: getRandomInt(10, 20)));
// Fetching the data from the database
salesData = await dbHelper.getSales();
// Calling the chart state to redraw the chart
chartKey.currentState!.setState(() {});
count++;
},
child: Text('Add')
),
ElevatedButton(
onPressed: () async {
// To fetch the data from the database
salesData = await dbHelper.getSales();
if (salesData.isNotEmpty) {
data = salesData.last;
// Delete the last data from the database
dbHelper.delete(
salesData[salesData.indexOf(data)].xValue!.toInt());
// Get data after deleting the database
salesData = await dbHelper.getSales();
// Calling the chart state after deleting the data to redraw with the new data.
chartKey.currentState!.setState(() {});
count--;
}
},
child: Text('Delete')
)
After adding data to the database and fetch data from it by calling getSales method and call the chart state method to redraw the chart with the newly fetched data from the database.
Thus, we have bind that data to the SfCartesianChart widget using the SQLite database.
