Unity—Mono.Data.Sqlite

街角的、寂寞

已于 2024-05-16 12:26:58 修改

阅读量1.1k

点赞数

分类专栏： Unity 文章标签： sqlite unity 数据库

于 2022-08-29 13:42:21 首次发布

本文链接：https://blog.csdn.net/qq_35867189/article/details/126583219

版权

Unity 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

Mono.Data.Sqlite

一、常用属性
Depth:获取一个值，用于指示当前行的嵌套深度
FieldCount：获取当前行中的列数
HasRows：获取一个值，该值指示SQLDataReader是否有行
IsClosed:指定的SQLDataReader实例是否已关闭
Item[Int32]：获取指定列（数字索引），通常在While.Read()中使用
Item[String]：获取指定列（字符串索引），通常在While.Read()中使用
RecordsAffected：获取执行SqlDataReader中未隐藏的字段数目

class Program
    {
        static void Main(string[] args)
        {
            string str = "server=.;database=JunTest;uid=sa;pwd=123;Asynchronous Processing=true";
            SqlConnection conn = new SqlConnection(str);    //创建连接
            SqlCommand cmd = conn.CreateCommand();          //创建SqlCommand对象
            cmd.CommandText = "SELECT * FROM Person";
            conn.Open();                            //打开连接
 
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                Console.WriteLine(reader.FieldCount);   //2  获取列数
                Console.WriteLine(reader.Depth);        //0  嵌套深度
                Console.WriteLine(reader.HasRows);      //true  是否包含行
                Console.WriteLine(reader.IsClosed);     //false SqlDataReader是否关闭 
                Console.WriteLine(reader.RecordsAffected);      //-1 执行T-SQL语句所插入、修改、删除的行数
                Console.WriteLine(reader.VisibleFieldCount);    //2  未隐藏的字段数目(一共就两列)
 
                while (reader.Read())
                {
                    Console.WriteLine(reader["PersonName"]);
                  //Console.WriteLine(reader[1]);   通过数字索引或字符串索引访问
                }
            }
 
            conn.Close();                           //关闭连接
            Console.ReadKey();
        }
    }

二、常用方法
Read：前进到下一记录，异步版本ReadAsync
GetString：返回指定类型的值，其他的类型
NextResult：当处理批批处理T-SQL语句时，跳到下一结果异步版本NextResultAsync
GetValue：获取该列的值，返回object类型
GetValues：使用当前列，来填充参数中的对象数组
Close：关闭SqlDataReader对象

class Program
    {
        static void Main(string[] args)
        {
            string str = "server=.;database=JunTest;uid=sa;pwd=123;Asynchronous Processing=true";
            SqlConnection conn = new SqlConnection(str);    //创建连接
            SqlCommand cmd = conn.CreateCommand();          //创建SqlCommand对象
            cmd.CommandText = "SELECT * FROM Person";
            conn.Open();                            //打开连接
 
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader.IsDBNull(1));      //是否是null值
                    Console.WriteLine(reader.GetString(1));     //Get什么类型就返回什么类型，这没啥好说的。
                }
                Console.WriteLine(reader.GetName(1));               //PersonName 由数字获得列名
                Console.WriteLine(reader.GetOrdinal("PersonName")); //1 由列名获取其在reader中的数字索引
 
                if (reader.NextResult())
                {
                    Console.WriteLine(reader.GetString(1));
                }
            }
            conn.Close();                           //关闭连接
            Console.ReadKey();
        }
    }

Demo :

using UnityEngine;
using System;
using System.Collections;
using Mono.Data.Sqlite;
/// <summary>
/// SQLite数据库操作类
/// </summary>
public class SqliteAccess
{
	private SqliteConnection conn; // SQLite连接
	private SqliteCommand cmd; // SQLite命令
	private SqliteDataReader reader;
	public SqliteAccess(string connectionString)
	{
		OpenDB(connectionString);
	}
	public SqliteAccess() { }
	/// <summary>
	/// 打开数据库
	/// </summary>
	/// <param name="connectionString"></param>
	public void OpenDB(string connectionString)
	{
		try
		{
			conn = new SqliteConnection(connectionString);
			conn.Open();
			Debug.Log("Connected to db,连接数据库成功！");
		}
		catch (Exception e)
		{
			string temp1 = e.ToString();
			Debug.Log(temp1);
		}
	}
	/// <summary>
	/// 关闭数据库连接
	/// </summary>
	public void CloseSqlConnection()
	{
		if (cmd != null) { cmd.Dispose(); cmd = null; }
		if (reader != null) { reader.Dispose(); reader = null; }
		if (conn != null) { conn.Close(); conn = null; }
		Debug.Log("Disconnected from db.关闭数据库！");
	}
	/// <summary>
	/// 执行SQL语句
	/// </summary>
	/// <param name="sqlQuery"></param>
	/// <returns></returns>
	public SqliteDataReader ExecuteQuery(string sqlQuery)
	{
		cmd = conn.CreateCommand();
		cmd.CommandText = sqlQuery;
		reader = cmd.ExecuteReader();
		return reader;
	}

	/// <summary>
	/// 查询表中全部数据 param tableName=表名 
	/// </summary>
	public SqliteDataReader ReadFullTable(string tableName)
	{
		string query = "SELECT * FROM " + tableName;
		return ExecuteQuery(query);
	}
	/// <summary>
	/// 插入数据 param tableName=表名 values=插入数据内容
	/// </summary>
	public SqliteDataReader InsertInto(string tableName, string[] values)
	{
		string query = "INSERT INTO " + tableName + " VALUES (" + values[0];
		for (int i = 1; i < values.Length; ++i)
		{
			query += ", " + values[i];
		}
		query += ")";
		return ExecuteQuery(query);
	}
	/// <summary>
	/// 更新数据 param tableName=表名 cols=更新字段 colsvalues=更新内容 selectkey=查找字段（主键) selectvalue=查找内容
	/// </summary>
	public SqliteDataReader UpdateInto(string tableName, string[] cols, string[] colsvalues, string selectkey, string selectvalue)
	{
		string query = "UPDATE " + tableName + " SET " + cols[0] + " = " + colsvalues[0];
		for (int i = 1; i < colsvalues.Length; ++i)
		{
			query += ", " + cols[i] + " =" + colsvalues[i];
		}
		query += " WHERE " + selectkey + " = " + selectvalue + " ";
		return ExecuteQuery(query);
	}

	/// <summary>
	/// 删除数据 param tableName=表名 cols=字段 colsvalues=内容
	/// </summary>
	public SqliteDataReader Delete(string tableName, string[] cols, string[] colsvalues)
	{
		string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + colsvalues[0];
		for (int i = 1; i < colsvalues.Length; ++i)
		{
			query += " or " + cols[i] + " = " + colsvalues[i];
		}
		return ExecuteQuery(query);
	}
	/// <summary>
	/// 插入数据 param tableName=表名 cols=插入字段 value=插入内容
	/// </summary>
	public SqliteDataReader InsertIntoSpecific(string tableName, string[] cols, string[] values)
	{
		if (cols.Length != values.Length)
		{
			throw new SqliteException("columns.Length != values.Length");
		}
		string query = "INSERT INTO " + tableName + "(" + cols[0];
		for (int i = 1; i < cols.Length; ++i)
		{
			query += ", " + cols[i];
		}
		query += ") VALUES (" + values[0];
		for (int i = 1; i < values.Length; ++i)
		{
			query += ", " + values[i];
		}
		query += ")";
		return ExecuteQuery(query);
	}
	/// <summary>
	/// 删除表中全部数据
	/// </summary>
	public SqliteDataReader DeleteContents(string tableName)
	{
		string query = "DELETE FROM " + tableName;
		return ExecuteQuery(query);
	}
	/// <summary>
	/// 创建表 param name=表名 col=字段名 colType=字段类型
	/// </summary>
	public SqliteDataReader CreateTable(string name, string[] col, string[] colType)
	{
		if (col.Length != colType.Length)
		{
			throw new SqliteException("columns.Length != colType.Length");
		}
		string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
		for (int i = 1; i < col.Length; ++i)
		{
			query += ", " + col[i] + " " + colType[i];
		}
		query += ")";
		return ExecuteQuery(query);
	}
	/// <summary>
	/// 按条件查询数据 param tableName=表名 items=查询字段 col=查找字段 operation=运算符 values=内容
	/// </summary>
	public SqliteDataReader SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values)
	{
		if (col.Length != operation.Length || operation.Length != values.Length)
		{
			throw new SqliteException("col.Length != operation.Length != values.Length");
		}
		string query = "SELECT " + items[0];
		for (int i = 1; i < items.Length; ++i)
		{
			query += ", " + items[i];
		}
		query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
		for (int i = 1; i < col.Length; ++i)
		{
			query += " AND " + col[i] + operation[i] + "'" + values[i] + "' ";
		}
		return ExecuteQuery(query);
	}
	/// <summary>
	/// 查询表
	/// </summary>
	public SqliteDataReader Select(string tableName, string col, string values)
	{
		string query = "SELECT * FROM " + tableName + " WHERE " + col + " = " + values;
		return ExecuteQuery(query);
	}
	public SqliteDataReader Select(string tableName, string col, string operation, string values)
	{
		string query = "SELECT * FROM " + tableName + " WHERE " + col + operation + values;
		return ExecuteQuery(query);
	}
	/// <summary>
	/// 升序查询
	/// </summary>
	public SqliteDataReader SelectOrderASC(string tableName, string col)
	{
		string query = "SELECT * FROM " + tableName + " ORDER BY " + col + " ASC";
		return ExecuteQuery(query);
	}
	/// <summary>
	/// 降序查询
	/// </summary>
	public SqliteDataReader SelectOrderDESC(string tableName, string col)
	{
		string query = "SELECT * FROM " + tableName + " ORDER BY " + col + " DESC";
		return ExecuteQuery(query);
	}
	/// <summary>
	/// 查询表行数
	/// </summary>
	public SqliteDataReader SelectCount(string tableName)
	{
		string query = "SELECT COUNT(*) FROM " + tableName;
		return ExecuteQuery(query);
	}
}

using Mono.Data.Sqlite;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;

public class User : MonoBehaviour
{
    private SqliteAccess db;
    protected SqliteDataReader reader;
     private string dbName =Application.streamingAssetsPath+ "/data.db";
    
    private void Start()
    {  
    	 db = new SqliteAccess();
         db.OpenDB("Data Source=" + dbName);

        reader = db.ReadFullTable("User");

        for(int i = 0; i < reader.VisibleFieldCount; i++)
        {
            Debug.Log(reader.GetName(i)+"--"+reader.GetValue(i));
        }
        while (reader.Read())
        {
            Debug.Log(reader["username"]);
            //Console.WriteLine(reader[1]);   通过数字索引或字符串索引访问
        }

        reader.Close();
    }  
}