SQLlite的高级用法=>用更简洁的方式去完成增删改查四个功能
一、创建表
public void CreatUserTabel()
{
if (connection!=null)
{
接收
//SQLiteCommand liteCommand= connection.CreateCommand("CREATE TABLE User(UserName varchar(255),Password varchar(255))");
执行
//liteCommand.ExecuteNonQuery();
connection.CreateTable<User>();
}
}
二、增加数据
public void InsertUser()
{
if (connection!=null)
{
//SQLiteCommand liteCommand = connection.CreateCommand("INSERT INTO User VALUES("+"\"admin1\",\"111111\")");
//liteCommand.ExecuteNonQuery();
User user = new User()
{
UserName = "admin1",
Password = "11111"
};
connection.Insert(user);
}
}
三、删除数据
public void UpdateUser()
{
if (connection!=null)
{
//SQLiteCommand liteCommand = connection.CreateCommand("UPDATE User SET Password=\"22222\"WHERE UserName=\"admin1\"");
//liteCommand.ExecuteNonQuery();
User user = new User()
{
UserName = "admin",
Password = "22222"
};
connection.Update(user);
}
}
四、查询数据
public void SelecUser()
{
if (connection!=null)
{
//SQLiteCommand liteCommand = connection.CreateCommand("SELECT * FROM User");
//List<User> list = liteCommand.ExecuteQuery<User>();
TableQuery<User> user = connection.Table<User>();
foreach (var item in user)
{
print("UserName:" + item.UserName + ",Password:" + item.Password);
}
}
}
public void SelectUserById()
{
if (connection!=null)
{
TableQuery<User> user = connection.Table<User>().Where(t => t.UserName == "admin");
foreach (var item in user)
{
print("UserName:" + item.UserName + ",Password:" + item.Password);
}
}
}
五、修改数据
(1)对数据模块User进行修改(添加主键)
using System.Collections;
using System.Collections.Generic;
using SQLite4Unity3d;
public class User
{
[PrimaryKey]
public string UserName { get; set; }
public string Password { get; set; }
}
(2)修改数据
public void UpdateUser()
{
if (connection!=null)
{
//SQLiteCommand liteCommand = connection.CreateCommand("UPDATE User SET Password=\"22222\"WHERE UserName=\"admin1\"");
//liteCommand.ExecuteNonQuery();
User user = new User()
{
UserName = "admin",
Password = "22222"
};
connection.Update(user);
}
}
六、整体代码展示
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using SQLite4Unity3d;
using System;
using System.IO;
public class SQLiteController : MonoBehaviour {
//定义数据库名称
public string dataBaseName;
//申明连接对象
public SQLiteConnection connection;
//创建数据库连接
public void CreatSQLite()
{
string path = Path.Combine(Application.streamingAssetsPath,dataBaseName);
connection = new SQLiteConnection(path,SQLiteOpenFlags.ReadWrite|SQLiteOpenFlags.Create);
}
//数据库建表
public void CreatUserTabel()
{
if (connection!=null)
{
接收
//SQLiteCommand liteCommand= connection.CreateCommand("CREATE TABLE User(UserName varchar(255),Password varchar(255))");
执行
//liteCommand.ExecuteNonQuery();
connection.CreateTable<User>();
}
}
//数据库添加数据
public void InsertUser()
{
if (connection!=null)
{
//SQLiteCommand liteCommand = connection.CreateCommand("INSERT INTO User VALUES("+"\"admin1\",\"111111\")");
//liteCommand.ExecuteNonQuery();
User user = new User()
{
UserName = "admin1",
Password = "11111"
};
connection.Insert(user);
}
}
//查询数据库数据
public void SelecUser()
{
if (connection!=null)
{
//SQLiteCommand liteCommand = connection.CreateCommand("SELECT * FROM User");
//List<User> list = liteCommand.ExecuteQuery<User>();
TableQuery<User> user = connection.Table<User>();
foreach (var item in user)
{
print("UserName:" + item.UserName + ",Password:" + item.Password);
}
}
}
public void SelectUserById()
{
if (connection!=null)
{
TableQuery<User> user = connection.Table<User>().Where(t => t.UserName == "admin");
foreach (var item in user)
{
print("UserName:" + item.UserName + ",Password:" + item.Password);
}
}
}
//修改数据库数据
public void UpdateUser()
{
if (connection!=null)
{
//SQLiteCommand liteCommand = connection.CreateCommand("UPDATE User SET Password=\"22222\"WHERE UserName=\"admin1\"");
//liteCommand.ExecuteNonQuery();
User user = new User()
{
UserName = "admin",
Password = "22222"
};
connection.Update(user);
}
}
//删除数据库数据
public void DeleteUser()
{
if (connection!=null)
{
//SQLiteCommand liteCommand = connection.CreateCommand("DELETE FROM User WHERE UserName=\"admin1\"");
//liteCommand.ExecuteNonQuery();
connection.Delete<User>("admin");
}
}
//断开连接
public void OnDestroy()
{
if (connection!=null)
{
connection.Close();
connection.Dispose();
GC.Collect();
}
}
private void Start()
{
CreatSQLite();
}
}