Rick Drizin

Short-circuitable Pipelines

Rick Drizin — Thu, 01 Aug 2024 00:39:17 GMT

Intro

I’ve been working in a multitenant application where some services need to know what’s the current tenant for each request. It would be possible to identify the tenant in upper layers (controllers) and pass that information all the way down to the services, but that generates a lot of repetitive/polluted code. A natural idea is to think about using Dependency Injection to pass this information around.

Injecting Runtime Data into Services

Immutable data (like connection strings or any other configuration data that is used during app initializatio) are things that certainly need to be injected, and are NOT considered runtime data.

Runtime data is information that may change across requests, like current logged user, or current tenant (organization).

Using dependency-injection to inject runtime data into your services is a common but controversial practice. The argument in favor of injecting runtime info is that it can improve code readability/maintenance by avoiding having to add a parameter all the way up the chain (in all direct and indirect consumers).

This is an example of how we can inject runtime data into our services:

// Registration
services.AddTransient<ClaimsPrincipal>(s =>
    s.GetService<IHttpContextAccessor>().HttpContext.User);

// Usage
public class MyService
{
    public MyService(ClaimsPrincipal user)
    {
        // now you have user.Identity.Name;
    }
}

In the example aboveClaimsPrincipal is calculated by ASP.NET Identity middleware (it’s a cheap call based solely on cookies) and it’s saved into HttpContext.User. So whoever calls this service can just invoke it without having to pass the current logged user.

Using the same idea it would be possible to add to the pipeline a middleware to resolve the current tenant and make it available in HttpContext:

app.Use(async (context, next) =>
{
    var context = context.RequestServices
        .GetRequiredService<IHttpContextAccessor>().HttpContext;
    var tenant = await ResolveTenantAsync(context);
    context.Items["Tenant"] = tenant;
    await next.Invoke();
});

// Registration
services.AddTransient<ITenantContext>(s =>
    (ITenantContext) s.GetService<IHttpContextAccessor>().HttpContext.Items["Tenant"]);

The first problem here is that this approach forces all requests in the pipeline to resolve a Tenant (which might be expensive), so unless you really need it in all requests (or unless you can decide based on the request if you need it) then I think it’s a better idea to just rely on DI and let the container resolve your tenant only when required (and cache it), instead of resolving in the request pipeline.

The other problem here is that your runtime data have a short lifetime and you don’t want to inject that by mistake into objects with a longer lifetime (like singletons). In other words, if MyService is a singleton then it would be storing internally the identity of the user making the first request, and all subsequent requests (from other users) would be associated to the wrong user, so you will have to correctly (re)define the lifetime of your components.

So injecting runtime data is a bad idea?

Many developers believe that you should not inject runtime data to services. This article explains that components should ideally be stateless, and that runtime data should not contain behavior and should be passed across methods (instead of injected into constructors).

But then the same article explains that passing runtime data across a chain of callers can pollute the code, and shows examples of components that can benefit from having runtime data injected into it (Repository and more specifically Unit of Work).

Injecting runtime data is especially helpful for lower layers (like data-access) because the lowest you go the more layers you would have to modify just to pass repetitive/boilerplate data (all the way up to the layer where the runtime data can be obtained).

To sum we want a solution where we don’t need to pass repetitive runtime data through a bunch of layers but where we don’t have to directly inject transient runtime data as it would affect the lifetime of our components.

Adapters

Those problems can be solved writing an adapter to resolve your runtime data (e.g. identify your tenant). The lifetime mismatch is now handled in a single place:

public class AspNetTenantAccessor : ITenantAccessor
{ 
  private IHttpContextAccessor _accessor;
  public AspNetTenantAccessor(IHttpContextAccessor accessor)
  {
    _accessor = accessor;
  }

  // or maybe Task<AppTenant> GetCurrentTenantAsync()
  public AppTenant CurrentTenant
  {
    get
    {
      // resolve (only when required) based on _accessor.HttpContext
      // cache it (per-request) in _acessor.HttpContext.Items["Tenant"]
    }
  }
}

// Registrations:
builder.Services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();
builder.Services.AddSingleton<ITenantAccessor, AspNetTenantAccessor>();

Now in your service layer you can just inject ITenantAccessor and get the current tenant (or any other runtime data), and we won’t have runtime dependency-resolution problems (we don’t need to test if all different consumers are correctly resolving the current tenant).

You may be wondering: Why can’t you just inject IHttpContextAccessor and get the current tenant directly from HttpContext.Items["Tenant"]? That’s because it would be a leaky abstraction - you don’t want your services to be tightly coupled to this Http abstraction (it would be harder to reuse them from other callers like unit tests).

So is that the “right way” of doing it? What does “Microsoft tells us to do”?

In 2015 David Fowler (from ASP.NET Core team) showed an example of how to Inject ClaimsPrincipal in Service Layer by using an IUserAccessor adapter (exactly like our example above):

public class UserAccessor : IUserAccessor
{ 
      private IHttpContextAccessor _accessor;
      public UserAccessor(IHttpContextAccessor accessor)
      {
           _accessor = accessor;
      }
      public ClaimsPrincipal User => _accessor. HttpContext.User;
}

That example alone might be interpreted as if injecting transient runtime data is just plain wrong, but then more recently in 2021 someone suggested that ClaimsPrincipal should be made available (as injectable) in ASP.NET Core Minimal APIs (avoiding dependence on IHttpContext(Accessor), and the same David Fowler accepted that suggestion and the PR that registers ClaimsPrincipal / HttpRequest / HttpResponse (they are all transient runtime data) - so this is a hint that the “don’t inject runtime info” rule is not set in stone, and might be just a design choice.

Configurable Pipeline

So now I have an ITenantAccessor interface, and I can switch between multiple implementations.

For ASP.NET I could have an accessor that would identify tenants by the hostname, for local (localhost) debugging or for tests I can use a different accessor that would fake my test tenant.

But then there was another requirement: tenants will be usually identified by their hostnames, but in some cases the same hostname will be shared among all tenants and they should be identified by the request path or by some request header or cookie. So I wanted a configurable pipeline where I can eventually add or remove some steps, and more importantly: I don’t need to run all steps - each step may short-circuit the next steps.

The Pipeline Design Pattern is a common design-pattern where we have a Pipeline with a list of steps and they are processed in order, usually one task builds upon the results of the previous task. One nice implementation of this pattern is the ASP.NET Core Request Pipeline, where each step creates (and runs) a Task that receive the next step as input and it can can short-circuit the request (skip the next steps) or it can proceed to the next step in the pipeline. I was familiar with using this request pipeline (e.g. adding middlewares) but I hadn’t had a chance to look how it works internally, so I decided to make something similar, which led me to this StackOveflow answer that led me to the source of Microsoft.AspNetCore.Builder.ApplicationBuilder.Build()

Basically this is how it works:

Each step in the pipeline is a Task, but they are registered as a factory Func that get the next Task (if any) and create the step Task
Since those Func factories always get the next pipeline step (the next Task) each Task can decide to run the next step or skip it (short-circuit).
The Build() method starts by creating the Task for the last step, which is a fallback step to check if things went fine or if there is a misconfigured pipeline. Then it goes in reverse order through the previous pipeline steps, chaining each step with the subsequent step. The last Task created in the loop is actually the first step where the pipeline starts, and that’s what’s returned by Build().
Tasks from ASP.NET request pipeline don’t return anything and besides the next Task they also get HttpContext as input.
In my case I don’t get any input but each step can return a Tenant.

Based on that code I’ve created a quick implementation to resolve tenants using a generic/configurable pipeline:

public class PipelineTenantResolver<TTenant> : ITenantAccessor
{
    // Steps from my pipeline don't get anything, 
    // and may short-circuit to return a TTenant
    public delegate Task<TTenant> IPipelineTenantRepositoryDelegate();

    // Steps from ASP.NET pipeline (for comparison) get HttpContext 
    // but don't return anything:
    //public delegate Task RequestDelegate(HttpContext context)

    protected List<Func<IPipelineTenantRepositoryDelegate,
        IPipelineTenantRepositoryDelegate>> _steps = new();

    // After Build() we cache pipeline first step
    protected IPipelineTenantRepositoryDelegate? _built;

    public void Use(Func<IPipelineTenantRepositoryDelegate,
        IPipelineTenantRepositoryDelegate> step)
    {
        _steps.Add(step);
        _built = null;
    }

    protected IPipelineTenantRepositoryDelegate Build()
    {
        // We start building pipeline with last step
        IPipelineTenantRepositoryDelegate app = () =>
        {
            // If we reach the end of the pipeline then no Tenant was found
            throw new TenantNotFoundException();

            // A non-throwing alternative would be:
            //return Task.FromResult(default(TTenant)!);
        };

        // Then we go back passing the next step to each previous step
        for (var c = _steps.Count - 1; c >= 0; c--)
            app = _steps[c](app);

        _built = app;
        return app;
    }

    public async Task<TTenant?> GetTenantAsync() => await (_built ?? Build()).Invoke();
}

Sample test:

var pipelineResolver = new PipelineTenantResolver<AppTenant>();

// First step
Task<AppTenant> resolveTenant = Task.FromResult(new AppTenant());
pipelineResolver.Use((next) => {
    if (shouldShortCircuit)
        return () => resolveTenant;
    return next;
});
// next steps...
//pipelineResolver.Use(next => next); // etc..
// Last step (defined in Build()) will just throw

var tenant = await pipelineResolver.GetTenantAsync();

Finally, in my services registration I register different implementations for ITenantResolver depending on the entry-point (web app, web api, unit tests, etc), and Task<AppTenant> is registered with a factory that depends on ITenantResolver.ResolveAsync():

builder.Services.AddTransient<Task<AppTenant>>(sp => sp.GetRequiredService<ITenantAccessor>().ResolveAsync());

PS: all code above assumes that resolving a tenant is an async call, that’s why I’ve registered a Task<> (if the code wasn’t async it would be even easier). If you want to inject directly the resolved AppTenant (instead of a Task<AppTenant>) that would be a blocking call (not recommended in constructors) and mixing sync with async (not recommended). In Autofac that’s possible (registering a factory that uses async code), I’m not sure if that’s also possible in Microsoft.Extensions.DependencyInjection - but anyway - blocking calls during injection/constructors is not recommended so it’s best to inject Task<AppTenant> instead.

It’s been a while since I don’t write anything, so I hope you enjoyed this article.

WhatsApp Recovery Tools for Android

Rick Drizin — Mon, 11 Jul 2022 23:18:03 GMT

A few days ago my wife just deleted by mistake some old videos from our daughter (when she was like saying her first words) which were only stored in her WhatsApp. I don’t even have to mention how those videos were important to us (she wasn’t following my advice about how to backup her media) - so I had to help recover the videos.

Since WhatsApp stores backups in Google Drive I thought I could just uninstall and reinstall and it would get the backups. That WOULD be easy, but her WhatsApp was still using an old phone number from Brazil which wasn’t even here with us in America (yeah, I know - she just didn’t want to change the number that all her family had, etc). Since we wouldn’t be able to reactivate WhatsApp using that old number the first thing I made was updating her number, to allow me to restore from the Google Drive backup of previous week.

After we updated her WhatsApp number, I disabled backups or else I would be overwriting the backup of the previous week and would lose the videos she was looking for.

So after uninstalling and reinstalling the app, I’ve learned the hard way that WhatsApp backups (both the messages and the media) which were taken using one phone number cannot be restored to a new number. So basically I lost not only all her messages but also all her media.

So then I found this great tool which allows us to Extract (download) the full WhatsApp backup from Google Drive: https://github.com/YuriCosta/WhatsApp-GD-Extractor-Multithread.
Basically you get your device id, then you fill it together with your Google username/password, and the script will download all pictures/videos and even the encrypted backup of the chats (msgstore.db.crypt14).

Then to restore her chat history back we had to change her number again back to the old number in Brazil, and ask her family in Brazil to get the activation code for us, so we could restore from the Google Drive Backup.

In the end of the day, I didn’t even had to dump the Google Drive backup, but at least I learned something new that can be useful.

And also, while trying to recover her messages, I learned about this other whapa toolkit which contains tools to encrypt or decrypt WhatsApp datastore (requires private key), merge two data stores (merge chat histories from different phones, also requires private key), as well as extracting media from Google Drive (I haven’t tested) or iCloud. If you’re trying to decrypt new format (15) you may need this other tool.

And last: if you need to extract the private key of your WhatsApp and your Android is not rooted, you can use this tool - https://github.com/YuvrajRaghuvanshiS/WhatsApp-Key-Database-Extractor (I had to use python wa_kdbe.py --allow-reboot) which basically will install an old build of WhatsApp and exploit it to dump the private key using adb.

Fun learning anyway.

Using Multiple Return Values instead of Exceptions

Rick Drizin — Sat, 02 Jan 2021 21:28:34 GMT

Exceptions vs Error Codes

A question that frequently arises in software engineering is whether/when you should throw exceptions or return error codes.

There are many different opinions about this subject, and there’s a large number of C#/Java developers who throw Exceptions to indicate all kinds of errors. However, there’s also a large number of developers who believe that Exceptions should not be used as control flow and argue that exceptions act like non-local GOTO statements but even more evil because it’s difficult to know where the control goes to (some people compare exceptions to invisible gotos or gotos which lead to invisible labels).

I tend to agree with the second group: I think exceptions are for unexpected and unhandleable situations - when there’s nothing I can do with the error code, and the only expected action is to bubble-up the error in the stack and let the upper levels handle it (to display the error, maybe offer a retry mechanism). This means that for all kinds of “expected errors” I expect them to be returned by my methods, and treated (or intentionally ignored) by the caller method.

GO Language: Panic vs Errors, and Explicit Error Checking

When talking about Exceptions vs Error Codes I usually mention the GO language which has some clear guidelines about error handling.

One of their design principles is that they have “panic” for fatal unexpected situations (which is pretty much like Exceptions in Java/C#) and they also have “Errors” (any object that you return which implements Error interface) which should be used for regular expected situations. This is pretty much like the distinction which I explained above about when I like to use exceptions and when I like to use error codes.

This means that the language encourages you to explicitly check for errors where they occur, as opposed to the paradigm that expects you to throw (and catch) exceptions even for expected errors. Since the language has this clear distinction between exceptions and return errors, it has some conventions and constructs for error handling that allows developers to easily (and concisely) get and test errors.

Basically, all functions where errors are expected to happen should always return an error object, and if the function is also expected to return some other object (in case of success) then it should use multiple return values so that it can simultaneously return both the expected result objects (at the first position) and then return the possible errors (at the last position), like this:

file, err := os.Open("filename.txt")
if err != nil {
    // abort the program...
}
fmt.Print(string(file))

This multiple return values feature makes the code more concise and it’s now also available in C# 7. And by receiving the error as a regular return value our error handling becomes less verbose, more explicit, and easier to read since it uses regular if/else statements.

Another nice convention is that when returning multiple parameters the return should always be ONE OR ANOTHER, meaning that if there’s an error you can expect that the other object is null, and vice-versa. A simple convention that makes error handling even easier.

Last, another major benefit of explicitly returning error codes is that exceptions can easily be ignored while it’s much harder to ignore errors if your methods force you to receive the returned error. Returning codes obviously don’t allow us to bubble-up automatically (as exceptions do), but the idea is exactly that - you want to check (and act) on your errors right where they occur.

Returning Errors as Enums instead of Classes

As I’ve explained above, errors are about regular expected situations where the caller code should know the possible results and should decide how to handle each possible error. If what you get is a class instance (either as a thrown Exception or as a regular returned object) you really don’t know what kind of errors you might receive.

The first problem about using exceptions is that the caller code (or compiler) doesn’t know what exceptions each method may throw. Java has checked exceptions but all other languages learned that checked exceptions are evil and don’t even have that alternative. Obviously you could describe in your documentation instructions like “This CreateUser() method may throw an exception of CreateUserException”, and then the caller code would know what to expect, but what’s the point if you could just define what your method returns in the return type?

The second problem about returning classes (either as exceptions or return objects) is that developers usually design the different errors as subtypes (subtype polymorphism), and the caller code (or compiler) can’t know/test for all possible errors.

try
{
    // CreateUserCommand xmldoc will warn me that it may throw a CreateUserCommandException
    User createdUser = CreateUserCommand(newUserInfo);
}
catch (CreateUserCommandException)
{
    // How many possibilities do we have here?
    // Which subtypes should we check?
}

One alternative for this second problem is adding an enum CreateUserCommandErrorEnum inside CreateUserCommandException. Then we could use if/switch statements, and the compiler can check if we cover all possible errors. But that looks too complex (both an exception and an enum for each possible method) and without any benefit - in the end it’s all about Enums, since there are a predefined number of possible outcomes.

To sum, I prefer returning enums directly (instead of throwing exceptions or returning error classes) because:

The caller code is forced to receive the error, less prone to ignore the possibility of an error
The caller code will know in advance all possible errors
We can treat the possible errors with switch statements and we can easily ensure that all possible returns are handled.
If I add a new possible return in my method, I can even check all callers if they are covering that new value in a switch statement for example.
I believe that we should explicitly test for expected errors right where they occur.

Given the reasons above, for the rest of this post I’ll assume that your methods are returning errors as enum, like this one:

public enum CreateUserCommandError
{
    USERNAME_NOT_AVAILABLE,
    WEAK_PASSWORD,
}

In the next sections I’ll show a few different ways of returning multiple parameters in C#.

Using OUT parameters

Using out parameters is as simple as this:

public User CreateUserCommand(UserDTO newUserInfo, out CreateUserCommandError? error)
{
    if (somethingBad)
    {
        error = CreateUserCommandError.USERNAME_NOT_AVAILABLE;
        return null;
    }
    // ...
    error = null; // out parameters need to be assigned even if null
    return user;
}

CreateUserCommandError? error = null;
User createdUser = CreateUserCommand(newUserInfo, out error);
if (error != null)
{
    // early abort..
}
LoginUser(createdUser);

In the new C# 7 we don’t even have to define the out variables anymore, we can use implicitly typed local variable (out var):

User createdUser = CreateUserCommand(newUserInfo, out var error);

Using regular Tuples

Using Tuples is a little more verbose, but returns all parameters in a single Tuple object:

public Tuple<User, CreateUserCommandError?> CreateUserCommand(UserDTO newUserInfo)
{
    if (somethingBad)
        return new Tuple<User, CreateUserCommandError?>(null, CreateUserCommandError.USERNAME_NOT_AVAILABLE);
    // ...
    return new Tuple<User, CreateUserCommandError?>(user, null);
}

var result = CreateUserCommand(newUserInfo);
if (result.Item2 != null) // Item2 is the Error
{
    // early abort..
}
LoginUser(result.Item1); // Item1 is the User returned

Using the new ValueTuple

In the new C# 7 there’s this new ValueTuple struct, where we can give more meaningful names to the tuple members, and we can also use a simplified syntax both for creating new ValueTuple and for deconstructing the ValueTuple:

public (User createdUser, CreateUserCommandError? error) CreateUserCommand(UserDTO newUserInfo)
{
    if (somethingBad)
        return (null, CreateUserCommandError.USERNAME_NOT_AVAILABLE);
    // ...
    return (user, null);
}

var result = CreateUserCommand(newUserInfo);
// The names "error" and "createdUser" come directly from the method signature above
if (result.error != null)
{
    // early abort..
}
LoginUser(result.createdUser);

We can deconstruct the ValueTuple in a single statement which can both declare the variables and assign values to them:

var (user, error) = CreateUserCommand(newUserInfo);
// or: (var user, var error) = CreateUserCommand(newUserInfo);
// or: (User user, CreateUserCommandError? error) = CreateUserCommand(newUserInfo);

if (error != null) // Error
{
    // early abort..
}
LoginUser(user);

Using Generics

Another popular method is to Wrap your returns in a generic class which wraps both your return object and the possible error (actually it should return ONE OR ANOTHER, but not both).

public class CommandResult<TEntity, TError>
        where TEntity : class
        where TError : struct, Enum
{
    public TEntity Entity { get; set; }
    public TError? Error { get; set; }
    public bool IsSuccess => (Error == null);

    // Many developers also include a "Message" property
    // which usually can be both the success message or the error description
    // public string Message { get; set; }

    public static CommandResult<TEntity, TError> Success(TEntity entity)
    {
        return new CommandResult<TEntity, TError>() { Entity = entity };
    }

    public static CommandResult<TEntity, TError> Fail(TError errorCode)
    {
        return new CommandResult<TEntity, TError>() { Error = errorCode };
    }
}

public CommandResult<User, CreateUserCommandError> CreateUserCommand(UserDTO newUserInfo)
{
    if (somethingBad)
        return CommandResult<User, CreateUserCommandError>.Fail(CreateUserCommandError.USERNAME_NOT_AVAILABLE);
    // ...
    return CommandResult<User, CreateUserCommandError>.Success(user);
}

var result = CreateUserCommand(newUserInfo);
if (result.Error != null) // Error
{
    // early abort..
}
LoginUser(result.Entity);

Using generics is more verbose than the ValueTuple syntax but it’s much more powerful since we can enhance the class with extra information. Later I’ll show how to enhance this class with more information.

But this verbose syntax makes me a little annoyed, so let’s try to get the best of both solutions…

Combining Generics with the concise ValueTuple Syntax

There are a few tricks that we can use to make the Generics version more friendly and less verbose.

First, inside the CommandResult<> class we can create an implicit conversion operator that will convert (at the compiler level) a ValueTuple to a CommandResult<>:

public static implicit operator CommandResult<TEntity, TError>(ValueTuple<TEntity, TError?> tuple)
{
    if (tuple.Item1 != null && tuple.Item2 == null)
        return Success(tuple.Item1);
    if (tuple.Item1 == null && tuple.Item2 != null)
        return Fail(tuple.Item2.Value);
    throw new NotImplementedException("When error is returned you cannot return any other value together");
}

Then we can return our results as if they were ValueTuples:

//return CommandResult<User, CreateUserCommandError>.Fail(CreateUserCommandError.USERNAME_NOT_AVAILABLE);
return (null, CreateUserCommandError.USERNAME_NOT_AVAILABLE);

//return CommandResult<User, CreateUserCommandError>.Success(user);
return (user, null);

Inside the CommandResult<> class we can also create a deconstruct method so that CommandResult<> can be deconstructed into its two parts:

public void Deconstruct(out TEntity entity, out TError error) => (entity, error) = (this.Entity, this.Error);

This mean that we can deconstruct (declare the variables deconstruct the different parts) in a single call like this:

var (user, error) = CreateUserCommand(newUserInfo);
if (error != null) // Error
{
    // early abort..
}
LoginUser(user);

So cool and so easy, isn’t it?

Enhancing the Error enum

In all previous examples the returned error was only an enum. In the last example above (Generics with concise syntax) the enum was part of the Generic class:

public class CommandResult<TEntity, TError>
        where TEntity : class
        where TError : struct, Enum
{
    public TEntity Entity { get; set; }
    public TError? Error { get; set; }
    public ErrorResult<TError> Error { get; set; }
    public bool IsSuccess => (Error == null);
    // ...
}

But we can enhance the error object (TError?) with more information by wrapping in inside another class:

public class ErrorResult<TError>
    where TError : struct, Enum
{
    /// <summary>
    /// This MAY or may not be defined (even if an error happened!).
    /// If this is null, you should check the <see cref="ValidationErrors"/> to see why the command failed.
    /// </summary>
    public TError? ErrorCode { get; set; }
    public string ErrorMessage { get; set; }
    public IList<ValidationError> ValidationErrors;
}
public class ValidationError
{
    public string PropertyName { get; set; }
    public string ErrorMessage { get; set; }
}

public class CommandResult<TEntity, TError>
        where TEntity : class
        where TError : struct, Enum
{
    public TEntity Entity { get; set; }
    public ErrorResult<TError> Error { get; set; } // THIS!
    public bool IsSuccess => (Error == null);
    // ...
}

This design gives us a few advantages over a simple enum:

We can add a descriptive string ErrorMessage
We can add a list of ValidationErrors which can be helpful to display in the UI
We don’t need to define enums for all possible situations - the caller code can just assume that if CommandResult.Error is non-null then some error happened, and it can use the ErrorMessage and/or ValidationErrors. It only needs to test the TError? ErrorResult.ErrorCode if it needs to handle specific cases - else it can just have this “general” error handling.
ValidationErrors could be filled automatically. In my next post, I’ll show how this can be done using MediatR pipeline (behaviors) to check for validation errors using FluentValidation and automatically fill CommandResult.ValidationErrors, without even hitting our Command Handlers.

Well, at the beginning of this post I wrote that I would return simple enums, and in the end I’m returning classes. But basically, this is just a thin wrapper to enhance the enum with additional features - but it doesn’t invalidate the downsides that I’ve mentioned earlier (about type polymorphism uncertainty, and about how throwing exceptions is bad when you need to handle the outcomes).

Last, this wrapper around ErrorCode enum shouldn’t stop us from doing direct comparisons. We can still compare the error with the enum values as long as we overload the equality operators like this:

public static bool operator ==(ErrorResult<TError> left, TError right)
{
    return left.ErrorCode != null && left.ErrorCode.Value.Equals(right)
}

var (user, error) = CreateUserCommand(newUserInfo);
if (error == CreateUserCommandError.USERNAME_NOT_AVAILABLE) { ... }
else if (error == CreateUserCommandError.WEAK_PASSWORD) { ... }
else if (error == null)
{
   // ...
}

You can find full source code here.

Troubleshooting High CPU in IIS Application Pool

Rick Drizin — Sun, 08 Nov 2020 21:09:34 GMT

My client had this issue where their web application (deployed across multiple servers) was randomly making the servers unresponsive with 100% cpu usage.

The first action we took was to configure the IIS to automatically recycle the Application Pools when they are using high CPU for more than a few minutes. In the example below we kill AppPools after 3 minutes of using more than 80% cpu.

dir IIS:\AppPools  | ForEach-Object {
	Write-Host  "Updating $($_.Name) ..."

	$appPoolName = $_.Name
	$appPool = Get-Item "IIS:\AppPools\$appPoolName"
	$appPool.cpu.limit = 80000
	$appPool.cpu.action = "KillW3wp"
	$appPool.cpu.resetInterval = "00:03:00"
	$appPool | Set-Item
}

That solved the problems, servers stopped getting unresponsive, but we had to investigate what was eating all CPU.

See below how I proceeded with the troubleshooting:

1. Create a Memory Dump

Task Manager - Right Button in the IIS Worker Process, and create a Dump File

2. Install Debug Diagnostic Tool

Download and install Debug Diagnostic Tool

3. Run Crash & Hang Analysis for ASP.NET / IIS

Add your dump (DMP) file, select “CrashHangAnalysis”, and click “Start Analysis”.

4. Review Analysis for Problems

The first page immediately suggests that there’s a Generic Dictionary which is being used by multiple threads and is blocking one thread.

A few pages later we can find the threads which are consuming the most of the CPU:

If we check those top threads we can see that both are blocked in the same call which is invoking GetVersion() on an API client-wrapper. One thread is trying to Insert on the dictionary (cache the API version), while the other is trying to Find (FindEntry) on the dictionary.

5. What was the issue?

Long Explanation:
Dictionary<T> is a HashMap implementation, and like most HashMap implementations it internally uses LinkedLists (to store multiple elements in case different keys result into the same bucket position after being hashed and after taking the hash modulo). The problem is that since Dictionary<T> is not thread-safe, multiple threads trying to change the dictionary may put it into an invalid state (race condition).

Probably there were different threads trying to add the same element to the dictionary at the same time (invoking Insert method which internally invokes the Resize method which modifies the LinkedList), which was putting the LinkedList (and therefore the whole HashMap) into an inconsistent state. If the LinkedList goes into an inconsistent state it can put the threads into an infinite loop, since both Insert() and FindEntry() iterate through the LinkedList and could go into an infinite loop if the LinkedList was inconsistent.

Short Explanation:
Since Dictionary<T> is not thread-safe, multiple threads trying to change the dictionary may put it into an invalid state (race condition). So if you want to share a dictionary across multiple threads you should use a ConcurrentDictionary<T> which as the name implies is a thread-safe class.

It’s a known-issue that concurrent access to Dictionary can cause an infinite-loop and high-CPU in IIS: Link 1, Link 2, Link 3, Link 4.

6. Advanced Troubleshooting using WinDbg

If the Debug Diagnostic Tool didn’t gave us any obvious clue about the root cause, we could use WinDbg to inspect a memory dump (it also supports .NET/CLR). See example here.

Install Redis Server on a Windows Server using WSL

Rick Drizin — Sat, 26 Sep 2020 21:37:20 GMT

Windows Subsystem for Linux (WSL)

If you’re a Windows developer that needs to compile or run Linux binaries in Windows, then the Windows Subsystem for Linux (WSL) is for you. WSL is a tool for developers and sysadmins that need Linux interoperability in Windows.

The most important thing to understand about WSL is that it isn’t based on virtual machines or containers - when you download a supported Linux distro from the Microsoft Store, it doesn’t mean that a virtual machine is created. What WSL provides is just a layer for mapping Windows kernel system calls to Linux kernel system calls - this allows Linux binaries to run in Windows unmodified. WSL also maps Windows services, like the filesystem and networking, as devices that Linux can access.

Instead of using a VM or container, WSL virtualizes a Linux kernel interface on top of the Windows kernel. This means that running WSL only requires a minimal amount of RAM. And when not in use, the WSL driver isn’t loaded into memory, making it much more efficient than a solution based on a VM or container.

Installing WSL and Ubuntu in a Windows Server

Run this in a Powershell Administrator Prompt (you’ll have to reboot after this):

Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux

Run this in a Powershell Administrator Prompt to install Ubuntu 18.04:

curl.exe -L -o ubuntu-1804.appx https://aka.ms/wsl-ubuntu-1804
Rename-Item ubuntu-1804.appx ubuntu-1804.zip
Expand-Archive ubuntu-1804.zip ubuntu1804

cd ubuntu1804
.\ubuntu1804.exe

You’ll be asked to choose a Linux username and password.

Installing Redis on Ubuntu (under Windows Server WSL)

Invoke a bash shell as superuser (for the next commands which require root):

sudo bash

(you’ll be asked for administrator’s password created earlier)

Update apt-get and install redis:

apt-get update && apt-get upgrade
apt-get install redis-server

Configuring Redis for external access (Optional)

If you’ll only use Redis in your internal protected network you don’t need this.

WSL only provides a translation layer between the Linux apps and the Windows kernel, so some core parts of the Ubuntu system (including networking) are just not there - WSL just translates the Linux system calls into windows ones so the Ubuntu network data flows through the exact same TCP/IP stack as the windows data.

This means that to open Redis server to other servers (or to the public internet) you just have to configure Redis to listen on the correct interfaces, and open the appropriate ports (there’s no need to do “port forwarding” since this is not a VM with its own networking interfaces).

By default Redis will only bind to loopback (localhost) interfaces. If you open /etc/redis/redis.conf (by running nano /etc/redis/redis.conf) you’ll find a line like bind 127.0.0.1 ::1 which means that Redis by default listens on ipv4 loopback (127.0.0.1) and ipv6 loopback (::1). Just change it to bind 0.0.0.0 ::1 to make Redis listen in all interfaces (including public IPs), or if it’s just for internal network you can add the internal ips in which this server should listen on.

And in case you’re exposing it through the internet you’ll also have to open your Windows Firewall ports:

netsh advfirewall firewall add rule name="Redis Port 6379" dir=in action=allow protocol=TCP localport=6379

Authentication

If you’re exposing your server to public internet you’ll have to configure a password, because by default Redis does not accept external connections without a password.
Yet in /etc/redis/redis.conf you just have to uncomment the line requirepass and set next to it a very-strong password (since it’s very fast to run a brute-force attack on a Redis server).

Start and test server

Start service:

sudo service redis-server restart

To test the service, run redis-cli and use the following commands:

AUTH yourstrongpassword  (if you have enabled authentication, above)
set user:1 "Oscar"
get user:1

To stop your server you can use:

sudo service redis-server stop

If you want to put your Windows Server to automatically start this Linux service on every boot you can configure this command to be executed on every boot:

C:\windows\system32\wsl.exe -u root service redis-server start

References:

String Interpolation and Dynamic Query Builder for Dapper

Rick Drizin — Thu, 06 Aug 2020 23:01:05 GMT

We all love Dapper micro-ORM.

Many developers have realized that although Entity Framework offers some features that Dapper doesn’t, Dapper is still more flexible (and much much faster) than EF. And many have realized that the right tool for the right job sometimes involving using both EF for some things and Dapper for others. And one of the usages where Dapper is the king is for building dynamic queries.

Dynamic SQL

Old-school developers (those who have been coding since the late 90s or early 2000s, probably in VB6, ASP3, or PHP) will probably remember code like this:

string sql = "SELECT * FROM [Product] WHERE 1=1";

if (!string.IsNullOrEmpty(Request["ProductName"]))
   sql += " AND Name LIKE '" + Request["ProductName"].Replace("'", "''") + "'"; // this replace!
if (!string.IsNullOrEmpty(Request["SubCategoryId"]))
    sql += " AND ProductSubcategoryID = " + Request["SubCategoryId"].Replace("'", "''");  // this replace!

// here is where you pray that you've correctly sanitized inputs against sql-injection
var products = cn.Query<Product>(sql);

Basically we were all sending dynamic SQL statements directly to our databases. And since user-input can be malicious (see below), we had to manually sanitize user-input to avoid SQL-Injection attacks.

Hello Bobby Tables

If you weren’t sanitizing your input correctly, you were at serious risk. SQL-injection is probably the most popular web-hacking technique. If you forgot to sanitize something like an “id” querystring, you could be opening a huge vulnerability and possibly exposing all your database, not only for reading but also for modifications.

Parameterized SQL

Even if you’re old school (hey VB6 guy, I’m looking at you again) you probably know that Dynamically building SQL like that is not only error-prone (vulnerable to SQL-injection) but also does not benefit from SQL Server Cached Execution Plans. You probably used some kind of code-generation tool which helped you to build code like this (maybe to invoke a stored procedure, since 3-tier-architecture and “Windows DNA” were in the hype in the early 2000s):

Dim strSQL As String
Dim cmd As New ADODB.Command

strSQL = "UPDATE MyTable SET " & vbNewLine _
    & " NEEDS_ID     = @NEEDS_ID, " & vbNewLine _
    & " OBJ_ID       = @OBJ_ID, " & vbNewLine _
    & " OBJ_COMMENTS = @OBJ_COMMENTS, " & vbNewLine _
    & " TIME21_ID    = @TIME21_ID, " & vbNewLine _
    & " WHERE ID     = @WHEREID"

With cmd
    .ActiveConnection = Cn
    .CommandText = strSQL
    .Parameters.Append .CreateParameter("@NEEDS_ID", adInteger, adParamInput, 2, 12)
    .Parameters.Append .CreateParameter("@OBJ_ID", adInteger, adParamInput, 2, 23)
    .Parameters.Append .CreateParameter("@OBJ_COMMENTS", adVarChar, adParamInput, 250, "Some text")
    .Parameters.Append .CreateParameter("@TIME21_ID", adInteger, adParamInput, 2, 34)
    .Parameters.Append .CreateParameter("@ID", adInteger, adParamInput, 18, 456)
    .Execute
End With

I hope the millennial developers are not so surprised that parameterized SQL already existed in the past century.

Back to modern software

Time flew by, Java and .NET emerged (and maybe submerged a little?), Reflection, Bytecode emission, Full-ORMs, Micro-ORMs, 3-tier was deprecated in favor of dozens of modern architectures, and now database access is much easier, right?

Now we don’t have to manually describe each one of our parameters to SQL. Our favorite ORMs will do that for us.

Dapper Example:

var dynamicParams = new DynamicParameters();

string sql = "SELECT * FROM [Product] WHERE 1=1";

if (productName != null)
{
    sql += " AND Name LIKE @productName"; 
    dynamicParams.Add("productName", productName);
}


if (subCategoryId != null)
{
    sql += " AND ProductSubcategoryID = @subCategoryId"; 
    dynamicParams.Add("subCategoryId", subCategoryId);
}

var products = cn.Query<Product>(sql, dynamicParams);

Don’t Repeat Yourself

The example below makes me a little upset for 2 reasons:

I have to pass productName twice, both to the sql string and to the dynamicParams dictionary.
Their names should match.
I have to keep those two variables (the SQL statement and the list of parameters) separated, even though they are closely related to each other.

String Interpolation Internals

String Interpolation was introduced in C# in 2016.

Instead of doing code like this:

string name = "Rick";
int accesses = 10;
string output = string.Format("{0}, you've been here {1:n0} times.",
                              name, accesses);

You could do like this:

string name = "Rick";
int accesses = 10;
string output = $"{name}, you've been here {accesses:n0} times.";

Internally, when you write an interpolated string (starting with $), the compiler generates a FormattableString class, which contains both the template (as if it was {0}, you've been here {1:n0} times.), and also the list of parameters (string name and int accesses).

If your method expects a regular string, the FormattableString will be implicitly converted into a regular string, and you get the same behavior as if you just passed a string.format to your method. However, if your method expects a FormattableString class, then you have access to format and arguments isolated from each other.

FormattableString can be useful for example if we wanted to build parameterized SQL statements while letting users build their strings as if it was just regular string concatenation:

QueryBuilder ParseSQL(FormattableString query)
{
    QueryBuilder myWrapper = new QueryBuilder();

    string dapperSql = query.Format;
    // replace {0} by "@p0", 
    // replace {1} by "@p1", etc..
    myWrapper.SQL = dapperSql;

    var dapperArgs = new Dapper.DynamicParameters();
    var args = query.GetArguments();
    // dapperArgs.Add("p0", args[0]);
    // dapperArgs.Add("p1", args[1]); ...
    myWrapper.Arguments = dapperArgs;

    return myWrapper;
    // now anyone can use Dapper like  
    // var pocos = connection.Query<POCO>(myWrapper.SQL, myWrapper.Parameters);
}

DapperQueryBuilder

Based on the idea above, I’ve created DapperQueryBuilder - which is a simple wrapper around Dapper which allows us to pass SQL parameters using string interpolation.

You can code like this:

var query = cn.QueryBuilder($"SELECT * FROM [Product] WHERE 1=1");

if (productName != null)
    query.Append($"AND Name LIKE {productName}"); 

if (subCategoryId != null)
    query.Append($"AND ProductSubcategoryID = {subCategoryId}"); 

var products = query.Query<Product>();

Although it looks like you’re just building a dynamic SQL (with values as inline literals), actually what you get is parameterized SQL.

In case, query will have this underlying statement: SELECT * FROM [Product] WHERE 1=1 AND Name LIKE @p0 AND ProductSubcategoryId = @p1, and will also hold the parameters @p0 = productName and @p1 = subCategoryId.

To sum, instead of using Dapper’s extension .Query<T> which extends IDbConnection and accepts a SQL string and a list of parameters, you use QueryBuilder() extension which creates a QueryBuilder where you can dynamically (and in a single statement) add new parameters and add the associated SQL clause.

Quickstart

If you liked and want to start using right now,

Install the NuGet package Dapper-QueryBuilder
Start using like this:

using DapperQueryBuilder;
// ...

cn = new SqlConnection(connectionString);

// If you have all your parameters at once and just want to benefit from string interpolation:
var products = cn.QueryBuilder($@"
    SELECT ProductId, Name, ListPrice, Weight
    FROM [Product]
    WHERE [ListPrice] <= {maxPrice}
    AND [Weight] <= {maxWeight}
    AND [Name] LIKE {search}
    ORDER BY ProductId").Query<Product>();

Or building dynamic conditions like this:

using DapperQueryBuilder;
// ...

cn = new SqlConnection(connectionString);

// If you want to dynamically add conditions
var q = cn.QueryBuilder($@"
    SELECT ProductId, Name, ListPrice, Weight
    FROM [Product]
    WHERE 1=1 ");

q.AppendLine("AND [ListPrice] <= {maxPrice}");
q.AppendLine("AND [Weight] <= {maxWeight}");
q.AppendLine("AND [Name] LIKE {search}");
q.AppendLine("ORDER BY ProductId");

var products = q.Query<Product>();

Filters list

Some people believe incorrectly that WHERE 1=1 causes a performance hit. It doesn’t. So using this fake condition is a great trick so that you can just append additional conditions (if any) like shown in the example above.

Another option is that you can build your whole query but leave the filters to be dynamically defined (and rendered) later. DapperQueryBuilder has this special command Where() which is used to save the filters internally, and later they are replaced.

int maxPrice = 1000;
int maxWeight = 15;
string search = "%Mountain%";

var cn = new SqlConnection(connectionString);

// You can build the query manually and just use QueryBuilder to replace "where" filters (if any)
var q = cn.QueryBuilder(@"SELECT ProductId, Name, ListPrice, Weight
    FROM [Product]
    /**where**/
    ORDER BY ProductId
    ");
    
// You just pass the parameters as if it was an interpolated string, 
// and QueryBuilder will automatically convert them to Dapper parameters (injection-safe)
q.Where($"[ListPrice] <= {maxPrice}");
q.Where($"[Weight] <= {maxWeight}");
q.Where($"[Name] LIKE {search}");

// Query() will automatically build your query and replace your /**where**/ (if any filter was added)
var products = q.Query<Product>();

You can also create OR filters like this:

q.Where(new Filters(Filters.FiltersType.OR)
{
    new Filter($"[Weight] <= {maxWeight}"),
    new Filter($"[Name] LIKE {search}")
});

Or you can mix and match OR/AND like this:

q.Where(new Filters(Filters.FiltersType.OR)
{
    new Filters(Filters.FiltersType.AND)
    {
        $"[Weight] <= {maxWeight}",
        $"[Weight] >= {minWeight}",
    }
    new Filter($"[Name] LIKE {search}")
});

And even arrays can be used as interpolated strings:

var categories = new string[] { "Components", "Clothing", "Acessories" };
q.Append($"WHERE c.[Name] IN {categories}");

Fluent API (Chained-methods)

For those who like method-chaining guidance (or for those who allow end-users to build their own queries), there’s a Fluent API that allows you to build queries step-by-step mimicking dynamic SQL concatenation.

So, basically, instead of starting with a full query and just appending new filters (.Where()), the QueryBuilder will build the whole query for you:

var q = cn.QueryBuilder()
    .Select($"ProductId")
    .Select($"Name")
    .Select($"ListPrice")
    .Select($"Weight")
    .From($"[Product]")
    .Where($"[ListPrice] <= {maxPrice}")
    .Where($"[Weight] <= {maxWeight}")
    .Where($"[Name] LIKE {search}")
    .OrderBy($"ProductId");
    
var products = q.Query<Product>();

You would get this query:

SELECT ProductId, Name, ListPrice, Weight
FROM [Product]
WHERE [ListPrice] <= @p0 AND [Weight] <= @p1 AND [Name] LIKE @p2
ORDER BY ProductId

Hope you enjoyed this post as much as I did when I wrote this library!

Full source code here.

Code Generation in C# / CSX - Extracting SQL Server Schema

Rick Drizin — Sun, 05 Jul 2020 16:35:40 GMT

This is the second part of a multi-part post series I’m going to write about Code Generation using C#. In the previous part I’ve shown how to invoke C# Scripts (and CSX files) from PowerShell and Visual Studio. In this part, I’ll create a simple extractor to read the physical schema of a SQL Server Database and save it as a JSON file.

Creating the Project

The first step is creating a .NET project.

Running CSX scripts do not require csproj (or Visual Studio), but it’s very helpful to have a csproj for many reasons:

You get intellisense (mostly on CS, not so much on CSX - so try to use CSX as little as possible)
You can check for compilation-errors
You can configure a Console App (or any other entry point) to launch and debug your scripts
csproj is required for automatically restoring NuGet packages (although if you already have the NuGet packages you may just use them without csproj)

All libraries which I’ll use in my generator (Dapper and Newtonsoft) are compatible with netstandard2.0.
This means that the project type can be either .NET Framework (net472, which used to be called “full framework”) or .NET Core (netcore21 or netcore31) - both should work.

NuGet Packages

I’ll use two third-party libraries: Dapper (micro-ORM, for loading objects from SQL Queries) and Newtonsoft.Json (JSON library, just for serialization). All we need is their dlls, but the easiest way to getting those is by installing their NuGet packages to our csproj (which will automatically install those packages to anyone who tries to build our csproj, so it’s hassle-free collaboration among multiple developers).
If you’re not using Visual Studio IDE you can install those packages by running
dotnet add package Dapper and dotnet add package Newtonsoft.Json
in the same folder of your csproj.
If you’re using Visual Studio you can use the Package Manager Console and run this:

Install-Package Dapper
Install-Package Newtonsoft.Json

Sql Server Schema objects

Next, I’ll create my objects that will represent my physical SQL database. One important thing to remember is that C# scripting engines do NOT allow the use of namespaces.

SqlServerTable.cs:

using System;
using System.Collections.Generic;

public class SqlServerTable
{
    public string Database { get; set; }
    public string TableSchema { get; set; }
    public string TableName { get; set; }

    /// <summary>
    /// Can be "TABLE" or "VIEW"
    /// </summary>
    public string TableType { get; set; }

    public string TableDescription { get; set; }

    public List<SqlServerColumn> Columns { get; set; } = new List<SqlServerColumn>();

    /// <summary>
    /// FKs which point from THIS (Child) table to the primary key of OTHER (Parent) tables
    /// </summary>
    public List<SqlServerForeignKey> ForeignKeys { get; set; } = new List<SqlServerForeignKey>();

    /// <summary>
    /// FKs which point from OTHER (Child) tables to the primary key of THIS (Parent) table
    /// </summary>
    public List<SqlServerForeignKey> ChildForeignKeys { get; set; } = new List<SqlServerForeignKey>();

}

I’ll omit other classes for brevity, but you can refer to all code here (classes SqlServerColumn.cs, SqlServerForeignKey.cs, SqlServerForeignKeyMember.cs ).

Schema Reader

Finally, I’ll create the class which will read SQL metadata - SqlServerSchemaReader.cs:

using Dapper;
using System;
using System.Data;
using System.IO;
using System.Linq;

public class SqlServerSchemaReader
{
  public Func<IDbConnection> CreateDbConnection { get; set; }

  public SqlServerSchemaReader(Func<IDbConnection> createDbConnection)
  {
    CreateDbConnection = createDbConnection;
  }

  public void ExportSchemaToJSON(string outputJsonSchema)
  {
    Console.WriteLine("Reading Database...");

    using (var cn = CreateDbConnection())
    {
      var tables = cn.Query<SqlServerTable>(@"
        SELECT 
          t.TABLE_CATALOG as [Database], 
          t.TABLE_SCHEMA as [TableSchema], 
          t.TABLE_NAME as [TableName], 
          CASE WHEN t.TABLE_TYPE='VIEW' THEN 'VIEW' ELSE 'TABLE' END as [TableType],
          ep.value as [TableDescription]
		    FROM  INFORMATION_SCHEMA.TABLES t
		    INNER JOIN sys.schemas sc ON t.TABLE_SCHEMA = sc.[name]
          ... full code omitted for brevity - please refer to: 
          ... https://github.com/Drizin/CodegenCS/tree/master/src/CodegenCS.SqlServer
      ").AsList();

      var allColumns = cn.Query<SqlServerColumn>(@"
          ... full code omitted for brevity... 
      ").AsList();

      var fks = cn.Query<SqlServerForeignKey>(@"
          ... full code omitted for brevity... 
      ").AsList();

      var fkCols = cn.Query<SqlServerForeignKeyMember>(@"
          ... full code omitted for brevity... 
      ").AsList();
      foreach (var fk in fks)
      {
        fk.Columns = fkCols.Where(c => 
            c.ForeignKeyConstraintName == fk.ForeignKeyConstraintName && 
            c.FKTableSchema == fk.FKTableSchema
        ).OrderBy(c => c.PKColumnOrdinalPosition).ToList();
      }

      foreach (var table in tables)
      {
        table.Columns = allColumns.Where(c => c.TableSchema == table.TableSchema && c.TableName == table.TableName).ToList();
        foreach(var column in table.Columns)
          column.ClrType = GetClrType(table, column);
        table.Columns.ForEach(c => { c.Database = null; c.TableSchema = null; c.TableName = null; });

        // We copy FKs and remove redundant properties of the parent object (table) which we're attaching this FK into
        table.ForeignKeys = Clone(fks.Where(fk => fk.FKTableSchema == table.TableSchema && fk.FKTableName == table.TableName).ToList());
        table.ForeignKeys.ForEach(fk => { fk.FKTableSchema = null; fk.FKTableName = null; });

        // We copy FKs and remove redundant properties of the parent object (table) which we're attaching this FK into
        table.ChildForeignKeys = Clone(fks.Where(fk => fk.PKTableSchema == table.TableSchema && fk.PKTableName == table.TableName).ToList());
        table.ChildForeignKeys.ForEach(fk => { fk.PKTableSchema = null; fk.PKTableName = null; });

      }

      SqlServerDatabaseSchema schema = new SqlServerDatabaseSchema()
      {
        LastRefreshed = DateTimeOffset.Now,
        Tables = tables,
      };

      Console.WriteLine($"Saving into {outputJsonSchema}...");
      File.WriteAllText(outputJsonSchema, Newtonsoft.Json.JsonConvert.SerializeObject(schema, Newtonsoft.Json.Formatting.Indented));
    }

    Console.WriteLine("Success!");
  }

  string GetClrType(SqlServerTable table, SqlServerColumn column)
  {
    string sqlDataType = column.SqlDataType;
    switch (sqlDataType)
    {
      case "bigint":
        return typeof(long).FullName;
      case "smallint":
        return typeof(short).FullName;
      case "int":
        return typeof(int).FullName;
      case "uniqueidentifier":
        return typeof(Guid).FullName;
      case "smalldatetime":
      case "datetime":
      case "datetime2":
      case "date":
      case "time":
        return typeof(DateTime).FullName;
      case "datetimeoffset":
        return typeof(DateTimeOffset).FullName;
      case "float":
        return typeof(double).FullName;
      case "real":
        return typeof(float).FullName;
      case "numeric":
      case "smallmoney":
      case "decimal":
      case "money":
        return typeof(decimal).FullName;
      case "tinyint":
        return typeof(byte).FullName;
      case "bit":
        return typeof(bool).FullName;
      case "image":
      case "binary":
      case "varbinary":
      case "timestamp":
        return typeof(byte[]).FullName;
      case "nvarchar":
      case "varchar":
      case "nchar":
      case "char":
      case "text":
      case "ntext":
      case "xml":
        return typeof(string).FullName;
      default:
        Console.WriteLine($"Unknown sqlDataType for {table.TableName}.{column.ColumnName}: {sqlDataType}");
        return null;

      // Vendor-specific types
      case "hierarchyid":
        return "Microsoft.SqlServer.Types.SqlHierarchyId"; // requires Microsoft.SqlServer.Types.dll (EF or Dapper 1.34+)
      case "geography":
        return "Microsoft.SqlServer.Types.SqlGeography";  // requires Microsoft.SqlServer.Types.dll (EF or Dapper 1.32+)
      case "geometry":
        return "Microsoft.SqlServer.Types.SqlGeometry";  // requires Microsoft.SqlServer.Types.dll (EF or Dapper 1.33)+
    }
  }

  public static T Clone<T>(T source)
  {
    var serialized = JsonConvert.SerializeObject(source);
    return JsonConvert.DeserializeObject<T>(serialized);
  }

}

CSX

The idea is that we should put as much as possible into .cs files (and as little as possible in csx script), since intellisense and compile-time checks work better than in the CSX. The script file RefreshDatabaseSchema.csx will basically load libraries, define connection strings and paths, and invoke SqlServerSchemaReader.cs:

/// <summary>
/// This CSX Script will invoke SqlServerSchemaReader, which extracts the schema of SQL database and saves into a JSON file.
/// The easiest way to launch csi.exe (which is shipped with Visual Studio) to run this script is by using PowerShell script RefreshDatabaseSchema.ps1
/// You can do that from Visual Studio (see instructions in RefreshDatabaseSchema.ps1) or you can just execute "Powershell RefreshDatabaseSchema.ps1"
/// </summary>

// System libraries
#r "System.Data.dll"

// Load 3rd-party libraries by their relative paths, relative to "$Env:userprofile\.nuget\packages\"
#r "dapper\2.0.35\lib\netstandard2.0\Dapper.dll"
#r "newtonsoft.json\12.0.3\lib\netstandard2.0\Newtonsoft.Json.dll"

// CS files are better than CSX because Intellisense and Compile-time checks works better. 
#load "SqlServerTable.cs"
#load "SqlServerColumn.cs"
#load "SqlServerForeignKey.cs"
#load "SqlServerForeignKeyMember.cs"
#load "SqlServerDatabaseSchema.cs"
#load "SqlServerSchemaReader.cs"

using System;
using System.IO;
using System.Runtime.CompilerServices;
using System.Data;
using System.Data.SqlClient;

// Helpers to get the location of the current CSX script
public static string GetScriptPath([CallerFilePath] string path = null) => path;
public static string GetScriptFolder([CallerFilePath] string path = null) => Path.GetDirectoryName(path);


// location relative to the CSX script
string outputJsonSchema = Path.GetFullPath(Path.Combine(GetScriptFolder(), "AdventureWorksSchema.json")); 
string connectionString = @"Data Source=MYWORKSTATION\SQLEXPRESS;
                            Initial Catalog=AdventureWorks;
                            Integrated Security=True;";

Func<IDbConnection> connectionFactory = () => new SqlConnection(connectionString);
var reader = new SqlServerSchemaReader(connectionFactory);
reader.ExportSchemaToJSON(outputJsonSchema);

Powershell

Last, I’ll create a PowerShell to invoke the CSX file, which is useful because it can locate the csi.exe in multiple locations and because it can provide to csi.exe the location of per-user NuGet packages, so that CSX can load libraries by their relative-locations, without having to hard-code user-specific folders. RefreshDatabaseSchema.ps1:

# To Execute Powershell Scripts from Visual Studio:
# 1) Right-button PS1 file - "Open With...""
# 2) Configure:
#      Program: Powershell.exe
#      Arguments: -noexit -File %1
#      Friendly Name: Execute PowerShell Script

# To execute CSX scripts you'll need CSI.EXE (C# REPL) which is shipped with Visual Studio
# but can also be installed by using the NuGet package Microsoft.Net.Compilers.Toolset - https://www.nuget.org/packages/Microsoft.Net.Compilers.Toolset/

# For more info about launching CSX scripts from PowerShell or from Visual Studio, check https://drizin.io/code-generation-csx-scripts-part1/

$dir = Split-Path $MyInvocation.MyCommand.Path 
$script = Join-Path $dir ".\RefreshDatabaseSchema.csx"


# Locate CSI.EXE by searching common paths
$csi = ( 
    "$Env:userprofile\.nuget\packages\microsoft.net.compilers.toolset\3.6.0\tasks\net472\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2019\Professional\MSBuild\Current\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2019\Community\MSBuild\Current\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\15.0\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Roslyn\csi.exe"
) | Where-Object { Test-Path $_ } | Select-Object -first 1

if (!$csi)
{
    Write-Host "---------------------------------------" -for red
    Write-Host "Can't find csi.exe" -for red
    Write-Host "Please fix search paths above, or install NuGet Microsoft.Net.Compilers.Toolset" -for red
    Write-Host "---------------------------------------" -for red
    Exit 1
}


$stopwatch = [System.Diagnostics.Stopwatch]::StartNew()

Write-host "Starting csi.exe $script ..." -for yellow
& $csi /lib:"$Env:userprofile\.nuget\packages\" $script

Write-Host "Finished in $($stopwatch.Elapsed.TotalMilliSeconds) milliseconds"

# Since I configured "-noexit" parameter in Visual Studio I don't need this
#if ($host.Name -notmatch 'ISE') { Write-Host -NoNewLine "(Just press Enter to exit)" -for cyan; read-host; }

Running Powershell:

Result file AdventureWorksSchema.json:

Full Source code available here

This is the second part of a multi-part post series I’m going to write about Code Generation using C#:

Part 1 - Code Generation in Pure C# (and CSX scripts) directly from inside Visual Studio
Part 2 - Extracting SQL Server Schema

Code Generation in Pure C# (and CSX scripts) directly from inside Visual Studio

Rick Drizin — Fri, 03 Jul 2020 02:48:29 GMT

This is the first part of a multi-part post series I’m going to write about Code Generation using C#.

I’ve mentioned before that I’ve been a fan of code generation for a long-time. I have used CodeSmith generator, MyGeneration Code Generator, and in the past few years, I’ve been using T4 templates.

The main advantage of code generation (which includes DB scaffolding, also called database first by some ORMs) is that it does the work for you, is repeatable, and less error-prone than manually writing everything. So it’s mostly about productivity and consistency. If you’re writing repetitive code by hand, you’re stealing from your employer or clients. And if you have a data-oriented application, almost certainly you have repetitive code to some degree.

The right tool for the job

Until recently the gold standard for code generation in Microsoft stack was using T4 templates, which is the out-of-the-box tool provided by Microsoft and shipped with Visual Studio. The major problem with T4 is that it has terrible syntax, terrible tooling and debugging support, and can get as ugly as this:

C# Script Files (CSX files)

In the search of a modern code-generation tool I’ve explored Razor Templates, Mustache, and Mustache-like templates (like DotLiquid and Handlebars), and others. I’ve noticed that there’s a growing popularity of using C# code (Roslyn or not) to do the code-generation using pure C# - which makes sense, since you can get strong typing, compile-time checking, full IDE support (with debugging), cross-platform (dotnet core), full access to all .NET Framework (SqlServer, Dapper, Newtonsoft JSON, etc). So you get a full-fledged language instead of using a templating-engine which only offers a subset of features of the underlying language.

C# Script Files (CSX) were introduced with Roslyn, and can be executed in Roslyn or in other compatible cross-platform scripting engines like dotnet-script or even with C# REPL called csi.exe. Those scripting engines certainly have some limitations (like using namespaces), but they allow us to virtually invoke any C# code, with essential features like loading external assemblies, loading code organized across multiple source files, etc. And it’s much easier than using Powershell to invoke C#.

Sample CSX Script

CSX scripts inside Visual Studio have some support for Intellisense (auto-completion) and compile-time checks, but those features work much better in CS files. So it’s a good idea to put as much as possible into cs files and as little as possible in CSX scripts. I like to use CSX only for basic things like loading libraries, setting connection strings, settings paths, and invoking the real code in CS files.

MyProgram.cs:

public class MyProgram
{
   public void MyMethod()
   {
      Console.WriteLine("Hello from MyMethod");
   }  
}

MyScript.csx:

#load "MyProgram.cs" 

new MyProgram().MyMethod(); 
Console.WriteLine("Hello Code-Generation!");

Running CSX Script using C# REPL (CSI.EXE)

Visual Studio ships with a command line REPL called CSI that can be used to run .csx scripts.

You can run CSI.EXE directly from Visual Studio Developer Command Prompt (csi MyScript.csx):

Assembly References

In the same sense that it’s a good idea to use simple statements in CSX to invoke more complex CS code, it’s also a good idea to load external assemblies when you can rely on existing libraries.

CSX allows loading assembly references by using the #r directive in the top of your scripts:

// CSI.EXE requires absolute paths for loading external assemblies: 
#r "C:\Users\drizin\.nuget\packages\dapper\2.0.35\lib\netstandard2.0\Dapper.dll" 

#load "File1.cs" 
#load "File2.cs" 
#load "MyProgram.cs" 

new MyProgram().MyMethod(); 
Console.WriteLine("Hello Code-Generation!");

NuGet Packages

If you need to reference a NuGet package, you can just rely on NuGet tools (and Visual Studio build process) to automatically restore the packages required by your script. For achieving that, you can just add the CSX as part of a Visual Studio project, so when each developer tries to build the project Visual Studio will download the missing packages, and the developer just needs to fix the assemblies location.

Another alternative, which does not require to use csproj at all, is using PowerShell to download the required nugets (see script at the end of this post).

Invoking C# REPL (CSI running CSX scripts) from PowerShell

Although you can run CSI.exe directly from Visual Studio Developer Command Prompt, invoking it through PowerShell is very helpful for a few reasons:

You can run outside of Visual Studio. You don’t even need Visual Studio to run CSX.
Allows us to find NuGet locations and reference external assemblies with relative paths on the CSX (more about this below).
Restoring NuGet files (example script at the end of the post)

To invoke CSI using Powershell, we must know the location of csi.exe.

CSI is shipped with Visual Studio but can also be installed by using the NuGet package Microsoft.Net.Compilers.Toolset (warning: as described here the installation script for the package changes .csproj file to use the compiler provided by the package - this is likely not what you want, so make sure to roll-back such changes if they are made during installation).

So the first step is to search for csi.exe in multiple locations as I show in the sample Powershell script RunMyScript.ps1 below:

# Locate CSI.EXE by searching common paths
$csi = ( 
    "$Env:userprofile\.nuget\packages\microsoft.net.compilers.toolset\3.6.0\tasks\net472\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2019\Professional\MSBuild\Current\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2019\Community\MSBuild\Current\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\15.0\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Roslyn\csi.exe"
) | Where-Object { Test-Path $_ } | Select-Object -first 1

$dir = Split-Path $MyInvocation.MyCommand.Path 
$script = Join-Path $dir "MyScript.csx"

& $csi $script

To launch the PowerShell script from the command line, it’s just about running Powershell Full-Path-To-Your-Script-ps1.

Running from Visual Studio IDE

To run from Visual Studio, you can just add the PS1 to your project or solution, right-click the file, and click the option “Open with PowerShell ISE”, which is the IDE for editing/running PowerShell scripts.

Another alternative is that you can add new actions to your right-button actions - you can click “Open With…” and configure PowerShell to be executed directly from Visual Studio:

The list of possible actions will include this new option of invoking PS1 scripts directly from the IDE, and you can also set this as the default action for opening PS1 files.

Allowing Unsigned Scripts

If you have never executed unsigned PowerShell scripts you’ll have to enable PowerShell unsigned scripts by running Powershell as Administrator and running this command:
Set-ExecutionPolicy -ExecutionPolicy Unrestricted.
Don’t forget to enable for both Windows PowerShell (64-bits) and for Windows PowerShell (x86), which is the one that is invoked from inside Visual Studio IDE.

Relative Assembly References

As we’ve seen before, CSX accepts absolute references like this:

#r "C:\Users\drizin\.nuget\packages\dapper\2.0.35\lib\netstandard2.0\Dapper.dll"

One of the major problems with CSI is that the #r directive (for loading assembly references) doesn’t accept nuget-like references or environment variables so all assembly references should be specified with full paths. This is not a showstopper but it’s a little annoying since it makes it harder to share code among multiple developers since each developer would have to fix their references.

One of the advantages of using PowerShell (as described above) is that we can use environment-variables and use #r directive with relative paths. In the PowerShell script, we just have to locate the base path where your assemblies are located and pass that to CSI so it can use this folder to search for referenced assemblies, like this:

$assemblies = "${env:userprofile}\.nuget\packages\";
& $csi /lib:"$assemblies" $script

And then in the CSX, you can use relative paths like this:

#r "dapper\2.0.35\lib\netstandard2.0\Dapper.dll"

PackageReference (NuGet 4) vs packages.config (NuGet 3)

The new MSBuild format (“SDK-Style”, which uses PackageReference inside the csproj) installs the NuGet packages in this per-user folder.

The old MSBuild format (“non-SDK-Style”, before Visual Studio 2017, which uses packages.config) installs the NuGet packages in the “packages” folder under the Solution folder.

We can adjust our PowerShell scripts according to where our project will restore NuGet packages:

$csi = ... # (locate your csi.exe)
$dir = Split-Path $MyInvocation.MyCommand.Path 
$script = Join-Path $dir "MyScript.csx"

# Call csi.exe and specify that libraries referenced by #r directives 
# should search in a few nuget locations

# New NuGet 4.0+ (PackageReference) saves User-specific packages
# in "%userprofile%\.nuget\packages\"
$nuget1 = "${env:userprofile}\.nuget\packages\";

# New NuGet 4.0+ (PackageReference) saves Machine-wide packages 
# in "%ProgramFiles(x86)%\Microsoft SDKs\NuGetPackages\"
$nuget2 = "${env:ProgramFiles(x86)}\Microsoft SDKs\NuGetPackages\";

# Old NuGet (packages.config) saves packages in "\packages" folder at solution level.
# Locate by searching a few levels above
$nuget3 = ( 
    (Join-Path $dir ".\packages\"),
    (Join-Path $dir "..\packages\"),
    (Join-Path $dir "..\..\packages\"),
    (Join-Path $dir "..\..\..\packages\"),
    (Join-Path $dir "..\..\..\..\packages\")
) | Where-Object { Test-Path $_ } | Select-Object -first 1

# if you're using new NuGet format (PackageReference defined inside csproj) 
& $csi /lib:"$nuget1" $script  

# if you're using old NuGet format (packages.config)
# & $csi /lib:"$nuget3" $script

And our CSX would use relative references:

// CSX can load libraries by defining their relative paths

// New NuGets (PackageReference) are installed under "${env:userprofile}\.nuget\packages\" 
// or "${env:ProgramFiles(x86)}\Microsoft SDKs\NuGetPackages\")
// and have this format:
#r "dapper\2.0.35\lib\netstandard2.0\Dapper.dll"

// Old NuGets (packages.config) are installed under "(SolutionFolder)\packages"
// and have this format
// #r "Dapper.2.0.35\lib\netstandard2.0\Dapper.dll"

//...
new MyProgram().MyMethod();
Console.WriteLine("Hello Code-Generation!");

So cool and so easy, isn’t it?

Other tools

I have played with other tools like dotnet-script, nake, and the popular scriptcs. For different reasons I couldn’t make any of them work fine (some weren’t even installing in a traditional .net framework project, some had complex methods for using nugets, and some simply weren’t working (not finding include files, etc)). So I decided to stick with the plain csi.exe, which most users will already have installed.

Final PowerShell Script

This script below has some nice features:

Allows to search for assemblies in multiple paths, including all NuGet locations
Can pass multiple paths to csi.exe, even though csi does not accept spaces in paths
Can restore missing NuGet packages (will even download nuget.exe)

# To Execute Powershell Scripts from Visual Studio:
# 1) Right-button PS1 file - "Open With...""
# 2) Configure:
#      Program: Powershell.exe
#      Arguments: -noexit -File %1
#      Friendly Name: Execute PowerShell Script

# To execute CSX scripts you'll need CSI.EXE (C# REPL) which is shipped with Visual Studio
# but can also be installed by using the NuGet package Microsoft.Net.Compilers.Toolset - https://www.nuget.org/packages/Microsoft.Net.Compilers.Toolset/

# For more info about launching CSX scripts from PowerShell or from Visual Studio, check https://drizin.io/code-generation-csx-scripts-part1/

$dir = Split-Path $MyInvocation.MyCommand.Path 
$script = Join-Path $dir ".\GenerateSimplePOCOs.csx"
$requiredLibs = @(
    @{ Name = "Newtonsoft.Json"; Version = "12.0.3" },
    @{ Name = "CodegenCS"; Version = "1.0.5" }
);

# By default we'll only use NuGet 4 locations. But you can change to 3 if you're hosting 
# your scripts in a project with the old packages.config format and want to rely on existing project packages
$NuGetVersion = 4; 



$ErrorActionPreference = "Stop"

# Locate CSI.EXE by searching common paths
$csi = ( 
    "$Env:userprofile\.nuget\packages\microsoft.net.compilers.toolset\3.6.0\tasks\net472\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2019\Professional\MSBuild\Current\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2019\Community\MSBuild\Current\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\15.0\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin\Roslyn\csi.exe",
    "$Env:programfiles (x86)\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\Roslyn\csi.exe"
) | Where-Object { Test-Path $_ } | Select-Object -first 1

if (!$csi)
{
    Write-Host "---------------------------------------" -for red
    Write-Host "Can't find csi.exe" -for red
    Write-Host "Please fix search paths above, or install NuGet Microsoft.Net.Compilers.Toolset" -for red
    Write-Host "---------------------------------------" -for red
    Exit 1
}
Write-Host "Found csi.exe: $csi" -for cyan

# List of locations to search for assembly references
$libPaths = @()
$libPaths += $dir

if ($NuGetVersion -eq 4)
{
    # New NuGet 4.0+ (PackageReference) saves User-specific packages in %userprofile%\.nuget\packages\
    $libPaths += "${env:userprofile}\.nuget\packages";
    if (Test-Path "${env:userprofile}\.nuget\packages") { $missingNuGetPackagesLocation = "${env:userprofile}\.nuget\packages" }

    # New NuGet 4.0+ (PackageReference) saves Machine-wide packages in %ProgramFiles(x86)%\Microsoft SDKs\NuGetPackages\"
    $libPaths += "${env:ProgramFiles(x86)}\Microsoft SDKs\NuGetPackages";
}

if ($NuGetVersion -eq 3)
{
    # Old NuGet (packages.config) saves packages in "\packages" folder at solution level.
    # Locate by searching a few levels above the script
    $missingNuGetPackagesLocation = ( 
        (Join-Path $dir ".\packages"),
        (Join-Path $dir "..\packages"),
        (Join-Path $dir "..\..\packages"),
        (Join-Path $dir "..\..\..\packages"),
        (Join-Path $dir "..\..\..\..\packages")
    ) | Where-Object { Test-Path $_ } | Select-Object -first 1
    $libPaths += $missingNuGetPackagesLocation
}

# where to download missing NuGet packages
if ((Test-Path $missingNuGetPackagesLocation) -eq $false)
{
    $missingNuGetPackagesLocation = $dir
}


# csi /lib parameter allows multiple paths but does not accept spaces (or quotes) so we have to use short DOS 8.3 paths
$fso = New-Object -ComObject Scripting.FileSystemObject
$libPaths = ($libPaths | Where-Object { Test-Path $_ } | ForEach { $fso.GetFolder($_).shortpath  });


Write-Host "CSI will use the following paths to search for assembly references:`r`n   - $($libPaths -Join "`r`n   - ")" -for cyan


$missingLibs = @()
$requiredLibs | foreach {
    $requiredLib = $_;
    Write-Host "Checking for $($requiredLib.Name) version $($requiredLib.Version)..." -for Cyan -NoNewLine

    if ($NuGetVersion -eq 4)
    {
        # NuGet 4+ format
        $found = $libPaths | 
        ForEach { Join-Path $_ ($requiredLib.Name + '\' + $requiredLib.Version) } | 
        Where-Object { Test-Path $_ } | Select-Object -first 1

        if ($found -eq $null)
        {
            Write-Host "`n$($requiredLib.Name) not found. Will install using NuGet" -for Yellow
            $missingLibs += $requiredLib
        }
        else
        {
             Write-Host "Found: $found" -for Cyan
        }
    }

    if ($NuGetVersion -eq 3)
    {
        # NuGet <=3 format
        $found = $libPaths | 
        ForEach { Join-Path $_ ($requiredLib.Name + '.' + $requiredLib.Version) } | 
        Where-Object { Test-Path $_ } | Select-Object -first 1

        if ($found -eq $null)
        {
            Write-Host "`n$($requiredLib.Name) not found. Will install using NuGet" -for Yellow
            $missingLibs += $requiredLib
        }
        else
        {
             Write-Host "Found: $found4 $found3" -for Cyan
        }
    }
}

if ($missingLibs)
{
    $nuget = Join-Path $env:TEMP "nuget.exe"
    if ((Test-Path $nuget) -eq $False)
    {
        Write-Host "Downloading NuGet.exe into $nuget" -for cyan
        $webClient = New-Object System.Net.WebClient 
        $webClient.DownloadFile("https://dist.nuget.org/win-x86-commandline/latest/nuget.exe", $nuget)
    }

    $missingLibs | foreach {
        $missingLib = $_
        Write-Host "Downloading $missingLib...";
        $libName = $missingLib.Name
        $libVersion = $missingLib.Version
        if ($libVersion -eq $null)
        {
            & $nuget install $libName -OutputDirectory $missingNuGetPackagesLocation
        }
        else
        {
            & $nuget install $libName -Version $libVersion -OutputDirectory $missingNuGetPackagesLocation
        }
        if ($lastExitCode -ne 0)
        {
            Write-host "-------------`nError downloading $missingLib - aborting...`n-------------" -for red
            Exit 1
        }
    }
}


$stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
Write-host "Starting csi.exe $script ..." -for yellow
& $csi /lib:$($libPaths -Join ';') $script

$stopwatch.Stop()
Write-Host "Finished in $($stopwatch.Elapsed.TotalMilliSeconds) milliseconds"

# Since I configured "-noexit" parameter in Visual Studio I don't need this
#if ($host.Name -notmatch 'ISE') { Write-Host -NoNewLine "(Just press Enter to exit)" -for cyan; read-host; }

And my final CSX

/// <summary>
/// This CSX Script will invoke SimplePOCOGenerator, which builds simple POCO classes based on a JSON file with schema of SQL database
/// The easiest way to launch csi.exe (which is shipped with Visual Studio) to run this script is by using PowerShell script GenerateSimplePOCOs.ps1
/// You can do that from Visual Studio (see instructions in RefreshDatabaseSchema.ps1) or you can just execute "Powershell GenerateSimplePOCOs.ps1"
/// </summary>

// System libraries
#r "System.Data.dll"

// Load third-party libraries by their relative paths, relative to "$Env:userprofile\.nuget\packages\"
#r "newtonsoft.json\12.0.3\lib\netstandard2.0\Newtonsoft.Json.dll"
#r "codegencs\1.0.5\lib\netstandard2.0\CodegenCS.dll"

// CS files are better than CSX because Intellisense and Compile-time checks works better. 
#load "DatabaseSchema.cs"
#load "SimplePOCOGenerator.cs"

using System;
using System.IO;
using System.Runtime.CompilerServices;
using System.Data;
using System.Data.SqlClient;

// Helpers to get the location of the current CSX script
public static string GetScriptPath([CallerFilePath] string path = null) => path;
public static string GetScriptFolder([CallerFilePath] string path = null) => Path.GetDirectoryName(path);


// locations relative to the CSX script
string inputJsonSchema = Path.GetFullPath(Path.Combine(GetScriptFolder(), "AdventureWorksSchema.json"));
string targetFolder = Path.GetFullPath(Path.Combine(GetScriptFolder(), @".\POCOs\"));
string csProj = Path.GetFullPath(Path.Combine(GetScriptFolder(), @".\CSX-PowerShell-VisualStudio2015.csproj"));

var generator = new SimplePOCOGenerator(inputJsonSchema);
generator.Namespace = "CSX_PowerShell_VisualStudio2015";
generator.Generate(targetFolder, null);

Full source code is available here, with a sample project that reads a JSON file with AdventureWorks database schema and generates POCOs, both for SDK-Style (new csproj format) and for non-SDK-style (old Visual Studio 2015 format).

So cool and so easy, isn’t it?

In my next post, I’ll create a SQL schema extractor, and this schema will be used in next posts to build code generator for POCOs, EFCore, and other related services.

Enjoy!

This was the first part of a multi-part post series I’m going write do about Code Generation using C#:

Part 1 - Code Generation in Pure C# (and CSX scripts) directly from inside Visual Studio
Part 2 - Extracting SQL Server Schema

Web API 2 - API versioning & Multiple Controllers with Same Name

Rick Drizin — Sat, 20 Jun 2020 01:55:52 GMT

ASP.NET Web API by default does not allow 2 controllers with same name.

Basically, this is what happens:

IHttpControllerSelector is resolved (by default it’s DefaultHttpControllerSelector) to call IHttpControllerSelector.GetControllerMapping()
IHttpControllerSelector.GetControllerMapping() will resolve (through a HttpControllerTypeCache cache manager) IHttpControllerTypeResolver to call IHttpControllerTypeResolver.GetControllerTypes().
By default IHttpControllerTypeResolver is DefaultHttpControllerTypeResolver, in which GetControllerTypes() will loop through all loaded assemblies and will return all types which implement IHttpController and which end with "Controller".
Going back… IHttpControllerSelector.GetControllerMapping() will return a map of all controllers, but in case will ignore namespace, strip "Controller" suffix, and will also ignore duplicates (they are moved into another dictionary which is used just to throw an exception that there are multiple matching controllers).
IHttpControllerSelector.SelectController is invoked on each request and will also use a cached map of controllers (cached by that same cache manager HttpControllerTypeCache) which will lookup controller name in the route (like {controller}) and will choose the correct controller.

I’ve found some some solutions to solve the problem of duplicated names, but most sounded too complex to me, since they were based on tagging the namespaces of each controller as DataTokens applied to the routes registered.

Since I’m using Attribute-Based routes and since I have (for legacy reasons) some non-attribute-based (regular explicitly defined routes), I ended up developing my own solution.

This NamespaceHttpControllerSelector below allows the use of multiple controllers with same name (obviously in different namespaces) by prepending controller identifier with their namespaces (if they have [RoutePrefix] attribute). And if there are controllers without [RoutePrefix] they are just registered as usual, without their namespace, and will be matched by their route {controller} as usual.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Web.Http.Controllers;
using System.Web.Http.Dispatcher;
using System.Web.Http.Routing;

/// <summary>
/// Allows the use of multiple controllers with same name (obviously in different namespaces) 
/// by prepending controller identifier with their namespaces (if they have [RoutePrefix] attribute).
/// Allows attribute-based controllers to be mixed with explicit-routes controllers without conflicts.
/// </summary>
public class NamespaceHttpControllerSelector : DefaultHttpControllerSelector
{
    // Some sample references: 
    // https://www.strathweb.com/2013/08/customizing-controller-discovery-in-asp-net-web-api/
    // https://github.com/WebApiContrib/WebAPIContrib/blob/master/src/WebApiContrib/Selectors/NamespaceHttpControllerSelector.cs 
    // https://github.com/umbraco/Umbraco-CMS/blob/7.2.0/src/Umbraco.Web/WebApi/NamespaceHttpControllerSelector.cs
    // https://github.com/mono/aspnetwebstack/blob/master/src/System.Web.Http/Dispatcher/DefaultHttpControllerSelector.cs
    // https://github.com/mono/aspnetwebstack/blob/master/src/System.Web.Http/Dispatcher/HttpControllerTypeCache.cs

    private HttpConfiguration _configuration;
    private IHttpControllerSelector _previousSelector;
    private readonly Lazy<Dictionary<string, HttpControllerDescriptor>> _controllers;

    public NamespaceHttpControllerSelector(HttpConfiguration httpConfiguration) : base(httpConfiguration)
    {
        _configuration = httpConfiguration;
        _previousSelector = _configuration.Services.GetService(typeof(IHttpControllerSelector)) as IHttpControllerSelector;
        _controllers = new Lazy<Dictionary<string, HttpControllerDescriptor>>(InitializeControllerDictionary);
    }

    public override IDictionary<string, HttpControllerDescriptor> GetControllerMapping()
    {
        return _controllers.Value; // just cache the list of controllers, so we load only once at first use
    }

    /// <summary>
    /// The regular DefaultHttpControllerSelector.InitializeControllerDictionary() does not 
    ///  allow 2 controller types to have same name even if they are in different namespaces (they are ignored!)
    /// 
    /// This method will map ALL controllers, even if they have same name, 
    /// by prepending controller names with their namespaces if they have [RoutePrefix] attribute
    /// </summary>
    /// <returns></returns>
    private Dictionary<string, HttpControllerDescriptor> InitializeControllerDictionary()
    {
        IAssembliesResolver assembliesResolver = _configuration.Services.GetAssembliesResolver();
        IHttpControllerTypeResolver controllersResolver = _configuration.Services.GetHttpControllerTypeResolver(); 
        ICollection<Type> controllerTypes = controllersResolver.GetControllerTypes(assembliesResolver); 

        // simple alternative? in case you want to map maybe "UserAPI" instead of "UserController"
        // var controllerTypes = System.Reflection.Assembly.GetExecutingAssembly().GetTypes()
        // .Where(t => t.IsClass && t.IsVisible && !t.IsAbstract && typeof(IHttpController).IsAssignableFrom(t));

        var controllers = new Dictionary<string, HttpControllerDescriptor>(StringComparer.OrdinalIgnoreCase);
        foreach (Type t in controllerTypes)
        {
            var controllerName = t.Name;

            // ASP.NET by default removes "Controller" suffix, let's keep that convention
            if (controllerName.EndsWith(ControllerSuffix))
                controllerName = controllerName.Remove(controllerName.Length - ControllerSuffix.Length);

            // For controllers with [RoutePrefix] we'll register full name (namespace+name). 
            // Those routes when matched they provide the full type name, so we can match exact controller type.
            // For other controllers we'll register as usual
            bool hasroutePrefixAttribute = t.GetCustomAttributes(typeof(RoutePrefixAttribute), false).Any();
            if (hasroutePrefixAttribute)
                controllerName = t.Namespace + "." + controllerName;

            if (!controllers.Keys.Contains(controllerName))
                controllers[controllerName] = new HttpControllerDescriptor(_configuration, controllerName, t);
        }
        return controllers;
    }

    /// <summary>
    /// For "regular" MVC routes we will receive the "{controller}" value in route, and we lookup for the controller as usual.
    /// For attribute-based routes we receive the ControllerDescriptor which gives us 
    /// the full name of the controller as registered (with namespace), so we can version our APIs
    /// </summary>
    /// <param name="request"></param>
    /// <returns></returns>
    public override HttpControllerDescriptor SelectController(HttpRequestMessage request)
    {
        HttpControllerDescriptor controller;
        IDictionary<string, HttpControllerDescriptor> controllers = GetControllerMapping();
        IDictionary<string, HttpControllerDescriptor> controllersWithoutAttributeBasedRouting =
            GetControllerMapping().Where(kv => !kv.Value.ControllerType
               .GetCustomAttributes(typeof(RoutePrefixAttribute), false).Any())
            .ToDictionary(kv => kv.Key, kv => kv.Value);

        var route = request.GetRouteData();

        // regular routes are registered explicitly using {controller} route - and in case we'll match by the controller name,
        // as usual ("CourseController" is looked up in dictionary as "Course").
        if (route.Values != null && route.Values.ContainsKey("controller"))
        {
            string controllerName = (string)route.Values["controller"];
            if (controllersWithoutAttributeBasedRouting.TryGetValue(controllerName, out controller))
                return controller;
        }

        // For attribute-based routes, the matched route has subroutes, 
        // and we can get the ControllerDescriptor (with the exact name that we defined - with namespace) associated, to return correct controller
        if (route.GetSubRoutes() != null)
        {
            route = route.GetSubRoutes().First(); // any sample route, we're just looking for the controller

            // Attribute Routing registers a single route with many subroutes, and we need to inspect any action of the route to get the controller
            if (route.Route != null && route.Route.DataTokens != null && route.Route.DataTokens["actions"] != null)
            {
                // if it wasn't for attribute-based routes which give us the ControllerDescriptor for each route, 
                // we could pick the correct controller version by inspecting version in accepted mime types in request.Headers.Accept
                string controllerTypeFullName = ((HttpActionDescriptor[])route.Route.DataTokens["actions"])[0].ControllerDescriptor.ControllerName;
                if (controllers.TryGetValue(controllerTypeFullName, out controller))
                    return controller;
            }
        }

        throw new HttpResponseException(HttpStatusCode.NotFound);
    }

}

How to register this - before auto-mapping your routes (in your Startup.cs, or RouteConfig.cs, or Global.asax.cs, or similar) you register your new IHttpControllerSelector:

config.Services.Replace(typeof(IHttpControllerSelector), new NamespaceHttpControllerSelector(config));
config.MapHttpAttributeRoutes();

Install Chrome using Powershell Script

Rick Drizin — Fri, 06 Mar 2020 02:08:52 GMT

I know everyone hates Internet Explorer, and only use Internet Explorer to download Chrome or Firefox.

To avoid you further pain, this is a Powershell script that you can use to install Chrome without even touching IE. Enjoy! :-)

# Install Chrome
$Path = $env:TEMP; 
$Installer = "chrome_installer.exe"; 
Invoke-WebRequest "http://dl.google.com/chrome/install/375.126/chrome_installer.exe" -OutFile $Path\$Installer; 
Start-Process -FilePath $Path\$Installer -Args "/silent /install" -Verb RunAs -Wait; 
Remove-Item $Path\$Installer

Zabbix UI showing UTC timezone on Timeline

Rick Drizin — Sun, 08 Dec 2019 17:16:08 GMT

I’m using Zabbix 4.4.3 (latest version), installed using RedHat packages, and the timezone was showing UTC and I needed to change it to my local timezone (EST).

In dozens of different sites I found instructions explaining to configure timezone in php.ini (for me it was in /etc/php.ini) like this:

[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
;date.timezone =
date.timezone = "America/New_York"

I restarted the services and it wasn’t still working. Although the Local Clock works, the Problems Timeline was still showing UTC timezone:

So I decided to put directly this configuration in the PHP application (Zabbix). I found the UI configuration to be in /etc/zabbix/web/zabbix.conf.php, and just had to add this line:

date_default_timezone_set('America/New_York');

And now it works (no need to restart the services):

PS: Somewhere I read thatI should configure /etc/apache2/conf-available/zabbix-frontend-php.conf, but I couldn’t find this file anywhere in my RedHat installation.

Powershell script to Stamp pictures with Date/Time

Rick Drizin — Wed, 04 Dec 2019 03:13:52 GMT

Quick tip:

I hate having my pictures automatically named like IMG_0384.CR2, because during backups (moving around hard drives, cloud, etc) it happens that I lose the original date of the files. And although I group pictures in folders, it’s still sometimes easy to lost the original date.

So I just created (well, I copied from others but made some adjustments) this helpful Powershell script, which will stamp the filenames with the date when the picture was taken.

# Based on https://ss64.com/ps/syntax-stampme.html
# usage: PS C:\>./stampme.ps1 "F:\somepicture.png"
# usage: PS C:\> foreach ($file in get-ChildItem *.CR2) { ./stampme.ps1 $file.name }


#StampMe.ps1
param( [string] $fileName)

# Check the file exists
if (-not(Test-Path $fileName)) {break}

# Display the original name
"Original filename: $fileName"

$fileObj = get-item $fileName

# https://stackoverflow.com/questions/6834259/how-can-i-get-programmatic-access-to-the-date-taken-field-of-an-image-or-video
try {


  $pic = New-Object System.Drawing.Bitmap($fileName)
  $bitearr = $pic.GetPropertyItem(36867).Value 
  $string = [System.Text.Encoding]::ASCII.GetString($bitearr) 
  $DateTime = [datetime]::ParseExact($string,"yyyy:MM:dd HH:mm:ss`0",$Null)
}
catch 
{
  $DateTime =  (Get-Item $fileName).LastWriteTime
}

# Get the date
#$DateStamp = get-date -uformat "%Y-%m-%d_%H%M%S"
$DateStamp = $DateTime.ToString("yyyy-MM-dd_HHmmss")

$extOnly = $fileObj.extension

if ($extOnly.length -eq 0) {
   $nameOnly = $fileObj.Name
   rename-item "$fileObj" "$DateStamp-$nameOnly"
   }
else {
   $nameOnly = $fileObj.Name.Replace( $fileObj.Extension,'')
   rename-item "$fileName" "$DateStamp-$nameOnly$extOnly"
   }

# Display the new name
"New filename: $DateStamp-$nameOnly$extOnly"

Look how my pictures were badly organized before:

And look how they are much more beautiful now:

Enjoy!

Much better, uh? :-)

Yet Another Code Generator

Rick Drizin — Fri, 20 Sep 2019 04:16:03 GMT

I’ve been a fan of code generation (mostly for scaffolding data access objects from databases) for a long-time. I have used CodeSmith generator, MyGeneration Code Generator, LINQ to SQL, Rockford Lhotka CSLA, Subsonic Active Record Templates, and more recently Simon Hughes Entity Framework T4 templates.

The main advantage of code generation (which includes “db scaffolding”, also known as database first in some ORMs) is that it does the work for you, is repeatable, and less error-prone than manually writing everything. So it’s mostly about productivity and consistency. If you’re writing repetitive code by hand, you’re stealing from your employer or from your client. And if you have a data-oriented application, almost certainly you have repetitive code to some degree.

I frequently need to make some customizations over the code generation templates, and it’s always a hard task. Most code generators do not have good support for one or more of the following important features:

Indentation and whitespace control
Debugging
Includes (subtemplates)
Intellisense (code completion)
Compile-time checking (strong typing)
Integration with CSPROJ files

Among those, I had one important requirement: the included subtemplates should respect parent indentation, and even if they were multi-line templates those lines should be rendered in the correct position. Or as handlerbars describe , “By default, an indented partial-call causes the output of the whole partial being indented by the same amount.”.

While trying to find a good code generator (to replace what I currently do with T4 Templates) I explored the most popular free tools/libraries for code generation and text templating.

T4 Templates

T4 templates are based on ASPX control blocks <% %>. They tend to get very ugly, have low degree of intellisense/typing (it’s easy to write invalid code and you can only find out debugging which is even harder), have terrible support for indentation, and everytime that I upgrade my Visual Studio the T4 templates totally break, since they are very environment dependent.

And they look as ugly as this:

Razor Engines

Antaris RazorEngine is the top-leading razor engine, but there are other razor engines like RazorLight and WestWind.RazorHosting.

Razor is more concise (and less ugly) than ASPX control blocks, but for achieving concise code they have to make some “assumptions” about what is control logic and what is output code, and therefore it tries to automagically guess C# code that is intended to control logic so if you write Debug.WriteLine it will assume that this should be interpreted (and not written to output).
If you type an XML tag inside a code block it will automagically assume that it should be written to output (which makes sense, afterall XML tags are not control logic, but yet this gets very confusing). So to make it work you end up using escape characters like @, @{} and @: everywhere.

Razor templates are nice because they have good Visual Studio support (with intellisense and object models, although those unclear distintions between control logic and output code usually make intellisense fail), and allow partials, but unfortunately for having intellisense in partials you would have to split them into indivual files.

RazorEngine is very complete, very stable, and somehow easy to extend, but they have some minor annoyances like saving temporary DLLs (which don’t get deleted) everywhere.
But the major problems with all Razor-based engines is that Razor uses angle-based tags, and therefore is targeted at writing XML/HTML, and a little annoying to write other outputs. Additionally, the Partials (includes) do NOT respect parent indentation level - so if you include a multi-line subtemplate, you’ll get a messy indentation.

Scripty

Scripty describes itself as Tools to let you use Roslyn-powered C# scripts for code generation. You can think of it as a scripted alternative to T4 templates.
It can manipulate CSPROJ files, has support for both .NET Framework and .NET core, and has good indentation support: it has its own TextWriter which keeps tracks of current indent level, and restores this indent level when you start a new line). It also has great intellisense since it’s scripted using pure C#.
Unfortunately, it does not have integrated templating engine, so writing complex templates can get ugly and cumbersome. Additionally it looks like the author not only does not intend to support any specific templating engine, but also has somehow abandoned the project (and complex open source projects when abandoned usually decline fast unless a new group of supporters fork it into a new project) - so I was a little afraid of putting all my coins into a complex codebase which is not being maintained.

Templating Engines

Currently the most popular templating engines are based on the mustache standard, which is very nice because it uses “double-braces” to define control logic with tags {{ this }}, so you can still use regular single-braces for C# (or Java or any C-Style) code. Although mustache is the first engine using this, it was quickly followed by better alternatives like handlebars.js and Shopify liquid, which are the two most popular engines, and they were both respectively ported to .NET in the libraries Handlebars.Net and Dotliquid. There’s also a new kid on the block called Scriban which is based on Dotliquid but with some improvements, and growing fast.

Although the escape character in all those engines does not conflict with C# code, their syntax is very different from regular C# code (probably easier to parse), so you have to learn a new syntax. Learning a new syntax is not a problem, afterall we all have learned so many different syntaxes over time and old dogs love to learn new tricks, but the problem is that those engines also do NOT have any debugging support, which is a must, specially when you’re not familiar with the syntax, or when you’re writing complex templates. Debugging templates only by the error outputs is like debugging code only with Console.WriteLine when you could be using a real IDE.

Jinja2

Since none of the .NET alternatives fully matched what I was looking for, I also checked the most popular templating engine for non-NET languages.
Jinja2 engine is written in Python, and is similar to Handlebars and Liquid. They have good whitespacing control, good indentation, and a good ecosystem (parsers, editors, etc). However, similarly like Dotliquid and Handlebars.Net, it looks like all those templating tools are NOT focused on code generation but on used-defined-templates (like configuring automatic emails, configuring pages for your ecommerce, etc), so they have a different set of requirements in mind. For those tools it really makes sense to have a simple syntax, a sandboxed security model (isolated AppDomain), and loading templates from strings stored anywhere (like your database). A code generator does not need any of that, but needs good debugging support, good intellisense, and (for those of us who like it) strong typing.
Last, since I’m mostly into C#/.NET, it didn’t look like a good fit to start using a Python engine - especially if I want my templates to be easily adopted by other C# users. I was looking for something similar to T4 templates but with better readability, better tooling, and better reuse.

Writing code using C#

From the tools above, the only one which integrates with msbuild projects (in other words can write into csproj files) is Scripty, so this is where I started my tests. Scripty basically have their own TextWriter (Scripty.Core.Output.OutputFile) and some helpers like a collection of TextWriters for writing to multiple files, and for choosing how each file should be treated by the csproj, etc.
This is Scripty syntax:

w.WriteLine();
w.WriteLine("///<summary>");
w.WriteLine("/// {0}", System.Security.SecurityElement.Escape(c.SummaryComments));
w.WriteLine("///</summary>");

For writing indented code in Scripty you could manually indent your strings like this:

w.WriteLine("finally");
w.WriteLine("{");
w.WriteLine("    dbConnection.Close();");
w.WriteLine("}");

… or you could use their nice indentation helpers which control indentation level for you:

w.WriteLine($"public {Settings.DbContextName}(string connectionString) : base(connectionString)");
w.WriteLine("{");
using (w.WithIndent())
{
    w.WriteLine("InitializePartial();");
}
w.WriteLine("}");

As you can notice, this can get very ugly and cumbersome to write:

w.WriteLine($"{Settings.MigrationClassModifiers} class {Settings.MigrationConfigurationFileName}: System.Data.Entity.Migrations.DbMigrationsConfiguration<{Settings.DbContextName }> ");
w.WriteLine("{");
using (w.WithIndent())
{
    w.WriteLine($"public {Settings.MigrationConfigurationFileName}()");
    w.WriteLine("{");
    using (w.WithIndent())
    {
        w.WriteLine($"AutomaticMigrationsEnabled = { Settings.AutomaticMigrationsEnabled.ToString() };");
        w.WriteLine($"AutomaticMigrationDataLossAllowed = { Settings.AutomaticMigrationDataLossAllowed.ToString() };");
        if (!string.IsNullOrEmpty(Settings.ContextKey))
            w.WriteLine($@"ContextKey = ""{ Settings.ContextKey }"";");
    }
    w.WriteLine("}");
}
w.WriteLine("}");

Pretty ugly, right?

More concise manual indentation

The first helper method that I added to Scripty was something to make manual-indentation less verbose when all I need is to create C-style blocks ( { } ).

With my helpers, creating an indented code block became a little easier because a single statement will both write the line before the block starts, will write the brace which starts the block, will increase indent, and when being disposed it will decrease the indent and will also output the brace which closes the block. So it’s 3 lines (and increasing/decreasing inner indentation) in a single line.

using (w.WithCStyleBlock($"namespace {myNamespace}"))
{
    using (w.WithCStyleBlock($"public class {myClass}"))
    {
         w.WriteLine("// My Properties start here");
    }
}

…this generates this code:

namespace codegencs
{
    public class Test1
    {
        // My Properties start here
    }
}

Another example:

w.WriteLine($"{Settings.MigrationClassModifiers} class {Settings.MigrationConfigurationFileName}: System.Data.Entity.Migrations.DbMigrationsConfiguration<{Settings.DbContextName }> ");
w.WriteLine("{");
using (w.WithIndent())
{
    w.WriteLine($"public {Settings.MigrationConfigurationFileName}()");
    w.WriteLine("{");
    using (w.WithIndent())
    {
        w.WriteLine($"AutomaticMigrationsEnabled = { Settings.AutomaticMigrationsEnabled.ToString() };");
        w.WriteLine($"AutomaticMigrationDataLossAllowed = { Settings.AutomaticMigrationDataLossAllowed.ToString() };");
        if (!string.IsNullOrEmpty(Settings.ContextKey))
            w.WriteLine($@"ContextKey = ""{ Settings.ContextKey }"";");
    }
    w.WriteLine("}");
}
w.WriteLine("}");

Can be simplified into:

using (w.WithCStyleBlock($"{Settings.MigrationClassModifiers} class {Settings.MigrationConfigurationFileName}: System.Data.Entity.Migrations.DbMigrationsConfiguration<{Settings.DbContextName }> ))
{
    using (w.WithCStyleBlock($"public {Settings.MigrationConfigurationFileName}()"))
    {
        w.WriteLine($"AutomaticMigrationsEnabled = { Settings.AutomaticMigrationsEnabled.ToString() };");
        w.WriteLine($"AutomaticMigrationDataLossAllowed = { Settings.AutomaticMigrationDataLossAllowed.ToString() };");
        if (!string.IsNullOrEmpty(Settings.ContextKey))
            w.WriteLine($@"ContextKey = ""{ Settings.ContextKey }"";");
    }
}

As you can see, the boilerplate was condensed into a single statement, and yet the control logic braces/indentation clearly match the output code braces which are implicitally rendered.
(I know that some people will prefer opening braces in the same line - so sorry, you won’t see that helper in my code generator, ever!! :-) just kidding - let’s create a WithJavascriptStyleBlock() for opening block in same line ok? :-) )

Multiline statements and the Mixed Indentations Problem

Even with C-block helpers, writing line by line like previous examples can be cumbersome - you’ll probably want to write small blocks in a single multiline statement like this:

if (writeClass)
{
    if (writeDispose) 
    {
        w.WriteLine(
        @"protected virtual void Dispose(bool disposing)
        {
            base.Dispose()
        }");
    }
}

The problem is that in the code above I made the string aligned with the control logic, to be visually clear. But the output would be this (assuming that TextWriter is at IndentLevel=0):

protected virtual void Dispose(bool disposing)
        {
            base.Dispose()
        }

To make the output code correctly indented, you would have to use Mixed Indentations, where control logic has its own indentation, and output code has another:

if (writeClass)
{
    if (writeDispose) 
    {
        w.WriteLine(
@"protected virtual void Dispose(bool disposing)
{
    base.Dispose()
}");
    }
}

Looks ugly, isn’t it? This mixed-indentation problem also affects T4 templates, as you can see in the previous T4 template sample screenshot that I posted above. And as you’ll notice, T4 templates can get even worse because the syntax highlighting may get totally crazy (or not work at all) and you won’t be able to tell apart what is control logic and what is output code.

As you can see, when you write multiline statements if you want to correctly control indentation and whitespace you’ll have to use mixed indents of the generating code and the generated code, and that gets very confusing. If you indent the strings to the right (to be aligned with the outer control code), you’ll get extra indents in your output, which may not be desirable.
(And, yes, I’m aware that there are code formatting tools like Roslyn, but yet, having manual control over code is a good idea, and more than this - you may want to write non-C# code that can’t be automatically formatted)

Solving multi-line strings

To make the multi-line blocks a little less confusing (avoid “mixed indent levels”) I created a helper method which would allow me to write multiline string with any number of padding spaces (to make it aligned with the outer generating code), and yet those spaces will be ignored. Whenever I write a multi-line block, my helper assumes that the block should follow the current indentation level (of Scripty TextWriter), and do not add any further indentation.

if (something)
{
    if (something)
    {
        if (something)
        {
            w.WriteLine(@"
                namespace codegencs
                {
                    public class Test1
                    {
                        // My Properties start here
                    }
                }");
        }
    }
}

In case, my helper method will realign this whole block to the left, docking the outermost line to the left, while respecting “internal” indentation. So assuming that the current TextWriter was at IndentLevel 0 we get this output:

namespace codegencs
{
    public class Test1
    {
        // My Properties start here
    }
}

And if the TextWriter was at IndentLevel 1 we get this output:

    namespace codegencs
    {
        public class Test1
        {
            // My Properties start here
        }
    }

PS: Not only the block is docked to the left (removing from all lines the maximum number of spaces) but also we ignore the first empty line, which allows us to put the opening@" in a single line, making the first line correctly-aligned with the following lines.

// this
w.WriteLine(@"
        public class Test1
        {
            // My Properties start here
        }");

// is a little better than this, right?
w.WriteLine(
        @"public class Test1
        {
            // My Properties start here
        }");

Integrating a real Templating Engine

Now that indentation and multiline strings were more readable, the next step was to allow templates with more logic, and mostly with reusable code (functions or subtemplates). As I explained above, Scripty does not have ambition to be a Templating Engine, or even to have one integrated, so it was clear that I should integrate into Scripty a real engine like RazorEngine, Scriban or Dotliquid.

RazorEngine has good extensibility, it looked promising. I wrote my own BaseTemplate and registered it to be injected for all templates. The idea was to get the underlying Scripty TextWriter.

Basically I had this:

public class RazorIndentedTemplate<T> : TemplateBase<T>
{
    private readonly TextWriter _tw;

    public RazorIndentedTemplate(TextWriter tw)
    {
        _tw = tw;
    }
    public override void Write(object value)
    {
        _tw.Write(value);
    }

    public override void WriteLiteral(string literal)
    {
        _tw.Write(literal);
    }
...
    public override TemplateWriter Include(string name, object model, Type modelType)
    {
        return new TemplateWriter(w =>
        {
            var scripty  = ((Scripty.Core.Output.OutputFile)this._tw);
            scripty.IndentLevel++;
            Razor.Run(name: name, writer: this.CurrentWriter, modelType: modelType, model: model);
            scripty.IndentLevel--;
        });
    }
}

And then in your template:

var config = new FluentTemplateServiceConfiguration(c =>
    c
    //.WithEncoding(RazorEngine.Encoding.Raw)
    .WithBaseTemplateType(typeof(RazorIndentedTemplate.RazorIndentedTemplate<>))
    .ActivateUsing(context => (ITemplate)Activator.CreateInstance(context.TemplateType, writer))
);
using (var service = RazorEngineService.Create(config))
{
...
}

With the code above, everytime that I included a subtemplate (using @Include("Subtemplate")) I could find the TextWriter, and increase the indent level.

That’s not what I wanted.

I wanted that indent to be increased according to the inline position of the cursor:

string razorTemplate = @"
    public class Class1
    {
        @Include("Class1Method1") // this SHOULD increase indent level.
    }
    @Include("otherclass") // this should NOT increase indent level.
";

In other words, I wanted includes to respect parent indentation even if the parent indentation was inline defined by some spaces right before the include.

So I needed to get current cursor position (how many characters after the last line break, but not counting indent spaces added by Scripty). Scripty OutputFile does NOT allow me to get that, and also does not allow me to replace it’s OutputFile by my own implementation (like RazorEngine does), but it allows to pass an inner text writer.
I don’t remember if I really managed to make it work (I think I just gave up and decided to write my own before it), but it was certainly turning into a Frankestein:

In my template (outside Scripty) I would had multi-line helper.
In my template (outside Scripty) I would had indentation helper.
In my template I would have a PositionableTextWriter (to keep track of cursor position in current line), and would pass this as inner writer of Scripty OutputFile.
In another assembly I had to create my new RazorBaseTemplate, and register it to RazorEngine. Which in case would depend on my PositionableTextWriter.
Razor includes would automatically decide if they should increase indentation.

And in the end, it would still be hard to debug, bad intellisense (unless I was willing to create an individual cshtml file for each reusable block ever), and totally coupled.

Besides Scripty OuputFile which was limited, I also faced another dozen of weird problems when running Scripty (which uses Roslyn scripting, which have some serious issues like not allowing namespaces) like assembly conflicts, namespaces problems, etc, all hard to solve.

So after frustration with both Scripty and RazorEngine, I decided to write my own TextWriter not only to keep track of current cursor position, but also to completely replace Scripty indent-control.

My own TextWriter

Writing my TextWriter was basically to keep track of current cursor position, so I would have to count number of characters written after each line break. And then I added the indentation-control, similar to Scripty. And then I added my helpers methods: the one that will “trim” multi-line strings, and the one that is a shorthand for C-blocks.

Ok, all working great - Now that indentation and multiline strings were more readable, it was time to create a robust way of using subtemplates. Probably I could use RazorEngine more easily, but since I was already rewriting from scratch I thought I could just use pure C# strings as my templating engine.

This is what I had in mind:

// This is a reusable method which you can embed anywhere inside your string-interpolated templates
string RenderProperties(List<Property> props)
{
    return () => $@"
        {string.Join(Environment.NewLine, props.Select(prop => $"public {prop.Type} {prop.Name} {{ get; set; }}"))}";
}
public void GenerateMyClass()
{
    List<Property> props = new List<Property>() { new Property() { Name = "Name", Type = "string" }, new Property() { Name = "Age", Type = "int" } };
    var w = new TemplateTextWriter();
    string myNamespace = "codegencs";
    string myClass = "Test1";
    w.Write(@"
        namespace {myNamespace}
        {
            public class {myClass}
            {
                // My Properties start here
                { RenderProperties(props) }
            }
        }");
}

The problem with this is that my Write method (yes, that one which can handle multiline strings) will receive as argument a single string. And this is what it would output, since the included method renders a linebreak and has no idea of inline indentation:

namespace codegencs
{
    public class Test1
    {
        // My Properties start here
        public string Name { get; set; }
public int Age { get; set; }
    }
}

And as you may have noticed, I’m a little obsessed with whitespace control, so the whitespeak freak got angry again because this way of using subtemplates wouldn’t work. Subtemplates should “respect current indent” even if the indent was implicitally defined by some spaces before the subtemplate was included.

Why not C# ?

Since my TextWriter was fully C# (no templating language), and it was doing a good part of what I needed, why not do the whole subtemplating thing in C#? All I had to do is finding a way to capture current cursor position before starting a subtemplate. In other words, I wanted “inline templates” which should be executed while preserving the current indentation.

If it sounds weird to output C# code using C# code (really?), let me give some good reasons:

Compiled, typed, compile-time checking
Full IDE support. The IDE which you are already familiar. With debugging support.
Runs in .NET Standard and .NET Core
Full access to all C# Framework. You can connect to SqlServer, you can use Newtonsoft JSON, etc.
Full C# framework - you’re not limited to a small subset of string-manipulation functions.

After all those reasons, all I can think is “Why hadn’t this been done before”.

Interpolated Strings to the Rescue

As I explained above my inline templates would have to “capture” the current cursor position, and therefore I would have to process the inner template only after the outer template (at least the parts which precede the inner template) had been processed/written.

Now comes the interesting part…

As soon I started exploring how I could control the rendering of the interpolated strings, I discovered that string interpolation is not only syntactic-sugar offered by the compiler, but it also translates into a class FormattableString which offers the format and the arguments individually, and that would be a perfect use for outer template (format) and inner templates (arguments):

Func<List<Property>, FormattableString> RenderProperties = (props) => ...
string myClass = "Test1";
writer.Write($@"
    public class {myClass}
    {{
        // My Properties start here
        { RenderProperties(props) }
    }}");

… is translated into this:

Func<List<Property>, FormattableString> RenderProperties = (props) => ...
string myClass = "Test1";
writer.Write(FormattableStringFactory.Create(@"
    public class {0}
    {{
        // My Properties start here
        {1}
    }}", new object[] { myClass, RenderProperties(props) } );

PS: since interpolated strings use braces as control character, we have to use double braces for escaping braces.

FormattableStringFactory.Create creates an object of type FormattableString, which has the format and the arguments.
If my Write method expects a string, the FormattableString is implicitally converted into a regular string (and in case double braces will be rendered as single braces).
On the other hand, if method expects a FormattableString, we can use the format and arguments explicitly - so we can process the format block by block, and when we reach each argument {X} we can “save” the current position and therefore preserve the current indentation.
So all I have to do is process (writing to output) the string format until I found the embedded argument, then process the inline argument, and repeat until the end of the string format. So with string interpolation we can embed complex subtemplates inside the parent template, while respecting the “cursor position” where the inner template starts.

// This is a reusable method which you can embed anywhere inside your string-interpolated templates
Func<FormattableString> RenderProperties(List<Property> props)
{
    return () => $@"
        {string.Join(Environment.NewLine, props.Select(prop => $"public {prop.Type} {prop.Name} {{ get; set; }}"))}"
    ;
}
public void GenerateMyClass()
{
    List<Property> props = new List<Property>() { new Property() { Name = "Name", Type = "string" }, new Property() { Name = "Age", Type = "int" } };
    var writer = new TemplateTextWriter();
    string myNamespace = "codegencs";
    string myClass = "Test1";
    writer.Write($@"
        namespace {myNamespace}
        {{
            public class {myClass}
            {{
                // My Properties start here
                { RenderProperties(props) }
            }}
        }}");
}

And the output is:

namespace codegencs
{
    public class Test1
    {
        // My Properties start here
        public string Name { get; set; }
        public int Age { get; set; }
    }
}

As you can see, the inner block has multiple lines, and yet all those lines were written in the same position where the first line started. In other words, the inner block was fully written in the “current cursor position”. And again, if the text writer had indent level 1, all that output (outer and inner template) would have 4 more spaces before each line. Cool, uh?

For my inner template I used a Func<FormattableString> but it could be other types like string, Func<string>, or FormattableString itself. They would all be evaluated “on demand”, only by the moment we need to output those parameters.

FormattableString is automatically created by the compiler when you write an interpolated string.

The core method of my TextWriter is below, and basically it will get the format string, split by the arguments ({0}, {1}, etc, which are automatically generated by the compiler when you write a regular interpolated string), and will write (respecting current indent level, etc) a small part of the string format “up to the next argument”, then it will process the argument, which might be another interpolated string (entering into recursion), or might be a more reusable callback function, or just a plain string, and repeat until the end of the string.

#region WriteFormattable(string format, params object[] arguments) - Basically, we split any interpolated string, and write block by block, doing lazy-evaluation of arguments. 
/// <summary>
/// This is the "heart" of this class. Basically, we split any interpolated string, and write block by block, doing lazy-evaluation of arguments. 
/// The idea of writing Func<FormattableString> is that we do NOT evaluate the {arguments} BEFORE the outer string is being written - they are only evaluated when needed
/// so we can capture the cursor position in current line, and preserve-it if the arguments render multi-line strings
/// </summary>
public void WriteFormattable(FormattableString formattable)
{
    string format = formattable.Format;
    if (_trimBlockPaddings)
        format = TrimLeftPadding(format);
    if (_trimFirstEmptyLine)
        format = this.TrimFirstEmptyLine(format);
    object[] arguments = formattable.GetArguments();
    var matches = Regex.Matches(format, @"{\d}");
    int lastPos = 0;
    for (int i = 0; i < matches.Count; i++)
    {
        // unescape escaped curly braces
        string text = format.Substring(lastPos, matches[i].Index - lastPos).Replace("{{", "{").Replace("}}", "}"); 
        base.WriteWithIndents(text);
        // arguments[i] may not work because same argument can be used multiple times
        var arg = arguments[int.Parse(matches[i].Value.Substring(1, 1))];
        if (arg as FormattableString != null)
        {
            base.ExecuteInlineAction(() =>
            {
                FormattableString subText = (FormattableString)arg;
                this.Write(subText);
            });
        }
        else if (arg as Func<FormattableString> != null)
        {
            base.ExecuteInlineAction(() =>
            {
                Func<FormattableString> subText = ((Func<FormattableString>)arg);
                this.Write(subText);
            });
        }
        else if (arg as Action<TemplateTextWriter> != null)
        {
            base.ExecuteInlineAction(() =>
            {
                Action<TemplateTextWriter> action = ((Action<TemplateTextWriter>)arg);
                action(this);
            });
        }
        else if (arg as Action<TextWriter> != null)
        {
            base.ExecuteInlineAction(() =>
            {
                Action<TextWriter> action = ((Action<TextWriter>)arg);
                action(this);
            });
        }
        else if (arg as Func<string> != null)
        {
            base.ExecuteInlineAction(() =>
            {
                string exec = ((Func<string>)arg)();
                this.Write(exec);
            });
        }
        else if (arg as string != null)
        {
            base.ExecuteInlineAction(() =>
            {
                string subText = ((string)arg);
                this.Write(subText);
            });
        }
        else
        {
            base.ExecuteInlineAction(() =>
            {
                this.Write(arg.ToString());
            });
        }

        lastPos = matches[i].Index + matches[i].Length;
    }
    string lastPart = format.Substring(lastPos).Replace("{{", "{").Replace("}}", "}");
    base.WriteWithIndents(lastPart);
}
#endregion

And the indentation control basically will split all writes line-by-line, and for each new line will add indent before the text:

#region WriteWithIndents(string value): writes line by line, and writes the indent strings before each new line
/// <summary>
/// This is the "heart" of this class. Basically, we split any multi-line string, and write line by line. 
/// Before writing each new line we write the indent block, which could for example be 8 spaces (4 spaces in first indent level and 4 spaces for second indent level),
/// or 2 tabs (one for each indent level), or any combination.
/// </summary>
/// <param name="value"></param>
protected void WriteWithIndents(string value)
{
    if (string.IsNullOrEmpty(value))
        return;

    var matches = _lineBreaksRegex.Matches(value);

    int lastPos = 0;
    for (int i = 0; i < matches.Count; i++)
    {
        string line = value.Substring(lastPos, matches[i].Index - lastPos);
        string lineBreak = value.Substring(matches[i].Index, matches[i].Length);
        lastPos = matches[i].Index + matches[i].Length;

        // if _dontIndentCurrentLine is set, it's because we're starting an inner block right "at cursor position"-  no need to indent again - we're already positioned!
        if (line.Length > 0 && _currentLine.Length == 0 && !_dontIndentCurrentLine)
            this.WriteIndent();

        this.WriteRaw(line);
        if (_normalizeLineEndings)
            this.WriteRaw(_innerWriter.NewLine);
        else
            this.WriteRaw(lineBreak);
        _currentLine.Clear();
        _dontIndentCurrentLine = false;
    }
    string lastLine = value.Substring(lastPos);

    if (lastLine.Length > 0 && _currentLine.Length == 0 && !_dontIndentCurrentLine)
        this.WriteIndent();
    this.WriteRaw(lastLine);
    _currentLine.Clear().Append(lastLine);
    _dontIndentCurrentLine = false;
}
#endregion

Other than this, the last magic part is this function, which will “save current cursor position”, and will start a new “inline write”. For example, suppose that the outer template has implicitally written 4 spaces and then we’re starting an inline action, the TextWriter will “save” this 4 spaces, write the first line (no need to indent because it was “implicitally indented”), and then will explicitly indent the next lines with this same 4 spaces. (Would also work for tabs, or any other indentation string).

/// <summary>
/// If the current line has some manually-written whitespace, this method will "save" current cursor position, and in case the inline action writes a multi-line text,
/// the subsequent lines (after the first) will "preserve" the cursor position by adding this extra indent.
/// ("manually-written whitespace" does not count the automatic indents written by this class).
/// </summary>
protected void ExecuteInlineAction(Action inlineAction)
{
    string indent = _currentLine.ToString();
    if (indent != null && indent.Length > 0 && string.IsNullOrWhiteSpace(indent))
    {
        this._levelIndent.Push(indent); // we could convert tabs to spaces or vice-versa
        _dontIndentCurrentLine = true;
        _currentLine.Clear();
        inlineAction();
        this._levelIndent.Pop();
    }
    else
    {
        inlineAction();
    }
}

Hope you enjoyed as much as I did.

On next posts I may explore some new ideas, or maybe just develop some nice templates.

Full source code here

Installing Gitea (Git Server developed in GO) in Windows

Rick Drizin — Thu, 30 May 2019 04:16:47 GMT

I’ve been a long-time user of gogs, but I decided to update my version recently and found out this fork called gitea which seems to have more features.

How to install it on a Windows server:

Download and install GO language. (It’s as easy as next-next-finish)
Open a command prompt and type:
- mkdir %GOPATH%
- cd %GOPATH%
- go get -d -u code.gitea.io/gitea
- cd "%GOPATH%/src/code.gitea.io/gitea"
- go build
- Optional: copy this gitea folder to some other folder (e.g. C:\gitea\) if you don’t want to run this from your %GOPATH% folder
- .\gitea web
- launch http://localhost:3000
- Click "sign in" to configure initial configuration (I suggest to use PostgreSQL)
Instead of running as command prompt, you can configure it as Windows Service:
- Close gitea web (ctrl-c)
- Edit C:\gitea\custom\conf\app.ini: and set Gitea to run using local system user: RUN_USER = COMPUTERNAME$
- Open a command prompt (cmd) as an Administrator, then run the following command to register Gitea as a Windows service:
  sc create gitea start= auto binPath= ""C:\gitea\gitea.exe" web --config "C:\gitea\custom\conf\app.ini""
- Open “Windows Services”, search for the service named “gitea”, right-click it and click on “Run”. If everything is OK, Gitea will be reachable on http://localhost:3000

References:

Full Recovery vs Simple Recovery and the Ever-growing LDF file

Rick Drizin — Sat, 11 Mar 2017 02:48:19 GMT

Many newcomers (and even experienced developers who never had to manage their own SQL Servers) usually don’t understand the difference between Full Recovery mode and Simple Recovery mode, and more than that they don’t know how to manage their SQL databases so that their LDF files won’t grow indefinitely.

Full Recovery vs Simple Recovery

Simple Recovery uses transaction log only for uncommited transactions, so this file is very small, and doesn’t grow forever (allocated space is reused), so there is no Log backup for Simple Recovery.

Full Recovery tracks all past transactions in the transaction log, so log grows forever unless we do maintenance .

The major advantage of Full Recovery is that it allows us to use point in time restore, which allows to restore to an exact time, reverting (or inspecting) pontual database changes.

I strongly recommend Full recovery for any serious production environment.

Ola Hallengren Maintenance Scripts

Ola Hallengren is an experienced DBA who has developed these amazing SQL Server Maintenance scripts, comprised of scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server.

Download the scripts here, run then on your server, and it will install many useful stored procedures into your master database.

I strongly recommend those scripts so that you don’t need to reinvent the wheel.

Backup Databases

The following script will backup all your user databases (applies both to Full Recovery or Simple Recovery)

-- Full backup (keeping 7 last days)
EXECUTE master.dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'N',
@CheckSum = 'Y',
@CleanupTime = 168 -- 168hs =  7-day retention

OBS: “FULL” is for “FULL backup” (as opposed to DIFFERENTIAL backup), and has NOTHING to do with “Full Recovery”.
OBS: USER_DATABASES can be replaced by a single database

Backup Transaction Log

The following script will backup transaction log for your user databases (applies only to Full Recovery)

We should periodically backup the transaction log, so that it doesn’t grow indefinitely. Backing up the transaction log does NOT automatic shrink the transaction log, but ALLOWS us to shrink that log because SQL can only consider part of the transaction log as “unused space” if the respective transactions were backed up before.

-- Transaction Log Backup
EXECUTE master.dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@ChangeBackupType = 'Y'

OBS: Simple Recovery uses transaction log only for uncommited transactions, so there is no Log backup for Simple Recovery
OBS: USER_DATABASES can be replaced by a single database

Switching from Full to Simple Recovery

ALTER DATABASE yourdb SET RECOVERY SIMPLE WITH NO_WAIT;

OBS: The transaction log won’t shrink automatically! See below.

Switching from Simple to Full Recovery:

ALTER DATABASE yourdb SET RECOVERY FULL WITH NO_WAIT;

Shrink the Transaction Log file (LDF)

If the database was in Full Recovery and didn’t had log maintenance, probably the log file became huge.

Shrink the Transaction Log for a database in Simple Recovery Mode

If the database is running in Simple Recovery, you can shrink easily:

-- Shrink the file
USE yourdb;
DBCC SHRINKFILE(yourdb_log, 10); -- 10MB should be enough for us

OBS: yourdb_log is the LOGICAL NAME of the log file, and is not necessarily the same name as the physical file in disk, specially if your database was renamed or restored from a backup with different name.
e.g. if we create a database “Northwind” and later rename or restore into a database “Northwind2012” , the internal name of your log file will be Northwind_log, even if the physical file is called Northwind2012_log.ldf or whatever.
To obtain the logical name of your log file, use this command:
SELECT name FROM sys.database_files where type_desc='LOG'

Shrink the Transaction Log for a database in Full Recovery Mode

If the database is running in Full Recovery, shrinking may be harder. First of all, note that the transaction log has a pointer that indicates which part of the log file was backed up and which part wasn’t backed up. Shrinking can only remove parts which were backup up, so first of all, make a transaction log backup (see above Backup Transaction Log).

To shrink the transaction log, you should use the same shrinking command as used for Simple Recovery (above), however, it’s common that we get an annoying error where the log doesn’t shrink and you get an error Cannot shrink log file 2 (My DB_Log) because the logical log file located at the end of the file is in use.

To prevent/avoid this problem, just backup the transaction log of your database and shrink the log file immediately after:

use master;
EXECUTE master.dbo.DatabaseBackup
@Databases = 'yourdb',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@ChangeBackupType = 'Y';

USE yourdb;
DBCC SHRINKFILE(yourdb_log, 200);

If the transaction log is already too large and you can’t even backup, just truncate the log file by temporarily changing to simple recovery mode:

ALTER DATABASE yourdb SET RECOVERY SIMPLE WITH NO_WAIT;

USE yourdb;
DBCC SHRINKFILE(yourdb_log, 200);

ALTER DATABASE yourdb SET RECOVERY FULL WITH NO_WAIT;

Finally, after shrinking the file, configure it for auto-growth:

USE [master];
GO

ALTER DATABASE yourdb
  MODIFY FILE
  (NAME = yourdb_log, SIZE = 300MB, FILEGROWTH = 50MB);
GO

Maintenance Jobs

Based on the script’s author suggestion, this my suggested schedule for your servers:

User databases:

Integrity check (weekly)

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@PhysicalOnly = 'Y'

Index & Statistics maintenance (weekly)

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Full backup (daily)

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 168 -- 168hs =  7-day retention

Transaction log (daily or hourly [or even more frequent] if we want to set up database mirroring)

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'LOG',
@ChangeBackupType = 'Y'

System databases:

Full backup (daily)
Integrity check (daily)

Server:

sp_delete_backuphistory (weekly)
sp_purge_jobhistory (weekly)
CommandLog cleanup (weekly)
Output file cleanup (weekly)

TO DO: use xp_cmdshell to zip backups, because SQL Express can’t compress backups

TO DO: modify DatabaseBackup script to automatically shrink log file immediately after a LOG backup.

On this post I just wanted to make a cheat sheet and scratch the surface. If you want to gain a deep understanding of Transaction Log backup and shrinking, please refer to this answer and the references linked there.