DOT NET developer blog: January 2020

ASP.NET core In memory cache

Scenario:

Create a Sign on service to allow creation of users in our app. The web App calls this service to get all users. Cache the data into memory and serve the next request from cache till it expires.

Solution:

Implement ASP.NET core MemoryCache.

VS 2019 web project -> Add CacheKeys.cs

namespace Web.Services
{
    public static class CacheKeys
    {
        public static string Users { get { return "_allUsers"; } }
        public static string CallbackMessage { get { return "_callbackMessage"; } }
    }
}

Add ICache.cs

namespace Web.Services
{
    public interface ICache<TValue>
    {
        bool TrySet(string key, TValue value);

        bool TryGet(string key, out TValue value);

        void Remove(string key);

        void RemoveAll();
    }
}

Add ICacheService.cs

namespace Web.Services
{
    public interface ICacheService
    {
        ICache<TValue> GetCache<TValue>(string name);
    }
}

Add InMemoryCacheService.cs

using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Primitives;
using System;
using System.Collections.Generic;
using System.Threading;

namespace Web.Services
{
    //InMemory cache
    public class InMemoryCacheService : ICacheService
    {
        private static readonly Dictionary<string, object> _repo;
        private readonly ReaderWriterLockSlim Lock = new ReaderWriterLockSlim();

        static InMemoryCacheService()
        {
            //create cache repo
            _repo = new Dictionary<string, object>();
        }
        public ICache<TValue> GetCache<TValue>(string name)
        {
            object cache = null;

            Lock.EnterUpgradeableReadLock();
            try
            {
                Lock.EnterWriteLock();
                if (!_repo.TryGetValue(name, out cache))
                {
                    cache = new InMemoryCache<TValue>();
                    _repo[name] = cache;
                }
            }
            finally
            {
                Lock.ExitWriteLock();
                Lock.ExitUpgradeableReadLock();
            }

            return (ICache<TValue>)cache;
        }
    }

    public class InMemoryCache<TValue> : ICache<TValue>
    {
        private static IMemoryCache _cache = new MemoryCache(new MemoryCacheOptions());
        private readonly ReaderWriterLockSlim _lock = new ReaderWriterLockSlim();

        static InMemoryCache()
        {
        }

        public void Remove(string key)
        {
            _lock.EnterWriteLock();
            try
            {
                _cache.Remove(key);
            }
            finally
            {
                _lock.ExitWriteLock();
            }
        }

        public bool TryGet(string key, out TValue value)
        {
            _lock.EnterUpgradeableReadLock();
            try
            {
                if (_cache.TryGetValue(key, out value))
                {
                    return true;
                }
            }
            catch (Exception)
            {
                value = default(TValue);
            }
            finally
            {
                _lock.ExitUpgradeableReadLock();
            }

            return false;
        }

        public bool TrySet(string key, TValue value)
        {
            _lock.EnterWriteLock();

            if (!_cache.TryGetValue(key, out var result))
            {
                try
                {
                    var cts = new CancellationTokenSource(TimeSpan.FromSeconds(10));
                    var options = new MemoryCacheEntryOptions().
                        AddExpirationToken(new CancellationChangeToken(cts.Token)).
                        SetSlidingExpiration(TimeSpan.FromSeconds(10)).
                        //SetAbsoluteExpiration(TimeSpan.FromSeconds(60)).
                        SetPriority(CacheItemPriority.NeverRemove).
                        RegisterPostEvictionCallback(callback: EvictionCallBack, state: this);
                        //.SetSize(1024);

                    _cache.Set(key, value, options);
                    return true;
                }
                catch (Exception e)
                {
                    return false;
                }
                finally
                {
                    _lock.ExitWriteLock();
                }
            }

            return true;
        }

        public void RemoveAll()
        {
            _lock.EnterWriteLock();
            try
            {
                _cache.Dispose();
            }
            catch (Exception)
            {
            }
            finally
            {
                _lock.ExitWriteLock();
            }
        }

        private static void EvictionCallBack(object key, object value, EvictionReason evictionReason, object state)
        {
            var message = $"Entry {key} was evicted. Reason: {evictionReason}.";
            try
            {
                var options = new MemoryCacheEntryOptions();
                       //.SetSize(1024);
                _cache.Set(CacheKeys.CallbackMessage, message, options);
            }
        }
    }
}

In Startup.cs

 public void ConfigureServices(IServiceCollection services)
        {
            services.AddSingleton<ICacheService, InMemoryCacheService>();
            services.AddSingleton<ICache<object>, InMemoryCache<object>>();
        }

Add HomeController.cs

 public class HomeController : Controller
    {
private ILogger _logger;
private readonly ICache<List<string>> _userCache;
 public HomeController(ILoggerFactory loggerFactory, SignOn.SignOnClient signOnClient, ICacheService cacheService, IMemoryCache memoryCache)
        {
_logger = loggerFactory.CreateLogger<InMemoryCacheService>();
            _userCache = cacheService.GetCache<List<string>>("cache_users");
            _cache = memoryCache;
}
private async Task<Tuple<bool, List<string>>> GetUsers()
        {
            var userlist = new List<string>();
            string name = string.Empty;

            var cachekey = CacheKeys.Users;

            _usersLock.EnterReadLock();

            try
            {
                if (_userCache.TryGet(cachekey, out userlist))
                {
                    return new Tuple<bool, List<string>>(true, userlist);
                }

                if (_userCache.TryGet(CacheKeys.CallbackMessage, out var message))
                {
                    _logger.LogDebug(message.First());
                }
            }
            finally
            {
                _usersLock.ExitReadLock();
            }

            try
            {
                _usersLock.EnterWriteLock();

                using (var call = _signOnClient.GetAllUsers(new UserRequest()))
                {
                    userlist = new List<string>();
                    while (await call.ResponseStream.MoveNext())
                    {
                        var user = call.ResponseStream.Current;
                        userlist.Add(user.Name);
                    }
                }

                if (_userCache.TrySet(cachekey,userlist))
                {
                    return new Tuple<bool, List<string>>(true, userlist);
                }

                return new Tuple<bool, List<string>>(false, null);
            }
            catch (Exception)
            {
                return new Tuple<bool, List<string>>(false, null);
            }
            finally
            {
                _usersLock.ExitWriteLock();
            }
        }
 try
            {
                if (!all)
                {
                    _userCache.Remove(CacheKeys.Users);
                }
                else
                {
                    _userCache.RemoveAll();
                }
            }
            catch (Exception e)
            {
                throw e;
            }

            return View();
        }
    }

Grpc use clientfactory to register gRPC clients in .NET core

Scenario:

Create a Sign on service to return list of users in out system. Register the gRPC client in the .NET core web app.

Solution:

Add nuget package Grpc.Net.ClientFactory

In startup.cs, ConfigureServices method, register the service as below:


1
2
3
4
 public void ConfigureServices(IServiceCollection services)
        {
           services.AddGrpcClient<SignOn.SignOnClient>(options => { options.Address = new Uri(ConfigurationManager.AppSetting["gRPC:url"]); });
        }

In controller invoke the client as below:


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 private readonly SignOn.SignOnClient _signOnClient;

        public HomeController(ILogger<HomeController> logger, SignOn.SignOnClient signOnClient)
        {
            _logger = logger;
            _signOnClient = signOnClient;
        }

..
..

 using (var call = _signOnClient.GetAllUsers(new UserRequest()))
            {
...

Grpc Server - Server streaming RPC - Create a SignOn service to return list of users

Scenario:

Create a Sign on service to return list of users in out system. The web App calls this service to fetch list of users one by one and render on UI.

Solution:

Create Server streaming Grpc service for sign on in .Net core 3.0 with GetAllUsers method.

per gRPC documentation

A server-streaming RPC is similar to our simple example, except the server sends back a stream of responses after getting the client’s request message.The client completes once it has all the server’s responses.

VS 2019 -> New Project -> gRPC Service -> GrpcServer
Create Service definition [proto file], name signon.proto and define the service as below

syntax = "proto3";

option csharp_namespace = "GrpcServer";

service SignOn {
    rpc GetAllUsers (UserRequest) returns (stream User);
}

message UserRequest {

}

message User {
  string email = 1;
  string name = 2;
}

Right click on the proto file and make sure below properties are correctly set

gRPC Stub Class
Server only

Protobuf 
Class access = Public

Override GetAllUsers in SignOnService.cs


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
public override async Task GetAllUsers(UserRequest request, IServerStreamWriter<User> responseStream, ServerCallContext context)
        {
            var response = new SignOnResponse();
            response.Sucess = false;

            var json = JsonConvert.SerializeObject(request);
            var elasticSearch = new ElasticSearchService();
            var esResponse = elasticSearch.SearchData(json, "ELK:indexes:userIndex");

            var users = new List<User>();

            if (!string.IsNullOrWhiteSpace(esResponse))
            {
                response.Sucess = true;
                var rootHits = (JObject)JObject.Parse(esResponse)["hits"];
                var hits = (JArray)rootHits["hits"];

                for (int i = 0; i < hits.Count; i++)
                {
                    if (hits[i]["_source"]["Email"] != null)
                    {
                        users.Add(new User
                        {
                            Email = hits[i]["_source"]["Email"] == null ? "" : hits[i]["_source"]["Email"].ToString(),
                            Name = hits[i]["_source"]["Name"] == null ? "" : hits[i]["_source"]["Name"].ToString(),
                        });
                    }
                }
            }

            //return back to client one user at a time
            foreach (var user in users)
            {
                await responseStream.WriteAsync(user);
            }
        }

Create web project - VS 2019 -> New Project -> Web -> MyApp
Add below nuget packages

1
2
3

Google.Protobuff
Grp.Net.Client
Grpc.Tools

Add Proto folder to project -> copy file signon.proto from server to here
Invoke the service from client as below

public async Task<IActionResult> Index()
        {

            var users = new List<string>();
var channel = GrpcChannel.ForAddress(grpServerUrl);
            var client = new SignOn.SignOnClient(channel);

            using (var call = client.GetAllUsers(new UserRequest()))
            {
                while (await call.ResponseStream.MoveNext())
                {
                    var user = call.ResponseStream.Current;
                    users.Add(user.Name);
                }
            }
            ViewBag.success = true;

            return View();
        }

Grpc Intercept request at server side to capt

Scenario:

Sign on service is a Unary Grpc server on in .Net core 3.0 which allows creation of users in our app. Server should start logging api context and artifacts data to ELK for logging and references.

Solution:

Use Grpc service interceptor to intercept the api calls and log the required data.

VS 2019 -> Grpc server Project -> add ServicesInterceptor.cs
Add below code in interceptor code


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
public class ServicesInterceptor: Interceptor
    {
        private readonly ILogger<ServicesInterceptor> _logger;
        private readonly IElasticSearch _elasticSearch;

        public ServicesInterceptor(ILogger<ServicesInterceptor> logger, IElasticSearch elasticSearch)
        {
            _logger = logger;
            _elasticSearch = elasticSearch;
        }

        public override async Task<TResponse> UnaryServerHandler<TRequest, TResponse>(TRequest request, ServerCallContext context, UnaryServerMethod<TRequest, TResponse> continuation)
        {
            var sw = Stopwatch.StartNew();

            var response = await base.UnaryServerHandler(request, context, continuation);

            LogCall<TRequest, TResponse>(MethodType.Unary, context, JsonConvert.SerializeObject(request), JsonConvert.SerializeObject(response), sw.Elapsed.TotalMilliseconds);

            sw.Stop();

            return response;
        }

        private void LogCall<TRequest, TResponse>(MethodType methodType, ServerCallContext context, string request, string response, double duration) where TRequest: class where TResponse: class
        {
            var esApiLog = new GrpcElasticApiLogModel
            {
                RequestType  = typeof(TRequest).Name,
                MethodType = methodType.ToString(),
                Request = request,
                Response = response,
                Duration = duration,
                Server = context.Host
            };

            var json = JsonConvert.SerializeObject(esApiLog);

            _elasticSearch.PostData(json, "ELK:indexes:webApiIndex");
        }
    }

In startup.cs under in ConfigureServices method register the interceptor and elastic service


1
2
3
4
5
6
7
8
9
public void ConfigureServices(IServiceCollection services)
        {
            services.AddGrpc(options =>
            {
                options.Interceptors.Add<ServicesInterceptor>();
            });

            services.AddSingleton<IElasticSearch, ElasticSearchService>();
        }

Output:

Log entry in ELK

{
        "_index" : "log-webapi",
        "_type" : "documents",
        "_id" : "4BrQ2G8Bf2bSL1EXGEgL",
        "_score" : 1.0,
        "_source" : {
          "MethodType" : "Unary",
          "RequestType" : "SignOnRequest",
          "Request" : """{"Email":"test@test.com","Name":"Test User","Password":"=dsdsdrt99"}""",
          "Response" : """{"Sucess":true,"Name":"Test User"}""",
          "Duration" : 3237.8186,
          "Server" : "localhost:5001"
        }

Sql Server - Find column/tables by column/table name

Scenario:

Find all column/tables which has user in it.

Query:

In each database run below query

SELECT t.[Name] [Table Name],
       c.[Name] [Column Name],
       *
FROM   sys.columns c
       JOIN sys.tables t
         ON c.object_id = t.object_id
WHERE  c.[Name] LIKE '%user%'
ORDER  BY t.[Name],
          c.[Name]

Grpc Server - Unary RPC - Create a SignOn service to create an user

Scenario:

Create a Sign on service to allow creation of users in our app. The web App calls this service to create the user and based on response update the UI.

Solution:

Create Unary Grpc service for sign on in .Net core 3.0.

per gRPC documentation

Unary RPCs where the client sends a single request to the server and gets a single response back, just like a normal function call.

VS 2019 -> New Project -> gRPC Service -> GrpcServer

namespace Web.Services
{
    public static class CacheKeys
    {
        public static string Users { get { return "_allUsers"; } }
        public static string CallbackMessage { get { return "_callbackMessage"; } }
    }
}

Create Service definition [proto file], name signon.proto and define the service as below

syntax = "proto3";

option csharp_namespace = "GrpcServer";

service SignOn {
    rpc CreateNewUser (SignOnRequest) returns (SignOnResponse);
}

message SignOnRequest {
  string email = 1;
  string name = 2;
  string password = 3;
}

message SignOnResponse {
    bool sucess = 1;
    string name = 2;
}

Right click on the proto file and make sure below properties are correctly set

gRPC Stub Class
Server only

Protobuf 
Class access = Public

Override CreateNewUser in SignOnService.cs

  public class SignOnService : SignOn.SignOnBase
    {
        public override Task<SignOnResponse> CreateNewUser(SignOnRequest request, ServerCallContext context)
        {
            var response = new SignOnResponse();
            response.Sucess = false;

            var json = JsonConvert.SerializeObject(request);
            var elasticSearch = new ElasticSearchService();

            //Post data to ELK
            var success = elasticSearch.PostData(json, "ELK:indexes:userIndex");

            if (success)
            {
                response = new SignOnResponse
                {
                    Name = request.Name
                };
            }
        }

Create web project - VS 2019 -> New Project -> Web -> MyApp
Add below nuget packages

1
2
3

Google.Protobuff
Grp.Net.Client
Grpc.Tools

Add Proto folder to project -> copy file signon.proto from server to here
Invoke the service from client as below

 public IActionResult CreateNewUser(UserRequest userRequest)
        {
            try
            {
                var channel = GrpcChannel.ForAddress(grpServerUrl);
                var client = new SignOn.SignOnClient(channel);

                var response = client.CreateNewUser(new SignOnRequest
                {
                    Email = userRequest.Email,
                    Name = userRequest.Name,
                    Password = userRequest.Password
                });

                ViewBag.success = true;
            }
            catch (Exception e)
            {
                throw e;
            }

            return View();
        }

.NET core app config

Config file

Add custom appsettings.json like "ELKServerSetting.json" in project.

Config:

{
  "ELK": {
    "url": "http://localhost:9200",
    "indexes": {
      "userIndex": "user-log",
      "webApiIndex": "webapi-log"
    }
  }
}

Code to read config:

1
2
3

  using (var httpRequest = new HttpRequestMessage(HttpMethod.Post, $"{ConfigurationManager.AppSetting["ELK:url"]}/{ConfigurationManager.AppSetting[documentType]}/documents/"))
  {
  }

Generate report using API response

Scenario:

List [input .csv file] with user id and Salary API calls. Based on API response, calculate the total salary and append it as a new column to csv against each user id to generate a report.

Input:

User ID	Name	Salary API	Total Salary
1	Jerry	http://SalaryAPI/User/1
2	Terry	http://SalaryAPI/User/2

Solution:

Postman create new collection
Add requests API call and push to ELK
In API call -> Tests add below to read the API response and set variables

// convert xml to json
const xml2js = require('xml2js');
var jsonResponse = customXML2JSON(responseBody);

function customXML2JSON(string) {
    var JSON = {},
        options = {
            explicitArray: false,
            async: false,
            trim: false,
            mergeAttrs: false,
            valueProcessors: [parseNumbers, parseBooleans],
            attrValueProcessors: [parseNumbers, parseBooleans]
        };
    xml2js.parseString(string, options, function(err, result) {
       console.log(err);
       console.log(result);
       JSON = result; 
    });
    
    return JSON;
}

//You can log it to Console 
console.log(jsonResponse);
console.log(JSON.stringify(jsonResponse));
 

jsonResponse.MyResponse.Users.forEach(obj => {
 if (obj.Type == "Employee") {
  var TotalSalary = 0.0;
  if (Array.isArray(obj.Details.Detail)) {
   obj.Details.Detail.forEach(item => {
    TotalSalary = TotalSalary + item.Salary;
   });
  } else {
   TotalSalary = item.Salary;
  }
  
  //read header of xlsx UserId and append the TotalSalary to it
  console.log (pm.iterationData.get("UserId") + "," + TotalSalary);
  
  //set properties for next step
  pm.variables.set("UserId",pm.iterationData.get("UserId"));
  pm.variables.set("Salary",TotalSalary);
 }
})

In Save to Elk request ->

url -> http://elasticurl:9200/users-log/documents/body ->
Push to ELK. Include columns in the xslx as field here. Property is refereed in {{}}

{
 "UserID": "{{UserId}}",
 "url": "{{url}}",
 "TotalSalary": "{{totalSalary}}",
 "datetime" : "{{datetime}}",
 "response" : {{jsonResponse}}
}

Go to Kibana

Index Patterns -> Create Index pattern -> [Index name] -> Time Filter field name [datetime] -> Create Index Pattern
Discover -> change index = users-log -> Select fields you want -> save -> MyReport1
Share -> CSV Reports -> Generate CSV. This would queue the report.
Manage Spaces -> Reporting -> MyReport1 -> Actions [download]

Output:

User ID	Name	Salary API	Total Salary
1	Jerry	http://SalaryAPI/User/1	4000
2	Terry	http://SalaryAPI/User/2	5000

Sql Server - Find duplicates and return all but the original record

Scenario:

List Duplicates from UserLog by User but excluding the original record.

User
ID	Name
1	Terry
2	Jerry

User Log
ID	User ID	Created Date
1	1	01/21/2020
2	1	01/20/2020
3	1	01/19/2020
4	2	01/18/2020
5	2	01/17/2020

Query:

DECLARE @BeginDate DATETIME = '2019-01-10'
DECLARE @EndDate DATETIME = GETDATE()

SELECT ul.*
FROM   UserLog ul
       JOIN (SELECT UserLogID,
                    ROW_NUMBER()
                      OVER (
                        Partition BY u.UserID
                        ORDER BY u.CreatedDate ASC) rowId
             FROM   (SELECT UserID
                     FROM   UserLog u
                     WHERE  u.CreatedDate <= GETDATE()
                     GROUP  BY UserID
                     HAVING COUNT(DISTINCT UserID) > 1)dups) u2
         ON ul.UserID = u2.UserID
WHERE  ul.CreatedDate BETWEEN @BeginDate AND @EndDate
       AND u2.rowId <> 1

Output:

User ID	Data	Created Date
1	entry 3	01/21/2020
1	entry 2	01/20/2020
2	entry 2	01/18/2020

.NET Resource files

Scenario:

Based on locale or other variable select the value of a config.

Version
ID	Value
1	true
2	false

Steps:

New File -> Resource file [.resx]. It is key value pair file
Name Value

1 true

2 false

In your class add below code:

private readonly ResourceManager _resourceManager = new ResourceManager(
            "MyProject.MyApp",
            Assembly.GetExecutingAssembly());

...
...

var _versionQuery = _resourceManager.GetString(ConfigurationManager.AppSettings["version"]);

Output:

Config [version]	Return Value
1	true
2	false

SQL Server STUFF() function to flatten a table column

Scenario:

List Employees with their the roles.

Role
ID	Name
1	Developer
2	Manager

Employee
ID	Name
1	Terry
2	Jerry

Employee Role Xref
Employee ID	RoleID
1	1
1	2
2	1

Query:

SELECT e.EmployeeID,
       (SELECT STUFF((SELECT ',' + Cast([name] AS VARCHAR(MAX))
                      FROM   Roles r
       JOIN [EmployeeRoleXref] erx ON erx.EmployeeID = e.EmployeeID AND  r.RoelID = erx.RoelID
                      FOR XML PATH('')), 1, 1, ''))
FROM   [Employee] e

Output:

Name	Roles
Terry	Developer, Manager
Jerry	Manager