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

Threadpool - A deadly poison wait for SQL Server (The What, When and How)

Introduction  Threadpool is a  poison  wait. Yes, I mean it. Its poison for SQL Server, its poison for the Business and of course, the end-users! The most devastating thing about threadpool is you hardly recognize it because it comes in disguise, meaning you see no memory or cpu pressure in the system, yet you cannot run any query, it seems like your SQL Server is frozen solid. That scary, isn't it?
2 comments
Read more

Intelligent Query Processing in SQL Server 2019 Big Data

SQL Server 2019: Intelligent Query Processing SQL Server 2019 ships with some brand-new features. Many of these features are targeted for Big Data Solutions. No wonder in that, since the world is moving faster towards Big Data and it is absolutely necessary to cope up with that. Today we will discuss one such feature called Approximate Query Processing. Approximate Query Processing SQL Server ships with Intelligent Query Processing out of the box with SQL Server 2019 installation. Approximate Query processing is a part of Intelligent Query Processing. Things we will be covering in this article – Understand the need for Approximation with Case Study Case Study 1: Railway Case Study 2: e-commerce How to use Approximate Query Processing Demo Code for Comparing Performance Results Limitations When to avoid Approximate Query Processing Understand the need for Approximate Query Processing Before using any technological feature, we must understand why we should use it? Should we jus...
Post a Comment
Read more

Maxdop and Cost theshold for parallelism SQL Server

 Maxdop  Maxdop stands for max degree for parallelism.  Let's say, the maxdop is set to 4, it means during parallel plan execution, SQL server is going to use 4 processors. If you set your Maxdop settings to 0, it means SQL server is going to use as many processors it needs to complete your request. 
Post a Comment
Read more

Why not use Select * in SQL Server

  Select * We often use the Select *  to fetch data from tables of SQL Server.
2 comments
Read more

SQL Schema Compare with Visual Studio (A complete Guide)

Introduction When you're working on your Dev Database, an urgent issue comes along, and you instantly solve it by changing Scheme in the Staging Database or Production Database :3, few more these type of patching and you're completely out of sync! A lot of paid alternatives are there like SQL Data Compare by RedGate, but my first choice is Visual Studio's SQL Data Tools. In the following article, I tried to image-describe the steps for SQL Data Tool. Like I said before, there are lots of handly DBAtools out there to compare Schema between two DB Sources. I would like to discuss how you can compare two SQL Server DB with Visual Studio. Make sure you have SQL Server Data tools checked while installing Visual Studio.
Post a Comment
Read more

How to Backup SQL server like Batman: The Ultimate SQL DBA Guide

Post a Comment
Read more

How to configure your Availability Group listener to ASP.NET

SQL Server’s availability group Always On feature is great to have features for your Database. Anytime one of your database nodes goes down, your secondary replica will automatically take over. After a failover, your secondary cluster node becomes the primary cluster. Now the question arises, “Do I need to configure my APP server connectionstring each time I face a failover cluster?”. The answer is NO, you don’t have to configure your app server connectionstring every time. Default ConnectionString By default, your App server connectionstring looks something like this – <connectionStrings>    <add name="ConnStringDb1" connectionString="Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient" />   </connectionStrings> ConnectionString for Failover Partner You can manually specify the failover partner in your connectionstring like this <connectionStrings>     <a...
Post a Comment
Read more

SQL Data Tools - Compare Data

Compare Data between two tables SQL Server Database with the same schema architecture can differ in different environments like Dev, Staging, and Production, especially in configuration tables. Let's see how we can easily sync the data in two different tables.
1 comment
Read more

Slow SQL Server : What we should NOT do

 Try to list the best practices of SQL Server. It will require a heck of a time. Try to list the Bad Practices and it will require more than the best practice list , of course, probably you’ll end up getting frustrated . (seeing all the oops configurations and its effect on SQL Server )
Post a Comment
Read more