SQLite之C#版 System.Data.SQLite使用方法

本文整理自网络，侵删。

SQLite简介

SQLite，是一款轻型的关系型数据库。它的设计目标是嵌入式。

它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 C++、C#、Object-C、PHP、Java等。

我是做手游开发的，在手机上使用SQLite 存储数据是很方便的。 

System.Data.SqLite实践前期准备

1. System.Data.SQLite 库下载，用于C#操作SQLite的dll文件。下载地址：http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
2. SQLite可视化工具下载，用于查看SQLite库数据表数据。下载地址：http://www.sqliteexpert.com/download.html

Tips:

1.根据自己的电脑是64bit或者32bit 以及使用的.NET Freamwork版本下载相应的的System.Data.SQLite。    下载解压文件如：

安装在指定目录后生成多个dll文件：

本文测试用的只有 System.Data.SQLite.dll 。对于SQLite库数据的增删改查已经足够用了。

2.SQLite数据库文件以.db为后缀。安装SQLite可视化工具后如图：

mydb是我测试用的数据库。

实践

对SQLite的增、删、改、查。

测试环境：VS2015 语言：C#

创建控制台新工程，引入System.Data.SQLite.dll 文件。

1.编写SQLite 操作类， 对SQLite数据操作进行封装为 SqLiteHelper，代码如下：

/// <summary>  /// SQLite 操作类  /// </summary>  class SqLiteHelper  {    /// <summary>    /// 数据库连接定义    /// </summary>    private SQLiteConnection dbConnection;    /// <summary>    /// SQL命令定义    /// </summary>    private SQLiteCommand dbCommand;    /// <summary>    /// 数据读取定义    /// </summary>    private SQLiteDataReader dataReader;    /// <summary>    /// 构造函数    /// </summary>    /// <param name="connectionString">连接SQLite库字符串</param>    public SqLiteHelper(string connectionString)    {      try      {        dbConnection = new SQLiteConnection(connectionString);        dbConnection.Open();      }catch(Exception e)      {        Log(e.ToString());      }    }    /// <summary>    /// 执行SQL命令    /// </summary>    /// <returns>The query.</returns>    /// <param name="queryString">SQL命令字符串</param>    public SQLiteDataReader ExecuteQuery(string queryString)    {      try      {        dbCommand = dbConnection.CreateCommand();        dbCommand.CommandText = queryString;        dataReader = dbCommand.ExecuteReader();      }catch(Exception e)      {        Log(e.Message);      }      return dataReader;    }    /// <summary>    /// 关闭数据库连接    /// </summary>    public void CloseConnection()    {      //销毁Commend      if(dbCommand !=null)      {        dbCommand.Cancel();      }      dbCommand = null;      //销毁Reader      if(dataReader != null)      {        dataReader.Close();      }      dataReader = null;      //销毁Connection      if(dbConnection != null)      {        dbConnection.Close();      }      dbConnection = null;    }    /// <summary>    /// 读取整张数据表    /// </summary>    /// <returns>The full table.</returns>    /// <param name="tableName">数据表名称</param>    public SQLiteDataReader ReadFullTable(string tableName)    {      string queryString = "ｓｅｌｅｃｔ * FROM " + tableName;      return ExecuteQuery(queryString);    }    /// <summary>    /// 向指定数据表中插入数据    /// </summary>    /// <returns>The values.</returns>    /// <param name="tableName">数据表名称</param>    /// <param name="values">插入的数值</param>    public SQLiteDataReader InsertValues(string tableName, string[] values)    {      //获取数据表中字段数目      int fieldCount = ReadFullTable(tableName).FieldCount;      //当插入的数据长度不等于字段数目时引发异常      if (values.Length != fieldCount)      {        throw new SQLiteException("values.Length!=fieldCount");      }      string queryString = "ｉｎｓｅｒｔ INTO " + tableName + " VALUES (" + "'"+ values[0]+"'";      for (int i = 1; i < values.Length; i++)      {        queryString += ", " + "'"+values[i] + "'";      }      queryString += " )";      return ExecuteQuery(queryString);    }    /// <summary>    /// 更新指定数据表内的数据    /// </summary>    /// <returns>The values.</returns>    /// <param name="tableName">数据表名称</param>    /// <param name="colNames">字段名</param>    /// <param name="colValues">字段名对应的数据</param>    /// <param name="key">关键字</param>    /// <param name="value">关键字对应的值</param>    /// <param name="operation">运算符：=,<,>,...，默认“=”</param>    public SQLiteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string value, string operation="=")    {      //当字段名称和字段数值不对应时引发异常      if (colNames.Length != colValues.Length)      {        throw new SQLiteException("colNames.Length!=colValues.Length");      }      string queryString = "ｕｐｄａｔｅ " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'";      for (int i = 1; i < colValues.Length; i++)      {        queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'";      }      queryString += " WHERE " + key + operation + "'" + value + "'";      return ExecuteQuery(queryString);    }    /// <summary>    /// 删除指定数据表内的数据    /// </summary>    /// <returns>The values.</returns>    /// <param name="tableName">数据表名称</param>    /// <param name="colNames">字段名</param>    /// <param name="colValues">字段名对应的数据</param>    public SQLiteDataReader DeleteValuesOR(string tableName, string[] colNames, string[] colValues, string[] operations)    {      //当字段名称和字段数值不对应时引发异常      if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)      {        throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");      }      string queryString = "ｄｅｌｅｔｅ FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";      for (int i = 1; i < colValues.Length; i++)      {        queryString += "OR " + colNames[i] + operations[0] + "'" + colValues[i] + "'";      }      return ExecuteQuery(queryString);    }    /// <summary>    /// 删除指定数据表内的数据    /// </summary>    /// <returns>The values.</returns>    /// <param name="tableName">数据表名称</param>    /// <param name="colNames">字段名</param>    /// <param name="colValues">字段名对应的数据</param>    public SQLiteDataReader DeleteValuesAND(string tableName, string[] colNames, string[] colValues, string[] operations)    {      //当字段名称和字段数值不对应时引发异常      if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)      {        throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");      }      string queryString = "ｄｅｌｅｔｅ FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";      for (int i = 1; i < colValues.Length; i++)      {        queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'";      }      return ExecuteQuery(queryString);    }    /// <summary>    /// 创建数据表    /// </summary> +    /// <returns>The table.</returns>    /// <param name="tableName">数据表名</param>    /// <param name="colNames">字段名</param>    /// <param name="colTypes">字段名类型</param>    public SQLiteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes)    {      string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colNames[0] + " " + colTypes[0];      for (int i = 1; i < colNames.Length; i++)      {        queryString += ", " + colNames[i] + " " + colTypes[i];      }      queryString += " ) ";      return ExecuteQuery(queryString);    }    /// <summary>    /// Reads the table.    /// </summary>    /// <returns>The table.</returns>    /// <param name="tableName">Table name.</param>    /// <param name="items">Items.</param>    /// <param name="colNames">Col names.</param>    /// <param name="operations">Operations.</param>    /// <param name="colValues">Col values.</param>    public SQLiteDataReader ReadTable(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)    {      string queryString = "ｓｅｌｅｃｔ " + 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 = 0; i < colNames.Length; i++)      {        queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";      }      return ExecuteQuery(queryString);    }    /// <summary>    /// 本类log    /// </summary>    /// <param name="s"></param>    static void Log(string s)    {      Console.WriteLine("class SqLiteHelper:::" + s);    }  }

Tips:1.别忘了引入：using System.Data.SQLite;

阅读剩余部分

相关阅读 >>

最新统计排名前十的sql和nosql数据库排行榜

python sqlalchemy库的使用方法

Sqlite3中的日期时间函数使用小结

qt入门学习之数据库操作指南

python之sqlalchemy创建表的实例详解

android Sqlite命令详解(基本命令)

android中的Sqlite数据库简介

详解python中executemany和序列的使用方法

django数据库(Sqlite)基本入门使用教程

Sqlite3 sql命令行怎么使用？

更多相关阅读请进入《Sqlite》频道 >>

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。