此文目的,是因为在WPF程序中,新增记录后,原来新增记录没有保存ID属性,无法进行删除,因此完善ExecuteNonQuery方法,此方法在执行Insert 命令时,返回newId(一般来讲,均大于0),否则返回记录变化数。
项目实例
ViewModel:执行插入命令,返回新ID,
private void SaveRenKeItemCommandAction(object obj)
{
var item = renKeItem;
if (string.IsNullOrEmpty(item.RenKeBiaoName))
{
MessageBox.Show("课程名称不能为空", "提示", MessageBoxButton.OK, MessageBoxImage.Warning); return;
}
string sql = "Insert into RenKeBiao(RenKeBiaoName,ReMark) Values(@name,@remark)";
SQLiteParameter[] ps = {
new SQLiteParameter("@name",item.RenKeBiaoName),
new SQLiteParameter("@remark",item.ReMark)
};
int n = App.Db.ExecuteNonQuery(sql, ps);//返回新增ID
if ( n> 0)
{
item.Id = n;
RenKeList.Add(item);
VisibilityTag = Visibility.Collapsed;
}
}
关键的App.Db.ExecuteNonQuery
#region 执行插入语句并且返回当前的NewID
/// <summary>
/// 执行插入语句并且返回当前的ID
/// </summary>
/// <param name="SQLString"></param>
/// <param name="ps"></param>
/// <returns></returns>
public int ExecuteNonQuery(string SQLString, params SQLiteParameter[] ps)
{
using (SQLiteConnection connection = new SQLiteConnection(_SQLiteConnString))
{
using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.Parameters.AddRange(ps);
int rows = 0;
if (SQLString.ToLower().IndexOf("insert") != -1)
{
cmd.ExecuteScalar();
cmd.CommandText = "select last_insert_rowid() newid";
rows = Convert.ToInt32(cmd.ExecuteScalar());
}
else
rows = cmd.ExecuteNonQuery();
return rows;
}
catch (SQLiteException e)
{
connection.Close();
throw e;
}
}
}
}
#endregion
相关知识
SQLite的语法和sqlserver的语法是有一些区别的,比如插入数据而获得新id,在sql server中取新增记录自增字段ID,
SELECT @@IDENTITY [id]
SQLite 中不是这样
insert into...;
select last_insert_rowid() newid;
获得新id的函数是last_insert_rowid(),必须保证 同一SQLiteConnection下执行插入和查询