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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CREATE FUNCTION [dbo].[InitCap] ( @InputString VARCHAR(4000) ) 
RETURNS VARCHAR(4000)
AS
BEGIN
 
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)
BEGIN
    SET @CHAR     = SUBSTRING(@InputString, @INDEX, 1)
    SET @PrevChar = CASE WHEN @INDEX = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @INDEX - 1, 1)
                    END
 
    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@CHAR) != 'S'
            SET @OutputString = STUFF(@OutputString, @INDEX, 1, UPPER(@CHAR))
    END
 
    SET @INDEX = @INDEX + 1
END
 
RETURN @OutputString
 
END
GO

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

1
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
USE YourDB_name_here
GO
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,
         TableName,
         ordinal_position

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public class UsersMap : EntityMap<users>
{
	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");
	}
}
</users>

Leave a Reply

You must be logged in to post a comment.