How can I generate database tables from C# classes?
It's really late, and I only spent about 10 minutes on this, so its extremely sloppy, however it does work and will give you a good jumping off point:
using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
namespace TableGenerator
{
class Program
{
static void Main(string[] args)
{
List<TableClass> tables = new List<TableClass>();
// Pass assembly name via argument
Assembly a = Assembly.LoadFile(args[0]);
Type[] types = a.GetTypes();
// Get Types in the assembly.
foreach (Type t in types)
{
TableClass tc = new TableClass(t);
tables.Add(tc);
}
// Create SQL for each table
foreach (TableClass table in tables)
{
Console.WriteLine(table.CreateTableScript());
Console.WriteLine();
}
// Total Hacked way to find FK relationships! Too lazy to fix right now
foreach (TableClass table in tables)
{
foreach (KeyValuePair<String, Type> field in table.Fields)
{
foreach (TableClass t2 in tables)
{
if (field.Value.Name == t2.ClassName)
{
// We have a FK Relationship!
Console.WriteLine("GO");
Console.WriteLine("ALTER TABLE " + table.ClassName + " WITH NOCHECK");
Console.WriteLine("ADD CONSTRAINT FK_" + field.Key + " FOREIGN KEY (" + field.Key + ") REFERENCES " + t2.ClassName + "(ID)");
Console.WriteLine("GO");
}
}
}
}
}
}
public class TableClass
{
private List<KeyValuePair<String, Type>> _fieldInfo = new List<KeyValuePair<String, Type>>();
private string _className = String.Empty;
private Dictionary<Type, String> dataMapper
{
get
{
// Add the rest of your CLR Types to SQL Types mapping here
Dictionary<Type, String> dataMapper = new Dictionary<Type, string>();
dataMapper.Add(typeof(int), "BIGINT");
dataMapper.Add(typeof(string), "NVARCHAR(500)");
dataMapper.Add(typeof(bool), "BIT");
dataMapper.Add(typeof(DateTime), "DATETIME");
dataMapper.Add(typeof(float), "FLOAT");
dataMapper.Add(typeof(decimal), "DECIMAL(18,0)");
dataMapper.Add(typeof(Guid), "UNIQUEIDENTIFIER");
return dataMapper;
}
}
public List<KeyValuePair<String, Type>> Fields
{
get { return this._fieldInfo; }
set { this._fieldInfo = value; }
}
public string ClassName
{
get { return this._className; }
set { this._className = value; }
}
public TableClass(Type t)
{
this._className = t.Name;
foreach (PropertyInfo p in t.GetProperties())
{
KeyValuePair<String, Type> field = new KeyValuePair<String, Type>(p.Name, p.PropertyType);
this.Fields.Add(field);
}
}
public string CreateTableScript()
{
System.Text.StringBuilder script = new StringBuilder();
script.AppendLine("CREATE TABLE " + this.ClassName);
script.AppendLine("(");
script.AppendLine("\t ID BIGINT,");
for (int i = 0; i < this.Fields.Count; i++)
{
KeyValuePair<String, Type> field = this.Fields[i];
if (dataMapper.ContainsKey(field.Value))
{
script.Append("\t " + field.Key + " " + dataMapper[field.Value]);
}
else
{
// Complex Type?
script.Append("\t " + field.Key + " BIGINT");
}
if (i != this.Fields.Count - 1)
{
script.Append(",");
}
script.Append(Environment.NewLine);
}
script.AppendLine(")");
return script.ToString();
}
}
}
I put these classes in an assembly to test it:
public class FakeDataClass
{
public int AnInt
{
get;
set;
}
public string AString
{
get;
set;
}
public float AFloat
{
get;
set;
}
public FKClass AFKReference
{
get;
set;
}
}
public class FKClass
{
public int AFKInt
{
get;
set;
}
}
And it generated the following SQL:
CREATE TABLE FakeDataClass
(
ID BIGINT,
AnInt BIGINT,
AString NVARCHAR(255),
AFloat FLOAT,
AFKReference BIGINT
)
CREATE TABLE FKClass
(
ID BIGINT,
AFKInt BIGINT
)
GO
ALTER TABLE FakeDataClass WITH NOCHECK
ADD CONSTRAINT FK_AFKReference FOREIGN KEY (AFKReference) REFERENCES FKClass(ID)
GO
Some further thoughts...I'd consider adding an attribute such as [SqlTable] to your classes, that way it only generates tables for the classes you want. Also, this can be cleaned up a ton, bugs fixed, optimized (the FK Checker is a joke) etc etc...Just to get you started.
Create SQL Server table from user-defined class
Which SQL-type should I use for Name ???
If you're looking at Entity Framework as an example to follow, by default they map System.String
to NVARCHAR(MAX)
in all cases. Using property attributes in the Code First entity model (e.g. [Column(DbType = "CHAR(10)")]
allows the developer to explicity specify column types.
Maybe you could follow this approach and default to the all encompassing NVARCHAR(MAX)
for all strings and create some simple attributes within your ORM for special cases. You wont know string lengths until runtime, after all.
Transferring database tables to Classes/ Classes to database tables
You could use just one class and just one query to load your data. It is just a matter to build the correct sql query and let Dapper do its magic in mapping the data retrieved to your Component class.
Suppose you change your component class like this
public class Component
{
public int ID { get; set; }
public string ComponentX { get; set; }
public int Color_ID { get; set; }
public int Material_ID { get; set; }
public string Material { get; set; }
public string Color {get;set;}
}
now you can retrieve your data using a proper join between the tables
IEnumerable<Component> SelectComponents()
{
using (IDbConnection connection = OpenConnection())
{
const string query = @"SELECT p.ID, p.Component as ComponentX,
p.Color_ID, p.Material_ID,
c.Color, m.Material
FROM Component p
JOIN Color c on p.Color_ID = c.ID
JOIN Material m on p.Material_ID = m.ID";
return connection.Query<Component>(query, null);
}
}
Notice that I have renamed the member Component to ComponentX because you can't have a member name with the same name of the enclosing type
Related Topics
Easiest Way to Check If an Arbitrary String Is a Valid Filename
Choosing the Default Value of an Enum Type Without Having to Change Values
How to Get Column Names of Table At Runtime in C#
How to Get the Xml Soap Request of an Wcf Web Service Request
Windows Service Cannot Access Network Location (Unc) Path
Securely Store a Password in the Application in C#
How to Pass Multiple Arguments in Processstartinfo
How to Read Appsettings.Json With Array of Values
How to Add/Update Child Entities When Updating a Parent Entity in Ef
Pass Array into ASP.NET Core Route Query String
How to Trim All Elements in a List
C# Download Big File from Server With Less Memory Consumption
Generate C# Class from SQL Server Table Without Store Procedure
Convert Json File to C# and Stored in Database
Return Json, But It Includes Backward Slashes "\", Which I Don't Want
How to Format Number as Money Using Regex
Passing a List of Objects into an MVC Controller Method Using Jquery Ajax