Question

在使用sqlite时有什么需要注意的地方吗

Answer 1

首先介绍我用Sqlite解决的实际问题是什么？
　　问题1：某个功能的数据需要连接一个远程数据库查询速度很慢，查一次数据不容易，希望能够重复利用之前查过的数据集。
　　问题2：非常大的数据量比如几千万甚至几亿条数据，一次性读取到DataTable中，会内存溢出的，所以在第一次分析时就是通过Reader的方式，分析完一条后并不在内存中保存，但是紧接着用户的第二次分析、第三次分析还是要用到的第一次分析的数据，如果我们重新查询一次远程服务器，效率可想而知啊。
　　结合上面的2个问题，为了解决效率问题和数据重复利用度，减少数据库服务器的压力，我才用Sqlite缓存数据（当然这不是唯一也不是最好的解决方案） 。
　　优化SQLiteHelper
　　陆兄的SQLiteHelper类我增加了几个有用的方法：
　　第一个方法是GetSchema，得到某个表的表结构。
　　/// <summary>
/// 查询数据库中的所有数据类型信息
/// </summary>
/// <returns></returns>
public DataTable GetSchema()
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
DataTable data = connection.GetSchema("TABLES");
connection.Close();
//foreach (DataColumn column in data.Columns)
//{
// Console.WriteLine(column.ColumnName);
//}
return data;
}
}

　　第二个方法是IsTableExist，判断SQLite数据库重某个表是否存在 。
　　/// <summary>
/// 判断SQLite数据库表是否存在
/// </summary>
/// <param name="dbPath">要创建的SQLite数据库文件路径</param>
public bool IsTableExist(string tableName)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{

command.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + tableName + "'";
int iaaa = Convert.ToInt32(command.ExecuteScalar());
if (Convert.ToInt32(command.ExecuteScalar()) == 0)
{
return false;
}
else
{
return true;
}
}
}
}

　　第三个方法是Query，执行查询语句，返回DataSet
　　/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet Query(string SQLString)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SQLite.SQLiteException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}

　　构建缓存对象模型和缓存控制器
　　每一块缓存对象，在数据库中会产生一个表，而表名称是有缓存控制器自动生成的，访问缓存的工作全部交由缓存控制器完成，通过缓存项的ID和ModuleKey来访问。
　　在Sqlite中还需要一个系统表来维护每个缓存项和实际缓存存储表之间的对应关系，我们称之为配置表，它将在缓存控制器创建Sqlite缓存数据库文件时创建。
　　配置表共有以下几个字段，分别和缓存对象模型CdlCacheItem类映射：
　　列名称 说明
　　Id 缓存的唯一数字编号
　　ModuleKey 缓存模块名称，一个模块可以有多个缓存数据，ID可以区分。实际应用时，某个功能时会经常缓存数据的，所以通过ModuleKey就可以得到这个功能所有的缓存列表，然后选定其中的部分缓存来进行使用。
　　Comments 缓存说明
　　TableName 缓存数据存储的数据表名称
　　AddDate 缓存时间戳
　　创建数据库的方法如下：
　　static void CreateDB()
{
//总共有ID、ModuleKey、Comments、AddDate这几列
string sql = "CREATE TABLE SYSCDLTABLES(ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,MODULEKEY VARCHAR(200),COMMENTS VARCHAR(500),TABLENAME VARCHAR(100),ADDDATE DATETIME)";
SQLiteDBHelper.CreateDB(CACHEFILEPATH, sql);
}

　　每个缓存项（缓存对象模型）定义如下，和配置表对应：
　　/// <summary>
/// 缓存项对象
/// </summary>
/// <Author>Tecky Lee</Author>
/// <Date>2011-1-11 15:11</Date>
public class CdlCacheItem
{
int m_id;

public int Id
{
get { return m_id; }
set { m_id = value; }
}
string m_moduleKey;

public string ModuleKey
{
get { return m_moduleKey; }
set { m_moduleKey = value; }
}
string m_comments;

public string Comments
{
get { return m_comments; }
set { m_comments = value; }
}
string m_tableName;

public string TableName
{
get { return m_tableName; }
set { m_tableName = value; }
}
DateTime m_timestamp;

public DateTime Timestamp
{
get { return m_timestamp; }
set { m_timestamp = value; }
}
}

　　下面是控制器的接口定义：
　　public interface ICdlCacheController
{
void BeginLoadRow();
void EndLoadRow();
System.Collections.Generic.IList<CdlCacheItem> GetCdlCacheItems(string moduleKey);
CdlCacheItem GetCdlCacheItems(int id);
void LoadRow(System.Data.DataRow row, string tableName);
void LoadRow(IEnumerable<object> row, string tableName);
string LoadTable(System.Data.DataTable dt, string moduleKey, string comments);
System.Data.Common.DbDataReader QueryCdlTableReader(CdlCacheItem item);
System.Data.DataTable QueryCdlTables(CdlCacheItem item);
System.Data.DataTable QueryCdlTables(string sql);
void RemoveAllTables();
void RemoveCdlTables(string moduleKey);
void RemoveCdlTables(System.Collections.Generic.IList<CdlCacheItem> items);
void RemoveCdlTables(CdlCacheItem item);
void RemoveCdlTables(int id);
}