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



当前回答

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

(Select id from sysobjects where name = @TableName)

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

其他回答

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

(Select id from sysobjects where name = @TableName)

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

是的,这些是伟大的,如果你使用一个简单的ORM像Dapper。

如果你使用。net,你可以在运行时使用WriteXmlSchema方法用任何数据集生成XSD文件。http://msdn.microsoft.com/en-us/library/xt7k72x8 (v = vs.110) . aspx

是这样的:

using (SqlConnection cnn = new SqlConnection(mConnStr)) {
DataSet Data = new DataSet();
cnn.Open();
string sql = "SELECT * FROM Person";

using (SqlDataAdapter Da = new SqlDataAdapter(sql, cnn))
{
try
{
    Da.Fill(Data);
    Da.TableMappings.Add("Table", "Person");
    Data.WriteXmlSchema(@"C:\Person.xsd");
}
catch (Exception ex)
{ MessageBox.Show(ex.Message); }
}
cnn.Close();

从那里,您可以使用xsd.exe创建一个可从开发人员命令提示符序列化XML的类。 http://msdn.microsoft.com/en-us/library/x6c1kb0s (v = vs.110) . aspx

是这样的:

xsd C:\Person.xsd /classes /language:CS

Visual Studio杂志发表了这篇文章:

为SQL查询结果生成。net POCO类

它有一个可下载的项目,你可以构建,给它你的SQL信息,它会为你制作出类。

现在,如果该工具刚刚为SELECT、INSERT和UPDATE创建了SQL命令....

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; }
}

因为之前没有人提到它,所以还有Scaffold-DbContext

在NuGet Package Manager控制台类型如下: Microsoft.EntityFrameworkCore.SqlServer -OutputDir "输出目录"