如何从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 |
+----+-------+----------------+



当前回答

Oracle DB的另一个解决方案-> c#

单查询 无功能无步骤 Mulitple表

添加DataAnnotations

(例子) (需要) (表) (StringLength) (列) 可以为空

查询

https://gist.github.com/omansak/f19eefffd2d639ac72a1f4b506d8471a

输出

[Table("AGENTS")]
public class Agents
{
    [Key]
    [Required]
    [Column("INT_ID", TypeName = "NUMBER(10,0)", Order = 1)]
    public long IntId { get; set; }
    [Key]
    [Required]
    [StringLength(15)]
    [Column("REFERENCE_CODE", TypeName = "VARCHAR2(15)", Order = 2)]
    public string ReferenceCode { get; set; }
    [Required]
    [Column("PARENT_INT_ID", TypeName = "NUMBER(10,0)", Order = 3)]
    public long ParentIntId { get; set; }
    [Required]
    [StringLength(200)]
    [Column("TITLE", TypeName = "VARCHAR2(200)", Order = 4)]
    public string Title { get; set; }
    [Required]
    [Column("START_DATE", TypeName = "DATE", Order = 5)]
    public DateTime StartDate { get; set; }
    [Required]
    [Column("END_DATE", TypeName = "DATE", Order = 6)]
    public DateTime EndDate { get; set; }
    [Required]
    [StringLength(1)]
    [Column("AGENT_TYPE", TypeName = "VARCHAR2(1)", Order = 7)]
    public string AgentType { get; set; }
    [Required]
    [Column("CREATE_DATE", TypeName = "DATE", Order = 8)]
    public DateTime CreateDate { get; set; }
    [Required]
    [StringLength(32)]
    [Column("CREATE_USER", TypeName = "VARCHAR2(32)", Order = 9)]
    public string CreateUser { get; set; }
    [StringLength(200)]
    [Column("RESPONSIBLE_CONTACT", TypeName = "VARCHAR2(200)", Order = 10)]
    public string ResponsibleContact { get; set; }
    [StringLength(100)]
    [Column("RESPONSIBLE_TITLE", TypeName = "VARCHAR2(100)", Order = 11)]
    public string ResponsibleTitle { get; set; }
    [StringLength(100)]
    [Column("AGENCY_PLATE_NO", TypeName = "VARCHAR2(100)", Order = 12)]
    public string AgencyPlateNo { get; set; }
    [Column("AGENCY_COVER_AMOUNT", TypeName = "NUMBER(24,2)", Order = 13)]
    public double? AgencyCoverAmount { get; set; }
    [StringLength(100)]
    [Column("MERSIS_NO", TypeName = "VARCHAR2(100)", Order = 14)]
    public string MersisNo { get; set; }
    [StringLength(100)]
    [Column("TECH_PERSONEL_NO", TypeName = "VARCHAR2(100)", Order = 15)]
    public string TechPersonelNo { get; set; }
    [StringLength(100)]
    [Column("TECH_PERSONEL_NAME", TypeName = "VARCHAR2(100)", Order = 16)]
    public string TechPersonelName { get; set; }
    [Column("COVER_END_DATE", TypeName = "DATE", Order = 17)]
    public DateTime? CoverEndDate { get; set; }
    [Column("BRANCH_NUMBER", TypeName = "NUMBER(10,0)", Order = 18)]
    public long? BranchNumber { get; set; }
    [Column("ACTION_NUMBER", TypeName = "NUMBER(10,0)", Order = 19)]
    public long? ActionNumber { get; set; }
    [Column("CLUB_PARTICIPATION_COUNT", TypeName = "NUMBER(10,0)", Order = 20)]
    public long? ClubParticipationCount { get; set; }
    [Column("AGENCY_CONTRACT_DATE", TypeName = "DATE", Order = 21)]
    public DateTime? AgencyContractDate { get; set; }
    [StringLength(200)]
    [Column("KEP_ADDRESS", TypeName = "VARCHAR2(200)", Order = 22)]
    public string KepAddress { get; set; }
}

其他回答

你刚刚做了,只要你的表包含两列,并被称为'tblPeople'之类的东西。

您总是可以编写自己的SQL包装器。实际上我更喜欢这样做,我讨厌生成代码,以任何方式。

也许创建一个DAL类,并有一个名为GetPerson(int id)的方法,用于查询数据库中的person,然后从结果集中创建person对象。

我尝试过node.js,它对我来说工作得很好。

它将为您创建模型文件。您可以创建多个模型文件

前提条件:安装node.js

需要更改:

在你的工作区中创建index.js文件 在“allTable”中添加你的tables对象(截图中高亮显示) 更改文件夹路径(我已经给出了我的系统路径) 执行命令节点index.js

节点index.js

输出

const fs = require('fs/promises'); async function convertToDataType(dataArray, fileName) { let count = 0; let tempArray = []; var dataTypeArray = [ { "key": "bigint", "value": "long" }, { "key": "binary", "value": "byte[]" }, { "key": "bit", "value": "bool" }, { "key": "char", "value": "string" }, { "key": "date", "value": "DateTime" }, { "key": "datetime", "value": "DateTime" }, { "key": "datetime2", "value": "DateTime" }, { "key": "datetimeoffset", "value": "DateTimeOffset" }, { "key": "decimal", "value": "decimal" }, { "key": "float", "value": "double" }, { "key": "image", "value": "byte[]" }, { "key": "int", "value": "int" }, { "key": "money", "value": "decimal" }, { "key": "nchar", "value": "string" }, { "key": "ntext", "value": "string" }, { "key": "numeric", "value": "decimal" }, { "key": "nvarchar", "value": "string" }, { "key": "real", "value": "float" }, { "key": "smalldatetime", "value": "DateTime" }, { "key": "smallint", "value": "short" }, { "key": "smallmoney", "value": "decimal" }, { "key": "text", "value": "string" }, { "key": "time", "value": "TimeSpan" }, { "key": "timestamp", "value": "long" }, { "key": "tinyint", "value": "byte" }, { "key": "uniqueidentifier", "value": "Guid" }, { "key": "varbinary", "value": "byte[]" }, { "key": "varchar", "value": "string" } ] dataArray.map(i => { let objDataType = ''; objDataType = dataTypeArray.filter(data => data.key == i.split(' ')[1].replace('[', '').replace(']', ''))[0].value; if (objDataType == '') { count++; } let isNull = i.includes('NULL') && !(i.includes('varchar') || i.includes('bit')) ? '?' : ''; isNull = i.includes('NOT NULL') ? '' : isNull; const varValue = i.split(' ')[0].replace('[', '').replace(']', ''); if (count != 0) { console.warn(`\n\n\n ======> Error:: Check data type is missing. Datatype => ${i.split(' ')[1]} Object Name: ${fileName} \n\n\n`); } else { tempArray.push(`public ${objDataType}${isNull} ${varValue} { get; set; }`); } }); return tempArray; } async function convertToModel() { try { let allTable = { EmployeeAllowancesHistory: [ "[EmployeeAllowanceHistoryID] [int] NOT NULL", "[EmployeeID] [int] NOT NULL", "[AllowanceID] [int] NOT NULL", "[DateID] [int] NULL", "[Amount] [numeric] NOT NULL", "[Insured] [bit] NULL", "[ChangeDate] [datetime] NOT NULL", "[NewAmount] [numeric] NULL" ], Cities: [ "[CityID] [int] NOT NULL", "[CityCode] [varchar] NOT NULL", "[CityNameAr] [varchar] NULL", "[CityNameEn] [varchar] NULL", "[InKSA] [bit] NOT NULL", "[HighClass] [bit] NOT NULL", "[TravelDays] [int] NULL" ], Regions: [ "[RegionID] [int] NOT NULL", "[RegionCode] [nvarchar] NULL", "[RegionNameEn] [nvarchar] NULL", "[RegionNameAr] [nvarchar] NULL", "[CityID] [int] NULL" ] } for (var file in allTable) { const tableObject = await convertToDataType(allTable[file], file); let tempContent = "[key]"; tableObject.map(obj => { tempContent = `${tempContent} ${obj}` }); const content = `using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; namespace Core.Entities { public class ${file} { ${tempContent} } }` fs.writeFile(`/model_files/${file}.cs`, content); } console.log('Created successfully...'); } catch (err) { console.log(err); } } convertToModel();

我无法让亚历克斯的答案在Sql Server 2008 R2上工作。所以,我用同样的基本原理重写了它。它现在支持模式,并且对列属性映射(包括将可为空的日期类型映射为可为空的c#值类型)进行了一些修复。下面是Sql语句:

   DECLARE @TableName VARCHAR(MAX) = 'NewsItem' -- Replace 'NewsItem' with your table name
    DECLARE @TableSchema VARCHAR(MAX) = 'Markets' -- Replace 'Markets' with your schema name
    DECLARE @result varchar(max) = ''

    SET @result = @result + 'using System;' + CHAR(13) + CHAR(13) 

    IF (@TableSchema IS NOT NULL) 
    BEGIN
        SET @result = @result + 'namespace ' + @TableSchema  + CHAR(13) + '{' + CHAR(13) 
    END

    SET @result = @result + 'public class ' + @TableName + CHAR(13) + '{' + CHAR(13) 

    SET @result = @result + '#region Instance Properties' + CHAR(13)  

   SELECT
      @result = @result + CHAR(13)
      + ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13)
    FROM (SELECT
      c.COLUMN_NAME AS ColumnName,
      CASE c.DATA_TYPE
        WHEN 'bigint' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Int64?'
            ELSE 'Int64'
          END
        WHEN 'binary' THEN 'Byte[]'
        WHEN 'bit' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'bool?'
            ELSE 'bool'
          END
        WHEN 'char' THEN 'string'
        WHEN 'date' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?'
            ELSE 'DateTime'
          END
        WHEN 'datetime' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?'
            ELSE 'DateTime'
          END
        WHEN 'datetime2' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?'
            ELSE 'DateTime'
          END
        WHEN 'datetimeoffset' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTimeOffset?'
            ELSE 'DateTimeOffset'
          END
        WHEN 'decimal' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?'
            ELSE 'decimal'
          END
        WHEN 'float' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Single?'
            ELSE 'Single'
          END
        WHEN 'image' THEN 'Byte[]'
        WHEN 'int' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'int?'
            ELSE 'int'
          END
        WHEN 'money' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?'
            ELSE 'decimal'
          END
        WHEN 'nchar' THEN 'string'
        WHEN 'ntext' THEN 'string'
        WHEN 'numeric' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?'
            ELSE 'decimal'
          END
        WHEN 'nvarchar' THEN 'string'
        WHEN 'real' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Double?'
            ELSE 'Double'
          END
        WHEN 'smalldatetime' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'DateTime?'
            ELSE 'DateTime'
          END
        WHEN 'smallint' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Int16?'
            ELSE 'Int16'
          END
        WHEN 'smallmoney' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'decimal?'
            ELSE 'decimal'
          END
        WHEN 'text' THEN 'string'
        WHEN 'time' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'TimeSpan?'
            ELSE 'TimeSpan'
          END
        WHEN 'timestamp' THEN 'Byte[]'
        WHEN 'tinyint' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Byte?'
            ELSE 'Byte'
          END
        WHEN 'uniqueidentifier' THEN CASE C.IS_NULLABLE
            WHEN 'YES' THEN 'Guid?'
            ELSE 'Guid'
          END
        WHEN 'varbinary' THEN 'Byte[]'
        WHEN 'varchar' THEN 'string'
        ELSE 'Object'
      END AS ColumnType,
      c.ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_NAME = @TableName
    AND ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA) t
    ORDER BY t.ORDINAL_POSITION

    SET @result = @result + CHAR(13) + '#endregion Instance Properties' + CHAR(13)  

    SET @result = @result  + '}' + CHAR(13)

    IF (@TableSchema IS NOT NULL) 
    BEGIN
        SET @result = @result + CHAR(13) + '}' 
    END

    PRINT @result

它生成的c#代码如下所示:

using System;

namespace Markets
{
    public class NewsItem        {
        #region Instance Properties

        public Int32 NewsItemID { get; set; }

        public Int32? TextID { get; set; }

        public String Description { get; set; }

        #endregion Instance Properties
    }

}

It may be an idea to use EF, Linq to Sql, or even Scaffolding; however, there are times when a piece of coding like this comes in handy. Frankly, I do not like using EF navigation properties where the code it generates made 19,200 separate database calls to populate a 1000 row grid. This could have been achieved in a single database call. Nonetheless, it could just be that your technical architect does not want you to use EF and the like. So, you have to revert to code like this... Incidentally, it may also be an idea to decorate each of the properties with attributes for DataAnnotations, etc., but I'm keeping this strictly POCO.

编辑 修正了时间戳和Guid?

有点晚了,但我已经创建了一个web工具来帮助创建一个c#(或其他)对象从SQL结果,SQL表和SQL SP。

sql2object.com

这可以让你安全地输入所有的属性和类型。

如果无法识别类型,则将选择默认类型。

之前的解决方案的一个小补充: object_id(@TableName)仅在默认模式中有效。

(Select id from sysobjects where name = @TableName)

只要@tableName是唯一的,就可以在任何模式中工作。