Skip to content

Instantly share code, notes, and snippets.

@rflechner
Last active January 19, 2023 02:33
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save rflechner/fab685187f10b8eb9815c6af1f874d3d to your computer and use it in GitHub Desktop.
Save rflechner/fab685187f10b8eb9815c6af1f874d3d to your computer and use it in GitHub Desktop.
How to store SQL files in assembly and execute them in C#
-- this file Embedded resource
SELECT * FROM "Sellers"
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.CompilerServices;
namespace ConsoleApp2
{
class Program
{
static void Main(string[] args)
{
var repository = new SellerStatsRepository();
foreach (var seller in repository.GetSellers())
{
Console.WriteLine($"Seller {seller.Name} is {seller.Age} years old.");
}
foreach (var seller in repository.GetSellersHavingInvoiceCountOf(42))
{
Console.WriteLine($"Seller {seller.Name} is {seller.Age} years old.");
}
Console.ReadKey(true);
}
}
public class SellerModel
{
public string Name { get; set; }
public int Age { get; set; }
}
public class SellerStatsRepository: RepositoryBase
{
public IList<SellerModel> GetSellersHavingInvoiceCountOf(int invoiceCount)
{
var param = new { invoiceCount };
return ExecuteView(record => new SellerModel
{
Name = (string)record["Name"],
Age = (int)record["Age"]
}, param).ToList().AsReadOnly();
}
public IList<SellerModel> GetSellers()
{
return ExecuteView(record => new SellerModel
{
Name = (string) record["Name"],
Age = (int) record["Age"]
}).ToList().AsReadOnly();
}
}
public abstract class RepositoryBase
{
public static IDictionary<string, object> ToDictionary(object o)
=> o?.GetType()?.GetProperties()?.ToDictionary(member => member.Name, member => member.GetValue(o)) ?? new Dictionary<string, object>();
[MethodImpl(MethodImplOptions.NoInlining)]
//object is type of record set and builder contains logic of mapping
//TODO: change Dictionary<string, object> type to ORM record type
public IEnumerable<T> ExecuteView<T>(Func<Dictionary<string, object>, T> builder, object args = null)
{
var parameters = ToDictionary(args);
var stackTrace = new StackTrace();
var frame = stackTrace.GetFrame(1);
var method = frame.GetMethod();
var assembly = method.DeclaringType.Assembly;
using (var stream = assembly.GetManifestResourceStream($"{method.DeclaringType.Namespace}.{method.Name}.sql"))
{
using (var reader = new StreamReader(stream))
{
var sql = reader.ReadToEnd();
Console.WriteLine("Executing SQL {0}", sql);
Console.WriteLine("With params:");
foreach (var parameter in parameters)
{
Console.WriteLine($"- {parameter.Key}: {parameter.Value}");
}
// TODO: execute SQL passing parameters
var records = new List<Dictionary<string, object>>
{
new Dictionary<string, object>
{
{ "Name", "nom 1" },
{ "Age", 32 }
},
new Dictionary<string, object>
{
{ "Name", "nom 2" },
{ "Age", 35 }
}
};
return records.Select(builder);
}
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment