Auto-generate C# Classes from Database Schema

SQL Server Information Schema Tables

SQL Server provides a number of Schema views that you can call to get the structure of the database. The link below has more information from Microsoft.

• Microsoft Information-Schema-Views

To build the C# classes we are going to use two of the schema views

• Information_Schema.Tables - returns Catalog, Schema and Table/View name
• Information_Schema.Columns - returns table, name. type, nullable and more

Using these two queries we can get a list of all of the tables, views and column details for the database. Then in C# we can create the class properties and serialize the results into a new class.

Auto-generate the Classes

The script below is designed to run using LINQPad. The query has been setup with the local database connection and is using a database called "KanbanTasks". If you don't have LINQPad setup please follow this link first Setup LinqPad.

Configure Script

The script is configured by a class called QueryOptions. This takes two parameters

• CatalogName - SQL Server Database Name
• AddVirtualPropertyGetterSetter - boolean set to true to have a virtual getter setter on the class output.

Set Language and Database Connection

In LINQPad set the Language to C# Program and the database connection to the database that you are wanting to create the classes from. You can click copy on the code below to then paste into LinqPad.

The Script

public class QueryOptions
{
	public string CatalogName => "KanbanTasks";
	public bool AddVirtualPropertyGetterSetter => false;
}

void Main()
{
	var qo = new QueryOptions();
	var tablesAndViews = GetAllTablesAndViews(qo);
	var outputDir = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop),"Dev-Master-Classes");

	foreach (var item in tablesAndViews)
	{
		var fieldDefinitions = GenerateCSharpFieldDefinitions(item);
		var classDefinition = GetClassDefinition(fieldDefinitions,item,qo);
		OutputFile(classDefinition,item,outputDir);
	}
}

#region HideMe
private List<TableViewDefinition> GetAllTablesAndViews(QueryOptions qo)
{
	var query = $@"SELECT TABLE_CATALOG [CatalogName],
						  TABLE_SCHEMA [SchemaName],
						  TABLE_NAME   [TableViewName],
						  CASE TABLE_TYPE  WHEN 'BASE TABLE' THEN 1 ELSE 0 END [IsTable]
					FROM [{qo.CatalogName}].INFORMATION_SCHEMA.TABLES ";


	var lst = new List<TableViewDefinition>();

	var results = this.ExecuteQuery(typeof(TableViewDefinition),query);
	foreach (TableViewDefinition tvd in results)
		lst.Add(tvd);

	return lst.OrderBy(c=> c.SchemaName).ThenBy(c=> c.TableViewName).ToList();

}
private List<SchemaDefinition> GenerateCSharpFieldDefinitions(TableViewDefinition tvd)
{
	var allFields = new List<SchemaDefinition>();
	var query = $@"SELECT COLUMN_NAME AS ColumnName, ORDINAL_POSITION AS Position,IS_NULLABLE AS IsNullable,DATA_TYPE AS DataType
				   FROM INFORMATION_SCHEMA.COLUMNS
				   WHERE TABLE_CATALOG = '{tvd.CatalogName}'
				   AND TABLE_SCHEMA = '{tvd.SchemaName}'
				   AND TABLE_NAME = '{tvd.TableViewName}'";


	var results = this.ExecuteQuery(typeof(SchemaDefinition), query);

	foreach (SchemaDefinition sd in results)
		allFields.Add(sd);

	return allFields;
}
private string GetClassDefinition(List<SchemaDefinition> SchemaDefinitions, TableViewDefinition tvd, QueryOptions qo)
{
	var pgs = new StringBuilder();
	var virtualText = qo.AddVirtualPropertyGetterSetter ? "virtual " : string.Empty;

	pgs.AppendLine($"// {tvd.SchemaName}.{tvd.TableViewName}");
	pgs.AppendLine($"public class {tvd.TableViewName}\n{{");

	foreach (var sd in SchemaDefinitions)
	{
		var propName = CSharpPropertyName(sd);
		pgs.AppendLine($"\tpublic {virtualText}{propName} {sd.ColumnName} {{ get; set; }}");
	}

	pgs.AppendLine("}");
	return pgs.ToString();
}
public string CSharpPropertyName(SchemaDefinition sd)
{
	var nullable = sd.IsNullable == "YES" ? "?" : string.Empty;
	return sd.DataType switch
	{
		"bit" 		 		=> $"bool{nullable}",
		"tinyint" 	 		=> $"Int16{nullable}",
		"int" 		 		=> $"int{nullable}",
		"bigint"	 		=> $"long{nullable}",
		"smallmoney" 		=> $"decimal{nullable}",
		"money" 	 		=> $"decimal{nullable}",
		"decimal" 	 		=> $"decimal{nullable}",
		"numeric" 	 		=> $"decimal{nullable}",
		"char" 		 		=> $"string",
		"nchar"		 		=> $"string",
		"varchar"	 		=> $"string",
		"nvarchar"	 		=> $"string",
		"text"		 		=> $"string",
		"ntext"		 		=> $"string",
		"xml"		 		=> $"System.Xml.Linq.XElement",
		"smalldatetime"		=> $"DateTime{nullable}",
		"datetime"			=> $"DateTime{nullable}",
		"datetime2"			=> $"DateTime{nullable}",
		"date"				=> $"DateTime{nullable}",
		"datetimeoffset"	=> $"DateTimeOffset{nullable}",
		"time" 				=> $"TimeSpan{nullable}",
		"uniqueidentifier"	=> $"Guid{nullable}",
		"sql_variant"		=> $"object{nullable}",
		"timestamp" 		=> $"byte[]",
		_ 					=> $">{sd.DataType}",

	};
}
public void OutputFile(string classDefinition, TableViewDefinition tvd, string targetFolder)
{
	var nsDir = Path.Combine(targetFolder, tvd.SchemaName);
	if (!Directory.Exists(nsDir))
		Directory.CreateDirectory(nsDir);

	var fileName = Path.Combine(nsDir, $"{tvd.TableViewName}.cs");

	using var writer = new StreamWriter(fileName);

	writer.Write(classDefinition);
	writer.Close();

}
public class SchemaDefinition
{
	public string ColumnName { get; set; }
	public string IsNullable { get; set; }
	public string DataType { get; set; }
	public int Position { get; set; }
}
public class TableViewDefinition
{
	public string CatalogName {get;set;}
	public string SchemaName { get; set; }
	public string TableViewName { get; set; }
	public int IsTable { get; set; }
}

#endregion

Output

The script will create a folder on the desktop named Dev-Master-Classes. Then a sub folder for every schema in that database specified. Finally it will create the C# classes for each of the tables sorted by schema.

Column.cs

// dbo.Column
public class Column
{
	public int Id { get; set; }
	public string Title { get; set; }
	public int? BoardId { get; set; }
}

Boards.cs

// dbo.Boards
public class Boards
{
	public int Id { get; set; }
	public string Title { get; set; }
}