Updating Multiple Tables using single connection object in System.Data.SQLite

Question

I am using Compact Framework SQLite ADO adapter(System.Data.SQLite) to update the database on my device. I am storing my DB in file. The DB contains 3-4 table which may get access concurrently. My query is, can I execute insert, update, read on these tables using single SQLiteConnection object?

Thanks, Omky

Answer 1

can I execute insert, update, read on these tables using single SQLiteConnection object?

Absolutely. Yes.

See how I pass an open SQLiteConnection object to the foreach loop in my one public Save(Location) method to save the data in the other tables:

public static int Save(Location location) {
  if ((location == null) || (location.Text == Location.NEW_LOCATION)) {
    return 0;
  }
  location.Result = 0;
  using (SQLiteConnection con = GetConnection) {
    try {
      con.Open();
      using (SQLiteTransaction txn = con.BeginTransaction()) {
        if (!String.IsNullOrEmpty(location.Text) && (location.Text != Location.NEW_LOCATION) && location.HasChanged) {
          string description = (location.Description != Location.NEW_LOCATION) ? location.Description : null;
          using (SQLiteCommand cmd = new SQLiteCommand(null, con)) {
            cmd.CommandText = Location.SQL_UPDATE;
            cmd.Parameters.AddWithValue(Location.AT_ID, location.ID);
            cmd.Parameters.AddWithValue(Location.AT_NUSE, location.InUse);
            cmd.Parameters.AddWithValue(Location.AT_TEXT, location.Text);
            if (location.Text == location.Description) {
              cmd.Parameters.AddWithValue(Location.AT_DESC, DBNull.Value);
            } else {
              cmd.Parameters.AddWithValue(Location.AT_DESC, location.Description);
            }
            location.Result = cmd.ExecuteNonQuery();
            location.HasChanged = false;
          }
        }
        foreach (var tank in location.Tanks) {
          tank.LocationID = location.ID;
          location.Result += Save(tank, con);
        }
        txn.Commit();
      }
      if (0 < location.Result) {
        dataSet.Tables.Remove(Location.TABLENAME);
        DataTable locationTable = new DataTable(Location.TABLENAME);
        using (SQLiteCommand cmd = new SQLiteCommand(Location.SQL_SELECT, con)) {
          locationTable.Load(cmd.ExecuteReader());
        }
        dataSet.Tables.Add(locationTable);
      }
    } catch (SQLiteException err) {
      location.Result = -1;
      LogError("Save(Location)", err);
    } finally {
      con.Close();
    }
  }
  return location.Result;
}

Here is my private Save(Tank) method that takes the open SQLiteConnection, which in turn calls another private Save(Task) in a similar foreach loop:

private static int Save(Tank tank, SQLiteConnection con) {
  if ((tank == null) || (tank.Text == Location.NEW_TANK)) {
    return 0;
  }
  if (tank.LocationID < 0) {
    throw ExceptionFor("Location");
  }
  tank.Result = 0;
  if (!String.IsNullOrEmpty(tank.Text) && (tank.Text != Tank.NEW_TANK) && tank.HasChanged) {
    object description = (tank.Description != tank.Text) ? tank.Description : null;
    using (SQLiteCommand cmd = new SQLiteCommand(Tank.SQL_UPDATE, con)) {
      cmd.Parameters.AddWithValue(Tank.AT_ID, tank.ID);
      cmd.Parameters.AddWithValue(Tank.AT_LOC, tank.LocationID);
      cmd.Parameters.AddWithValue(Tank.AT_NUSE, tank.InUse);
      cmd.Parameters.AddWithValue(Tank.AT_TEXT, tank.Text);
      cmd.Parameters.AddWithValue(Tank.AT_DESC, description);
      try {
        tank.Result = cmd.ExecuteNonQuery();
      } catch (SQLiteException err) {
        tank.Result = -1;
        LogError("Save(Tank)", err);
      }
    }
  }
  if (0 < tank.Result) {
    tank.HasChanged = false;
    dataSet.Tables.Remove(Tank.TABLENAME);
    DataTable tanksTable = new DataTable(Tank.TABLENAME);
    using (SQLiteCommand cmd = new SQLiteCommand(Tank.SQL_SELECT, con)) {
      tanksTable.Load(cmd.ExecuteReader());
    }
    dataSet.Tables.Add(tanksTable);
    tank.Feeding.TankID = tank.ID;
    tank.Cleaning.TankID = tank.ID;
    tank.Filters.TankID = tank.ID;
  }
  tank.Result += Save(tank.Feeding, con);
  tank.Result += Save(tank.Cleaning, con);
  tank.Result += Save(tank.Filters, con);
  return tank.Result;
}

This is my private Save(Task) method which also takes the same open SQLiteConnection object:

private static int Save(Task task, SQLiteConnection con) {
  if (task.TankID < 0) {
    throw ExceptionFor("Tank");
  }
  //task.Date.TaskID = task.ID;
  task.Result = 0;
  task.DateResult = 0;
  if (!String.IsNullOrEmpty(task.Text)) {
    if (task.HasChanged || task.DateHasChanged) {
      using (SQLiteCommand cmd = new SQLiteCommand(Task.SQL_UPDATE, con)) {
        cmd.Parameters.AddWithValue(Task.AT_ID, task.ID);
        cmd.Parameters.AddWithValue(Task.AT_TANK, task.TankID);
        cmd.Parameters.AddWithValue(Task.AT_NUSE, task.InUse);
        cmd.Parameters.AddWithValue(Task.AT_TEXT, task.Text);
        cmd.Parameters.AddWithValue(Task.AT_LASTID, task.DateID);
        cmd.Parameters.AddWithValue(Task.AT_NUMBER, task.Number);
        cmd.Parameters.AddWithValue(Task.AT_UNIT, task.Units);
        if (task.Text == task.Description) {
          cmd.Parameters.AddWithValue(Task.AT_DESC, DBNull.Value);
        } else {
          cmd.Parameters.AddWithValue(Task.AT_DESC, task.Description);
        }
        try {
          if (task.HasChanged) {
            task.Result = cmd.ExecuteNonQuery();
          }
          if (0 < Save(task.GetDate(), con)) {
            if (task.DateHasChanged) {
              task.Date(GetLastInsertRowID(con), task.ID, DateTime.Today);
              cmd.Parameters[Task.AT_LASTID].Value = task.DateID;
              task.Result += cmd.ExecuteNonQuery();
            }
          }
        } catch (SQLiteException err) {
          task.Result = -1;
          LogError("Save(Task)", err);
        }
      }
      if (0 < task.Result) {
        dataSet.Tables.Remove(Task.TABLENAME);
        DataTable tasksTable = new DataTable(Task.TABLENAME);
        using (SQLiteCommand cmd = new SQLiteCommand(Task.SQL_SELECT, con)) {
          tasksTable.Load(cmd.ExecuteReader());
        }
        dataSet.Tables.Add(tasksTable);
      }
      task.HasChanged = false;
    }
  }
  return task.Result;
}

Answer 2

I found this [link][1] [1]: http://sqlite.phxsoftware.com/forums/t/56.aspx And found that it is not advisable.

Thanks, Omky