Unity3D SQLlite的高级用法

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