20

I would like to store lengthy .sql scripts in my solution and execute them programmatically. I've already figured out how to execute a string containing my sql script but I haven't figured out how to read the string from a file that would be stored in the solution (under a /Scripts subfolder for example).

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Yann Semet
  • 623
  • 2
  • 5
  • 12
  • There is a well detailed answer about embedded resource in a duplicate question https://stackoverflow.com/questions/23301964/ef-6-code-first-with-custom-stored-procedure/39673550#39673550 – Michael Freidgeim Dec 02 '17 at 06:26

4 Answers4

37

Add the SQL files to your project then create a new resource file. Open up the SQL file and select 'Files' from the top-left drop down (default is Strings). Then hit add resource and navigate to/select the SQL file. This allows you to get SQL from the resource file without losing your type-safety like so:

SqlFiles.SqlScript

The above is the process in Visual Studio 2010. I also wrote about this on my blog.

Daniel Imms
  • 47,944
  • 19
  • 150
  • 166
  • Know this is an old post. Looks very neat, but how do you go about adding parameters to the script ? – zmaster Sep 08 '16 at 09:28
  • You should be able to use `string.Format` for this. – Daniel Imms Sep 08 '16 at 14:53
  • Add to resrouces, and set file to Resources, in code write the NAMESPACE.Properties.Resources.ScriptDB.ToString() – Darlan Dieterich Sep 30 '16 at 03:23
  • 1
    For parameters, see my answer. [QueryFirst](https://marketplace.visualstudio.com/items?itemName=bbsimonbb.QueryFirst) will detect parameters used in your SQL, retrieve their type from the DB, declare them as local variables in a special comments section so you can test-run your query in the editor window, then generate the parameter loading code. Your parameters pop out as typed input arguments to the generated Execute() methods. (Execute, ExecuteScalar, GetOne). The right way becomes the easiest way, and the wrong way (concatenation) becomes impossible :-) – bbsimonbb Feb 14 '17 at 11:30
23

First, edit the .sql file's properties so that it will be embedded as a resource.

Then use code similar to the following to retrieve the script:

string commandText;
Assembly thisAssembly = Assembly.GetExecutingAssembly();
using (Stream s = thisAssembly.GetManifestResourceStream(
      "{project default namespace}.{path in project}.{filename}.sql"))
{
   using (StreamReader sr = new StreamReader(s))
   {
      commandText = sr.ReadToEnd();
   }
}
Av Pinzur
  • 2,188
  • 14
  • 14
  • 2
    @Will - In a SQL CLR Project there seems to be no way to add a strongly typed resource file. – jpierson Feb 07 '12 at 22:49
  • 1
    @Will resources are a poor solution too, imho. There doesn't seem to be a way to modify the generated XML comments, and summaries like *Looks up a localized string similar to create table dbo.Foo ( ...* are hardly useful. – user247702 Apr 15 '17 at 16:03
1

Use QueryFirst. You put your sql in .sql template provided by the tool. Behind the scenes, QueryFirst compiles it as a resource and wires up the call to retrieve it at runtime. You only need to worry about calling Execute() on the generated wrapper class, and your results are accessible via generated POCOs. End to end type safety. Never have to remember a column name or datatype, plus the considerable advantages of having your sql where god intended... in a .sql file.

disclaimer: I wrote QueryFirst

bbsimonbb
  • 27,056
  • 15
  • 80
  • 110
  • Reader passing by, admire the absolutely incredible bias of stackoverflow against tools. Q. How do I do x? A. Here are some lines of code you can copy and paste. (accolades) A. Here is a free open-source tool I wrote that does exactly that. (downvotes) – bbsimonbb Dec 18 '20 at 07:29
0

Add file to Resources, and set file to Resource, in code write:

String SQLCommand = NAMESPACE.Properties.Resources.ScriptDB.ToString()
Darlan Dieterich
  • 2,369
  • 1
  • 27
  • 37