参考:https://blog.csdn.net/penghao_1/article/details/82624453
首先创建两个类(数据库对象映射)
public class BOOKINFO
{
public string user_id { get; set; }
public string book_id { get; set; }
public int book_price { get; set; }
public int user_age { get; set; }
public string user_habbit { get; set; }
}
public class JOINTEST
{
public string user_id;
public string user_habbit;
}
获取连接字符串,用于之后连接数据库
private static string LoadConnectString(string id = "F:\\C#联合SQLite学习\\C#DapperSQLite\\WindowsFormsApplication1\\AllCode.db")
{
SQLiteConnection dbConnection = new SQLiteConnection();
SQLiteConnectionStringBuilder dbConnectionstr = new SQLiteConnectionStringBuilder();
dbConnectionstr.DataSource = id;
return dbConnectionstr.ToString();
}
插入单值
public static void MineInsert(BOOKINFO bookinfo)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
{
cnn.Execute("INSERT INTO bookinfo(user_id,book_id,book_price,user_age,user_habbit) VALUES (@user_id,@book_id,@book_price,@user_age,@user_habbit)", bookinfo);
}
}
插入多值
public static void MutiInsert(List<BOOKINFO> bookinfo)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
{
cnn.Execute("INSERT INTO bookinfo(user_id,book_id,book_price,user_age,user_habbit) VALUES (@user_id,@book_id,@book_price,@user_age,@user_habbit)", bookinfo);
}
}
删除指定数据
public void MineDelete(BOOKINFO bookinfo)
{
using(IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
{
cnn.Execute("DELETE FROM bookinfo WHERE user_id = @USER_ID",bookinfo);
}
}
删除多条数据
public void MutiDelete(List<BOOKINFO> bookinfo)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
{
cnn.Execute("DELETE FROM bookinfo WHERE user_id = @USER_ID", bookinfo);
}
}
查找指定数据
public void MineSelect(BOOKINFO bookinfo)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
{
cnn.Execute("SELECT * FROM bookinfo WHERE user_id = @USER_ID",bookinfo);
}
}
查找所有数据
public void MineSelectAll()
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
{
//var output = cnn.Execute("SELECT * FROM bookinfo");
var output = cnn.Query<BOOKINFO>("SELECT * FROM bookinfo");
foreach (var item in output)
{
string str = "编号为" + item.user_id + "的聊天内容" + item.book_id;
MessageBox.Show(str);
}
}
}
IN操作
public void MineIn<T>(T[] listtmp)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
{
var output = cnn.Query<BOOKINFO>("SELECT * FROM bookinfo WHERE user_id IN @listtmp", new { listtmp}).ToList();
foreach (var item in output)
{
string str = "编号为" + item.user_id + "的聊天内容" + item.book_id;
MessageBox.Show(str);
}
}
}
访问不同的表,执行多条语句
public void QueryMultiple()
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
{
var sql = "SELECT * FROM bookinfo ; SELECT * FROM jointest";
var multiReader = cnn.QueryMultiple(sql);
var bookinfolist = multiReader.Read<BOOKINFO>();
var jointestlist = multiReader.Read<JOINTEST>();
foreach (var item in bookinfolist)
{
string str = "编号为" + item.user_id + "的聊天内容" + item.book_id;
MessageBox.Show(str);
}
foreach (var item in jointestlist)
{
string str = "编号为" + item.user_id + "的聊天内容" + item.user_habbit;
MessageBox.Show(str);
}
multiReader.Dispose();
}
}
测试Excute方法
public void test()
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
{
string sql = "SELECT * FROM bookinfo ; SELECT * FROM jointest";
string sql1 = "DELETE FROM bookinfo WHERE user_id = \" 卡莎\"";
string sql2 = "INSERT INTO bookinfo VALUES (\"卡莎 \",\"虚空的生存法则\",13,13,\" 狩猎\")";
cnn.Execute(sql);
cnn.Execute(sql1);
cnn.Execute(sql2);
}
}
不急 一步一步