Reading data embedded into a .NET assembly

Question

For a school project I'm not allowed to use stored procedures to store my SQL scripts.To solve this I tried to create a SQL_scripts folder inside my class assembly.

Now I'm stuck on how to read my script from a relative path?

Here what I tried without success:

var appDomain = System.AppDomain.CurrentDomain;
                var basePath = appDomain.RelativeSearchPath ?? appDomain.BaseDirectory;
                string path = Path.Combine(basePath, "SQL_scriptes", "GetAllEmptyRoomsAtDateRange.sql");
                string query = File.ReadAllText(path);

But I'm always in the following folder:

• MyProject\Tests\bin\Debug\SQL_scriptes\GetAllEmptyRoomsAtDateRange.sql

Any idea?

Answer 1

You should add your files with sql code as embedded resources:

And use the following function to get SQL from file:

public string GetScript(string scriptName)
{
    var assembly = Assembly.GetExecutingAssembly();
    var resourceName = "Q46868043.SQL_scripts." + scriptName;

    using (Stream stream = assembly.GetManifestResourceStream(resourceName))
    using (StreamReader reader = new StreamReader(stream))
    {
        return reader.ReadToEnd();
    }
}