数据库工具类

using System;-------------数据库工具类--------------
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;
//如何在unity中使用数据库
//封装一个工具类 负责创建 增删改查等操作
public class SqlLiteDataHelper : MonoBehaviour
{
    //数据库连接定义
    private SqliteConnection dbConnection;
    //SQL命令定义
    private SqliteCommand dbCommand;
    //查询记录数据读取器
    private SqliteDataReader dataReader;
    //构造函数，负责创建数据库的连接
    public SqlLiteDataHelper(string dbName)
    {
        try
        {
            //拼接数据库连接字符串
            string connectionStr = "data source=" + Application.dataPath + "/" + dbName + ".db";
            //构造数据库的连接对象
            dbConnection=new SqliteConnection(connectionStr);
            //打开这个数据库
            dbConnection.Open();
            Debug.Log("数据库打开成功");
        }
        catch (Exception e)
        {
            Debug.LogError("数据库打开失败"+e.Message);
            throw;
        }
    }
    //执行sql语句，使用sql语句字符串
    public SqliteDataReader ExecuteQuery(string querying)
    {
        //创建命令
        dbCommand= dbConnection.CreateCommand();
        //设置执行sql语句
        dbCommand.CommandText = querying;
        //执行命令并返回结果
        dataReader= dbCommand.ExecuteReader();
        return dataReader;
    }
    //关闭数据库连接，释放系统资源
    public void CloseConnection()
    {
        //销毁command
        if (dbCommand!=null)
        {
            dbCommand.Cancel();
            dbCommand = null;
        }
        //销毁reader
        if (dataReader!=null)
        {
            dataReader.Close();
            dataReader = null;
        }
        //销毁connection
        if (dbConnection!=null)
        {
            dbConnection.Close();
            dbConnection = null;
        }
        Debug.Log("数据库已关闭");
    }
    //创建数据库的表
    public SqliteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes)
    {
        //判断字段个数跟类型个数是否一致
        if (colNames.Length!=colTypes.Length)
        {
            Debug.LogError("创表失败，字段个数与类型个数不匹配！");
            return null;
        }
 
        string queryString = "CREATE TABLE " + tableName + "(" + colNames[0] + " " + colTypes[0];
 
        for (int i = 1; i < colNames.Length; i++)
        {
            queryString += "," + colNames[i] + " " + colNames[i];
        }
 
        queryString += ");";
        Debug.Log("创建表" + tableName + "成功");
        return ExecuteQuery(queryString);
 
    }
    //读取整张表的所有数据
    public SqliteDataReader ReadFullTable(string tableName)
    {
        string queryString = "SELECT*FROM " + tableName;
        return ExecuteQuery(queryString);
    }
    //向指定表格中插入数据
    public SqliteDataReader InsertValues(string tableName, params string[] values)
    {
        //获取表格数据中的所有字段个数
        int fieldCount=ReadFullTable(tableName).FieldCount;
        //如果插入数据的个数与表格中字段个数不一致
        if (values.Length!=fieldCount)
        {
            Debug.LogError("向表"+tableName+"中插入字段失败，值与字段不相符");
            return null;
        }
        //拼接sql语句字符串
        string queryString = "INSERT INTO " + tableName + " VALUES(" + values[0];
        for (int i = 1; i < values.Length; i++)
        {
            queryString += "," + values[i];
        }
 
        queryString += ")";
        Debug.Log("向表" + tableName + "中插入数据成功");
        //执行命令并返回
        return ExecuteQuery(queryString);
 
    }
    //更新指定表格中的记录
    /// <summary>
    /// UpdataValues
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="colNames">字段</param>
    /// <param name="colValues">字段值</param>
    /// <param name="key">条件key</param>
    /// <param name="operation">条件</param>
    /// <param name="value">条件的值</param>
    /// <returns></returns>
    public SqliteDataReader UpdataValues(string tableName,string[] colNames,
        string[]colValues,string operation)
    {
        if (colNames.Length!=colValues.Length)
        {
            Debug.LogError("更新表"+tableName+"失败，字段个数与值的个数不匹配！");
            return null;
        }
        //拼接sql语句字符串
        string queryString = "UPDATE " + tableName + " SET " + colNames[0] + " = " + colValues[0];
        for (int i = 1; i < colNames.Length; i++)
        {
            queryString += "," + colNames[i] + " = " + colValues[i];
        }
 
        //拼接条件语句
        queryString += " WHERE " +  operation ;
        Debug.Log("更新表格"+tableName+"，更新成功！");
        return ExecuteQuery(queryString);
    }
    //删除指定的记录
    /// <summary>
    /// DeleteValues
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="colNames">字段</param>
    /// <param name="operations">字段条件</param>
    /// <param name="colValues">字段值</param>
    /// <param name="delmode">and or</param>
    /// <returns></returns>
    private SqliteDataReader DeleteValues(string tableName,string[] colNames,
        string[] operations,string[] colValues,string delmode)
    {
        if (colNames.Length!=operations.Length||colValues.Length!=operations.Length)
        {
            Debug.LogError("删除数据表"+tableName+"中的记录失败：字段，运算符，值的个数不一样");
            return null;
        }
        //拼接字符串
        string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] +
                             operations[0] + colValues[0];
        for (int i = 1; i < colNames.Length; i++)
        {
            queryString += " " + delmode + " " + colNames[i] + operations[i] + colValues[i];
        }
        Debug.Log("删除数据表"+tableName+"中的记录成功！");
        return ExecuteQuery(queryString);
 
    }
 
    //删除指定表格中的记录 条件之间用and连接
    public SqliteDataReader DeleteValuesAnd(string tableName, string[] colNames,
        string[] operations, string[] colValues)
    {
        return DeleteValues(tableName, colNames, operations, colValues, " AND ");
    }
    //删除指定表格中的记录 条件之间用or连接
    public SqliteDataReader DeleteValuesOr(string tableName, string[] colNames,
        string[] operations, string[] colValues)
    {
        return DeleteValues(tableName, colNames, operations, colValues, " OR ");
    }
    //查询数据
    public SqliteDataReader ReadTableDatas(string tableName,string[] items,
        string[] colNames,string[]operations,string[]colValues){
        //判断字段个数和运算符个数和字段值个数是否相同
        if (colNames.Length != operations.Length || operations.Length != colValues.Length)
        {
            Debug.LogError("查询数据表" + tableName + "失败:字段个数,运算符,字段值个数不匹配!");
            return null;
        }
        //拼接SQL字符串语句
        string queryString = "SELECT " + items[0];
        for (int i = 1; i < items.Length; i++)
        {
            queryString += ", " + items[i];
        }
        //指定查询的字段已拼接完毕
        queryString += " FROM " + tableName + " WHERE " + colNames[0] + operations[0] + colValues[0];
        for (int i = 1; i < colNames.Length; i++)
        {
            queryString += " AND " + colNames[i] + operations[i] + colValues[i];
        }
        return ExecuteQuery(queryString);
    }
    
}