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