Page 1 of 1
"Bulk INSERT"
Posted: Fri 31 Jan 2014 19:02
by Yahia
Hi,
SQLite support so-called "bulk insert" sincs V 3.7.11 - see
http://www.sqlite.org/lang_insert.html
In dotconnect for Oracle we have an ExecuteArray method which basically does something similar...
Can you provide an example on how to do use bulk insert in SQLite?
Thanks in advance.
Best Regards
Yahia
Re: "Bulk INSERT"
Posted: Mon 03 Feb 2014 15:47
by Pinturiccio
dotConnect for SQLite supports SQLIte 3.7.11 in dotConnect for SQLite 3.80.341. You can use the features described in the
http://www.sqlite.org/lang_insert.html article since dotConnect for SQLite 3.80.341.
Can you provide an example on how to do use bulk insert in SQLite?
Here is an example of using bulk insert in SQLite:
Code: Select all
static void Main(string[] args)
{
SQLiteConnection conn = new SQLiteConnection("Data Source=test.db;FailIfMissing=false");
conn.Open();
ValueExpr(conn);
InsertSelect(conn);
DefaultValue(conn);
}
static void ValueExpr(SQLiteConnection conn)
{
SQLiteCommand comm = new SQLiteCommand("CREATE TABLE Test(Id INTEGER PRIMARY KEY, Val INTEGER);", conn);
comm.ExecuteNonQuery();
comm.CommandText = "INSERT INTO Test(Val) VALUES (1), (3), (5), (6), (7), (8), (6), (4), (9);";
comm.ExecuteNonQuery();
ShowTable("Test", conn);
}
static void InsertSelect(SQLiteConnection conn)
{
SQLiteCommand comm = new SQLiteCommand("CREATE TABLE Test2(Id INTEGER PRIMARY KEY, Val INTEGER);", conn);
comm.ExecuteNonQuery();
comm.CommandText = "INSERT INTO Test2 SELECT * FROM Test";
comm.ExecuteNonQuery();
ShowTable("Test2", conn);
}
static void DefaultValue(SQLiteConnection conn)
{
SQLiteCommand comm = new SQLiteCommand("INSERT INTO Test2 DEFAULT VALUES", conn);
comm.ExecuteNonQuery();
ShowTable("Test2", conn);
}
static void ShowTable(String tableName, SQLiteConnection conn)
{
SQLiteCommand comm = new SQLiteCommand("select * from " + tableName, conn);
SQLiteDataReader reader = comm.ExecuteReader();
Console.WriteLine("Select from " + tableName);
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
Console.Write(reader.GetValue(i) + "\t");
Console.WriteLine();
}
Console.WriteLine();
}
Re: "Bulk INSERT"
Posted: Mon 03 Feb 2014 16:25
by Yahia
Thanks for the answer...
Basically this is only "half" the answer to my question...
I would like to use real parameters - in your code of
it is just a plain string...
I avoid that anytime possible since it is bad practice and a possible security problem...
With dotconnect for Oracle we can use parameters (arrays) together with
... thus we are on the "safe side"...
Is it possible to get a feature similar to what is available in dotconnect for Oracle, i.e.
?
Re: "Bulk INSERT"
Posted: Tue 04 Feb 2014 16:18
by Pinturiccio
Oracle has the functionality that allows us to implement the array binding feature in dotConnect for Oracle. Other databases does not provide the corresponding functionality, that's why array binding and the ExecuteArray method are implemented only in dotConnect for Oracle, and are not implemented in other providers, such as dotConnect for MySQL or dotConnect for PostgreSQL.
SQLite database, as well as others, doesn't have such functionality, and it's impossible to implement the array binding feature in dotConnect for SQLite.