csrobot为开发提高效率，增加乐趣！
https://github.com/axzxs2001/CSRobot
安装命令：
dotnet tool install --global CSRobot --version 0.0.4
Nuget地址：https://www.nuget.org/packages/CSRobot/
一、gen
csrobot gen是什么？
gen是完成从数据库表结构生成实体类的小工具，其工作原理是
-
按照--sql指定的sql模板或内置的sql语句查询库中的表信息，表中字段信息
-
按照--map配置库字段和实体属性类型映射关系
-
按照--tep指定的实体模板或内置的实体模板
生成实体类文件
csrobot gen [options]
| 参数选项 | 描述 |
|---|---|
| --dbtype | 数据库类型，必填，例如:--dbtype=mysql,--dbtype=mssql,--dbtype=postgressql |
| --out,--o | 生成实体类的路径，缺省默认输出文件到当前路径下entities目录中 |
| --tep | 生成实体类的模板，可以是内置的模板cs，或指定本地路径，或指定url，生成文件的扩展名与指定的模板扩展名匹配。缺省默认cs内置模板，例如:--tep=/usr/abc/bcd.cs；--tep=https://github.com/abc/bcd.cs；--tep=cs |
| --sql | 查询表结构的sql语句，模板有两个属性：tablesql是查询库中全部表的信息，表名必须用tablename命名，fieldsql是查询tablename表中的全部字段，这里两个sql的字段除了tablename都可以自定义，在--tep模板中应用，例如:--sql=/usr/abc/mssql-cs.sql；--map=https://github.com/axzxs2001/CSRobot/blob/main/CSRobot/gen/mssql-cs.sql |
| --map | 生成实体类字段时，数据库到实体类的字段映射模板，缺省值为内置的模板，或指定本地路径，或指定url，例如:--map=/usr/abc/bcd.json；--map=https://github.com/axzxs2001/CSRobot/blob/main/CSRobot/gen/map.json |
| --host | 连接数据所在主机，如果缺少此项，会查找--constr,或查找当前目录或子目录中的是否存在appsettings.json配置文件，并取配置文件下的ConnectionStrings节点的第一个子节点的值作为连接字符串 |
| --db | 数据库名称，如果缺少此项，会查找--constr,或查找当前目录或子目录中的是否存在appsettings.json配置文件，并取配置文件下的ConnectionStrings节点的第一个子节点的值作为连接字符串 |
| --user | 数据库用户名，如果缺少此项，会查找--constr,或查找当前目录或子目录中的是否存在appsettings.json配置文件，并取配置文件下的ConnectionStrings节点的第一个子节点的值作为连接字符串 |
| --pwd | 数据库密码，如果缺少此项，会查找--constr,或查找当前目录或子目录中的是否存在appsettings.json配置文件，并取配置文件下的ConnectionStrings节点的第一个子节点的值作为连接字符串 |
| --port | 数据库端口号，如果缺少此项，会查找--constr,或查找当前目录或子目录中的是否存在appsettings.json配置文件，并取配置文件下的ConnectionStrings节点的第一个子节点的值作为连接字符串 |
| --constr | 或一个完整的连接字符串 ，如果缺少此项，会查找--host,--db,--user,--pwd,--port组，或查找当前目录或子目录中的是否存在appsettings.json配置文件，并取配置文件下的ConnectionStrings节点的第一个子节点的值作为连接字符串 |
说明： 如果连接字符串属性同时出现，--constr优先级最高，其次是--host,--db,--user,--pwd,--port组，两者都不存在再查询appsettings.json
模板文件夹：https://github.com/axzxs2001/CSRobot/tree/main/CSRobot/gen，包含实体类模板，数据库到实体类型映射模板，查询表结构sql语句模板
--tep
如果生成的实体是cs的，模板文件的扩展名为.cs,内容如下例， ${}的选项是固定选项，分别代表从数据库中查询到的数据字段 $?{}是判断条件，如果有值，本行显示，否则本行不显示 $map{}本字段完成类型映射
using System;
namespace MyNameSpace
{
/// <summary>
/// ${tabledescribe}
/// </summary>
public class ${tablename}
{
${Fields}
$?{fielddescribe}/// <summary>
$?{fielddescribe}/// ${fielddescribe}
$?{fielddescribe}/// </summary>
$?{fieldsize}[BField(Length=${fieldsize},Name="${fieldname}")]
public $map{dbtype} ${fieldname}
{ get; set; }
${Fields}
}
}
下面例子是DB与c#的类型映射，工具内置了三种数据库：mssql，mysql，postgresql,对于转化成的实体类，按照--tep模板生成，可以是非C#实体类
{
"mssql-cs": {
"bigint": "long",
"binary": "Byte[]",
"bit": "bool",
"char": "string",
"date": "DateTime",
"datetime": "DateTime",
"datetime2": "DateTime",
"datetimeoffset": "DateTimeOffset",
"decimal": "decimal",
"float": "double",
"image": "Byte[]",
"int": "int",
"money": "decimal",
"nchar": "string",
"ntext": "string",
"numeric": "double",
"nvarchar": "string",
"real": "float",
"rowversion": "Byte[]",
"smalldatetime": "DateTime",
"smallint": "short",
"smallmoney": "decimal",
"text": "string",
"time": "TimeSpan",
"timestamp": "Byte[]",
"tinyint": "Byte",
"uniqueidentifier": "Guid",
"varbinary": "Byte[]",
"varchar": "string",
"xml": "string"
},
"mysql-cs": {
"char": "string",
"varchar": "string",
"binary": "byte[]",
"varbinary": "byte[]",
"tinyblob": "btye[]",
"tinytext": "string",
"text": "string",
"blob": "byte[]",
"mediumtext": "string",
"mediumblob": "byte[]",
"longtext": "string",
"longblob": "byte[]",
"enum": "string",
"bit": "short",
"tinyint": "byte",
"bool": "bool",
"boolean": "bool",
"smallint": "short",
"mediumint": "short",
"int": "int32",
"integer": "int32",
"bigint": "long",
"float": "float",
"double": "double",
"double precision": "double",
"decimal": "decimal",
"dec": "decimal",
"date": "datetime",
"datetime": "datetime",
"timestamp": "datetime",
"time": "datetime",
"year": "short"
},
"postgresql-cs": {
"bigint": "long",
"int8": "long",
"bigserial": "long",
"serial8": "long",
"boolean": "bool",
"bool": "bool",
"bytea": "Byte[]",
"character": "string",
"char": "string",
"character varying": "string",
"varchar": "string",
"date": "DateTime",
"double precision": "double",
"float8": "double",
"integer": "int",
"int4": "int",
"interval": "string",
"money": "decimal",
"numeric": "decimal",
"decimal": "decimal",
"real": "float",
"float4": "float",
"smallint": "short",
"int2": "short",
"smallserial": "short",
"serial2": "short",
"serial": "int",
"serial4": "int",
"text": "string",
"time": "DateTime",
"time with time zone": "DateTimeOffset",
"timetz": "DateTimeOffset",
"timestamp": "DateTime",
"timestamp with time zone": "DateTimeOffset",
"timestamptz": "DateTimeOffset",
"uuid": "GUID"
}
}
--sql
mssql.json
{
"tablesql": "Select Name as tablename,'' as tabledescribe FROM SysObjects Where XType='U' ;",
"fieldsql": "SELECT a.name as fieldname,b.name as dbtype,case when a.xprec=0 then COLUMNPROPERTY(a.id,a.name,'PRECISION') else null end as fieldsize,isnull(g.[value],'') as fielddescribe FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id where d.name='${tableName}'"
}
mysql.json
{
"tablesql": "select table_name as tablename,table_comment as tabledescribe from information_schema.tables where table_schema='${databasename}' and table_type='BASE TABLE';",
"fieldsql": "select character_maximum_length as fieldsize,column_name as fieldname,data_type as dbtype,column_comment as fielddescribe from information_schema.columns where table_name = '${tableName}' "
}
postgresql.json
{
"tablesql": "select relname as tablename,cast(obj_description(relfilenode,'pg_class') as varchar) as tabledescribe from pg_class c where relname in (SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%');",
"fieldsql": "SELECT a.attname AS fieldname,t.typname AS dbtype,case when a.atttypmod=-1 then null else a.atttypmod end AS fieldsize, b.description AS fielddescribe FROM pg_class c, pg_attribute a LEFT JOIN pg_description b ON a.attrelid = b.objoid AND a.attnum = b.objsubid, pg_type t WHERE c.relname = '${tableName}' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid"
}
案例
最简单的
csrobot gen --dbtype=mssql
从MySql库生成实体类
csrobot gen --dbtype=mssql --tep=https://raw.githubusercontent.com/axzxs2001/CSRebot/main/CSRebot/gen/gen_cs_record.cs --host=127.0.0.1 --db=stealthdb --user=sa --pwd=sa
想要更快更方便的了解相关知识，可以关注微信公众号
