C#利用System.Data.SQLite实现对SQLite的操作

Myth丶恋晨 2022-04-22 13:54 255阅读 0赞

## **SQLite介绍** ##

SQLite是一个类似于Access的单机版数据库管理系统，它将所有数据库的定义（包括定义、表、索引和数据本身）都保存在一个单一的文件中。并且，SQLite是一个用C实现的类库，它在内存消耗、文件体积、简单性方面都有不错的表现，如果数据在10W条以下，查询速度也是相当快的。  
SQLite具有以下特征：  
实现多数SQL92的标准，包括事务（原子性、一致性、隔离性和持久性）、触发器和大多数的复杂查询。  
不对插入或者更新的数据进行类型检查，你可以将字符串插入到整数列中（这个可能让有些用户不太适应）。  
支持Windows/Linux/Unix等主流系统，还支持嵌入式系统如Android或Windows Mobile。

## **System.Data.SQLite的前期准备** ##

1、System.Data.SQLite 库下载，用于C\#操作SQLite的dll文件。下载地址：[http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki][http_system.data.sqlite.org_index.html_doc_trunk_www_downloads.wiki]  
2、SQLite可视化工具下载，用于查看SQLite库数据表数据。下载地址：[http://www.sqliteexpert.com/download.html][http_www.sqliteexpert.com_download.html]

## **System.Data.SQLite通用类** ##

可以分为以下几种情况：  
1、创建数据库文件；  
2、返回DataTable；  
3、返回DataReader；  
4、执行增删改，返回受影响的行数；  
5、执行查询，返回第一行第一列（通常用于带有行函数的查询，如SUM/AVG/COUNT等）；  
6、返回库中所有的表；  
**因为在System.Data.SQLite中不存在存储过程，所以所有的操作都是基于文本的SQL语句，为了避免SQL注入，所以使用了参数化的SQL语句。**

## 一、创建、删除SQLite数据库文件 ##

为了增加格式的规范性，在项目的App.config文件的配置文本中添加数据库文件路径。  
如下connectionStrings标签：

<?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <startup> 
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
        </startup>
      <connectionStrings>
        <add name="itcastCater" connectionString="data source=C:\Users\**\**\itcastCater.db;version=3;"/>
      </connectionStrings>
    </configuration>

运行方法 *SQLiteConnection.open* 就会创建个空的指定名字的数据库文件。

using System.Data.SQLite;
    using System.Configuration;
    
     //从app.config配置文本中读取连接字符串,获取数据库文件的绝对路径“Data Sounce”
        private static string connStr = ConfigurationManager.ConnectionStrings["itcastCater"].ConnectionString;
        
    static void Create()
    {
        SQLiteConnection cn = new SQLiteConnection(connStr);
        //按照路径创建数据库文件
        cn.Open();
        cn.Close();
    }

由于数据库是文件类型的，直接用System.IO.File.Delete(string path) 方法来删除文件。

//删除数据库
    static void Delete()
    {
        if (System.IO.File.Exists(connStr))
        {
            System.IO.File.Delete(connStr);
        }
    }

## 二、创建、删除数据库表 ##

创建表格要用到 SQL 命令。  
建立一个表的顺序如下步骤（也可以用可视化工具 SQLiteExpert 来创建）：  
1、建立数据库连接；  
2、打开数据库（如果没有数据库，Open 也会新创建一个数据库）；  
3、声明一个 **SQLiteCommand** 类，主要用来放置和运行 SQL 命令的；  
4、把 **SQLiteCommand** 的 **Connection** 和 **SQLiteConnection** 联系起来；  
5、往 **SQLiteCommand** 的 **CommandText** 输入 SQL 语句 **CREATE TABLE** 语句；  
6、调用 **SQLiteCommand.ExcuteNonQuery()** 方法运行。

//创建数据库表
    static void CreateTable()
    {
        SQLiteConnection cn = new SQLiteConnection(connStr);//建立数据库连接
        if (cn.State!= System.Data.ConnectionState.Open)
        {
            cn.Open();//打开数据库
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = cn;//把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
            cmd.CommandText = "CREATE TABLE IF NOT EXISTS t1(id varchar(4),score int)";//输入SQL语句
            cmd.ExecuteNonQuery();//调用此方法运行
        }
        cn.Close();
    }

上面的SQL语句中存在一句“IF NOT EXISTS”，最好加上此句，如果不加此句且原数据库文件要是存在同名要创建的表，会出现报错。SQL语句大小写并没有规定，一般为了规范用大写。

删除表和建立表的步骤一样，只是把 SQL 语句改了而已：

//删除数据库表
    static void DeleteTable()
    {
        SQLiteConnection cn = new SQLiteConnection(connStr);
        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();
    }

**更改表名**  
用 SQL 语句 ALTER TABLE 把 t1 表名改成 t3

//在与数据库文件连接成功后
    cmd.CommandText = "ALTER TABLE t1 RENAME TO t3";
    cmd.ExecuteNonQuery();

## 三、查询表结构 ##

查询表的结构需要用到SQLite特殊的PRAGMA命令  
以下为SQliteDataReader读出来的数据顺序列表：

<table> 
 <thead> 
  <tr> 
   <th>下标</th> 
   <th>名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0</td> 
   <td>cid</td> 
   <td>序号</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>name</td> 
   <td>名字</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>type</td> 
   <td>数据类型</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>notnull</td> 
   <td>能否null值，0不能，1能</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>dflt_value</td> 
   <td>缺省值</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>pk</td> 
   <td>是否主键primary key,0否，1是</td> 
  </tr> 
 </tbody> 
</table>

代码如下：

//连接到数据库文件，使用PRAGMA命令准备
    SQLiteConnection cn = new SQLiteConnection(connStr);
    cn.Open();
    SQLiteCommand cmd = cn.CreateCommand();
    cmd.CommandText= "PRAGMA table_info('t1')";
    
    //方法一：用DataAdapter和DataTable类，调用方法为using System.Data
    SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
    DataTable table = new DataTable();
    adapter.Fill(table);
    foreach(DataRow r in table.Rows)
    {
        Console.WriteLine($"{r["cid"]},{r["name"]},{r["type"]},{r["notnull"]},{r["dflt_value"]},{r["pk"]} ");
    }
    Console.WriteLine();
    
    //方法二：用DataReader，这个效率高些
    SQLiteDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        for(int i = 0; i < reader.FieldCount; i++)
        {
            Console.Write($"{reader[i]},");
        }
        Console.WriteLine();
    }
    reader.Close();

**如果不止一个表，要遍历所有表的结构如下，就要用到 SQLite 中的特殊表 sqlite\_master**  
当 type = table 时，name 和 tbl\_name 是一样的，其他比如 type =index 、view 之类时，tbl\_name 才是表名。

//遍历查询表结构
    static void QueryAllTableInfo()
    {
        SQLiteConnection cn = new SQLiteConnection(connStr);
        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 表添加一个名为 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())。

第二种是：  
更改 sqlite\_master 里面的 schema，很容易损坏数据库。  
依据是 SQLite 每次连接时，其实都是依据 schema 里面的每个表创建时的 CREATE TABLE 语句来动态建立 column 的信息的，只要 column 的数据类型和位置不变，更改 CREATE TABLE 语句就能更改 column 的信息。  
此为以下两种方法：

方式一：

//更改列名1
    //把旧表更名，建个带新列名的新表，拷贝数据
    //params string[] 中：connStr 数据库名，1 表名，2 旧列名 3 新列名
    static void RenameColumn1(params string[] str)
    {
        SQLiteConnection cn = new SQLiteConnection(connStr);
        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();
    
        string _sql = ""; 
        while (sr.Read())
        {
            if (string.Compare(sr.GetString(connStr), str[1], true) == 0)
            {
                _sql = sr.GetString(1);
                break;
            }
        }
        sr.Close();
    
        //更改旧表名为 带 _old 
        string _old = str[1] + "_old";
        cmd.CommandText = $"ALTER TABLE {str[1]} RENAME TO {_old}";
        cmd.ExecuteNonQuery();
    
        //建立新表，假设输入的旧列名和表中的列名大小写等完全一致，不写能容错的了
        _sql = _sql.Replace(str[2],str[3]);
        cmd.CommandText = _sql;
        cmd.ExecuteNonQuery();
    
        //拷贝数据
        using (SQLiteTransaction tr = cn.BeginTransaction())
        {
            cmd.CommandText = $"INSERT INTO {str[1]} SELECT * FROM {_old}";
            cmd.ExecuteNonQuery();
            cmd.CommandText = $"DROP TABLE {_old}";
            cmd.ExecuteNonQuery();
            tr.Commit();
        }
        cn.Close();
    }

方式二：

//更改列名2,改写schema里建表时的sql语句
    //原理：sqlite 每次打开的时候，都是依据建表时的sql语句来动态建立column的信息的
    static void RenameColumn2(params string[] str)
    {
        SQLiteConnection cn = new SQLiteConnection(connStr);
        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(connStr);
        sr.Close();
        //注意单引号 '
        _sql =$"UPDATE sqlite_master SET sql='{_sql.Replace(str[2],str[3])}' WHERE name= '{str[1]}' ";
    
        //设置 writable_schema 为 true，准备改写schema 
        cmd.CommandText = "pragma writable_schema=1";
        cmd.ExecuteNonQuery();
        cmd.CommandText = _sql;
        cmd.ExecuteNonQuery();
        //设置 writable_schema 为 false。
        cmd.CommandText = "pragma writable_schema=0";
        cmd.ExecuteNonQuery();
    
        cn.Close();
    }

**删除列**  
SQLite 也没有提供删除列的命令。和上面一样，也是两种方式。  
其一，把目标表改名，建立没有要删除列（字段）的新表，然后把旧表的数据拷贝至新表。  
其二，直接修改 schema 中建表的 SQL 语句。  
其中最主要的是要把建表的列的所有信息都保存下来，比如索引、缺省值之类的。  
示例使用第二种方式：

//删除列2，string[] ,connStr 数据库路径，1 表名，2 要删除的列名
    static void DeleteColumn2(params string[] str)
    {
        SQLiteConnection cn = new SQLiteConnection(connStr);
        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(connStr);
        sr.Close();
    
        //取得列的定义
        //C#7.0的新特征，Tuple<>的语法糖，需要 NuGet install-package system.valuetuple
        List<(string name, string define)> list = GetColumnDefine(_sql);
        //取得要删除列的序号
        int _index = list.IndexOf(list.Where(x => x.name == str[2]).First());
        //建立新的sql语句
        StringBuilder sb = new StringBuilder();
        sb.Append($"CREATE TABLE {str[1]}(");
        for (int i = 0; i < list.Count; i++)
        {
            if (i != _index)//除了要删除的列，其他列复制出来。
            {
                sb.Append($"{list[i].define},");
            }
        }
        sb.Remove(sb.Length - 1, 1);
        sb.Append(")");
        //改写schema
        _sql = $"UPDATE sqlite_master SET sql='{sb.ToString()}' WHERE name='{str[1]}'";
        //设置 writable_schema 为 true，准备改写schema 
        cmd.CommandText = "pragma writable_schema=1";
        cmd.ExecuteNonQuery();
        cmd.CommandText = _sql;
        cmd.ExecuteNonQuery();
        //设置 writable_schema 为 false。
        cmd.CommandText = "pragma writable_schema=0";
        cmd.ExecuteNonQuery();
    
        cn.Close();
    }

**取得列的定义**

//取得列的定义
    static List<(string, string)> GetColumnDefine(string SqlStr)
    {
        int n = 0;
        int _start = 0;
        string _columnStr = "";
        for (int i = 0; i < SqlStr.Length; i++)
        {
            if (SqlStr[i] == '(')
            {
                if (n++ == 0) { _start = i; }
            }
            else
            {
                if (SqlStr[i] == ')')
                {
                    if (--n == 0)
                    {
                        _columnStr = SqlStr.Substring(_start + 1, i - _start - 1);
                        break;
                    }
                }
    
            }
        }
        string[] ss = _columnStr.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
        //C#7.0的新特征，Tuple<>的语法糖，需要 NuGet install-package system.valuetuple
        List<(string name, string define)> reslut = new List<(string name, string define)>();
        foreach (var a in ss)
        {
            string s = a.Trim();
            n = 0;
            for (int i = 0; i < s.Length; i++)
            {
                if (s[i] == ' ')
                {
                    reslut.Add((s.Substring(0, i), s));
                    break;
                }
            }
        }
        return reslut;
    }

## 五、增删改查数据 ##

**插入数据**

插入数据主要是用 SQL 语句 INSERT INTO  
*示例1（简单插入）：*

cmd.CommandText = "INSERT INTO t1 VALUES('99999',11)";
    cmd.ExecuteNonQuery();

*示例2（变量插入，要引用 System.Data）：*

using System.Data;
    
    string s = "123456";
    int n = 10;
    cmd.CommandText = "INSERT 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 命令 REPLACE 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 教程][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();

[http_system.data.sqlite.org_index.html_doc_trunk_www_downloads.wiki]: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
[http_www.sqliteexpert.com_download.html]: http://www.sqliteexpert.com/download.html
[SQL]: http://www.w3school.com.cn/sql/index.asp