DateTime? startTime = null;

var session = LiteSqlFactory.GetSession();

session.OnExecuting = (s, p) => Console.WriteLine(s); //печать SQL

List<SysUser> list = session.CreateSql(@"
    select * from sys_user t where t.id <= @Id", new { Id = 20 })

    .Append(@" and t.create_userid = @CreateUserId 
        and t.password like @Password
        and t.id in @Ids",
        new
        {
            CreateUserId = "1",
            Password = "%345%",
            Ids = session.ForList(new List<int> { 1, 2, 9, 10, 11 })
        })

    .AppendIf(startTime.HasValue, " and t.create_time >= @StartTime ", new { StartTime = startTime })

    .Append(" and t.create_time <= @EndTime ", new { EndTime = new DateTime(2022, 8, 1) })

    .QueryList<SysUser>();

long id = session.CreateSql("select id from sys_user where id=@Id", new { Id = 1 })
    .QuerySingle<long>();
Assert.IsTrue(id == 1);

foreach (SysUser item in list)
{
    Console.WriteLine(ModelToStringUtil.ToString(item));
}
Assert.IsTrue(list.Count > 0);

Install-Package Dapper.LiteSql -Version 1.6.20

Install-Package MySql.Data -Version 6.9.12

using LiteSql;
using MySql.Data.MySqlClient;
using System.Data.Common;

namespace DAL
{
    public class MySQLProvider : MySQLProviderBase, IDBProvider
    {
        #region Создание DbConnection
        public override DbConnection CreateConnection(string connectionString)
        {
            return new MySqlConnection(connectionString);
        }
        #endregion

        #region Генерация DbParameter
        public override DbParameter GetDbParameter(string name, object value)
        {
            return new MySqlParameter(name, value);
        }
        #endregion

    }
}

using LiteSql;
using System.Configuration;
using System.Threading.Tasks;

namespace DAL
{
    public class LiteSqlFactory
    {
        #region Переменные
        private static ILiteSqlClient _liteSqlClient = new LiteSqlClient(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(), DBType.MySQL, new MySQLProvider());
        #endregion

        #region Получение ISession
        /// <summary>
        /// Получение ISession
        /// </summary>
        /// <param name="splitTableMapping">Разделение таблиц</param>
        public static ISession GetSession(SplitTableMapping splitTableMapping = null)
        {
            return _liteSqlClient.GetSession(splitTableMapping);
        }
        #endregion

        #region Получение ISession (асинхронно)
        /// <summary>
        /// Получение ISession (асинхронно)
        /// </summary>
        /// <param name="splitTableMapping">Разделение таблиц</param>
        public static async Task<ISession> GetSessionAsync(SplitTableMapping splitTableMapping = null)
        {
            return await _liteSqlClient.GetSessionAsync(splitTableMapping);
        }

``` **Условный запрос (с использованием вспомогательного метода ForList)**

ISqlString sql = session.CreateSql(@"
    select t.*, u.real_name as OrderUserRealName
    from bs_order t
    left join sys_user u on t.order_userid=u.id
    where 1=1");

sql.AppendIf(status.HasValue, " and t.status=@status", status);

sql.AppendIf(!string.IsNullOrWhiteSpace(remark), " and t.remark like @remark", "%" + remark + "%");

sql.AppendIf(startTime.HasValue, " and t.order_time >= @startTime ", startTime);

sql.AppendIf(endTime.HasValue, " and t.order_time <= @endTime ", endTime);

sql.Append(" and t.id in @ids ", sql.ForList(ids.Split(',').ToList()));

sql.Append(" order by t.order_time desc, t.id asc ");

List<BsOrder> list = session.QueryList<BsOrder>(sql);
return list;

**Использование Lambda-выражения для однотабличного запроса**

Однотабличный запрос с разбиением на страницы можно заменить на ToPageList.

```C#
public void TestQueryByLambda6()
{
    var session = LiteSqlFactory.GetSession();

    ISqlQueryable<BsOrder> sql = session.Queryable<BsOrder>();

    string remark = "测试";

    List<BsOrder> list = sql.WhereIf(!string.IsNullOrWhiteSpace(remark),
        t => t.Remark.Contains(remark)
        && t.CreateTime < DateTime.Now
        && t.CreateUserid == "10")

        .OrderByDescending(t => t.OrderTime).OrderBy(t => t.Id)
        .ToList();

    foreach (BsOrder item in list)
    {
        Console.WriteLine(ModelToStringUtil.ToString(item));
    }
}

public void TestQueryByLambda7()
{
    var session = LiteSqlFactory.GetSession();

    ISqlQueryable<BsOrder> sql = session.Queryable<BsOrder>();

    int total;
    List<string> idsNotIn = new List<string>() { "100007", "100008", "100009" };

    List<BsOrder> list = sql
        .Select<SysUser>(u => u.UserName, t => t.OrderUserName)
        .Select<SysUser>(u => u.RealName, t => t.OrderUserRealName)
        .LeftJoin<SysUser>((t, u) => t.OrderUserid == u.Id)
        .LeftJoin<BsOrderDetail>((t, d) => t.Id == d.OrderId)
        .Where<SysUser, BsOrderDetail>((t, u, d) => t.Remark.Contains("订单") && u.CreateUserid == "1" && d.GoodsName != null)
        .WhereIf<BsOrder>(true, t => t.Remark.Contains("测试"))
        .WhereIf<BsOrder>(true, t => !idsNotIn.Contains(t.Id))
        .WhereIf<SysUser>(true, u => u.CreateUserid == "1")
        .OrderByDescending(t => t.OrderTime).OrderBy(t => t.Id)
        .ToPageList(1, 20, out total);

    foreach (BsOrder item in list)
    {
        Console.WriteLine(ModelToStringUtil.ToString(item));
    }
}

public void TestQueryByLambda9()
{
    var session = LiteSqlFactory.GetSession();

    ISqlQueryable<BsOrder> sql = session.CreateSql<BsOrder>(@"
        select t.*, u.real_name as OrderUserRealName
        from bs_order t
        left join sys_user u on t.order_userid=u.id");

    List<BsOrder> list = sql.Where(t => t.Status == int.Parse("0")
        && t.Status == new BsOrder().Status
        && t.Remark.Contains("订单")
        && t.Remark != null
        && t.OrderTime >= new DateTime(2010, 1, 1)
        && t.OrderTime <= DateTime.Now.AddDays(1))
        .WhereIf<SysUser>(true, u => u.CreateTime < DateTime.Now)
        .OrderByDescending(t => t.OrderTime).OrderBy(t => t.Id)
        .ToList();

    foreach (BsOrder item in
``` **System.Data**

    public string CloseQuote
    {
        get
        {
            return "\"";
        }
    }
    #endregion

    #region 创建Db对象
    public DbConnection CreateConnection(string connectionString)
    {
        return new ClickHouseConnection(connectionString);
    }

    public DbCommand GetCommand(DbConnection conn)
    {
        DbCommand command = conn.CreateCommand();
        return command;
    }

    public DbCommand GetCommand(string sql, DbConnection conn)
    {
        DbCommand command = conn.CreateCommand();
        command.CommandText = sql;
        return command;
    }

    public DbParameter GetDbParameter(string name, object value)
    {
        DbParameter parameter = new ClickHouseDbParameter();
        parameter.ParameterName = name.Trim(new char[] { '{', '}' }).Split(':')[0];
        parameter.Value = value;
        DbType dbType = ColumnTypeUtil.GetDBType(value);
        parameter.DbType = dbType;
        return parameter;
    }
    #endregion

    #region Create SQL
    public string CreateGetMaxIdSql(string tableName, string key)
    {
        return string.Format("SELECT Max({0}) FROM {1}", key, tableName);
    }

    public string CreatePageSql(string sql, string orderby, int pageSize, int currentPage)
    {
        StringBuilder sb = new StringBuilder();
        int startRow = 0;
        int endRow = 0;

        #region 分页查询语句
        startRow = pageSize * (currentPage - 1);

        sb.Append("select * from (");
        sb.Append(sql);
        if (!string.IsNullOrWhiteSpace(orderby))
        {
            sb.Append(" ");
            sb.Append(orderby);
        }
        sb.AppendFormat(" ) row_limit limit {0},{1}", startRow, pageSize);
        #endregion

        return sb.ToString();
    }
    #endregion

    #region 删除SQL语句模板
    /// <summary>
    /// 删除SQL语句模板 两个值分别对应 “delete from [表名] where [查询条件]”中的“delete from”和“where”
    /// </summary>
    public Tuple<string, string> CreateDeleteSqlTempldate()
    {
        return new Tuple<string, string>("alter table", "delete where");
    }
    #endregion

    #region 更新SQL语句模板
    /// <summary>
    /// 更新SQL语стема 三个值分别对应 “update [表名] set [赋值语句] where [查询条件]”中的“update”、“set”和“where”
    /// </summary>
    public Tuple<string, string, string> CreateUpdateSqlTempldate()
    {
        return new Tuple<string, string, string>("alter table", "update", "where");
    }
    #endregion

    #region GetParameterName
    public string GetParameterName(string parameterName, Type parameterType)
    {
        return "{" + parameterName + ":" + parameterType.Name + "}";
    }
    #endregion

    #region ForList

    public SqlValue ForList(IList list)
    {
        List<string> argList = new List<string>();
        for (int i = 0; i < list.Count; i++)
        {
            argList.Add("@inParam" + i);
        }
        string args = string.Join(",", argList);

        return new SqlValue("(" + args + ")", list);
    }

    #endregion

}

#### Определение LiteSqlFactory

```C#
using LiteSql;
using LiteSql.Provider;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ClickHouseTest
{
    public class LiteSqlFactory
    {
        #region 变量
        private static ILiteSqlClient _liteSqlClient;
        #endregion

        #region 静态构造函数
        static LiteSqlFactory()
        {
            var configurationBuilder = new ConfigurationBuilder().AddJsonFile("config.json");
            var configuration = configurationBuilder.Build();
            string connectionString = configuration.GetConnectionString("DefaultConnection");

            _liteSqlClient = new LiteSqlClient(connectionString, typeof(ClickHouseProvider), new ClickHouseProvider());
        }
        #endregion

        #region 获取 ISession
        /// <summary>
        /// 获取 ISession
        /// </summary>
        /// <param name="splitTableMapping">分表映射</param>
        public static ISession GetSession(SplitTableMapping splitTableMapping = null)
        {
            return _liteSqlClient.GetSession(splitTableMapping);
        }
        #endregion

        #region 获取 ISession (异步)
        /// <summary>
        /// 获取 ISession (异步)
        /// </summary>
        /// <param name="splitTableMapping">分表映射</param>
        public static async Task<ISession> GetSessionAsync(SplitTableMapping splitTableMapping = null)
        {
            return await _liteSqlClient.GetSessionAsync(splitTableMapping);
        }
        #endregion
    }
}

using LiteSql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Models
{
    [Table("people_face_replica")]
    public class PeopleFace
    {
        [Column("captured_time")]
        public DateTime CapturedTime { get; set; }

        [Key]
        [Column("camera_id")]
        public string CameraId { get; set; }

        [Column("camera_fun_type")]
        public string CameraFunType { get; set; }

        [Key]
        [Column("face_id")]
        public string FaceId { get; set; }

        [Column("extra_info")]
        public string ExtraInfo { get; set; }

        [Column("event")]
        public string Event { get; set; }

        [Column("data_source3")]
        public string DataSource3 { get; set; }

        [Column("panoramic_image_url")]
        public string PanoramicImageUrl { get; set; }

        [Column("portrait_image_url")]
        public string PortraitImageUrl { get; set; }
    }
}

{
  "ConnectionStrings": {
    "DefaultConnection": "Database=default;Username=default;Password=;Host=192.168.120.130;Port=8123;Compression=False;UseSession=False;Timeout=120;allowMultiQueries=true"
  }
}

using Dapper.LiteSql;
using Models;
using Utils;
using ClickHouse.Client.ADO;
using ClickHouse.Client.ADO.Parameters;
using System.Text;
using Microsoft.Extensions.Configuration;

namespace ClickHouseTest
{
    [TestClass]
    public class QueryTest
    {
        #region 测试查询数量
        [TestMethod]
        public void Test1Count()
        {
            ISession session = LiteSqlFactory.GetSession();
            session.OnExecuting = (s, p) => Console.WriteLine(s);

            long count = session.Queryable<PeopleFace>().Count();
            Console.WriteLine("总数=" + count.ToString("# #### #### ####"));
            Assert.IsTrue(count > 0);
        }
        #endregion

        #region 测试插入(ADO.NET原生)
        [TestMethod]
        public void Test2Insert1()
        {
            var configurationBuilder = new ConfigurationBuilder().AddJsonFile("config.json");
            var configuration = configurationBuilder.Build();
            string connectionString =
``` Вот перевод текста на русский язык:

DateTime.Now;
peopleFace.CameraId = pre + "_" + i;
peopleFace.FaceId = "340104490011903" + i;
peopleFace.CameraFunType = "2";
peopleFace.PanoramicImageUrl = "PanoramicImageUrl";
peopleFace.PortraitImageUrl = "PortraitImageUrl";
peopleFace.Event = "UPSERT";
peopleFaceList.Add(peopleFace);

if (time == null) time = peopleFace.CapturedTime;
}
session.Insert(peopleFaceList, 100); //设置合理的pageSize

long count = session.Queryable<PeopleFace>().Where(t => t.CapturedTime >= time && t.CameraId.StartsWith(pre)).Count();
Console.WriteLine("count=" + count);
Assert.IsTrue(count > 0);
}
#endregion

#region 测试修改
[TestMethod]
public void Test3Update()
{
var configurationBuilder = new ConfigurationBuilder().AddJsonFile("config.json");
var configuration = configurationBuilder.Build();
string connectionString = configuration.GetConnectionString("DefaultConnection");

ISession session = LiteSqlFactory.GetSession();
session.OnExecuting = (s, p) => Console.WriteLine(s);

PeopleFace old = session.Queryable<PeopleFace>().Where(t => t.CameraId == "34010400000000000000").First();

session.AttachOld(old);
string newExtraInfo = DateTime.Now.ToString("yyyyMMddHHmmss");
old.ExtraInfo = newExtraInfo;
session.Update(old);

Thread.Sleep(100);

PeopleFace newPeopleFace = session.Queryable<PeopleFace>().Where(t => t.CameraId == "34010400000000000000").First();

Console.WriteLine(newExtraInfo);
Console.WriteLine(newPeopleFace.ExtraInfo);
Assert.IsTrue(newPeopleFace.ExtraInfo == newExtraInfo);
}
#endregion

#region 测试批量修改
[TestMethod]
public void Test4BatchUpdate()
{
var configurationBuilder = new ConfigurationBuilder().AddJsonFile("config.json");
var configuration = configurationBuilder.Build();
string connectionString = configuration.GetConnectionString("DefaultConnection");

ISession session = LiteSqlFactory.GetSession();
session.OnExecuting = (s, p) => Console.WriteLine(s);

List<PeopleFace> oldList = session.Queryable<PeopleFace>().Where(t => t.CapturedTime > DateTime.Now.AddMinutes(-1)).QueryList<PeopleFace>();

string newExtraInfo = DateTime.Now.ToString("yyyyMMddHHmmss");
oldList.ForEach(old =>
{
session.AttachOld(old);
old.ExtraInfo = newExtraInfo;
session.Update(old);
});
//session.Update(oldList); //似乎不支持，错误信息：Multi-statements are not allowed

Thread.Sleep(100);

long count = session.Queryable<PeopleFace>().Where(t => t.ExtraInfo == newExtraInfo).Count();

Console.WriteLine(count + "条已更新");
Assert.IsTrue(count > 0);
}
#endregion Консоль.WriteLine(количество + «уже обновлено»);
            Assert.IsTrue(количество > 0);
        }
        #endregion

        #region Удаление
        [TestMethod]
        public void Test9Delete()
        {
            var configurationBuilder = new ConfigurationBuilder().AddJsonFile("config.json");
            var configuration = configurationBuilder.Build();
            string connectionString = configuration.GetConnectionString("DefaultConnection");

            ISession session = LiteSqlFactory.GetSession();
            session.OnExecuting = (s, p) => Консоль.WriteLine(s);
            long количество = session.Queryable<PeopleFace>().Where(t => t.CapturedTime > DateTime.Now.AddMinutes(-1)).Count();
            Консоль.WriteLine("Количество перед удалением=" + количество);

            session.CreateSql("captured_time>@Time", new { Time = DateTime.Now.AddDays(-10) }).DeleteByCondition<PeopleFace>();

            Thread.Sleep(100);

            количество = session.Queryable<PeopleFace>().Where(t => t.CapturedTime > DateTime.Now.AddMinutes(-1)).Count();
            Консоль.WriteLine("Количество после удаления=" + количество);

            Assert.IsTrue(количество == 0);
        }
        #endregion

        #region Тестирование параметризованных запросов
        [TestMethod]
        public void Test5Query()
        {
            int количество запросов = 10;
            ISession session = LiteSqlFactory.GetSession();
            session.OnExecuting = (s, p) => Консоль.WriteLine(s);

            List<PeopleFace> список = session.CreateSql("select * from people_face_replica t")
                .Append("where t.captured_time <= @EndTime", DateTime.Now)
                .Append("order by captured_time desc")
                .Append("limit " + количество запросов)
                .QueryList<PeopleFace>();

            if (список.Count != количество запросов)
            {
                Консоль.WriteLine(список.Count + " / " + количество запросов);
            }
            else
            {
                Консоль.WriteLine("Общее количество=" + список.Count);
            }
            Assert.IsTrue(список.Count == количество запросов);

            список.ForEach(элемент => Консоль.WriteLine(ModelToStringUtil.ToString(элемент)));
        }
        #endregion

        #region Тестирование параметризованных запросов (параметры передаются через анонимный объект)
        [TestMethod]
        public void Test5Query2()
        {
            int количество запросов = 10;
            ISession session = LiteSqlFactory.GetSession();
            session.OnExecuting = (s, p) => Консоль.WriteLine(s);

            List<PeopleFace> список = session.CreateSql("select * from people_face_replica t")
                .Append("where t.captured_time <= @EndTime", new { EndTime = DateTime.Now })
                .Append("order by captured_time desc")
                .AppendFormat("limit {0}", количество запросов)
                .QueryList<PeopleFace>();

            if (список.Count != количество запросов)
            {
                Консоль.WriteLine(список.Count + " / " + количество запросов);
            }
            else
            {
                Консоль.WriteLine("Общее количество=" + список.Count);
            }
            Assert.IsTrue(список.Count == количество запросов);

            список.ForEach(элемент => Консоль.WriteLine(ModelToStringUtil.ToString(элемент)));
        }
        #endregion

        #region Тестирование параметризованных запросов (лямбда-выражение)
        [TestMethod]
        public void Test6QueryByLambda()
        {
            int количество запросов = 10;
            ISession session = LiteSqlFactory.GetSession();
            session.OnExecuting = (s, p) => Консоль.WriteLine(s);

            List<PeopleFace> список = session.Queryable<PeopleFace>()
                .Where(t => t.CapturedTime <= DateTime.Now)
                .OrderByDescending(t => t.CapturedTime)
                .ToPageList(1, количество запросов);

            if (список.Count != количество запросов)
            {
                Консоль.WriteLine(список.Count + " / " + количество запросов);
            }
            else
            {
                Консоль.WriteLine("Общее количество=" + список.Count);
            }
            Assert.IsTrue(список.Count == количество запросов);

            список.ForEach(элемент => Консоль.WriteLine(ModelToStringUtil.ToString(элемент)));
        }
        #endregion

        #region Тестирование параметризованных запросов (лямбда-выражения с одинаковыми параметрами)
        [TestMethod]
        public void

Здесь представлен перевод исходного текста на русский язык. В тексте запроса не было обнаружено специальных символов или непечатаемых символов.