微软官网资料链接(可下载文档)
教程参考链接: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添加即可
三、代码中数据库命令操作文章来源:https://www.toymoban.com/news/detail-700290.html
包含功能:增删数据库文件;增删改数据表;增删改查数据内容文章来源地址https://www.toymoban.com/news/detail-700290.html
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 int
cmd.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 int
cmd.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 int
cmd.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 int
cmd.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 = "查询数据完成";
}
}
}
到了这里,关于C#-SQLite-使用教程笔记的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!