Last active
June 28, 2024 13:13
-
-
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 contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- this file Embedded resource | |
SELECT * FROM "Sellers" | |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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