Quick Way to Create C# Models from SQL Tables
Recently, I was working on a project which used SQLCommands and I need to import a few SQL tables to start using as models. The tables were pretty large so I didn’t want to type them in manually. After a bit of searching, I found something on Stack Overflow that helped me out. Link
The code is pretty simple. You execute it against your table and it’ll output a C# class with all properties based on the table’s columns:
declare @TableName sysname = 'myTable' 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 'float' 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 'double' when 'smalldatetime' then 'DateTime' when 'smallint' then 'short' when 'smallmoney' then 'decimal' when 'text' then 'string' when 'time' then 'TimeSpan' when 'timestamp' then 'DateTime' 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
From there, I needed a way to fill the models with the data from the tables so I created a few helper methods:
public static class DAOHelper { public static List<T> GetData<T>(SqlDataReader reader) where T : new() { var lst = new List<T>(); while (reader.Read()) { lst.Add(Map<T>(reader)); } return lst; } public static List<T> GetData<T>(SqlDataReader reader, Func<SqlDataReader, T> mapper) { var lst = new List<T>(); while (reader.Read()) { lst.Add(mapper(reader)); } return lst; } public static T Map<T>(SqlDataReader reader) where T : new() { var t = new T(); var props = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public) .Where(x => !x.IsDefined(typeof(NotMappedAttribute))); foreach (var prop in props) { var columnName = prop.Name; var colAttrib = prop.GetCustomAttributes<ColumnAttribute>().SingleOrDefault(); if (colAttrib != null) columnName = colAttrib.Name; var divAttrig = prop.GetCustomAttributes<DivideAttribute>().SingleOrDefault(); if (divAttrig != null) { if (prop.PropertyType == typeof (int)) { var val = (int)reader[columnName]; val = val / (int)divAttrig.Divisor; prop.SetValue(t, val); } else { var val = (double)reader[columnName]; val = val / divAttrig.Divisor; prop.SetValue(t, val); } } else prop.SetValue(t, reader[columnName]); } return t; } }
This will dynamically fill a model (or list of models) by matching the model’s property names to the SQL Table’s columns. It can be used like this:
public async Task<List<AuctionCalc>> GetAuctionCalcsAsync(int fileId) { using (SqlConnection conn = new SqlConnection(Configuration.DbConnection)) { conn.Open(); using (var cmd = new SqlCommand()) { var sqlTxt = "SELECT * FROM AuctionCalcs ic JOIN [file] f on ic.fileId = f.id" + " WHERE f.id=@fileId"; cmd.CommandText = sqlTxt; cmd.Connection = conn; cmd.Parameters.AddWithValue("@fileId", fileId); using (var reader = await cmd.ExecuteReaderAsync()) { return DAOHelper.GetData<AuctionCalc>(reader); } } } }
And there you have it! A quick way to create C# classes and fill them with data from corresponding SQL tables!