如何从SQL Server表对象生成类?
我说的不是使用ORM。我只需要创建实体(简单类)。喜欢的东西:
public class Person
{
public string Name { get;set; }
public string Phone { get;set; }
}
给定一个表,比如:
+----+-------+----------------+
| ID | Name | Phone |
+----+-------+----------------+
| 1 | Alice | (555) 555-5550 |
| 2 | Bob | (555) 555-5551 |
| 3 | Cathy | (555) 555-5552 |
+----+-------+----------------+
从Postgres DB生成
DO $$
DECLARE v_tabela varchar;
DECLARE v_cursor_colunas record;
DECLARE v_nome_coluna varchar;
DECLARE v_classe VARCHAR;
DECLARE v_tipo VARCHAR;
DECLARE v_schema_name VARCHAR;
BEGIN
v_schema_name := 'my-schema';
v_tabela := 'my-table';
select table_name INTO v_tabela from information_schema.tables where table_schema = v_schema_name
and table_type = 'BASE TABLE'
and table_name = v_tabela;
v_classe := E'\r\n' || 'public class ' || v_tabela || ' {' || E'\r\n';
FOR v_cursor_colunas IN
SELECT column_name as coluna, is_nullable as isnull, data_type as tipo, character_maximum_length as tamanho
FROM information_schema.columns
WHERE table_schema = v_schema_name
AND table_name = v_tabela
LOOP
IF v_cursor_colunas.tipo='character varying' THEN
v_tipo:= 'string';
ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho=1 THEN
v_tipo:= 'char';
ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho<>1 THEN
v_tipo:= 'string';
ELSIF v_cursor_colunas.tipo like 'timestamp%' THEN
v_tipo:= 'DateTime';
IF v_cursor_colunas.isnull='YES' then
v_tipo:= 'DateTime?';
END IF;
ELSIF v_cursor_colunas.tipo='boolean' THEN
v_tipo:= 'bool';
IF v_cursor_colunas.isnull='YES' then
v_tipo:= 'bool?';
END IF;
ELSIF v_cursor_colunas.tipo='integer' THEN
v_tipo:= 'int';
IF v_cursor_colunas.isnull='YES' then
v_tipo:= 'int?';
END IF;
ELSIF v_cursor_colunas.tipo='numeric' THEN
v_tipo:= 'double';
IF v_cursor_colunas.isnull='YES' then
v_tipo:= 'double?';
END IF;
ELSIF v_cursor_colunas.tipo='text' THEN
v_tipo:= 'string';
ELSE
v_tipo:= 'another';
END IF;
v_nome_coluna := v_cursor_colunas.coluna;
v_classe := v_classe || 'public ' || v_tipo || ' ' || v_cursor_colunas.coluna || ' { get; set; }' || E'\r\n';
END LOOP;
v_class:= v_class || E'\r\n' || '}';
RAISE NOTICE '%', v_class;
$ $;
要打印NULLABLE属性,请使用此命令。
它对Alex Aza的CASE语句块脚本进行了轻微修改。
declare @TableName sysname = 'TableName'
declare @result varchar(max) = 'public class ' + @TableName + '
{'
select @result = @result + '
public ' + ColumnType + ' ' + ColumnName + ' { get; set; }
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id,
case typ.name
when 'bigint' then 'long'
when 'binary' then 'byte[]'
when 'bit' then 'bool'
when 'char' then 'string'
when 'date' then 'DateTime'
when 'datetime' then 'DateTime'
when 'datetime2' then 'DateTime'
when 'datetimeoffset' then 'DateTimeOffset'
when 'decimal' then 'decimal'
when 'float' then 'float'
when 'image' then 'byte[]'
when 'int' then 'int'
when 'money' then 'decimal'
when 'nchar' then 'char'
when 'ntext' then 'string'
when 'numeric' then 'decimal'
when 'nvarchar' then 'string'
when 'real' then 'double'
when 'smalldatetime' then 'DateTime'
when 'smallint' then 'short'
when 'smallmoney' then 'decimal'
when 'text' then 'string'
when 'time' then 'TimeSpan'
when 'timestamp' then 'DateTime'
when 'tinyint' then 'byte'
when 'uniqueidentifier' then 'Guid'
when 'varbinary' then 'byte[]'
when 'varchar' then 'string'
else 'UNKNOWN_' + typ.name
end +
CASE
WHEN col.is_nullable=1 AND
typ.name NOT IN (
'binary', 'varbinary', 'image',
'text', 'ntext',
'varchar', 'nvarchar', 'char', 'nchar')
THEN '?'
ELSE '' END AS [ColumnType]
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by column_id
set @result = @result + '
}'
print @result