How to create a models class from SQL Server

Question

How to create a models(.cs) class from SQL Server?

I will pass the Connection string of the SQL server and project name then it will generate a class for each table and also consider foreign keys in class as well.

Like this:

public class BankInfo
    {
        [JsonProperty("BankId")]
        public string BankId { get; set; }

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

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

score 0 · Accepted Answer · answered Jul 02 '21 at 16:54

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 a models class from SQL Server

1 Answers1