Updating multiple related tables in SQLite

Question

Just some background, sorry so long winded.

I'm using the System.Data.SQLite ADO.net adapter to create a local sqlite database and this will be the only process hitting the database, so I don't need to worry about concurrency.

I'm building the database from various sources and don't want to build this all in memory using datasets or dataadapters or anything like that. I want to do this using SQL (DdCommands). I'm not very good with SQL and complete noob in sqlite. I'm basically using sqlite as a local database / save file structure.

The database has a lot of related tables and the data has nothing to do with People or Regions or Districts, but to use a simple analogy, imagine:

Region table with auto increment RegionID, RegionName column and various optional columns.

District table with auto increment DistrictID, DistrictName, RegionId, and various optional columns

Person table with auto increment PersonID, PersonName, DistrictID, and various optional columns

So I get some data representing RegionName, DistrictName,PersonName, and other Person related data. The Region, District and/or Person may or may not be created at this point.

Once again, not being the greatest with this, my thoughts would be something like:

• Check to see if Region exists and if so get the RegionID
• else create it and get RegionID
• Check to see if District exists and if so get the DistrictID
• else create it adding in RegionID from above and get DistrictID
• Check to see if Person exists and if so get the PersonID
• else create it adding in DistrictID from above and get PersonID
• Update Person with rest of data.

In MS SQL Server I would create a stored procedure to handle all this.

Only way I can see to do this with sqlite is a lot of commands. So I'm sure I'm not getting this. I've spent hours looking around on various sites but just don't feel like I'm going down the right road. Any suggestions would be greatly appreciated.

Answer 1

Use last_insert_rowid() in conjunction with INSERT OR REPLACE. Something like:

INSERT OR REPLACE INTO Region (RegionName)
                       VALUES (:Region   );

INSERT OR REPLACE INTO District(DistrictName, RegionID           )
                        VALUES (:District   , last_insert_rowid());

INSERT OR REPLACE INTO Person(PersonName, DistrictID           )
                      VALUES (:Person   , last_insert_rowid());