Micro ORM - support for your SQL query strings

Question

I will not go into details of why I am studying using Micro ORM at this stage - except that I feel powerless when I use full-scale ORM. Too many things happen in the background, which happens automatically, and not all of them are the best choice. I was quite ready to return to raw access to the database, but I learned about three new guys on the block: Dapper, PetaPoco and Massive. So I decided to give a low-level approach to go with a home project. This is not relevant, but so far I am using PetaPoco.

In any case, I am having problems resolving the issue of how to maintain SQL strings, which I will use from higher levels. There are three main solutions that I can think of:

• Sprinkle SQL queries wherever I need them. This is the least complex infrastructure method. However, he suffers both in service and in verifiability.

• Limit the use of the request to certain classes of service. This helps maintainability, remains low in the infrastructure that I need to implement. It is also possible to build these classes of service in such a way that they are easy to deride for testing purposes.

• Prepare some classes to make the system somewhat flexible. I started along this path. I implemented the repository interface and the database-dependent Repository class. I also created some tiny interfaces to capture SQL queries that can be passed to my GetMany () method. All queries are implemented as separate classes right now, and I probably need a little more interface for this to add some level of database independence - and maybe for some flexibility in processing queries in exported and sorted queries (again, this is also would make them a little more flexible in processing various databases).

I am currently worried that I have entered a slippery slope to write all the functions necessary for a full-blown ORM, but badly. For example, now it seems reasonable to me that I write or find a library to convert linq calls to SQL statements so that I can easily process my queries or write expanders that can decorate any query that I pass to it, etc. But this is a big task, and the big guys have already done, so I resist the desire to go there. I also want to maintain control over what requests I send to the database - by explicitly writing them.

So what is the suggestion? Should I go for option # 2 or try to stumble on option # 3? I am sure that I can not show the code written in the first embodiment, to anyone without blushing. Is there any other approach you can recommend?

---

EDIT: after I asked the question, I realized that there is another option, somewhat orthogonal to these three options: stored procedures. There seem to be several advantages to putting all your queries in a database as stored procedures. They are stored in a central place and are not distributed through code (although maintenance is a problem - parameters may go out of sync). Reliance on the dialect of the database is solved automatically: if you move the database, you transfer all your stored procedures, and you're done. And there are also security benefits.

When using the stored procedure option, alternatives 1 and 2 seem a bit more appropriate. It seems that there are not enough objects to guarantee option 3, but you can still separate the command call procedures from the access code to the database.

I implemented option 3 without stored procedures and option 2 with stored procedures, and it seems to me that the latter is more suitable for me (in case someone is interested in the result of the question).

Answer 1

I would say put sql where you would put the equivalent LINQ or sql query for DataContext.ExecuteQuery. As for where it is ... well, it depends on you and depends on how much you want. - Mark Gravell, creator at Dapper

See Mark's opinion on this subject.

I think the key point is that you really should not reuse SQL. If your logic is reused, it should be wrapped in a method, which can then be called from several places.

Answer 2

I know that you have already accepted your answer, but I still wanted to show you a good alternative, which can be useful in your case. Now or in the future.

When using stored procedures, it is advisable to use T4

I try to use stored procedures in my project, even if it does not use PetaPoco, Dapper or Massive (the project started earlier than they were here). Instead, it uses BLToolkit. Anyway. Instead of writing my methods for running stored procedures and writing code to provide stored procedure parameters, I wrote a T4 template that generates code for me.

When changing stored procedures (some can be added / deleted, added / deleted / renamed / retyped parameters), my code will be broken into compilation, because method calls will no longer match their signature.

I store my stored procedures in a file (so that they control the version). If you work in a team with several developers, it may be wise to store the stored procedures each in its own file. This makes updates much less painful. I tried this on some project and it worked fine until the amount of SP is huge. You can rebuild them into folders based on the entity with which they are associated.

Anyway. Maintenance involves stored procedures, changing the code is just a click of a button in Visual Studio, which immediately converts all T4. You do not need to look for methods that use these procedures. Errors will be reported during compilation. The only thing to worry about.

Therefore, instead of writing

using (var db = new DbManager()) { return db .SetSpCommand( "Person_SaveWithRelations", db.Parameter("@Name", name), db.Parameter("@Email", email), db.Parameter("@Birth", birth), db.Parameter("@ExternalID", exId)) .ExecuteObject<Person>(); } 

and having a bunch of magic lines, I can simply write:

 using (var db = new DataManager()) { return db .Person .SaveWithRelations(name, email, birth, exId) .ExecuteObject<Person>(); } 

It’s better, it breaks cleaner at compilation and provides intellisense, so it also develops faster.

It's good that stored procedures can become very complex and can do a lot. In my upper example, I check some data, insert a person record and some associated with it, and at the end it returns a newly inserted Person record. Insertions and updates usually should return data that has been added / changed to reflect the actual state.