This is just a short post in using Dapper ORM to Map column names in Mssql generating c# csharp code.

I had a requirement using Dapper ORM to Easily Map Column Names, problem was that the columns names had Underscores in them and the need to Have uppercase letter at the beginning of each word to conform to naming conventions. The service we are re-writing has numerous mapping tables so it all started out fine doing the tables with only a few columns, but as things cot more intense at 32 plus columns then I needed a better way to do it. MSSQL, to the rescue, I found a method on the web to convert the initial capitalisation for the beginning of each word for the I used this method:

CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) ) 

DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index < = LEN(@InputString)
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))

    SET @Index = @Index + 1

RETURN @OutputString


Once that was achieved I created a second script to do the swapping out of the values. I also wanted to excluded all columns that didn't have an underscore. That was done by using CharIndex. The Code that is generated is used for dapper.fluentmap this can be installed using Nuget with the following command in the Package Manager Console

Install-Package Dapper.FluentMap 

To generate the mapping code you can now run the following query to generate the mapping class code. This will make it much easier to build the mappings. Swap out the @table_name variable below and then You are Ready to Go.

USE YourDB_name_here
DECLARE @table_name varchar(250) = 'Your_Table_Name_Here'

SELECT   SchemaName = c.table_schema,
         TableName = c.table_name,
         ColumnName = c.column_name,
		 ColumnName1 =   'Map(t => t.'+ replace(dbo.InitCap(Replace(c.column_name,'_',' ')),' ','') + ').ToColumn("' + c.column_name +'");'   ,
         DataType = data_type
FROM     information_schema.columns c
         INNER JOIN information_schema.tables t
           ON c.table_name = t.table_name
              AND c.table_schema = t.table_schema
              AND t.table_type = 'BASE TABLE'
WHERE c.Table_name = @table_name
AND   CHARINDEX('_',c.column_name) > 0
ORDER BY SchemaName,

Dapper is a lightweight ORM and can be found here
Dapper.FluentMapping can be found here
When all is said and “run” then we have a piece of generated code that looks like this

public class UsersMap : EntityMap
	public UsersMap()
		Map(t => t.UserId).ToColumn("user_id");
		Map(t => t.CompanyId).ToColumn("company_id");
		Map(t => t.UserTypeId).ToColumn("user_type_id");
		Map(t => t.UserAccessId).ToColumn("user_access_id");
		Map(t => t.DateAdded).ToColumn("date_added");
		Map(t => t.DateModified).ToColumn("date_modified");
		Map(t => t.AddedBy).ToColumn("added_by");
		Map(t => t.ModifiedBy).ToColumn("modified_by");

Leave a Reply

You must be logged in to post a comment.