SqlBatis

免责声明

本人对所有因使用本产品而导致的损失概不负责,使用前请先测试和阅读源代码并自觉遵守开源协议，可用于学习交流二次开发

打印日志

//通过重写DbContext，我们可以定制化一些需求
public class LoggerDbContext : DbContext
{
    public MyDbContext(DbContextBuilder builder)
        : base(builder)
    {

    }
    //通过覆盖CreateDbCommand，来实现日志打印，DbContext中有许多方法设计成可以被覆盖的，
    protected override IDbCommand CreateDbCommand(string sql, object parameter, int? commandTimeout = null, CommandType? commandType = null)
    {
        Trace.WriteLine("================Command===================");
        Trace.WriteLine(sql);
        Trace.WriteLine("===========================================");
        return base.CreateDbCommand(sql, parameter, commandTimeout, commandType);
    }
}
//创建数据上下文
var context = new LoggerDbContext(new DbContext()
{
    Connection = new MySqlConnector.MySqlConnection("server=127.0.0.1;user id=root;password=1024;database=test;"),
    DbContextType = DbContextType.Mysql
});

sql查询

//返回匿名类型的集合
List<dynamic> list = context.Query("select * from student").ToList();
//映射到指定类型，忽略大小写和下划线
List<Student> list = context.Query<Student>("select id,stu_name from student").ToList();
//多结果集
using(var multi = context.QueryMultiple("select id,stu_name from student;select count(1) from student"))
{
    var list = multi.Read<Student>();
    var count = multi.ReadFirst<int>();
}
public class Student
{
    public int Id {get;set;}
    public string StuName {get; set;}
}

Sqlbuilder

var b1 = new SqlBuilder();
b1.Where("id>@Id")
  .Where("score>@Score")
  .Join("student_name as b on a.id=b.sid");
var p = new { Age = 1, Score = 20 };
//对两个模板进行构建
var tmp1 = b1.Build("select * from student as a /**join**/ /**where**/ ");
var tmp2 = b1.Build("select count(1) from student as a /**join**/ /**where**/ ");
using(var multi = context.QueryMultiple($"{tmp1.RawSql};{tmp2.RawSql}",new {Id=1,Score=5}))
{
    var list = multi.Read<Student>();
    var count = multi.ReadFirst<int>();
}

Linq查询

var entity = context.From<Student>().Where(a=>a.id==1).Single();
var entitys = context.From<Student>().Where(a=>a.id>1).Select();
var entiityNames = context.From<Student>().Select(s=>new 
{ 
    s.Name,
    s.Age
});
//默认忽略：这会忽略id字段
var row = context.From<Student>().Insert(new {Name="zs",Age=1});
//默认忽略：这会忽略Age字段
var row = context.From<Student>().Update(new {Name="zs",Id=1});
//显示忽略：这会忽略所有为null的字段
var row = context.From<Student>().Ignore().Update(new Student{Name="zs",Id=1});

public class Student
{
   [PrimaryKey]
   [Indenity]
   public int Id {get;set;}
   public string Name {get;set;}
   public int Age {get;set;}
}

自定义类型映射

/// <summary>
/// 提供json转换能力
/// </summary>
public class MyDDbContextBehavior : DbContextBehavior
{
    readonly static MethodInfo _methodInfo = typeof(MyDDbContextBehavior).GetMethod("StringToJson");

    static readonly JsonSerializerOptions _jsonSerializerOptions;

    static MyDDbContextBehavior()
    {
        _jsonSerializerOptions = new JsonSerializerOptions
        {
            Encoder = JavaScriptEncoder.Create(UnicodeRanges.All),
            PropertyNameCaseInsensitive = true,
            PropertyNamingPolicy=JsonNamingPolicy.CamelCase
        };
        _jsonSerializerOptions.Converters.Add(new JsonDateTimeConverter("yyyy-MM-dd HH:mm:ss"));
        _jsonSerializerOptions.Converters.Add(new JsonDateTimeNullableConverter("yyyy-MM-dd HH:mm:ss"));
    }

    public static T StringToJson<T>(IDataRecord record, int i)
    {
        if (record.IsDBNull(i))
        {
            return default;
        }
        var json = record.GetString(i);
        return JsonSerializer.Deserialize<T>(json, _jsonSerializerOptions);
    }

    public override KeyValuePair<string, object> CreateDbCommandParameter(string name, object value)
    {
        if (value != null && typeof(ValueObject).IsInstanceOfType(value))
        {
            var json = JsonSerializer.Serialize(value, _jsonSerializerOptions);
            return base.CreateDbCommandParameter(name, json);
        }
        return base.CreateDbCommandParameter(name, value);
    }

    protected override MethodInfo FindConvertMethod(Type entityType, Type memberType, DataReaderField recordField)
    {
        if (typeof(ValueObject).IsAssignableFrom(memberType))
        {
            return _methodInfo.MakeGenericMethod(memberType);
        }
        return base.FindConvertMethod(entityType, memberType, recordField);
    }
}
var connectionString = @"server=127.0.0.1;user id=root;password=1024;database=sqlbatis;";
var connection = new MySqlConnection(connectionString);
//3.设置默认的数据库上下文行为
new MyDbContext(new DbContextBuilder()
{
    Connection = new MySqlConnector.MySqlConnection(configuration.GetConnectionString("Mysql")),
    DbContextType = DbContextType.Mysql,
    DbContextBehavior = new MyDDbContextBehavior()
})

定义简单的函数

[SqlBatis.Attributes.Function]
 public static class SqlFun
 {
     public static T2 IF<T1, T2>(T1 column, T2 v1, T2 v2)
     {
         return default;
     }

     public static bool ISNULL<T1>(T1 t1)
     {
         return default;
     }
 }
 
 var list = _context.From<StudentDto>()
  .Ignore(a => a.StuGender)
  .Select(s => new
  {
      FF = SqlFun.IF(SqlFun.ISNULL(s.SchId), 1, 2)
  });