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?

score 6 · Accepted Answer · answered Oct 21 '17 at 21:17

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

score 1 · Answer 2 · answered Oct 21 '17 at 21:20

The pathing is Relative, but the bigger issue you're running into is that your file isn't being copied on compilation, most likely.

Go to the properties for the file in visual studio, and set 'Copy to Output Directory' to be 'Copy Always' or 'Copy if Newer'.

Keep in mind, this doesn't mean the file is actually embedded into the assembly. Someone can change the text file, which would change the script. There is good and bad to this.

If you want the SQL to be built in to the actual DLL, that's a different process and you'd want to look into involving embedded resources.

score 1 · Answer 3 · answered Oct 21 '17 at 21:45

You are confusing assembly structure with folder layout in file system. This are entirely different things. Assembly (dll or exe) format is defined in CLI specification and has PE file format and is created during compilation of source code. Scripts which are in your project are stored in file system and unless they are marked for specific action in project i.e. resource they will remain were they are.

On the other hand if you mark them as a Embedded.Resource in Project Properties they will be stored in your project assembly as a managed resource which can be accessed during runtime. But it seems to be a weird method to having your scripts around while you are working with .NET.

May suggestion is to make static string or SqlCommand variables and assign to them your SQL queries. In this form they are not stored procedures.

The solution code based on managed resources is below. To get it working add to your project root file MySqlScripts.sql and mark it as Embedded.Resource. In this implementation default project root namespace was "SqlScriptAsAResource" and this is reflected in the name of embedded resources. Change file, namespace and embedded resource names accordingly.

using System;
using System.IO;
using System.Reflection;

namespace SqlScriptAsAResource
{
    class Program
    {
        static void Main(string[] args)
        {
            Assembly myAssembly = Assembly.GetExecutingAssembly();
            Stream resStream = myAssembly.GetManifestResourceStream("SqlScriptAsAResource.MySqlScripts.sql");
            using(StreamReader reader = new StreamReader(resStream))
            {
                String sqlScript = reader.ReadToEnd();
                // Use your SQL script
            }
        }
    }
}

The solution based on static members is a this one:

using System;
using System.Data.SqlClient;

namespace SqlScriptAsAResource
{
    internal static class SqlUtilities
    {
        public static readonly String SqlScriptValue = "SELECT * FROM Table1;";
        public static readonly SqlCommand Commadn = new SqlCommand("SELECT * FROM Table1;");
    }
}

Hello. I don't see the difference between your first option and the one from kmatyaszek. For the second option I'm using static strings for short SQL scripts. — Pierre Anken, Oct 21 '17 at 21:56
Hi, so you get proof that two coders arrived independently to the same solution - like in science you have two independent articles saying the same about new discovery in the very same issue of the very same scientific magazine :) personally know a lot of such publications — Jacek Blaszczynski, Oct 21 '17 at 22:16

Reading data embedded into a .NET assembly

3 Answers3