之前发布了一篇c#使用sqlite时正则表达式函数REGEXP如何使用的文章,今天摸索出了用正则表达式实现各种自己想要的排序,注册REGEXP函数时改用Regex.Match函数,直接上代码:
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
{
string sql = "insert into highscores (name, score) values ('A1', 3000)";
SQLiteCommand command = new SQLiteCommand(sql, sdbConn);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('A2', 3000)";
command = new SQLiteCommand(sql, sdbConn);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('A11', 3000)";
command = new SQLiteCommand(sql, sdbConn);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('AA1', 3000)";
command = new SQLiteCommand(sql, sdbConn);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('B1', 3000)";
command = new SQLiteCommand(sql, sdbConn);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('2', 3000)";
command = new SQLiteCommand(sql, sdbConn);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('11', 3000)";
command = new SQLiteCommand(sql, sdbConn);
command.ExecuteNonQuery();
trans.Commit();//提交事务
}
catch (Exception e)
{
trans.Rollback();//回滚事务
}
}
//使用sql查询语句,并显示结果
void printHighscores()
{
string sql = "select * from highscores WHERE LENGTH(name REGEXP '[A-Z]+') > 0 ORDER BY LENGTH (name REGEXP '[A-Z]+'), name REGEXP '[A-Z]+', LENGTH(name REGEXP '[0-9]+'), name REGEXP '[0-9]+' ";
SQLiteCommand command = new SQLiteCommand(sql, sdbConn);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
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]));
return System.Text.RegularExpressions.Regex.Match(Convert.ToString(args[1]), Convert.ToString(args[0])).ToString();
}
}
}
}