Skip to main content

How to generate C# Class from SQL Server Table

C# Class from SQL Database Table

There are multiple ways you can generate a C# class from your Database Table. We will be covering the following topics in today’s article.

  • Generate Class with foreign key relation
  • Generate Class with only entities

Generate Class with foreign key relations

For this we can simply use Entity Frameworks EDMX update feature which will generate our C# class from Database Tables. The output from EDMX will contain foreign key relations which we can see from the virtual interfaces like this - 


public partial class AssetItem
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public AssetItem()
        {
            this.AssetItemDepreciations = new HashSet<AssetItemDepreciation>();
            this.AssetTaxMappings = new HashSet<AssetTaxMapping>();
            this.AssetVatMappings = new HashSet<AssetVatMapping>();
            this.Assets = new HashSet<Asset>();
        }
    
        public int Id { get; set; }
        public int CategoryId { get; set; }
        public string ShortName { get; set; }
        public string Name { get; set; }
        public string PermittedOfficeLevel { get; set; }
        public Nullable<double> DefaultResidualValue { get; set; }
        public int Status { get; set; }
        public bool IsFixedStatus { get; set; }
        public string CreatedBy { get; set; }
        public System.DateTime CreatedOn { get; set; }
        public string UpdatedBy { get; set; }
        public Nullable<System.DateTime> UpdatedOn { get; set; }
    
        public virtual AssetCategory AssetCategory { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<AssetItemDepreciation> AssetItemDepreciations { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<AssetTaxMapping> AssetTaxMappings { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<AssetVatMapping> AssetVatMappings { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Asset> Assets { get; set; }

This can be a little bit problamatic if we want a class so that we can use it in our POST method model. Because ASP.NET makes it a hassle when we want to use this complex class as a model. For that, we might need a work-around so that we can omit these.

Generate Class with only entities

If we only want to create the class with entities, no need for foreign key relations (which is managed by virtual interfaces in C#), we might need a work-around.

The following code snippet will guide to create a simple C# Class from the table.

declare @TableName sysname = 'TableName'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        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 'double'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'float'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'long'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    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 ColumnId

set @Result = @Result  + '
}'

print @Result

We just need to set @TableName to out TableName. For example, let's say we want to convert our Asset table to a C# class.


So we have put 'Asset' in our @TableName.

The output is as follows- 

public class Asset
{
    public long Id { get; set; }

    public string AssetId { get; set; }

    public int ItemTypeId { get; set; }

    public int ItemCategoryId { get; set; }

    public int AssetType { get; set; }

    public bool IsFixedAsset { get; set; }

    public int PurchaseBranchId { get; set; }

    public int CurrentBranchId { get; set; }

    public long PurchaseWorkingDate { get; set; }

    public int? SupplierId { get; set; }

    public int? Manufacturer { get; set; }

    public string Brand { get; set; }

    public string Model { get; set; }

    public string SerialNumber { get; set; }

    public string Specification { get; set; }

    public long? WarrantyExpireDate { get; set; }

    public double ResidualValue { get; set; }

    public int UsefulLife { get; set; }

    public long? ExpiryDate { get; set; }

    public string PurchaseOrderNumber { get; set; }

    public long? PurchaseOrderDate { get; set; }

    public string InvoiceNumber { get; set; }

    public long? InvoiceDate { get; set; }

    public string ChalanNo { get; set; }

    public long? ChalanDate { get; set; }

    public double BillingPrice { get; set; }

    public bool IsVatDeductible { get; set; }

    public double? VatAmount { get; set; }

    public double? TaxAmount { get; set; }

    public int PurchaseMethod { get; set; }

    public string BankAccount { get; set; }

    public string ChequeNo { get; set; }

    public long? ChequeDate { get; set; }

    public int LocationOfficeType { get; set; }

    public int LocationOfficeId { get; set; }

    public string LocationName { get; set; }

    public int? DepartmentId { get; set; }

    public int? AssignedEmployee { get; set; }

    public int? EntryBatchId { get; set; }

    public int? BatchQuantity { get; set; }

    public int Status { get; set; }

    public int? SubStatus { get; set; }

    public double? AccumulatedDepreciationAmount { get; set; }

    public long? LastDepreciationPeriod { get; set; }

    public double? WrittenDownValue { get; set; }

    public double? CurrentYearDepreciationAmount { get; set; }

    public long? LocationEntryDate { get; set; }

    public long? LocationExitDate { get; set; }

    public long? DisposalDate { get; set; }

    public long? LastStatusChangeSystemDate { get; set; }

    public long? LastStatusChangeBranchDate { get; set; }

    public string CreatedBy { get; set; }

    public DateTime CreatedOn { get; set; }

    public string UpdatedBy { get; set; }

    public DateTime? UpdatedOn { get; set; }

    public long? BranchWorkingDate { get; set; }

    public int? PreviousSubStatus { get; set; }

    public int? DocumentId { get; set; }

    public int? BranchAmmsId { get; set; }  

}

So, we have seen how qucikly we can convert our SQL Server Database Table into a C# Table.

Labels:
Location: Dhaka, Bangladesh

Comments

Post a Comment

Most Loved Posts

How to encrypt an entire excel file using AES-128 encryption

What Is AES? AES stands for Advanced Encryption Standard. It is a symmetric block cipher that is used by the U.S. government to protect classified information. AES is used worldwide to protect classified data around the world. AES is essential in cybersecurity, electronic data protection, and computer security. Variations of AES AES is used in three block cipher versions namely AES 128, AES 192, AES 256. AES 128 uses 128-bit key length to encrypt and decrypt block messages. This is a symmetric secret key which means it uses same secret key for encrypting and decrypting message blocks. AES Encryption of Excel File Today we will AES 128 to encrypt an Excel File. The excel file looks like the following image. To encrypt this excel file, we will use C#. First, we need to create a console project. Let's name the project ExcelEncryption. First, we try to understand what we need to achieve. We need a KEY and Initialization Vector (IV) pair for AES 128 encryption. Let’s say, th
Post a Comment
Read more

SQL Insider 01 : An Anatomy of SELECT

Introduction When we write queries, we tend to think about the internals very little. In the new series of SQL Insider, I shall try to demonstrate what your SQL Server has to go through when you write a specific query, more specifically a specific operator. In the series, we shall try to cover all the important operators in SQL. Our today's SQL participant in SELECT. SELECT  With the SELECT query, we can select one, some, or all the columns of a SQL table. The typical syntax for SELECT is like this  SELECT * FROM Sales.SalesOrderDetail SELECT sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail sod Please note that we will not be dealing with WHERE clause in today's episode.  Database We will be using AdventureWorks2019 Database for the demonstration Important Configuration We will be setting STATISTICS IO ON like this - SET STATISTICS IO ON; SET STATISTICS IO ON ; We will turn on Actual Execution Plan to examine the query SQL Insider Let's start with
Post a Comment
Read more