Reading data embedded in a .NET assembly

Question

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

Now I am fixated on how to read my script from a relative path?

Here is 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 am always in the following folder:

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

Any idea?

Answer 1

You must add your sql code files as inline resources:

And use the following function to get SQL from a 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();
    }
}

Answer 2

, , , , .

visual studio " " " " ", ".

, , . - , script. .

, SQL DLL, , .

Answer 3

. . Assembly (dll exe) CLI PE . , , , , , .

, Embedded.Resource Project Properties, , . , , .NET.

SqlCommand SQL-. .

The following is a solution code based on managed resources. To make it work, add MySqlScripts.sql to the root file of your project and mark it as Embedded.Resource. In this implementation, the default root namespace of the project was "SqlScriptAsAResource", and this is reflected in the name of the embedded resources. Modify the 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 as follows:

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