Insert array to db

[vannakyet]

Hello may i know How to insert array to db?

// Prepare a statement to run it multiple times:
final stmt = db.prepare('INSERT INTO artists (name) VALUES (?)');
stmt
..execute(['The Beatles'])
..execute(['Led Zeppelin'])
..execute(['The Who'])
..execute(['Nirvana']);

// Dispose a statement when you don't need it anymore to clean up resources.
stmt.dispose();

i see the example but i don't how to loop insert

[simolus3]

i see the example but i don't how to loop insert

Well, you can pretty much just use a loop:

final artists = ['The Beatles', 'Led Zeppelin', 'The Who', 'Nirvana'];
final stmt = db.prepare('INSERT INTO artists (name) VALUES (?)');
try {
  for (final artist in artists) {
    stmt.execute([artist]);
  }
} finally {
  stmt.dispose();
}

Is that what you were looking for?

[vannakyet]

yes but when i loop to insert data the application will be Freeze for the loop insert

[simolus3]

How much data are you inserting? This library is synchronous, so it will block while the operation is happening.

You might be able to speed things up by running db.execute('BEGIN'); before and db.execute('COMMIT'); after the loop. That way, sqlite3 will only have to commit the database once and not for each write. If that doesn't fix the problem, you probably have to run the insert on another isolate.

[andre-ab]

The output of the sql string must be this
"INSERT INTO table_name (name_product,qty,price,discount) table_name VALUES (?), (?), (?), (?), (?), (?), (?), (?)"

execute should receive a list of data: "creatSql.valuesList"

[Teste01, 1, 1, 1, Teste02, 2, 2.5, 2]

and make a loading progress, to signal that there is a process in progress for your user

void main() {
  List<Map<String, dynamic>> dataList = [
    {
      "name_product": 'Teste01',
      "qty": 1,
      "price": 1.0,
      "discount": 1.0,
    },
    {
      "name_product": 'Teste02',
      "qty": 2,
      "price": 2.50,
      "discount": 2.0,
    }
  ];

  CreatSql creatSql = CreatSql();
  creatSql.toPreperSqle(dataList);

  String sql = creatSql.sql;

  try {
    // user transaction
    // sqlite3.execute('BEGIN;');
    final stmt = sqlite3.prepare(sql);
    stmt.execute(creatSql.valuesList);
    // end transaction
    // sqlite.execute('COMMIT;'); 
      sqlit3.dispose();
  } catch (e) {
    // sqlite.execute('ROLLBACK;');
    sqlit3.dispose();
    
  }
}

class CreatSql {
  List<dynamic>? valuesList;
  String _preperer ='';
  String _sql ='';
  
 String get sql => _sql;
//   CreatSql({this.valuesList, this.preperer ='', this.sql=''});
  late String _keys;
 
  toPreperSqle(List<Map<String, dynamic>> dataList) {
    // convert List in string
    _keys = dataList[0].keys.join(',');

    for (var val in dataList) {
      _returnValuesMap(val);
//     print(val);
    }
    String tabelName = '`table_name`';
    _sql = ''' INSERT INTO $tabelName ($_keys) $tabelName VALUES $_preperer''';
    _sql = _sql.substring(0,_sql.length - 2);
  }

  _returnValuesMap(Map values) {
// convert value of Map to list
     List<dynamic> valuesListAux = [];
    values.forEach((key, val) {
      valuesListAux.add(val);
      _preperer+='(?), ';
      
    });
    valuesList = [...valuesList ?? [], ...valuesListAux];
  }

}