Skip to content

Instantly share code, notes, and snippets.

@rflechner

rflechner/GetSellers.sql

Last active June 28, 2024 13:13
Show Gist options
  • Save rflechner/fab685187f10b8eb9815c6af1f874d3d to your computer and use it in GitHub Desktop.
Save rflechner/fab685187f10b8eb9815c6af1f874d3d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. rflechner revised this gist Apr 14, 2017. 1 changed file with 31 additions and 6 deletions.
    37 changes: 31 additions & 6 deletions Program.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -18,6 +18,11 @@ static void Main(string[] args)
    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);
    }
    }
    @@ -30,6 +35,17 @@ public class SellerModel

    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
    @@ -42,16 +58,19 @@ public IList<SellerModel> GetSellers()

    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)
    public IEnumerable<T> ExecuteView<T>(Func<Dictionary<string, object>, T> builder, object args = null)
    {
    StackTrace stackTrace = new StackTrace();
    var parameters = ToDictionary(args);
    var stackTrace = new StackTrace();
    var frame = stackTrace.GetFrame(1);
    MethodBase method = frame.GetMethod();


    var method = frame.GetMethod();
    var assembly = method.DeclaringType.Assembly;

    using (var stream = assembly.GetManifestResourceStream($"{method.DeclaringType.Namespace}.{method.Name}.sql"))
    @@ -60,7 +79,13 @@ public IEnumerable<T> ExecuteView<T>(Func<Dictionary<string, object>, T> builder
    {
    var sql = reader.ReadToEnd();
    Console.WriteLine("Executing SQL {0}", sql);
    // TODO: execute 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>
  2. rflechner created this gist Apr 14, 2017.
    4 changes: 4 additions & 0 deletions GetSellers.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,4 @@

    -- this file Embedded resource
    SELECT * FROM "Sellers"

    83 changes: 83 additions & 0 deletions Program.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,83 @@
    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.");
    }

    Console.ReadKey(true);
    }
    }

    public class SellerModel
    {
    public string Name { get; set; }
    public int Age { get; set; }
    }

    public class SellerStatsRepository: RepositoryBase
    {
    public IList<SellerModel> GetSellers()
    {
    return ExecuteView(record => new SellerModel
    {
    Name = (string) record["Name"],
    Age = (int) record["Age"]
    }).ToList().AsReadOnly();
    }
    }

    public abstract class RepositoryBase
    {
    [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)
    {
    StackTrace stackTrace = new StackTrace();
    var frame = stackTrace.GetFrame(1);
    MethodBase 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);
    // TODO: execute SQL
    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);
    }
    }
    }
    }
    }