sqlite insertRow question!

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
khanh
Posts: 8
Joined: Mon 13 Apr 2020 21:20

sqlite insertRow question!

Post by khanh » Sat 07 Nov 2020 14:10

Hi,
From this https://www.devart.com/dotconnect/oracl ... mmand.htmlit seems unclear.

I am wanting to add multiple rows to the sqlite table.
I have tried the following 2 pieces code but only the first row in my table added. Can you clarify more?
X
sqlitecommand.CommandText ="INSERT INTO " ...
//row1:
sqlitecommand.Parameters.Add("param1", number(1))
sqlitecommand.Parameters.Add("param2", number(2))
//row2:
sqlitecommand.Parameters.Add("param1", number(3))
sqlitecommand.Parameters.Add("param2", number(4))
....
sqlitecommand.ExecuteNonQuery()

ex2
//row1:
sqlitecommand.CommandText ="INSERT INTO " ...
sqlitecommand.Parameters.Add("param1", number(1))
sqlitecommand.Parameters.Add("param2", number(2))

sqlitecommand.ExecuteNonQuery()
//row2:
sqlitecommand.CommandText ="INSERT INTO " ...
sqlitecommand.Parameters.Add("param1", number(3))
sqlitecommand.Parameters.Add("param2", number(4))
sqlitecommand.ExecuteNonQuery()

Shalex
Site Admin
Posts: 9115
Joined: Thu 14 Aug 2008 12:44

Re: sqlite insertRow question!

Post by Shalex » Mon 09 Nov 2020 20:02

Try this code:

Code: Select all

    using (var conn = new Devart.Data.SQLite.SQLiteConnection())
    {
        conn.ConnectionString = @"Data Source=D:\Database.db;";
        conn.Open();
        var cmd = conn.CreateCommand();
        cmd.CommandText = "INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (:p1, :p2, :p3)";
        cmd.Parameters.Add("p1", Devart.Data.SQLite.SQLiteType.Int32).Value = 1;
        cmd.Parameters.Add("p2", Devart.Data.SQLite.SQLiteType.Text).Value = "a";
        cmd.Parameters.Add("p3", Devart.Data.SQLite.SQLiteType.Text).Value = "a";
        cmd.ExecuteNonQuery();

        cmd.Parameters["p1"].Value = 2;
        cmd.Parameters["p2"].Value = "b";
        cmd.Parameters["p3"].Value = "b";
        cmd.ExecuteNonQuery();
    }
The DDL script for the DEPT table is here: "C:\Program Files (x86)\Devart\dotConnect\SQLite\Samples\tables.sql".

khanh
Posts: 8
Joined: Mon 13 Apr 2020 21:20

Re: sqlite insertRow question!

Post by khanh » Wed 11 Nov 2020 13:52

Hi shalex, your code worked for me.
more question:
I am using the paramtercheck feature:
however it did not detect the Text type even though all my settings for the column are text and its value is clearly text.
my image:
https://prnt.sc/vhf4za

I had to use your way: parameters.add (.., sqlite.type) .value =.. to work!
I mean parametercheck probably won't recognize the data type?

question2: I'm using transaction, assuming I have 25k rows of data so, can i pass them all to the table and then commit only once?
Or do I need to split them up, say 2,500 rows and commit 10 times?
(The reason is I read it somewhere need to split them to running faster..)
Please help me clarify this problem!

Shalex
Site Admin
Posts: 9115
Joined: Thu 14 Aug 2008 12:44

Re: sqlite insertRow question!

Post by Shalex » Fri 13 Nov 2020 11:01

khanh wrote:
Wed 11 Nov 2020 13:52
I am using the paramtercheck feature:
however it did not detect the Text type even though all my settings for the column are text and its value is clearly text.
my image:
https://prnt.sc/vhf4za

I had to use your way: parameters.add (.., sqlite.type) .value =.. to work!
I mean parametercheck probably won't recognize the data type?
Please refer to https://www.devart.com/dotconnect/sqlit ... eters.html > the "Using automatic parameters synchronization" section. If this doesn't help, send us a small test project so that we can reproduce the issue in our environment.
khanh wrote:
Wed 11 Nov 2020 13:52
question2: I'm using transaction, assuming I have 25k rows of data so, can i pass them all to the table and then commit only once?
Or do I need to split them up, say 2,500 rows and commit 10 times?
(The reason is I read it somewhere need to split them to running faster..)
The SQLite documentation https://www.sqlite.org/np1queryprob.html says that both approaches should be fast:
"SQLite can also do large and complex queries efficiently, just like client/server databases. But SQLite can do many smaller queries efficiently too. Application developers can use whichever technique works best for the task at hand."

JIC:
* passing all 25k rows within one commit will consume more RAM but should be faster
* committing every 2,500 rows: if some transaction fails, the previous commits will not be rolled back

khanh
Posts: 8
Joined: Mon 13 Apr 2020 21:20

Re: sqlite insertRow question!

Post by khanh » Fri 13 Nov 2020 17:25

Hi shalex,
question1: I made a few mistakes when there was more than one data type in the column, now parametercheck works as I expected
question2: thanks for the clarification, your answers helped me a lot.
My problem has been solved! Many thanks.

Post Reply