IBS Microsoft 365 and SharePoint Blog

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!