在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
参考: 章节:
正文: 一、下载安装 这段时间在学习 C# 编程中,想写一个简单的进销存程序,就想到了用数据库,需要一个简单便携的桌面数据库,想自己写个,功力太浅,可以做为以后练手学习的项目。原来会用的 Foxpro 已经被微软不知丢在哪个旮旯了,在网上找了一下,发现只有 Access 和 Sqlite 可选,看了很多对比,决定还是学习使用 Sqlite。 在 System.Data.SQLite 官网的 download 中的 Setups for 64-bit Windows (.NET Framework 4.6) sqlite-netFx46-setup-x64-2015-1.0.104.0.exe (17.99 MiB) 下载然后运行安装。 更简单的做法是在 Visual Studio 2017 的 NuGet 中,输入:install-package system.data.sqlite.x64。 sqlite数据库的可视化工具中, SqliteExpert 不错,下载 SQLite Expert Personal 4.x 。 工具备齐了,由于知道上面这个System data Sqlite 是用 C# 封装好的,下来我们打开Visual Studio 2017,新开个工程,在菜单“项目”→“添加引用”→“浏览” 中,去 Sqlite 的安装目录下选择 System.Data.SQLite.dll,才305k的链接库。引用了后,在VS右上角的“解决方案资源管理器”中看看引用下的 System.Data.SQlite 的引用属性中的“复制到本地” 是不是 true,不是的话弄成 true。在工程中开头添加 using 语句: (ps:在 sqlite 的安装目录下有很详细的帮助文档 SQLite.NET.chm) 二、数据类型 储存的数据类型有以下5种:
注意了,SQLite 的存储宽度是根据输入来自动调整的,这点和原来我用过的 foxpro 不一样,比如就算你在 create 数据表中设定了一个字段 varchar(4) 4byte宽的字符串,但你输入了“hello”5个宽度的字符串时,它并不会截取成“hell”,而是完整地存储为“hello”。数字类型也是如此。 还有更有趣的是,它有个Type Affinity 功能,比如如下: CREATE TABLE t1(a INT, b VARCHAR(10)); INSERT INTO t1(a,b) VALUES('123',456); 三、创建数据库 SQLite 是文件型的数据库,创建很简单,直接指定一个数据库文件名,后缀名不一定非得是“.sqlite”,后缀随便命名为".db"也成。运行 SQLiteConnection.open 就会创建个空的指定名字的数据库文件。由于它是文件型的,我们也可以直接用 System.IO.File.Create() 来创建一个空的文件。 using System.Data.SQLite; //---创建数据库 static void CreateDB() { string path = @"d:\test\123.sqlite"; SQLiteConnection cn = new SQLiteConnection("data source=" + path); cn.Open(); cn.Close(); } 四、删除数据库 sqlite 命令中好像没有提供删除整个数据库的命令,但是由于它是个文件型的,我们直接用 System.IO.File.Delete(string path) 方法来删除文件。 //---删除数据库 static void DeleteDB() { string path = @"d:\test\123.sqlite"; if (System.IO.File.Exists(path)) { System.IO.File.Delete(path); } } 五、创建表 开始要用到 SQL 命令了。建立一个表的顺序如下步骤(也可以用可视化工具 SQLiteExpert 来创建): 1、建立数据库连接; 2、打开数据库(如果没有数据库,Open 也会新创建一个数据库); 3、声明一个 SQLiteCommand 类,主要用来放置和运行 SQL 命令的; 4、把 SQLiteCommand 的 Connection 和 SQLiteConnection 联系起来(切记,经常忘^_^!); 5、往 SQLiteCommand 的 CommandText 输入 SQL 语句 CREATE TABLE 语句,具体请参考 安装目录下的 SQLite.NET.chm 或 SQLite 创建表; 6、调用 SQLiteCommand.ExcuteNonQuery() 方法运行。 //---添加表 static void CreateTable() { string path = @"d:\test\123.sqlite"; 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(id varchar(4),score int)"; //cmd.CommandText = "CREATE TABLE IF NOT EXISTS t1(id varchar(4),score int)"; cmd.ExecuteNonQuery(); } cn.Close(); } 注意上面那句被注释掉的 CREATE TABEL IF NOT EXISTS ,一般情况下用这句比较好,如果原来就有同名的表,没有这句就会出错。SQL 语句其实也不用全部大写,全部大写是 SQL 语句约定俗成的(令我想起读书的时候学的 COBOL),全部小写也不会出错。 六、删除表 和建立表的步骤一样,只是把 SQL 语句改了而已。 //---删除表 static void DeleteTable() { string path = @"d:\test\123.sqlite"; 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(); } 七、查询表结构 需要用到 SQLite 特殊的 PRAGMA 命令, 具体参见 PRAGMA Statements PRAGMA table_info(tablename) ,tablename 用或不用单引号 ' ' 括起来都一样。 SQliteDataReader 读出来的数据顺序代表:
string path = @"d:\test\123.sqlite"; SQLiteConnection cn = new SQLiteConnection("data source=" + path); cn.Open(); SQLiteCommand cmd = cn.CreateCommand(); 如果不止一个表,要遍历所有表的结构如下,就要用到 SQLite 中的特殊表 sqlite_master,它的结构如下: 参考: 2.6. Storage Of The SQL Database Schema CREATE TABLE sqlite_master( type text, name text, tbl_name text, rootpage integer, sql text ); 当 type = table 时,name 和 tbl_name 是一样的,其他比如 type =index 、view 之类时,tbl_name 才是表名。 //---遍历查询表结构 static void QueryAllTableInfo() { string path = @"d:\test\123.sqlite"; 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 = "SELECT name FROM sqlite_master WHERE TYPE='table' "; SQLiteDataReader sr = cmd.ExecuteReader(); List<string> tables = new List<string>(); while (sr.Read()) { tables.Add(sr.GetString(0)); } //datareader 必须要先关闭,否则 commandText 不能赋值 sr.Close(); foreach (var a in tables) { cmd.CommandText = $"PRAGMA TABLE_INFO({a})"; sr = cmd.ExecuteReader(); while (sr.Read()) { Console.WriteLine($"{sr[0]} {sr[1]} {sr[2]} {sr[3]}"); } sr.Close(); } } cn.Close(); } 八、更改表名 用 SQL 语句 ALTER TABLE 把 t1 表名改成 t3: cmd.CommandText = "ALTER TABLE t1 RENAME TO t3"; cmd.ExecuteNonQuery(); 注意,表名是不分大小写的,也不用加单引号括起来。 九、增添列(字段) 还是用 SQL 命令 ALTER TABLE ,下例中为 t1 表添加一个名为 age,数据类型为 int 的新列: cmd.CommandText = "ALTER TABLE t1 ADD COLUMN age int"; cmd.ExecuteNonQuery(); 十、读取创建表的 SQL 语句 读取创建表时的 SQL 语句,在 SqliteExpert 中的 DDL 可以查看到。读取这个是为下面增添删除列做准备。 cmd.CommandText = "SELECT sql FROM sqlite_master WHERE TYPE='table'"; SQLiteDataReader sr = cmd.ExecuteReader(); while (sr.Read()) { Console.WriteLine(sr[0].ToString()); } sr.Close(); 十一、更改列名 SQLite 中并没有提供直接更改列名与删除列的命令,有两种方式, 第一种是: 1、把目标表改名; 2、创建一个带有新列名的新表; 3、把旧表数据拷贝至新表(记得要 Connection.BeginTransaction())。 方式一: //---更改列名1 //总思路:把旧表更名,建个带新列名的新表,拷贝数据 //params string[] 中:0 数据库名,1 表名,2 旧列名 3 新列名 static void RenameColumn1(params string[] str) { SQLiteConnection cn = new SQLiteConnection("data source=" + str[0]); cn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = cn; //取得str[1]表名的表的建表SQL语句 cmd.CommandText = "SELECT name,sql FROM sqlite_master WHERE TYPE='table' ORDER BY name"; SQLiteDataReader sr = cmd.ExecuteReader(); 方式二: //---更改列名2,改写schema里建表时的sql语句 //原理:sqlite 每次打开的时候,都是依据建表时的sql语句来动态建立column的信息的 static void RenameColumn2(params string[] str) { SQLiteConnection cn = new SQLiteConnection("data source=" + str[0]); cn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = cn; 十二、删除列 SQLite 也没有提供删除列的命令。和上面一样,也是两种方式。 其一,把目标表改名,建立没有要删除列(字段)的新表,然后把旧表的数据拷贝至新表。 其二,直接修改 schema 中建表的 SQL 语句。 其中最主要的是要把建表的列的所有信息都保存下来,比如索引、缺省值之类的。下面示例使用第二种方式。 //---删除列2,string[] ,0 数据库路径,1 表名,2 要删除的列名 static void DeleteColumn2(params string[] str) { SQLiteConnection cn = new SQLiteConnection("data source=" + str[0]); cn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = cn; //取得str[1]表名的表的建表SQL语句 cmd.CommandText = $"SELECT sql FROM sqlite_master WHERE TYPE='table' AND name='{str[1]}'"; SQLiteDataReader sr = cmd.ExecuteReader(); sr.Read(); string _sql = sr.GetString(0); sr.Close(); 十三、插入数据 插入数据主要是用 SQL 语句 INSERT INTO 示例1(简单插入): cmd.CommandText = "INSERT INTO t1 VALUES('99999',11)"; cmd.ExecuteNonQuery(); 示例2(变量插入,要引用 System.Data): using System.Data; 十四、替换数据 SQL 命令 INSERT INTO。 下面示例中, t1 表中 id 为主键,相同主键值的就 UPDATE,否则就 INSERT string s = "123456"; int n = 30; cmd.CommandText = "REPLACE INTO t1(id,age) VALUES(@id,@age)"; cmd.Parameters.Add("id", DbType.String).Value = s; cmd.Parameters.Add("age", DbType.Int32).Value = n; cmd.ExecuteNonQuery(); 十五、更新数据 SQL 命令 UPDATE tablename SET column1=value,column2=value... WHERE 条件 string s = "333444"; int n = 30; cmd.CommandText = "UPDATE t1 SET id=@id,age=@age WHERE id='0123456789'"; cmd.Parameters.Add("id", DbType.String).Value = s; cmd.Parameters.Add("age", DbType.Int32).Value = n; cmd.ExecuteNonQuery(); 十六、删除数据 SQL 命令:DELETE FROM tablename WHERE 条件 cmd.CommandText = "DELETE FROM t1 WHERE id='99999'"; cmd.ExecuteNonQuery(); 十七、查询数据 SQL 命令:SELETE 语句,具体的请参考 SQL 教程。 //查询第1条记录,这个并不保险,rowid 并不是连续的,只是和当时插入有关 cmd.CommandText = "SELECT * FROM t1 WHERE rowid=1"; SQLiteDataReader sr = cmd.ExecuteReader(); while (sr.Read()) { Console.WriteLine($"{sr.GetString(0)} {sr.GetInt32(1).ToString()}"); } sr.Close(); //运行以下的就能知道 rowid 并不能代表 行数 cmd.CommandText = "SELECT rowid FROM t1 "; sr = cmd.ExecuteReader(); while (sr.Read()) { Console.WriteLine($"{sr.GetString(0)} {sr.GetInt32(1).ToString()}"); } sr.Close(); 十八、获取查询数据的行数(多少条记录) 从上面示例中我们得知,rowid 并不是正确的行数(记录数),而是 INSERT 的时候的B-Tree 的相关数。 如要知道表中的行数(记录数),要如下: cmd.CommandText = "SELECT count(*) FROM t1"; sr = cmd.ExecuteReader(); sr.Read(); Console.WriteLine(sr.GetInt32(0).ToString()); sr.Close(); 十九、事务 事务就是对数据库一组按逻辑顺序操作的执行单元。用事务的好处就是成熟的数据库都对 密集型的磁盘 IO 操作之类进行优化,而且还能进行撤回回滚操作。其实在上面改变列名的示例中就用过。 //---事务 static void TransActionOperate(SQLiteConnection cn,SQLiteCommand cmd) { using (SQLiteTransaction tr = cn.BeginTransaction()) { string s = ""; int n = 0; cmd.CommandText = "INSERT INTO t2(id,score) VALUES(@id,@score)"; cmd.Parameters.Add("id", DbType.String); cmd.Parameters.Add("score", DbType.Int32); for (int i = 0; i < 10; i++) { s = i.ToString(); n = i; cmd.Parameters[0].Value = s; cmd.Parameters[1].Value = n; cmd.ExecuteNonQuery(); } tr.Commit(); } } 二十、整理数据库 SQLite 的自带命令 VACUUM。用来重新整理整个数据库达到紧凑之用,比如把删除的彻底删掉等等。 cmd.CommandText = "VACUUM"; cmd.ExecuteNonQuery(); 到这里 SQLite 数据库基本上能操作了,至于那些用 linq 操作等的需要安装 ORM 的,我想了一下,下次再学习吧。对于我的小项目来说,带着两个加起来不到 1.5M的 dll ,还是很简练的。 SQLite 也是数据库,主要的还是各种 SQL 语句的调用,着眼于 SQL 语句的学习是下段时间我折腾的目标。 看到满大街的 SQLiteHelper ,我想了下,就我这水平就不班门弄斧了,即使我也会偷偷写个,方便调用。 |
请发表评论