0

I have about 20 (relatively) big queries hardcoded in my C# code that I would like to move somewhere else, as they are now making my code look unmanageable. These SQL queries have in common that they receive a fixed set of parameters (2 parameters to be precise).

I am looking at ways of where to place them in my project and how to manage them, and so I was thinking of creating separate sql files for each query in a folder in which the code would look into, somehow passing these two parameters before actually executing the query.

The question is the following. Are there any standard/efficient ways of performing the above in C#? I really do not like these SQL queries hard-coded in my projects, but I am also mindful that these would be parametrised queries and so I might not be able to achieve the above.

Any guidance or help would be most welcome. In case it helps, I do not have access rights to write stored procedures to solve this situation.

Iliana
  • 33
  • 5
  • See this SO question: http://stackoverflow.com/q/576571/5077042 – Vishnu Prasad V Jul 04 '16 at 11:16
  • You can have placeholders for your parameters. After retrieving queryjust use `queryString.Replace("placeholder","yourValue")` for each of your parameters. Make sure your placeholders are unique and will not collide with actual SQL command. Something like `myParameterPlaceholder` – Vishnu Prasad V Jul 04 '16 at 11:21
  • Thanks, I had already seen this post. Unfortunately, it does not contemplate the possibility of having parametrised SQL queries. Only towards the end it mentions that tweaks could be made at run time when creating a database table, but I am looking at introducing those "tweaks" at compile time. – Iliana Jul 04 '16 at 11:21
  • 1
    Keeping them embedded in C# is fine. Or you can keep them in resources, or in external files which get deployed alongside your program. Please just be careful about SQL injection and use bound parameters - do **not** rely on simple string replacement (like Vishnu proposed). – Branko Dimitrijevic Jul 04 '16 at 11:44

1 Answers1

1

Too bad that it has to be a query not stored procedure If it is not possible to execute stored procedures then you can put the queries in text files and all those text files can be loaded in a resource file. You can access them with "resourceFileName.TextFileName" and please keep the parameters with @ and their names.

you can load the query with using System.Resources; var resourceManager = new ResourceManager("resourceFileName", Assembly.UnsafeLoadFrom("resourceDll")); string myQuery= resourceManager.GetString("TextFileName");

Sonja
  • 575
  • 1
  • 6
  • 20