How to embed sql queries in c #

Question

I am writing a new C # project with several SQL queries. Some of them are relatively large and complex. I want to know what is the best way to store them. Ideally, I would create stored procedures in a database, but the database is used by many other applications, so it’s better if I can save the procedures specific to my application in my application.

The parameters are as follows:

• string literal ( const string query ="Select * From MyTable" )
  • Pros: simple, short
  • Cons: no syntax highlighting, messy for long queries
• Create a file for each query as QueryName.sql
  • Pros: syntax highlighting, accuracy for large complex queries
  • Cons: many files for a large number of requests (one request per file), is it possible to read a request from a content file more slowly?
• Any other ideas?

As an additional thought, is there a way to easily generate strongly typed class definitions from SQL queries?

Answer 1

Another variant:

4: Create a file for each query as QueryName.sql

but make it an embedded resource in your assembly. Thus, you do not have physical files on disk, but your SQL queries are perfectly inserted into their own file in your Visual Studio solution, and you can easily extract SQL text from these built-in resources into SqlCommand objects.

Check out these resources to get you started:

• How to create embedded resources
• Understanding Embedded Resources in Visual Studio .NET
• How to insert resource files in .NET assemblies

Answer 2

Why not just use the Entity or Linq-to-SQL framework

If you have a table named Foos yoiu, enter the code, for example:

 using(var db = new MyEntityBase()){ var selectedFoo = from foo in db.Foos where foo.Bar > 4 select foo; //Do stuff } 

which in turn is converted to SQL as:

 select * from Foos where Bar = 4 

The above C # code is strongly typed, and the Entity structure will create all the necessary data classes for you.

Answer 3

I will look for any ORM like EF or Subsonic.