Creating Dapper entities using MSSQL and Mapping classes is rather easy, but very time consuming from scratch. This came around because I really got tired of the typing a multitude of class and mapping files So let’s jump into it.

Firstly, lets go get the list of tables, this will return a list in the correct format for the T-SQL in clause. The reason why I did it this way was because it’s easier to remove those that I don’t want to generate entities for.

1
2
3
4
5
6
7
8
9
10
DECLARE @db VARCHAR(50) = 'Your_db_name'
DECLARE @tempTable TABLE (TableName VARCHAR(250))
 
SELECT DISTINCT '''' +  ltrim(rtrim(t.table_name))+ ''',' AS TABLE_NAME
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 t.TABLE_CATALOG = @db

The Main Function

Take the list generated above and remove last trailing comma else you will get an error in the next step. Now trim this in a list that is easier to read by removing all the carriage returns and put this on one line or more, Either way it will work. Copy this list into the line t.table_name in (‘Company’,’Contact’, etc…) below. To simplify matter when we cut and paste this into visual studio, got Microsoft SQL Server Management Studio and click on the following menus, Tools / Options/Query Results/SQL Server/Results to Text there is a checkbox to untick entitled ‘Include column headers in the result set, uncheck it’, this will remove the header column names being generated.

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
USE YOUR_DB  -- Set Your Db name Here
GO
SET NOCOUNT ON
GO
/* 
Set your output mode in your query window to text output
Under Tools / Options/Query Results/SQL Server/Results to Text there is a checkbox to untick entitled 'Include column headers in the result set, uncheck it'.
*/
DECLARE @db VARCHAR(50) = 'YOUR_DB'
DECLARE @tempTable TABLE (TableName VARCHAR(250))
 
INSERT INTO @tempTable
 
SELECT  DISTINCT ltrim(rtrim(t.table_name)) AS TABLE_NAME
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 t.TABLE_CATALOG = @db
AND t.table_name IN ('Company','Contact', etc...)  -- Else you will have all those you don't want to
ORDER BY TABLE_NAME
 
SELECT TableName + ',' FROM @tempTable
 
DECLARE @currentTable VARCHAR(250)
 
SELECT top 1 @currentTable = TableName FROM @tempTable 
While (@@ROWCOUNT > 0)
BEGIN
	DELETE FROM @tempTable WHERE TableName = @currentTable
	DECLARE @TABLE_NAME VARCHAR(250) = @currentTable
	-- Entities
	SELECT --'[Serializable]'  + CHAR(13)+CHAR(10) +   -- needed for WCF services but not WebApi 2.0
		--'[DataContract]'  + CHAR(13)+CHAR(10) +  -- needed for WCF services but not WebApi 2.0
		'[Table("' + REPLACE(dbo.InitCap(REPLACE(@TABLE_NAME,'_',' ')),' ','') + '")]'  + CHAR(13)+CHAR(10) + 
		'public class ' + REPLACE(dbo.InitCap(REPLACE(@TABLE_NAME,'_',' ')),' ','') + CHAR(13)+CHAR(10) + 
		'{'  + CHAR(13)+CHAR(10) + 
		'public ' + REPLACE(dbo.InitCap(REPLACE(@TABLE_NAME,'_',' ')),' ','') + '()'  + CHAR(13)+CHAR(10) + 
		'{'  + CHAR(13)+CHAR(10) + 
		'}'
		SELECT   CHAR(13)+CHAR(10) + '[DataMember]'  + CHAR(13)+CHAR(10) + 
		'public ' +
			CASE WHEN data_type = 'uniqueidentifier' THEN 'Guid ' 
			ELSE 
				CASE WHEN data_type = 'varchar' OR data_type = 'nvarchar' THEN 'string ' 
				ELSE 
					CASE WHEN data_type = 'bit' THEN 'bool'  +  (CASE WHEN IS_NULLABLE = 'YES' THEN '? ' ELSE ' ' END)
					ELSE 
						CASE WHEN data_type = 'datetime' THEN 'DateTime' +  (CASE WHEN IS_NULLABLE = 'YES' THEN '? ' ELSE ' ' END) 
						ELSE 
							CASE WHEN data_type = 'float' THEN 'double'  +  (CASE WHEN IS_NULLABLE = 'YES' THEN '? ' ELSE ' ' END)
							ELSE 
								CASE WHEN data_type = 'bigint' THEN 'long'  +  (CASE WHEN IS_NULLABLE = 'YES' THEN '? ' ELSE ' ' END)
								ELSE
									CASE WHEN data_type = 'int' THEN 'int'  +  (CASE WHEN IS_NULLABLE = 'YES' THEN '? ' ELSE ' ' END)
									ELSE
										CASE WHEN data_type = 'varbinary' THEN 'sbyte[] '
											ELSE
											CASE WHEN data_type = 'Decimal' THEN 'decimal'  +  (CASE WHEN IS_NULLABLE = 'YES' THEN '? ' ELSE ' ' END)
											END
										END
									END
								END
							END
						END
					END
				END
			END 
		+ REPLACE(dbo.InitCap(REPLACE(c.column_name,'_',' ')),' ','')  + ' { get; set; }'
	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 column_name
 
	SELECT '}'
 
	-- Mapping
	SELECT 'public class ' + REPLACE(dbo.InitCap(REPLACE(@TABLE_NAME,'_',' ')),' ','') + 'Map : EntityMap< ' + REPLACE(dbo.InitCap(REPLACE(@TABLE_NAME,'_',' ')),' ','') + '>' + CHAR(13)+CHAR(10) + 
		'{' + CHAR(13)+CHAR(10) + 
		'public ' + REPLACE(dbo.InitCap(REPLACE(@TABLE_NAME,'_',' ')),' ','') + 'Map()' + CHAR(13)+CHAR(10) + 
		'{' + CHAR(13)+CHAR(10) 
		SELECT   'Map(t => t.'+ REPLACE(dbo.InitCap(REPLACE(c.column_name,'_',' ')),' ','') + ').ToColumn("' + c.column_name +'");'
		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 c.column_name
 
		SELECT '}' + CHAR(13)+CHAR(10) + '}'
 
	SELECT top 1 @currentTable = TableName FROM @tempTable
END

The Output

Now open a New Query and select the script below and put it into the new query window and run it. What you should get is a text result of your classes and Mapping classes. You can cut and paste this into a new class in Visual Studio and of your are using JetBrains Resharper easily refactor the code into separate files and classes Your Output should look like this:

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
	[Serializable]
	[DataContract]
	[Table("Company")]
	public class Company
	{
		public Company()
		{
		}
 
		[DataMember]
		public Guid AddedBy { get; set; }
 
		[DataMember]
		public string Address1 { get; set; }
 
		[DataMember]
		public string Address2 { get; set; }
 
		[DataMember]
		public Guid CompanyId { get; set; }
 
		[DataMember]
		public Guid ContactId1 { get; set; }
 
		[DataMember]
		public Guid ContactId2 { get; set; }
 
		[DataMember]
		public DateTime DateAdded { get; set; }
 
		[DataMember]
		public DateTime? DateModified { get; set; }
 
		[DataMember]
		public bool Deleted { get; set; }
 
		[DataMember]
		public string Description { get; set; }
 
		[DataMember]
		public bool Disabled { get; set; }
 
		[DataMember]
		public string Email1 { get; set; }
 
		[DataMember]
		public string Email2 { get; set; }
 
		[DataMember]
		public string Fax1 { get; set; }
 
		[DataMember]
		public string Fax2 { get; set; }
 
		[DataMember]
		public string Logo { get; set; }
 
		[DataMember]
		public Guid ModifiedBy { get; set; }
 
		[DataMember]
		public string Name { get; set; }
 
		[DataMember]
		public string Region { get; set; }
 
		[DataMember]
		public string Tel1 { get; set; }
 
		[DataMember]
		public string Tel2 { get; set; }
	}
 
	public class CompanyMap : EntityMap<company>
	{
		public CompanyMap()
		{
 
			Map(t => t.AddedBy).ToColumn("added_by");
			Map(t => t.CompanyId).ToColumn("company_id");
			Map(t => t.ContactId1).ToColumn("contact_id1");
			Map(t => t.ContactId2).ToColumn("contact_id2");
			Map(t => t.DateAdded).ToColumn("date_added");
			Map(t => t.DateModified).ToColumn("date_modified");
			Map(t => t.ModifiedBy).ToColumn("modified_by");
 
		}
	}
</company>

Conclusion

The script takes into account nulls to by marking them as Nullable. But by no means will the script cover all datatypes so you will need to modify the script to suit your needs, and it’s also not meant to cover all bases but it sure beats typing it all out by hand!!

Leave a Reply

You must be logged in to post a comment.