#mono sqlite关闭异常，DataReader already activie

Mono SQLite 关闭异常，DataReader already activie

• 这几天项目需要增加一个历史回放的记录，然后就了解到了SQLite，在网上无意间看到了一篇博客，具体地址忘了(- -找到后贴上)，然后直接把代码拷过来了。运行后发现有一些问题，就是项目关闭后操作db文件会提示文件已被占用，然后发现文档说是关闭db有可能会引发异常，然后按照文档加上了try catch finally之后就正常了。另外一个问题就是提示DataReader已经激活，原因是DataReader未释放，重复使用的话每次使用前先释放就行了。（像数据库连接这种稀缺的资源就应该尽早关闭…）
• 代码贴在下面，自己用的，比较简陋，很多问题没有考虑（小菜鸟一枚），大神看到可以帮忙指导一下。
• Sqlite区分大小写（大写会导致查询异常,代码已修改。（- - 。。。。。。））。

---

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

public class IBSQLiteHelper
{
   private SqliteConnection m_dbConnection;

   private SqliteCommand m_dbCommand;

   private SqliteDataReader m_dataReader;

   public IBSQLiteHelper(string connectionString)
   {
       try
       {
           m_dbConnection = new SqliteConnection(connectionString);
           m_dbConnection.Open();
       }
       catch (Exception e)
       {
           Debug.Log(e.Message);
       }
   }

   /// <summary>
   /// Execute query
   /// </summary>
   /// <param name="queryString"></param>
   /// <returns></returns>
   public SqliteDataReader ExecuteQuery(string queryString)
   {
       ReleaseCommand();
       m_dbCommand = m_dbConnection.CreateCommand();
       m_dbCommand.CommandText = queryString;
       m_dataReader = m_dbCommand.ExecuteReader();

       return m_dataReader;
   }

   /// <summary>
   /// NonQuery
   /// </summary>
   /// <param name="queryString"></param>
   public void ExecuteNonQuery(string queryString)
   {
       ReleaseCommand();
       m_dbCommand = m_dbConnection.CreateCommand();
       m_dbCommand.CommandText = queryString;
       m_dbCommand.ExecuteNonQuery();
   }

   /// <summary>
   /// ExecuteScalar
   /// </summary>
   /// <param name="queryString"></param>
   /// <returns></returns>
   public object ExecuteScalar(string queryString)
   {
       ReleaseCommand();
       m_dbCommand = m_dbConnection.CreateCommand();
       m_dbCommand.CommandText = queryString;
       return m_dbCommand.ExecuteScalar();
   }

   /// <summary>
   /// Close Connection
   /// </summary>
   public void CloseConnection()
   {
       ReleaseCommand();

       if (m_dbConnection != null)
       {
           try
           {
               m_dbConnection.Close();
           }
           catch (SqliteException ex)
           {
               Debug.Log(ex.ToString());
           }
          finally
           {
               m_dbConnection.Dispose();
           }
       }
       m_dbConnection = null;

       Debug.Log("CloseConnection");
   }

   /// <summary>
   /// Cache sql query 
   /// </summary>
   private StringBuilder m_queryString = new StringBuilder();
  
   /// <summary>
   /// Create Table
   /// </summary>
   /// <param name="tableName"></param>
   /// <param name="colNames"></param>
   /// <param name="colTypes"></param>
   public void CreateTable(string tableName, string[] colNames, string[] colTypes)
   {
       if (colNames.Length <= 0 || colNames.Length != colTypes.Length)
       {
           throw new SqliteException("Values length not match!");
       }

       m_queryString.Clear();

       m_queryString.Append(string.Format("create table {0}(",tableName));
       for (int i = 0; i < colNames.Length; i++)
       {
           string format = string.Format("{0} {1},", colNames[i], colTypes[i]);
           m_queryString.Append(format);
       }
       m_queryString.Remove(m_queryString.Length - 1 ,1);//Splite end char ,
       m_queryString.Append(")");

       ExecuteNonQuery(m_queryString.ToString());
   }

   /// <summary>
   /// Read table info
   /// </summary>
   /// <param name="tableName"></param>
   /// <returns></returns>
   public SqliteDataReader ReadTable(string tableName)
   {
       string queryString = $"select * from {tableName}";
       return ExecuteQuery(queryString);
   }

   /// <summary>
   /// I
   /// nsert values in table
   /// </summary>
   /// <param name="tableName"></param>
   /// <param name="values"></param>
   public void InsertValues(string tableName, string[] values)
   {
       //read colum count
       int fieldCount = ReadTable(tableName).FieldCount;
      
       if (values.Length != fieldCount)
       {
           throw new SqliteException("values.Length!=fieldCount");
       }

       m_queryString.Clear();
       m_queryString.Append($"insert into {tableName} values (");

       for (int i = 0; i < values.Length; i++)
       {
           m_queryString.Append(values[i]);
           m_queryString.Append(",");
       }
       m_queryString.Remove(m_queryString.Length - 1, 1);
       m_queryString.Append(")");
       ExecuteNonQuery(m_queryString.ToString());
   }

   /// <summary>
   /// Delete data
   /// </summary>
   /// <param name="tableName"></param>
   /// <param name="key"></param>
   /// <param name="operation"></param>
   public void Delete(string tableName, string key, string value,string opration)
   {
       string queryString = $"delete from {tableName} where {key}{opration}{value}";
       ExecuteNonQuery(queryString);
   }

   /// <summary>
   /// Updated data from table by operation 
   /// </summary>
   /// <param name="tableName"></param>
   /// <param name="key"></param>
   /// <param name="originalValue"></param>
   /// <param name="modifyValue"></param>
   /// <param name="operation"></param>
   /// <returns></returns>
   public void Update(string tableName, string key, string originalValue, string modifyValue,string operation)
   {
       string queryString = $"update {tableName} set {key} = {modifyValue} where {key}{operation}{originalValue}";
       ExecuteNonQuery(queryString);
   }

   /// <summary>
   /// Read single data by index
   /// </summary>
   /// <param name="tableName"></param>
   /// <param name="index"></param>
   /// <returns></returns>
   public SqliteDataReader ReadSingleData(string tableName, int index)
   {
       int offset = index - 1 > 0 ? index - 1 : 0;
       string sqlStr = $"select * from {tableName} limit 1 offset {offset}";
       return ExecuteQuery(sqlStr);
   }

   /// <summary>
   /// Get data by limit offset
   /// </summary>
   /// <param name="tableName"></param>
   /// <param name="limit"></param>
   /// <param name="offset"></param>
   /// <returns></returns>
   public SqliteDataReader GetDataByLimitOffset(string tableName, int limit, int offset)
   {
       string sqlStr = $"select * from {tableName} limit {limit} offset {offset}";
       return ExecuteQuery(sqlStr);
   }

   /// <summary>
   /// Get count by key
   /// </summary>
   /// <param name="key"></param>
   /// <param name="tableName"></param>
   /// <returns></returns>
   public int GetKeyCountFromTable(string key, string tableName)
   {
       string queryString = $"select count({key}) from {tableName}";
       return Convert.ToInt32(ExecuteScalar(queryString));
   }

   /// <summary>
   /// Table is exist
   /// </summary>
   /// <param name="name"></param>
   /// <returns></returns>
   public bool IsTableExist(string name)
   {
       string queryString = $"select count(*) from sqlite_master where type = 'table' and name = '{name}'";

       if (ExecuteScalar(queryString) != null)
       {
           return true;
       }
      
       return false;
   }

   /// <summary>
   /// Release Comand and reader
   /// </summary>
   private void ReleaseCommand()
   {
       if (m_dataReader != null)
       {
           m_dataReader.Close();
       }
       m_dataReader = null;

       if (m_dbCommand != null)
       {
           m_dbCommand.Dispose();
       }
       m_dbCommand = null;
   }
}

---