利用SQLiteFunction生成的自定义函数构建SQL语句示例

SQLite的内置函数非常有限，往往达不到实际需求，不过它提供了SQLiteFunction，可以让用户自定义函数来在sql语句中使用，如下是使用在C#中使用自定义函数的示例

函数定义

using System;
using System.Data.SQLite;
using System.Linq;

namespace TS.BLL
{
    /*SQLiteFunction 有三种类型函数可以自定义，分别是：Scalar,Aggregate,Collation。 
    Scalar：标量（对单条数据进行计算的函数） 
    Aggregate：聚合（对多条数据进行计算的函数） 
    Collation：集合（用于排序）
    Arguments = 1 如果为-1则可接受任意数量参数

     com.CommandText = "select name from student ORDER BY name COLLATE  pinyin";
     com.CommandText = "select sqrt(id) as value from student ";
     com.CommandText = "select mean(high) as val from student ";
 */

    /// <summary>
    /// Sqlite自定义函数
    /// </summary>
    public class SQLiteFun : SQLiteFunction
    {

        #region 注册自定义函数
        public static void RegisterFun()
        {
            //注册自定义函数
            SQLiteFunction.RegisterFunction(typeof(TotalNums));
            SQLiteFunction.RegisterFunction(typeof(TotalLevels));
            SQLiteFunction.RegisterFunction(typeof(Mean));
            SQLiteFunction.RegisterFunction(typeof(Sqrt));
            SQLiteFunction.RegisterFunction(typeof(PinYin));

        }


        #endregion
        [SQLiteFunction(Name = "TotalLevels", Arguments = 1, FuncType = FunctionType.Scalar)]
        public class TotalLevels : SQLiteFunction
        {
            public override object Invoke(object[] args)
            {
                var ms = args[0].ToString().Split(new string[] { "\r\n", "\n", "\r" }, StringSplitOptions.RemoveEmptyEntries);
                return ms.Count();

            }
        }

        [SQLiteFunction(Name = "TotalNums", Arguments = 1, FuncType = FunctionType.Scalar)]
        public class TotalNums : SQLiteFunction
        {
            public override object Invoke(object[] args)
            {
                var ms = args[0].ToString().Split(new string[] { "\r\n", "\n", "\r" }, StringSplitOptions.RemoveEmptyEntries);
                int n = 0;
                foreach (var s in ms)
                {
                    n += s.Split(',').Count();
                }
                return n;

            }
        }

        /// <summary>
        /// 求平方根
        /// </summary>
        [SQLiteFunction(Name = "sqrt", Arguments = 1, FuncType = FunctionType.Scalar)]
        public class Sqrt : SQLiteFunction
        {
            public override object Invoke(object[] args)
            {
                double d = Convert.ToDouble(args[0]);
                return Math.Sqrt(d);
            }
        }

        /// <summary>
        /// 求平均
        /// </summary>
        [SQLiteFunction(Name = "mean", Arguments = -1, FuncType = FunctionType.Aggregate)]
        public class Mean : SQLiteFunction
        {
            int step = 0;
            public override void Step(object[] args, int stepNumber, ref object contextData)
            {
                double sum = Convert.ToDouble(contextData);
                sum += Convert.ToDouble(args[0]);
                contextData = sum;
                step++;
            }
            public override object Final(object contextData)
            {
                double sum = Convert.ToDouble(contextData);
                double mean = sum / step;
                return mean;
            }

        }

        /// <summary>
        /// 中文排序
        /// </summary>
        [SQLiteFunction(FuncType = FunctionType.Collation, Name = "PinYin")]
        public class PinYin : SQLiteFunction
        {
            public override int Compare(string x, string y)
            {
                return string.Compare(x, y);
            }
        }
    }

}

SQL语句构建

 public DataTable GetTjFileDetailTable(CategoryCombin ccbInfo, DateTime beginDate, DateTime endDate)
 {
     if (ccbInfo.Content==null)
     {
         return null;
     }
     string dateSpanString = beginDate.ToString("yyyyMMdd") + "-" + endDate.ToString("yyyyMMdd");
     var ws = ccbInfo.Content.Split(',');
     var s1 = string.Join(",", ws.Select(x => $"Count(case when a.CategoryName='{x}' then a.CategoryName  end) as {x}记录").ToArray());
     var s2 = string.Join(",", ws.Select(x => $"sum(case when a.CategoryName='{x}' then a.Levels else 0 end) as {x}行组").ToArray());
     var s3 = string.Join(",", ws.Select(x => $"sum(case when a.CategoryName='{x}' then a.Words else 0 end) as {x}数据数").ToArray());
     var wheres = string.Join(" OR ", ws.Select(x => $"Name='{x}'").ToArray());

     var sql = $@"
           SELECT a.FName as 文件名称,
           a.DateSpan  as 日期区间,
           a.SelectOption 分类选择,
           Count() as 记录数 ,
           sum(a.Levels) as 行组合计数,
           sum(a.Words) as 数据总数,
           {s1},{s2},{s3} 
      FROM  
    (
        SELECT DT_StockFiles.FID,
               DT_StockFiles.FName,
               '{dateSpanString}' AS DateSpan,
               DT_StockFileDetails.CategoryID,
               BZ_Category.Name AS CategoryName,
               '{ccbInfo.Name}' AS SelectOption, 
               DT_StockFileDetails.Endorsement,
               TotalLevels(DT_StockFileDetails.Endorsement) AS Levels,
               TotalNums(DT_StockFileDetails.Endorsement) AS Words
          FROM DT_StockFileDetails
               INNER JOIN
               DT_StockFiles ON DT_StockFileDetails.FID = DT_StockFiles.FID
               INNER JOIN
               BZ_Category ON DT_StockFileDetails.CategoryID = BZ_Category.ID
         WHERE DT_Stockfiledetails.CategoryID IN (
                   SELECT ID
                     FROM BZ_Category
                    WHERE ({wheres}) 
               )
             AND 
               DT_StockFiles.FDate >= '{beginDate.ToString("yyyy-MM-dd")}' AND 
               DT_StockFiles.FDate <= '{endDate.ToString("yyyy-MM-dd")}'
         ORDER BY DT_StockFiles.FName,
                  DT_StockFiles.FDate
    )AS a  group by a.Fname";

     return App.DB.GetDataTable(sql);

 }