Generate Class from Database Table

Generate class from database table

Set @TableName to the name of your 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

Generate C# class from SQL Server table without Store Procedure

This online tool generates a class from SQL table. Class is based on the CREATE TABLE script in MS-SQL, ORACLE, MYSQL, POSTGRESQL and SQLite databases, to a class in C# and other programming languages.

https://codverter.com/src/sqltoclass

Sample Image

How to create a models class from SQL Server

the following code can be cloned from the following GitHub repository using for testing a modified NorthWind database, see the script here.

Code has been written with C#9, .NET Core 5 and with minimal tweaks can work with earlier versions of the .NET Framework
Core code inspired from this post.

Sample created class

public class Contacts
{
    [JsonProperty("ContactId")]
    public int ContactId { get; set; }

    [JsonProperty("FirstName")]
    public string FirstName { get; set; }

    [JsonProperty("LastName")]
    public string LastName { get; set; }

    [JsonProperty("ContactTypeIdentifier")]
    public int? ContactTypeIdentifier { get; set; }
}

In DataOperations class a query is read from a text file to generate class files for each table in a specific database.
To run the code from the repository code

DataOperations.Server = ".\\SQLEXPRESS";
DataOperations.Database = "NorthWind2020";
DataOperations.OutputFolder = "Classes";

DataOperations.Create();

When running this code in your project, make sure to create a folder named Classes under the same folder as your executable.

DataOperations.cs

using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace CreateClassesFromSqlServer
{
    public class DataOperations
    {
        /// <summary>
        /// SQL-Server name
        /// </summary>
        public static string Server { get; set; }
        /// <summary>
        /// Database in Server
        /// </summary>
        public static string Database { get; set; }
        /// <summary>
        /// Location to create classes
        /// </summary>
        public static string OutputFolder { get; set; }
        
        /// <summary>
        /// Iterate <see cref="Database"/> tables, create classes from
        /// SQL in the file ClassQuery.txt
        /// </summary>
        public static void Create()
        {
            var classQuery = File.ReadAllText("ClassQuery.txt");
            using var connection = new SqlConnection($"Server={Server};Database={Database};Integrated Security=true");
            
            connection.Open();
            
            var adapter = new SqlDataAdapter(
                "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
                $"FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_CATALOG = '{Database}') AND (TABLE_NAME != N'sysdiagrams') " + 
                "ORDER BY TABLE_NAME", connection);
            
            DataTable table = new DataTable();
            adapter.Fill(table);

            foreach (DataRow row in table.Rows)
            {
                var tableName = row["TABLE_NAME"].ToString();
                var fileName = tableName + ".cs";

                string sql = $"declare @TableName sysname = '{tableName}'{classQuery}";

                using var cmd = new SqlCommand(sql, connection);
                string code = (string)cmd.ExecuteScalar();

                if (File.Exists(Path.Combine(OutputFolder, fileName)))
                {
                    File.Delete(Path.Combine(OutputFolder, fileName));
                }
                
                File.WriteAllText(Path.Combine(OutputFolder, fileName), code);

            }
        }

    }
}

Query to generate class files

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

select @Result = @Result + CHAR(13) + '    [JsonProperty("' + ColumnName + '")]
    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  + '}'
select @Result

Important

No guarantees that the code fits all situations, has not been fully tested for all possible situations.

How to create entity classes using database for all the existing tables?

If you use JPA for persistence, you can use "JPA tools" provided by Eclipse to generate java entity class from tables.

Here is the [tutorial][1]

[1]: http://shengwangi.blogspot.com/2014/12/how-to-create-java-classes-from-tables.html on my personal website.

Generate entity class from database table

I found some nice T4 template I could use. Its from a project on codeplex.

LINQ to SQL templates for T4

The template is hard to read an it took me a while to simplify it.
Before you can use it you have to download an include (CSharpDataClasses.tt ) from the project.

Here is my template ():

        <# // L2ST4 - LINQ to SQL templates for T4 v0.82 - http://www.codeplex.com/l2st4
        // Copyright (c) Microsoft Corporation.  All rights reserved.
        // This source code is made available under the terms of the Microsoft Public License (MS-PL)
        #><#@ template language="C#v3.5" hostspecific="True"
        #><#@ include file="L2ST4.ttinclude"
        #><#@ output extension=".generated.cs"
        #><# // Set options here
        var options = new {
            DbmlFileName = Host.TemplateFile.Replace(".tt",".dbml"), // Which DBML file to operate on (same filename as template)
            SerializeDataContractSP1 = false, // Emit SP1 DataContract serializer attributes
            FilePerEntity = true, // Put each class into a separate file
            StoredProcedureConcurrency = false, // Table updates via an SP require @@rowcount to be returned to enable concurrency
            EntityFilePath = Path.GetDirectoryName(Host.TemplateFile) // Where to put the files 
        };
        var code = new CSharpCodeLanguage();
        var data = new Data(options.DbmlFileName);
        var manager = new Manager(Host, GenerationEnvironment, true) { OutputPath = options.EntityFilePath };
        data.ContextNamespace = (new string[] { manager.GetCustomToolNamespace(data.DbmlFileName), data.SpecifiedContextNamespace, manager.DefaultProjectNamespace }).FirstOrDefault(s => !String.IsNullOrEmpty(s));
        data.EntityNamespace = (new string[] { manager.GetCustomToolNamespace(data.DbmlFileName), data.SpecifiedEntityNamespace, manager.DefaultProjectNamespace }).FirstOrDefault(s => !String.IsNullOrEmpty(s));
        manager.StartHeader();

        manager.EndHeader();
        var tableOperations = new List<TableOperation>();
            foreach(var table in data.Tables)
                tableOperations.AddRange(table.Operations);
            foreach(Table table in data.Tables)
                foreach(OperationType operationType in Enum.GetValues(typeof(OperationType)))
                    if (!tableOperations.Any(o => (o.Table == table) && (o.Type == operationType))) {}
        if (!String.IsNullOrEmpty(data.ContextNamespace)) {}
        foreach(Table table in data.Tables) {
            foreach(TableClass class1 in table.Classes) {
                manager.StartBlock(Path.ChangeExtension(class1.Name + "Info" ,".cs"));
                if (!String.IsNullOrEmpty(data.EntityNamespace)) {#>
        using System;
        using System.Collections.Generic;
        using System.Linq;
        using System.Text;
        namespace <#=data.EntityNamespace#>
        {   
        <#      }

        #>  <#=code.Format(class1.TypeAttributes)#> class <#=class1.Name#>Info
            {

        <#      int dataMemberIndex = 1;
                if (class1.Columns.Count > 0) {
        #><#            foreach(Column column in class1.Columns) {#>
                private <#=code.Format(column.StorageType)#> <#= "_" + char.ToLower(column.Member[0]) +  column.Member.Substring(1) #><# if (column.IsReadOnly) {#> = default(<#=code.Format(column.StorageType)#>)<#}#>;

                <#=code.Format(column.MemberAttributes)#><#=code.Format(column.Type)#> <#=column.Member#>
                {
                    get { return <#= "_" + char.ToLower(column.Member[0]) +  column.Member.Substring(1) #>; }
                    set {<#= "_" + char.ToLower(column.Member[0]) +  column.Member.Substring(1) #> = value;}
                }

        <#          }
                }
        #>
            }
        }
        <#      
                manager.EndBlock();
            }
        }
        manager.StartFooter();
        manager.EndFooter(); 
        manager.Process(options.FilePerEntity);#>

Generate model classes from database using EF Core

For the same purpose, we use EF Core Power Tools Reverse Engineer feature. It lets you use handlebars templates for to-be-generated-entity-classes, so we can easily add custom using statements and inheritance from our base entity class. Hope it works for you as well.

Here is the link to the built-in scaffolding feature.

Generate Class from Database Table