在System.Data.SQLite中使用单个连接对象更新多个表

Question

我正在使用Compact Framework SQLite ADO adapter(System.Data.SQLite)来更新我设备上的数据库。我将我的DB存储在文件中。数据库包含3-4个可并发访问的表。我的问题是，我是否可以使用单个SQLiteConnection对象对这些表执行插入、更新、读取操作？

谢谢，Omky

Answer 1

我找到了这个链接1：http://sqlite.phxsoftware.com/forums/t/56.aspx，并发现它是不可取的。

谢谢，Omky

Answer 2

是否可以使用单个SQLiteConnection对象对这些表执行插入、更新和读取操作？

绝对一点儿没错。是。

看看我如何将一个打开的SQLiteConnection对象传递给我的一个公共Save(Location)方法中的foreach循环，以便将数据保存到其他表中：

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;
}

下面是我的私有Save(Tank)方法，它接受打开的SQLiteConnection，然后在类似的foreach循环中调用另一个私有Save(Task)：

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;
}

这是我的私有Save(Task)方法，它也接受相同的打开SQLiteConnection对象：

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;
}