Persist Method Execution Metadata In Oracle Using ASP.NET Core

 

Introduction

This article presents a discussion on how we can capture method execution metadata such as execution time, method name, controller name, etc, and save these details in an Oracle database. The database used is Oracle and Entity Developer is used to connect to the database.

Pre-requisites

You’ll need the following tools to deal with code examples:

• Visual Studio 2019 Community Edition (download)
• Oracle Express Edition (download)
• dotConnect for Oracle (download)

Create a new ASP.NET Core Web API Project

Earlier, we mentioned the necessary tools to proceed to the practical scenarios. The time has come to use those tools.

First, we need to create a new ASP.NET Core Web API project,

1. Open Visual Studio 2019.
2. Click Create a new project.
3. Select ASP.NET Core Web Application and click Next.
4. Specify the project name and location to store that project in your system. Optionally, checkmark the Place solution and project in the same directory checkbox.
5. Click Create.
6. In the Create a new ASP.NET Core Web Application window, select API as the project template.
7. Select ASP.NET Core 5 or later as the version.
8. Disable the Configure for HTTPS and Enable Docker Support options (uncheck them).
9. Since we won’t use authentication in this example, specify authentication as No Authentication.
10. Click Create to finish the process.

Now, create a new API controller named DefaultController. Replace the default generated code of the controller class with the following code:

[Route("api/[controller]")]
[ApiController]
public class DefaultController: ControllerBase {
    [HttpGet]
    public string Get() {
        Thread.Sleep(100);
        return "Hello World!";
    }
}

C#

We’ll use this project in the subsequent sections of this article.

Install NuGet Packages

To get started you should install the dotConnect for Oracle package in your project. You can install it either from the NuGet Package Manager tool inside Visual Studio or, from the NuGet Package Manager console using the following command:

PM> Install-Package dotConnect.Express.for.Oracle

BASIC

If the installation is successful, you're all set to get started using dotConnect for Oracle.

Retrieving Execution Metadata from an Action Method

In this section, we’ll examine how we can retrieve method execution metadata of an action method in ASP.NET Core.

Create an Action Filter

Create a new action filter class named MethodExecutionMetadataFilter by extending the ActionFilterAttribute class. Replace the default generated code with the following code:

public class MethodExecutionMetadataFilter: ActionFilterAttribute {
    Stopwatch stopWatch;
    string headerKey = "Execution Time: ";
    public override void OnActionExecuting(ActionExecutingContext context) {
        stopWatch = new Stopwatch();
        stopWatch.Start();
        string data = context.HttpContext.Response.Headers.ContainsKey(headerKey) ? context.HttpContext.Request.Headers[headerKey][0] : null;
        if (data == null) context.HttpContext.Response.Headers.Add(headerKey, string.Empty);
    }
    public override void OnActionExecuted(ActionExecutedContext context) {
        stopWatch.Stop();
        string data = context.HttpContext.Response.Headers.ContainsKey(headerKey) ? context.HttpContext.Response.Headers[headerKey][0] : null;
        if (string.Empty == data) context.HttpContext.Response.Headers[headerKey] = stopWatch.ElapsedMilliseconds.ToString() + " ms";
    }
}

C#

There are two overridden methods in the MethodExecutionMetadata class, OnActionExecuting and OnActionExecuted. While the former is executed before an action method is executed, the latter is executed once the execution of an action method is over. The StopWatch is started in the OnActionExecuting method, and the elapsed time is retrieved from the ElaspedMilliseconds property and then stored in the response header using the header key "Execution Time: ".

When you execute the application from Postman, here’s how the output will look like,

Figure 1

Let’s now retrieve the controller and method names of the currently running action method. Create a class named MethodExecutionMetadata in a file having the same name and write the following code there:

public class MethodExecutionMetadata {
    public string MethodName {
        get;
        set;
    }
    public string ControllerName {
        get;
        set;
    }
    public string ExecutionTime {
        get;
        set;
    }
}

C#

Now replace the existing source code of the MethodExecutionMetadataFilter with the following code:

public class MethodExecutionMetadataFilter: ActionFilterAttribute {
    MethodExecutionMetadata methodExecutionMetadata = new
    MethodExecutionMetadata();
    Stopwatch stopWatch;
    string headerKey = "Execution Time: ";
    public override void OnActionExecuting(ActionExecutingContext context) {
        string controller = null;
        string method = null;
        var routeValues = context.HttpContext.Request.RouteValues;
        if (routeValues.ContainsKey("controller")) controller = (string) routeValues["controller"];
        if (routeValues.ContainsKey("action")) method = (string) routeValues["action"];
        stopWatch = new Stopwatch();
        stopWatch.Start();
        string data = context.HttpContext.Response.Headers.ContainsKey(headerKey) ? context.HttpContext.Request.Headers[headerKey][0] : null;
        if (data == null) context.HttpContext.Response.Headers.Add(headerKey, string.Empty);
    }
    public override void OnActionExecuted(ActionExecutedContext context) {
        stopWatch.Stop();
        string data = context.HttpContext.Response.Headers.ContainsKey(headerKey) ? context.HttpContext.Response.Headers[headerKey][0] : null;
        if (string.Empty == data) context.HttpContext.Response.Headers["Execution Time: "] = stopWatch.ElapsedMilliseconds.ToString() + " ms";
    }
}

C#

Create Database Table

Use the following script to create a new database table in Oracle.

create table methodexecutionmetadata
  (
    id number GENERATED BY DEFAULT ON NULL AS IDENTITY,
    method_name varchar2(50) not null,
    controller_name varchar2(50) not null,
    execution_time varchar2(50) not null
  );

SQL

We’ll use this table to store method execution metadata of our action method.

Creating an OracleConnection

Now provide the Oracle database credentials in your application to establish a connection to the database. You can save this information configurable by storing in the application’s config file as well.

The code snippet given below illustrates how you can create an instance of OracleConnection:

String connectionString = "User Id=Your user Id; Password=Your password; Server = localhost; License Key = Specify your license key here; ";
OracleConnection oracleConnection = new OracleConnection(connectionString);

C#

You should include the following namespace in your program:

using Devart.Data.Oracle;

C#

Persist Method Execution Metadata in Oracle Database Table

The following code listing illustrates how you can save method execution metadata in a database table in Oracle:

string connectionString = "User Id=system; Password=your password; Server = localhost";
try {
    using(OracleConnection oracleConnection = new OracleConnection(connectionString)) {
        OracleCommand command = new OracleCommand("INSERT INTO MethodExecutionMetadata (method_name,
            controller_name, execution_time)
        " +
        "VALUES(:method_name, :controller_name, :execution_time)",
        oracleConnection);
    OracleParameter oracleParameterMethodName = command.Parameters.Add("method_name", OracleDbType.VarChar);
    oracleParameterMethodName.OracleValue = methodExecutionMetadata.MethodName;
    OracleParameter oracleParameterControllerName = command.Parameters.Add("controller_name", OracleDbType.VarChar);
    oracleParameterControllerName.OracleValue = methodExecutionMetadata.ControllerName;
    OracleParameter oracleParameterExecutionTime = command.Parameters.Add("execution_time", OracleDbType.VarChar);
    oracleParameterExecutionTime.OracleValue = methodExecutionMetadata.ExecutionTime;
    command.ExecuteNonQuery();
}
} catch {
    throw;
}

C#

The above code should be written inside the OnMethodExecuted method of our action filter class. The following is the complete source code of the action filter class:

public class MethodExecutionMetadataFilter: ActionFilterAttribute {
    MethodExecutionMetadata methodExecutionMetadata = new
    MethodExecutionMetadata();
    Stopwatch stopWatch;
    string headerKey = "Execution Time: ";
    public override void OnActionExecuting(ActionExecutingContext context) {
        string controller = null;
        string method = null;
        var routeValues = context.HttpContext.Request.RouteValues;
        if (routeValues.ContainsKey("controller")) controller = (string) routeValues["controller"];
        if (routeValues.ContainsKey("action")) method = (string) routeValues["action"];
        stopWatch = new Stopwatch();
        stopWatch.Start();
        string data = context.HttpContext.Response.Headers.ContainsKey(headerKey) ? context.HttpContext.Request.Headers[headerKey][0] : null;
        if (data == null) context.HttpContext.Response.Headers.Add(headerKey, string.Empty);
    }
    public override void OnActionExecuted(ActionExecutedContext context) {
        stopWatch.Stop();
        string data = context.HttpContext.Response.Headers.ContainsKey(headerKey) ? context.HttpContext.Response.Headers[headerKey][0] : null;
        if (string.Empty == data) context.HttpContext.Response.Headers["Execution Time: "] = stopWatch.ElapsedMilliseconds.ToString() + " ms";
        string connectionString = "User Id=system; Password=your password;
        Server = localhost ";
        try {
            using(OracleConnection oracleConnection = new OracleConnection(connectionString)) {
                OracleCommand command = new OracleCommand("INSERT INTO MethodExecutionMetadata (method_name,
                    controller_name, execution_time)
                " +
                "VALUES(:method_name, :controller_name, :execution_time)",
                oracleConnection);
            OracleParameter oracleParameterMethodName = command.Parameters.Add("method_name", OracleDbType.VarChar);
            oracleParameterMethodName.OracleValue = methodExecutionMetadata.MethodName;
            OracleParameter oracleParameterControllerName = command.Parameters.Add("controller_name", OracleDbType.VarChar);
            oracleParameterControllerName.OracleValue = methodExecutionMetadata.ControllerName;
            OracleParameter oracleParameterExecutionTime = command.Parameters.Add("execution_time", OracleDbType.VarChar);
            oracleParameterExecutionTime.OracleValue = methodExecutionMetadata.ExecutionTime;
            command.ExecuteNonQuery();
        }
    }
    catch {
        throw;
    }
  }
}

C#

Apply the MethodExecutionTimeFilter

You can apply the MethodExecutionTimeFilter to our controllers or the action methods of our controller classes. The following code snippet illustrates how this filter can be applied at the controller level:

[Route("api/[controller]")]
[ApiController]
[MethodExecutionMetadataFilter]
public class DefaultController: ControllerBase {
    [HttpGet]
    public string Get() {
        Thread.Sleep(100);
        return "Hello World!";
    }
}

C#

Summary

This article discussed how to capture and store method execution metadata in an ASP.NET Core application. You can use this knowledge to capture and store more detailed information of your action methods.