说明：本文的代码是基于Winform中举例的，经过实测可用。
1.封装Sqlite操作类：sqLiteHelper
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SQLite; namespace TestSqlite.sq { /// <summary> /// SQLite 操作类 /// </summary> public class SqLiteHelper { /// <summary> /// 数据库连接定义 /// </summary> public SQLiteConnection dbConnection; /// <summary> /// SQL命令定义 /// </summary> private SQLiteCommand dbCommand; /// <summary> /// 数据读取定义 /// </summary> private SQLiteDataReader dataReader; /// <summary> /// 数据库连接字符串定义 /// </summary> private SQLiteConnectionStringBuilder dbConnectionstr; /// <summary> /// 构造函数 /// </summary> /// <param name="connectionString">连接SQLite库字符串</param> public SqLiteHelper(string connectionString) { try { dbConnection = new SQLiteConnection(); dbConnectionstr = new SQLiteConnectionStringBuilder(); dbConnectionstr.DataSource = connectionString; dbConnectionstr.Password = "admin"; //设置密码，SQLite ADO.NET实现了数据库密码保护 dbConnection.ConnectionString = dbConnectionstr.ToString(); 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; //设置SQL语句 dataReader = dbCommand.ExecuteReader(); } catch (Exception e) { Log(e.Message); } return dataReader; } /// <summary> /// 关闭数据库连接 /// </summary> public void CloseConnection() { //销毁Command 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) { // 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> /// <param name="key">关键字</param> /// <param name="value">关键字对应的值</param> /// <param name="operation">运算符：=,<,>,...，默认“=”</param> public SQLiteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key1, string value1, string operation, string key2, string value2) { // 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] + "'"; } //表中已经设置成int类型的不需要再次添加‘单引号’，而字符串类型的数据需要进行添加‘单引号’ queryString += " WHERE " + key1 + operation + "'" + value1 + "'" + "OR " + key2 + operation + "'" + value2 + "'"; 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) { Console.WriteLine("class SqLiteHelper:::" + s); } } } 2.Form窗体中调用sqLiteHelper类，实现数据的增删等功能
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; using TestSqlite20180310.sq; using System.Net; namespace TestSqlite20180310 { public partial class Form1 : Form { private SqLiteHelper sql; private SqLiteHelper passWord; private DataSet ds; private SQLiteDataAdapter sda; private bool flag = false; public Form1() { InitializeComponent(); CreatePassWard(); ReadPassWardFromdb(); } private void btnTest_Click(object sender, EventArgs e) { try { // sql = new SqLiteHelper("data source=mydb.db"); sql = new SqLiteHelper("mydb.db"); //创建名为table1的数据表 sql.CreateTable("table1", new string[] { "ID", "Name", "Age", "Email" }, new string[] { "INTEGER", "TEXT", "INTEGER", "TEXT" }); //插入两条数据 sql.InsertValues("table1", new string[] { "1", "张三", "16", "Zhang@163.com" }); sql.InsertValues("table1", new string[] { "2", "李四", "17", "Li4@163.com" }); //更新数据，将Name="张三"的记录中的Name改为"小三" sql.UpdateValues("table1", new string[] { "Name" }, new string[] { "sunlei" }, "Name", "小三", "="); //删除Name="小三"且Age=16的记录,DeleteValuesOR方法类似 sql.DeleteValuesAND("table1", new string[] { "Name", "Age" }, new string[] { "小三", "16" }, new string[] { "=", "=" }); //读取整张表 SQLiteDataReader reader = sql.ReadFullTable("table1"); while (reader.Read()) { //读取ID Log("" + reader.GetInt32(reader.GetOrdinal("ID"))); //读取Name Log("" + reader.GetString(reader.GetOrdinal("Name"))); //读取Age Log("" + reader.GetInt32(reader.GetOrdinal("Age"))); //读取Email Log(reader.GetString(reader.GetOrdinal("Email"))); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void Log(string s) { Console.WriteLine("" + s); } private void CreatePassWard() { try { // passWord = new SqLiteHelper("data source=password.db"); passWord = new SqLiteHelper("password.db"); //创建名为table1的数据表 passWord.CreateTable("password", new string[] { "ID", "PW" }, new string[] { "TEXT", "TEXT" }); //插入两条数据 passWord.InsertValues("password", new string[] { "小三", "123456" }); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { passWord.CloseConnection(); } } private void ReadPassWardFromdb() { try { //连接数据库 // passWord = new SqLiteHelper("data source=password.db"); passWord = new SqLiteHelper("password.db"); //读取整张表 SQLiteDataReader reader = passWord.ReadFullTable("password"); if (reader.Read()) { //读取ID与pw richTextBox1.Text = reader.GetString(reader.GetOrdinal("ID")); richTextBox3.Text = reader.GetString(reader.GetOrdinal("PW")); } passWord.CloseConnection(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { passWord.CloseConnection(); } } private void button1_Click(object sender, EventArgs e) { string Id = ""; string pw = ""; //连接数据库 // passWord = new SqLiteHelper("data source=password.db"); passWord = new SqLiteHelper("password.db"); //读取整张表 SQLiteDataReader reader = passWord.ReadFullTable("password"); if (reader.Read()) { //读取ID Id = reader.GetString(reader.GetOrdinal("ID")); pw = reader.GetString(reader.GetOrdinal("PW")); if (pw == richTextBox2.Text.Trim().ToString() && Id == richTextBox1.Text.Trim().ToString()) { MessageBox.Show("用户名与密码正确：" + reader.GetString(0)); } else { MessageBox.Show("用户名与密码错误：" + reader.GetString(0)); } } passWord.CloseConnection(); } /// <summary> /// 修改密码 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button2_Click(object sender, EventArgs e) { if (!flag) { richTextBox3.Visible = true; label3.Visible = true; flag = true; return; } //连接数据库 try { //passWord = new SqLiteHelper("data source=password.db"); passWord = new SqLiteHelper("password.db"); passWord.UpdateValues("password", new string[] { "PW" }, new string[] { richTextBox3.Text.Trim().ToString() }, "ID", richTextBox1.Text.Trim().ToString(), "=", "ID","小四"); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { passWord.CloseConnection(); } } }
}
