Yann Semet Yann Semet - 2 months ago 9
SQL Question

Executing a SQL script stored as a resource

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).

Answer

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();
   }
}