Archive for February, 2017

In C# Getting the Constant Value from Class using Reflection need not be a difficult and there are quite few articles and blogs written on the subject. I wanted to mimic the GetNameQuery function that is in Hibernate, maintaining backward compatibility while converting services to C# using Dapper. For Inspiration I looked at this blog. This returned a list of the constant Names and what I actually wanted to do was look through all the constant names and then match mine and return the Sql value inside.

Where to start: reflection

Firstly we need to look at the FieldInfo Class looking at the example it shows us how we can get the field values.

Read Full Article

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

Read Full Article

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

Read Full Article