工具:
- 实例用vs2017编写
- sqlite数据库
sqlite数据库安装:
下载符合自己.net版本和操作系统的sqlite数据库,我下载的是:sqlite-netFx40-setup-bundle-x86-2010-1.0.108.0.exe 下载地址:http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki 下载后安装到任意位置即可。
操作过程:
项目》引用》浏览 添加sqlite安装目录中的System.Data.SQLite.dll
将 System.Data.SQLite.dll 和 System.Data.SQLite.dll.config 两个文件放在运行目录下,不然会报错。
修改运行平台:项目》属性》生成》平台目标 我下载的是32位的数据库,所以这里要修改为x86,如果是64位的就选x64,这一步很重要,如果和数据库的位数不对调用数据库的时候会出错。
命名空间填写以下代码
using System.Data.SQLite;
添加sqlite操作类:项目》添加》新建项 选择 类 命名为 sqlite.cs 代码如下
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SQLite; using System.Windows.Forms; namespace sqlite { class SqLite { /// <summary> /// 数据库连接定义 /// </summary> private SQLiteConnection dbConnection; /// <summary> /// SQL命令定义 /// </summary> private SQLiteCommand dbCommand; /// <summary> /// 数据读取定义 /// </summary> private SQLiteDataReader dataReader; /// <summary> /// 构造函数 /// </summary> /// <param name="connectionString">连接SQLite库字符串</param> public SqLite(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 = "SELECT * 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 = "INSERT 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 = "UPDATE " + 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 = "DELETE 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 = "DELETE 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 = "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 = 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) { MessageBox.Show(s); } } }
Form1.cs代码如下:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SQLite; namespace sqlite { public partial class Form1 : Form { //定义sqlite程序集变量 private static SqLite sql; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { //初始化数据库 sql = new SqLite("Data Source=data.db;"); } } }
至此所有操作完毕,可以正常运行程序了,有问题可以在下面留言。