How to work most competently with SQL from C #?

Are there any good tone conventions? Now I can write the queries directly in C # code and wrap it in a method,

I can also wrap all requests in procedures (up to Select * from table ) and pull them through the code.

Where is the golden mean?

I read several patterns, but not one did not answer the question when it is better to use the procedures, and when it is better to describe a separate method for this. And so and so the code will work, but another question: "How competently?"

If you wrap up all the procedures, the code will, in my opinion, be cleaner, since there will be no SELECT * from t JOIN t2 ... strings that are not usual for the language, but there will be just something like "exec Report". But nowhere did I find confirmation of my point of view.

Closed due to the fact that it is necessary to reformulate the question so that it was possible to give an objectively correct answer by the participants user194374, Vladimir Martyanov , tutankhamun , Pavel Parshin , aleksandr barakin 21 Feb '16 at 10:11 .

The question gives rise to endless debates and discussions based not on knowledge, but on opinions. To get an answer, rephrase your question so that it can be given an unambiguously correct answer, or delete the question altogether. If the question can be reformulated according to the rules set out in the certificate , edit it .

    2 answers 2

    Storing requests in an application is not the best option in terms of security / reliability (if you intend to use multiple clients with different access rights) and change management. In terms of performance, the same thing . Now everyone is really moving to ORM, more convenient, more reliable and "expandable."

    • My requests are not stored on the client, but in the service. The client communicates with the service. - iluxa1810
    • I try to avoid ORM. In my opinion, some operations look less natural and look more beautiful if direct requests are made - iluxa1810
    • @ iluxa1810 this is while your scheme does not change and the code is not enough. ORM allows you to calmly work with ordinary classes without thinking about storing \ name of properties in the database. You do not need to write a single sql line to create a class, save it in the database, change its properties. - Monk
    • Anyway, the ORM does not exempt from the fact that if, say, a column disappears in the database, you will have to edit the code. So there and there, something will have to change. Manual writing of requests, makes requests more productive. - iluxa1810
    • one
      @ iluxa1810 Do you already have specific problems with queries and non-optimal plans? ORM is a universal solution, and for your database you can always add manually written requests for frequent and / or hard cases. - Monk

    For one-time requests (database conversion, for example) it is easier to write directly in the code.

    For regular requests (data retrieval, sample update, some other frequent operation) - either some kind of ORM or procedures in the database.

    With the Core-First approach (when the application when it is first started, for example, it is able to create the database itself), the procedure still needs to be created in the database, which, for example, does not suit me somewhat and I usually screw nHibernate and leave complex queries to his conscience.

    Separately, it is worth thinking about this:

    1. Applications are sometimes written with the support of different databases - then perhaps everything is easier to keep in the code.
    2. Sometimes for the database they write applications in different languages ​​- then it is better to have a single procedure in the database, and in the application it is only to call it.
    3. If there can be many bases and many applications to them, then it’s time to dump on a separate service — that is, the service with ORM is able to interact with several types of databases and allows different clients to connect to them. Requests in this case are allowed only for the service (protection against incorrect client requests is important), and the client doesn’t care at all whether the service uses the database or otherwise stores the information.
    • What is the difference between NHibernate? - VladD
    • @VladD added explanation. - Monk
    • Hmm, but EF can not cope? Do you trust the conscience of NH more? (This is not sarcasm, I really do not know.) - VladD
    • @VladD, I don’t remember now what I was guided by when choosing the first one, but EF somehow seemed uncomfortable. If it is not difficult - can EF customize the mapping code, without touching the entity classes, without forcing to mark them with attributes? - Monk
    • As I understand it, yes: EF Fluent API . - VladD