C#-SQLite-使用教程笔记

微软官网资料链接（可下载文档）

教程参考链接：SQLite 教程 - SQLite中文手册

项目中对应的system.dat文件可以用SQLiteStudio打开查看

参考文档：https://d7ehk.jb51.net/202008/books/SQLite_jb51.rar

总结介绍

1、下载SQLiteStudio对数据库文件进行管理、查看

2、代码中通过NuGet添加System.Data.SQLite

3、代码类：SQLiteConnection连接本地数据库文件》SQLiteCommand配置命令并执行》连接关闭

一、SQLite数据下载，安装，使用（管理查看数据）

1，下载

下载绿色免安装版本

链接：百度网盘 请输入提取码

提取码：hgoc

1、参考网站：SQLite Home Page，下载地址：System.Data.SQLite: Downloads Page

2，安装

　　免安装，双击SQLiteStudio可以打开使用，如果放在项目中，建议复制到C:\Program Files (x86) ，防止被误删除掉

3，添加数据库

　　数据库》添加数据库》输入数据名称》确定

4，创建数据表

　　Tables》新建表

 自动生成的创建表的sql语句

1 2 3 4 5 6 7 8

CREATE TABLE SysAdmin (      LoginID   INTEGER      PRIMARY KEY AUTOINCREMENT                             DEFAULT (10000),      LoginName VARCHAR (20) NOT NULL,      LoginPwd  VARCHAR (50) NOT NULL,      Role      INTEGER      DEFAULT (0)                             NOT NULL );

　　

5，添加字段

6，数据库的位置

　　数据表都创建好了之后，鼠标放在数据库名上，就显示数据库所在的目录

二、C#使用SQLite数据需要添加的引用（上面的wiki路径下载里有）

1，VS使用NuGet添加使用，搜索：System.Data.SQLite添加即可

三、代码中数据库命令操作

包含功能：增删数据库文件；增删改数据表；增删改查数据内容

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;namespace MyFrom
{public partial class Form1 : Form{public Form1(){InitializeComponent();}// 添加数据表private void button1_Click(object sender, EventArgs e){string path = Application.StartupPath + "\\test.db";SQLiteConnection cn = new SQLiteConnection("data source=" + path);//cn.Open();cn.Close();label1.Text = "添加数据库完成";}// 删除数据表private void button2_Click(object sender, EventArgs e){string path = Application.StartupPath + "\\test.db";if (System.IO.File.Exists(path)){System.IO.File.Delete(path);}label1.Text = "删除数据库完成";}// 添加数据表private void button3_Click(object sender, EventArgs e){string path = Application.StartupPath + "\\test.db";SQLiteConnection cn = new SQLiteConnection("data source=" + path);if (cn.State != System.Data.ConnectionState.Open){cn.Open();SQLiteCommand cmd = new SQLiteCommand();cmd.Connection = cn;// cmd.CommandText = "CREATE TABLE t1(time string，id varchar(4),score int)";cmd.CommandText = "CREATE TABLE IF NOT EXISTS t1(time string,id varchar(4),score int)";cmd.ExecuteNonQuery();}cn.Close();label1.Text = "添加数据表完成";}// 删除数据表private void button4_Click(object sender, EventArgs e){string path = Application.StartupPath + "\\test.db";SQLiteConnection cn = new SQLiteConnection("data source=" + path);if (cn.State != System.Data.ConnectionState.Open){cn.Open();SQLiteCommand cmd = new SQLiteCommand();cmd.Connection = cn;cmd.CommandText = "DROP TABLE IF EXISTS t1";cmd.ExecuteNonQuery();}cn.Close();label1.Text = "删除数据表完成";}// 更改数据表名private void button5_Click(object sender, EventArgs e){string path = Application.StartupPath + "\\test.db";SQLiteConnection cn = new SQLiteConnection("data source=" + path);if (cn.State != System.Data.ConnectionState.Open){cn.Open();SQLiteCommand cmd = new SQLiteCommand();cmd.Connection = cn;cmd.CommandText = "ALTER TABLE t3 RENAME TO t1";cmd.ExecuteNonQuery();}cn.Close();label1.Text = "更改表名完成";}// 添加数据表列元素private void button6_Click(object sender, EventArgs e){string path = Application.StartupPath + "\\test.db";SQLiteConnection cn = new SQLiteConnection("data source=" + path);if (cn.State != System.Data.ConnectionState.Open){cn.Open();SQLiteCommand cmd = new SQLiteCommand();cmd.Connection = cn;cmd.CommandText = "ALTER TABLE t1 ADD COLUMN age int";cmd.ExecuteNonQuery();}cn.Close();label1.Text = "添加列完成";}// 添加数据private void button7_Click(object sender, EventArgs e){string path = Application.StartupPath + "\\test.db";SQLiteConnection cn = new SQLiteConnection("data source=" + path);if (cn.State != System.Data.ConnectionState.Open){cn.Open();SQLiteCommand cmd = new SQLiteCommand();cmd.Connection = cn;//time string,id varchar(4),score intcmd.CommandText = "INSERT INTO t1(time,id,score) VALUES(@time,@id,@score)";cmd.Parameters.Add("id", DbType.String).Value = "666";//cmd.Parameters.Add("age", DbType.Int32).Value = n;cmd.Parameters.Add("score", DbType.Int32).Value = 22;cmd.Parameters.Add("time", DbType.String).Value = DateTime.Now.ToString();cmd.ExecuteNonQuery();}cn.Close();label1.Text = "添加数据完成";}// 更改数据private void button8_Click(object sender, EventArgs e){string s = "888";int n = 1077777;int myscore = 1;string path = Application.StartupPath + "\\test.db";SQLiteConnection cn = new SQLiteConnection("data source=" + path);if (cn.State != System.Data.ConnectionState.Open){cn.Open();SQLiteCommand cmd = new SQLiteCommand();cmd.Connection = cn;//time string,id varchar(4),score intcmd.CommandText = "UPDATE t1 SET id=@id,age=@age WHERE id='666'";cmd.Parameters.Add("id", DbType.String).Value = s;cmd.Parameters.Add("age", DbType.Int32).Value = n;cmd.ExecuteNonQuery();}cn.Close();label1.Text = "更改数据完成";}// 删除数据private void button9_Click(object sender, EventArgs e){string path = Application.StartupPath + "\\test.db";SQLiteConnection cn = new SQLiteConnection("data source=" + path);if (cn.State != System.Data.ConnectionState.Open){cn.Open();SQLiteCommand cmd = new SQLiteCommand();cmd.Connection = cn;//time string,id varchar(4),score intcmd.CommandText = "DELETE FROM t1 WHERE id='888'";cmd.ExecuteNonQuery();}cn.Close();label1.Text = "删除数据完成";}// 查询数据private void button10_Click(object sender, EventArgs e){string path = Application.StartupPath + "\\test.db";SQLiteConnection cn = new SQLiteConnection("data source=" + path);if (cn.State != System.Data.ConnectionState.Open){cn.Open();SQLiteCommand cmd = new SQLiteCommand();cmd.Connection = cn;//time string,id varchar(4),score intcmd.CommandText = "SELECT * FROM t1 WHERE rowid=2";     // 读取第二行，行数从1开始SQLiteDataReader sr = cmd.ExecuteReader();Console.WriteLine("查询到的数据如下：");while (sr.Read()){int count = sr.VisibleFieldCount;for (int i = 0; i < count; i++){Console.WriteLine(sr[i].ToString() + " ");}string s = sr.GetString(0);Console.WriteLine(s);}sr.Close();}cn.Close();label1.Text = "查询数据完成";}}
}