ORM-SmartSql入门01

SmartSql详细介绍请点击这里。
放弃EF的原因有如下几点

1. 不喜欢EF库各种外键强关联
2. 不喜欢EF那种基本脱离Sql的写法，个人还是比较喜欢那种掌控每句Sql的传统姿势
3. EF每执行一次，还需要再看一下生成的sql是否符合心中的写法
4. 当然最主要的应该是EF学习得不到位

---

本章目标：控制台程序使用SmartSql，查询mysql数据

1. Nuget

    <PackageReference Include="MySql.Data" Version="9.4.0" />
    <PackageReference Include="SmartSql" Version="4.1.67" />

2.添加基本配置文件
根目录添加 SmartSqlMapConfig.xml,并设置为【始终复制】

<?xml version="1.0" encoding="utf-8" ?>
<SmartSqlMapConfig xmlns="http://SmartSql.net/schemas/SmartSqlMapConfig.xsd">
    <Settings IgnoreParameterCase="false" ParameterPrefix="#" IsCacheEnabled="true"/>
    <Properties>
        <Property Name="ConnectionString" Value="Server=localhost;database=tpl-db;Port=3306;Uid=xxx;Pwd=xxxx; Allow User Variables=True; SslMode=None; Pooling=True; Max Pool Size=40; CharSet=utf8mb4;"/>
    </Properties>
    <Database>
        <DbProvider Name="Mysql"/>
        <Write Name="WriteDB" ConnectionString="${ConnectionString}"/>
        <Read Name="ReadDb-1" ConnectionString="${ConnectionString}" Weight="100"/>
    </Database>
    <IdGenerator Type="SnowflakeId">
        <Properties>
            <Property Name="WorkerIdBits" Value="10"/>
            <Property Name="WorkerId" Value="888"/>
            <Property Name="Sequence" Value="14"/>
        </Properties>
    </IdGenerator>
    <SmartSqlMaps>
        <SmartSqlMap Path="Maps" Type="Directory"/> //对应sql xml文件位置
    </SmartSqlMaps>
</SmartSqlMapConfig>

3.根据数据库表生成实体

CREATE TABLE User(
    `Sysno` INT AUTO_INCREMENT COMMENT 'id' ,
    `Name` VARCHAR(20)   COMMENT '姓名' ,
    `Status` INT   COMMENT '数据状态' ,
    `CreateBy` INT   COMMENT '创建人' ,
    `CreateTime` DATETIME   COMMENT '创建时间' ,
    `UpdateBy` INT   COMMENT '更新人' ,
    `UpdateTime` DATETIME   COMMENT '更新时间' ,
    PRIMARY KEY (Sysno)
)  COMMENT = '测试用户表';

//普通对应实体类
public class UserDo
{
    public int Sysno { get; set; }
    public string Name { get; set; }
    public int Status { get; set; }
    public DateTime CreateDate { get; set; }
}

4.编写表对应xml文件
在根目录创建文件夹maps与User.xml(user.xml =>[始终复制])

<?xml version="1.0" encoding="utf-8" ?>
<SmartSqlMap Scope="UserDo" xmlns="http://SmartSql.net/schemas/SmartSqlMap.xsd">
    <Statements>
        <Statement Id="QueryParams">
            <Where>
                <IsNotEmpty Prepend="And" Property="Sysno">
                    T.Sysno = #Sysno
                </IsNotEmpty>
                <IsNotEmpty Prepend="And" Property="Name">
					T.Name = #Name
				</IsNotEmpty>
            </Where>
        </Statement>
        <!--获取数据列-->
        <Statement Id="Query" >
            SELECT 
            T.sysno,t.name,t.status,t.createdate From user T
            <Include RefId="QueryParams" />
            <Switch Prepend="Order By" Property="OrderBy">
                <Default>
                    T.sysno Desc
                </Default>
            </Switch>
        </Statement>
    </Statements>
</SmartSqlMap>

5.调试

var factory = new SmartSqlBuilder()
    .UseXmlConfig()
    .Build()
    .GetDbSessionFactory();
using (var dbSession = factory.Open())
{
    var list = dbSession.Query<UserDo>(new RequestContext()
    {
        Scope = nameof(UserDo),
        SqlId = "Query",
        Request = new UserDo
        {
            Sysno = 1,
            Name = "test111",
        }
    });
    if (list != null)
    {
// todo
    }
}