"Bulk INSERT"

[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

[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();
}

[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

Code: Select all

ValueExpr

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

Code: Select all

ExecuteArray

... 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.

Code: Select all

ExecuteArray

?

[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.