C# sqlite 正则表达式

1. List item

感谢：
1.https://www.cnblogs.com/luxiaoxun/p/3784729.html
2.https://blog.csdn.net/realtime2020/article/details/20778913
3.https://www.iteye.com/blog/lexus-742671
直接上代码，关键是最后，需要注册一个REGEXP函数

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace SQLlite
{
    class Program
    {
        static void Main(string[] args)
        {
            SQLiteDB sdb = new SQLiteDB();
            sdb.Program();
        }        
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace SQLlite
{
    class SQLiteDB
    {
        SQLiteConnection sdbConn = new SQLiteConnection();

        public void Program()
        {
            createNewDatabase();
            connectToDatabase();
            createTable();
            fillTable();
            printHighscores();
        }

        //创建一个空的数据库
        void createNewDatabase()
        {
            SQLiteConnection.CreateFile("MyDatabase.sqlite");
        }

        //创建一个连接到指定数据库
        void connectToDatabase()
        {
            sdbConn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
            sdbConn.Open();
        }

        //在指定数据库中创建一个table
        void createTable()
        {
            string sql = "create table highscores (name varchar(20), score int)";
            SQLiteCommand command = new SQLiteCommand(sql, sdbConn);
            command.ExecuteNonQuery();
        }

        //插入一些数据
        void fillTable()
        {
            IDbTransaction trans = sdbConn.BeginTransaction();
            try
            {
                for (int i = 0; i < 5; i++)
                {
                    string sql = "insert into highscores (name, score) values ('C123', 3000)";
                    SQLiteCommand command = new SQLiteCommand(sql, sdbConn);
                    command.ExecuteNonQuery();
                }
                for (int i = 0; i < 5; i++)
                {
                    string sql = "insert into highscores (name, score) values ('CNTR123', 3000)";
                    SQLiteCommand command = new SQLiteCommand(sql, sdbConn);
                    command.ExecuteNonQuery();
                }
                trans.Commit();//提交事务
            }
            catch (Exception e)
            {                
                trans.Rollback();//回滚事务
            }
        }

        //使用sql查询语句，并显示结果
        void printHighscores()
        {
            string sql = "select * from highscores where name REGEXP '^C[0-9]+' order by score desc";
                        
            SQLiteCommand command = new SQLiteCommand(sql, sdbConn);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
            }
            Console.WriteLine("OK");
            Console.ReadLine();
        }

        [SQLiteFunction(Name="REGEXP",Arguments=2,FuncType=FunctionType.Scalar)]
        public class REGEXP : SQLiteFunction
        {
            public override object Invoke(object[] args)
            {
                return System.Text.RegularExpressions.Regex.IsMatch(Convert.ToString(args[1]), Convert.ToString(args[0]));
            }
        }        
    }
}