SQL Query Generator for Dapper

c# dapper orm sql

Question

Hi was trying to find the ORM with the best performance to use in our new project. My final choice became Dapper. We also need to have our application to include the following features (at least) which prevent us from hard coding the SQL queries to pass to Dapper

  1. Database independent
  2. Run time entities definition

I thought about writing an SQL generator for Dapper but I am not sure the approach I am following is the best:

  1. Declare an Interface with methods signature. The implementation corresponds to the Database system to be used (SQL Server/ MySQL/ PostgreSql/ DB2/ Oracle/ etc...).
  2. Create Database XML Schema using the following format:

    <sqltable name="Foo">
        <sqlfield name="ID" primarykey="1" />
        <sqlfield name="Name" />
        <sqlfield name="Surname />
        <sqlfield name="etc" />
        <sqlreference name"KooID" table="Koo" field="ID" />
    </sqltable>
    
  3. Generate classes/entities using the XML provided above (allowing the extension of the schema on runtime). Objects created are POCO.

  4. Implement methods that loop on the properties of the current entity (using reflexion) and generate SQL statements where property is not null:

    String GetInsert(object currentEntity)
    return:
    "INSERT INTO Foo (ID, Name) VALUES (1, 'BooBoo')"
    
  5. The implementation will include at least

    SELECT
    INSERT INTO
    UPDATE
    DELETE
    JOIN /*(using references like the KooID above)*/
    WHERE /*(using filter expressions)*/
    

Can you think of any backwards/disadvantages of this approach? Can you recommend any improvements?

Thank you!

Accepted Answer

Using MEF instead of configurations

What if you'd dismiss all these configurations and just hard code these providers and use MEF to discover which one is included in your application and use that one? Then when you'd be connecting to a different DB you'd write a new provider and replace provider's assembly? MEF would then do the rest.

Adding new entities duting production without recompilation

But as you added a few more details in the comments I would like to say that the way you're trying to do it is the way to go except I'd introduce a few changes:

  1. Database provider discoverability could still be implemented using MEF so all you'd have to do is drop the provider assembly to your bin folder and your app would use that. This can of course be done by configuration as well. It's on you to decide how to instantiate correct provider.

  2. Your example database schema seems to have syntax defined by yourself. Maybe rather use something that's already proven and allows standardised and likely also more complex definition.

  3. Your UI (views or whatever you use) will actually be templates that can consume shcema XML and not POCOs. POCO objects will only deliver data to UI.

  4. As you'll be using reflection extensivelly in your app it would likely slow it down considerably. I suggest you use better (=faster) approaches. Look at this library on NuGet by Mark Gravell.

Generating and cosuming design-time unknown entities

Data entities are going to be unknown during design time (from the perspective of compiled code) because those POCOs are to be generated from XML schema at runtime. Unless your application is purely database oriented (as in direct manipulation of tables in the database) I don't see how you'll consume these entities with hardcoded UI?

As you mention your UI will actually be able to read the same data schema UI and populate it based on POCO instances read from the database. That is all fine as long as it makes sense for your application to be purely data-oriented without additional business rules or user interface processes.


Popular Answer

i propose my solution, in order to return the final text:

    createPROCEDURE [dbo].[Helper_CreatePocoFromTableName]    
    @tableName varchar(100)
AS
BEGIN
SET NOCOUNT ON;
declare @codeLines table (lineId int, lineText varchar(4000))

insert into @codeLines
Select  rowNr = ROW_NUMBER() over(order by rowNr), PropertyColumn from (
    SELECT 1 as rowNr, 'public class ' + @tableName + ' {' as PropertyColumn
    UNION
    SELECT  rowNr =2 , 'public ' + a1.NewType + ' ' + a1.COLUMN_NAME + ' {get;set;}' as PropertyColumn
    -- ,* comment added so that i get copy pasteable output
     FROM 
    (
        /*using top because i'm putting an order by ordinal_position on it. 
        putting a top on it is the only way for a subquery to be ordered*/
        SELECT TOP 100 PERCENT
        COLUMN_NAME,
        DATA_TYPE,
        IS_NULLABLE,
        CASE 
            WHEN DATA_TYPE = 'varchar' THEN 'string'
            WHEN DATA_TYPE = 'nvarchar' THEN 'string' 
            WHEN DATA_TYPE = 'char' THEN 'string'
            WHEN DATA_TYPE = 'nchar' THEN 'string'
            WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'smalldatetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'datetime2' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'smalldatetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'datetime2' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'YES' THEN 'int?'
            WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'NO' THEN 'int'
            WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'NO' THEN 'Int16'
            WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'YES' THEN 'Int16?'
            WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'NO' THEN 'long'
            WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'YES' THEN 'long?'
            WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'NO' THEN 'byte'
            WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'YES' THEN 'byte?'
            WHEN DATA_TYPE = 'char' THEN 'string'                       
            WHEN DATA_TYPE = 'timestamp' THEN 'byte[]'
            WHEN DATA_TYPE = 'varbinary' THEN 'byte[]'
            WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'NO' THEN 'bool'
            WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'YES' THEN 'bool?'
            WHEN DATA_TYPE = 'xml' THEN 'string'
        END AS NewType
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @tableName
        ORDER BY ORDINAL_POSITION
        ) AS a1 
    UNION 
    SELECT 1000 as rowNr,  '} // class ' + @tableName
    ) as t Order By rowNr asc


declare @max int=(select max(lineId) from @codeLines)

-- assembly result 
declare @i int=1
declare @res nvarchar(max)=''

while(@i<=@max)
begin
  set @res = @res +(select lineText +'
  ' from @codeLines l where l.lineId=@i )

  set @i=@i+1
end

select classCode=@res
END



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why