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()
sqlite insertRow question!
Re: sqlite insertRow question!
Try this code:
The DDL script for the DEPT table is here: "C:\Program Files (x86)\Devart\dotConnect\SQLite\Samples\tables.sql".
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();
}
Re: sqlite insertRow question!
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!
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!
Re: sqlite insertRow question!
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 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?
The SQLite documentation https://www.sqlite.org/np1queryprob.html says that both approaches should be fast:khanh wrote: ↑Wed 11 Nov 2020 13:52question2: 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..)
"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
Re: sqlite insertRow question!
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.
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.