Last active
May 21, 2024 06:24
-
-
Save ankesh-kumar/698819e25534c79ab91fa2604912e24c to your computer and use it in GitHub Desktop.
sqlite database helper file flutter
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import 'dart:io'; | |
import 'package:flutter/material.dart'; | |
import 'package:path/path.dart'; | |
import 'package:path_provider/path_provider.dart'; | |
import 'package:sqflite/sqflite.dart'; | |
import 'package:flutDBHelper/screens/models/FlutterDBHelperModelClass.dart'; | |
class DatabaseHelper { | |
static final _databaseName = "flutDB.db"; | |
static final _databaseVersion = 1; | |
static final table = 'tbl_new_db'; | |
static final id=1; | |
static final stCol1="stCol1"; | |
static final stCol2="stCol2"; | |
// make this a singleton class | |
DatabaseHelper._privateConstructor(); | |
static final DatabaseHelper instance = DatabaseHelper._privateConstructor(); | |
// only have a single app-wide reference to the database | |
static Database _database; | |
Future<Database> get database async { | |
if (_database != null) return _database; | |
// lazily instantiate the db the first time it is accessed | |
_database = await _initDatabase(); | |
return _database; | |
} | |
// this opens the database (and creates it if it doesn't exist) | |
_initDatabase() async { | |
Directory documentsDirectory = await getApplicationDocumentsDirectory(); | |
String path = join(documentsDirectory.path, _databaseName); | |
return await openDatabase(path, | |
version: _databaseVersion, | |
onCreate: _onCreate); | |
} | |
// SQL code to create the database table | |
Future _onCreate(Database db, int version) async { | |
await db.execute(''' | |
CREATE TABLE $table ( | |
id INTEGER PRIMARY KEY, $stCol1 TEXT,$stCol2 TEXT | |
) | |
'''); | |
} | |
// Helper methods | |
// Inserts a row in the database where each key in the Map is a column name | |
// and the value is the column value. The return value is the id of the | |
// inserted row. | |
Future insert(FlutterDBHelperModelClass row) async { | |
// Get a reference to the database. | |
final Database db = await database; | |
try { | |
await db.insert( | |
table, | |
row.toMap(), | |
conflictAlgorithm: ConflictAlgorithm.replace, | |
); | |
print('Db Inserted'); | |
} | |
catch(e){ | |
print('DbException'+e.toString()); | |
} | |
} | |
// All of the rows are returned as a list of maps, where each map is | |
// a key-value list of columns. | |
Future<List<Map<String, dynamic>>> queryAllRows() async { | |
Database db = await instance.database; | |
return await db.query(table); | |
} | |
Future<List<Map<String, dynamic>>> queryFilterRows() async { | |
Database db = await instance.database; | |
return await db.rawQuery("select * from $table where stCol2='111'"); | |
} | |
// All of the methods (insert, query, update, delete) can also be done using | |
// raw SQL commands. This method uses a raw query to give the row count. | |
Future<int> queryRowCount() async { | |
Database db = await instance.database; | |
return Sqflite.firstIntValue(await db.rawQuery('SELECT COUNT(*) FROM $table')); | |
} | |
// We are assuming here that the id column in the map is set. The other | |
// column values will be used to update the row. | |
// Future<int> update(Map<String, dynamic> row) async { | |
// Database db = await instance.database; | |
// int id = row[columnId]; | |
// return await db.update(table, row, where: '$columnId = ?', whereArgs: [id]); | |
// } | |
// Deletes the row specified by the id. The number of affected rows is | |
// returned. This should be 1 as long as the row exists. | |
Future<int> delete(int id) async { | |
Database db = await instance.database; | |
return await db.delete(table); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
class ModelClass{ | |
int id; | |
String stCol1=""; | |
String stCol2=""; | |
ModelClass({ | |
this.id, | |
this.stCol1, | |
this.stCol2}); | |
@override | |
String toString() { | |
return 'ModelClass{id:$id,stCol1: $stCol1, stCol2: $stCol2}'; | |
} | |
Map<String,dynamic> toMap() { | |
return { | |
'id':id, | |
'stCol1': stCol1, | |
'stCol2': stCol2 | |
}; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment