How best to store text queries? That it was convenient to work with them, edit, search. Interested in the storage of text, without using ORM. I will be glad to hear any suggestions and examples from personal experience.

    4 answers 4

    If it is SQL, then perhaps you should use stored procedures.

    • one
      Perhaps the procedures here will benefit from submissions, because there is the possibility of passing parameters. - Donil

    If these queries are to be executed, why not create views? It is stored, and you can edit them.

    • Hmm ... And if several applications work with the base, the number of these views can be very large, and, the same, SSMS presents very limited filtering functions. Although there is an option to "mark" your own prefix with something - Donil
    • 3
      > Although it remains the option to "mark" with your own prefix. You can use schemes for this, then no prefixes are needed. - msi

    Store constants in DAO. If each entity object has its own DAO for you, then searching for queries will not be difficult, and editing will recompile just one class. Do not store them in configs. If the user's knob curves get to the config, the whole program will die.

      In one of the projects I was working on, the requests were stored in a separate xml file in this form

      <queries> <data name="GetName"> <![CDATA[EXECUTE DB.dbo.p_GetName @id , @Parameter1, @Parameter2, @IP ]]> </data> <data name="GetMessagesList"> <![CDATA[SELECT id, Message FROM DB.dbo.f_GetMessagesList( @Id )]]> </data> ... </queries>

      Quite easy to look for requests. The application had a client-server architecture; the list of all requests was stored on the server side, and only the "specially trained" person had access to the database and the list itself. All programmers received only the query interface (Names, for example, GetName, in and out. Parameters). By the name of the file by the name of the request, the request was jerked, with the help of ADO.Net, a SqlCommand was created , filled with parameters and executed. Query texts themselves can be stored either in the same file, or in the form of a stored, and in this file only a call.