SQLite Forum

ADO.NET INSERT/UPDATE RETURNING statement question
Login

ADO.NET INSERT/UPDATE RETURNING statement question

(1) By leisaupei on 2021-08-17 11:13:49 [source]

Hello, I have a few question when i using INSERT/UPDATE RETURNING statement.

CREATE TABLE "test" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "name" text
);
INSERT INTO "test"("name") VALUES ('johnny') RETURNING "id","name";
UPDATE "test" as a set "name" = 'SAM' WHERE (a."id" = 1) RETURNING "id","name";
It worked, but

  1. The return value of [RecordsAffected] at System.Data.SQLite.SQLiteDataReader is -1, So I don't know how to judge thas rows has been affected.

  2. The return columns at System.Data.SQLite.SQLiteDataReader names '"id"' and '"name"', Column names containing quotation marks('"'), but this problem does not appear in SELECT statement.

Are they correct or should they be?

SQLite version is 3.55.5

Nuget Package is:

<PackageReference Include="System.Data.SQLite.Core" Version="1.0.114.3" />

(2) By Simon Slavin (slavin) on 2021-08-18 01:22:18 in reply to 1 [link] [source]

sqlite> .mode table
sqlite> INSERT INTO "test"("name") VALUES ('johnny2') RETURNING id,name;
+----+---------+
| id |  name   |
+----+---------+
| 4  | johnny2 |
+----+---------+
sqlite> INSERT INTO "test"("name") VALUES ('johnny3') RETURNING "id","name";
+----+---------+
| id |  name   |
+----+---------+
| 6  | johnny3 |
+----+---------+ 

I suspect this bug, if it is a bug, is in System.Data.SQLite . You have posted useful version numbers, so someone familiar with it should be able to help.

(3) By leisaupei on 2021-08-18 02:10:09 in reply to 2 [link] [source]

What about the return value of RecordsAffected at System.Data.SQLite.SQLiteDataReader is -1 question? Is that what you designed or is it a bug?

var affrows = 0;
long id = 0;
string name = null;
using (var conn = new SQLiteConnection(MainConnectionString))
{
	conn.Open();
	var cmd = conn.CreateCommand();
	cmd.CommandText = @"UPDATE ""test"" AS a SET ""name"" = 'Sam' WHERE (a.""id"" = 1) RETURNING id,name";
	var reader = cmd.ExecuteReader();
	while (reader.Read())
	{
		id = Convert.ToInt64(reader["id"]);
		name = reader["name"].ToString();
	}
	affrows = reader.RecordsAffected; // result is -1.
}