RSS

Tag Archives: web-api

Entity Framework, .Net and SQL Server Table Valued Parameter

This is step by step setup of using SQL Server TVP (Table Valued Parameter) in .Net application with EF (Entity Framework). In this example, I use SQL Server 2016 (SP2-CU3), .Net 4.5.1 and EF 6.20.

1. Create a table to store data.

CREATE TABLE [dbo].[Something] (
    [Id]            INT	IDENTITY(1,1)   NOT NULL,
    [Name]          VARCHAR(150)        NOT NULL,
    [Status]        CHAR(1)             NOT NULL,
    CONSTRAINT [PK_Something_Id] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

2. Create `User Defined Table Type` in SQL Server. For simplicity, in this example the type’s columns are same as table I created on step 1. In real-world, the type’s columns could be significantly different than table where we store the data, it might even used for join with other tables.

CREATE TYPE [dbo].[udt_Something] AS TABLE (
	[Name]		VARCHAR(150)	NOT NULL,
	[Status]	CHAR(1)		NOT NULL,
);
GO

3. Create stored procedure to take parameter (of a `User Defined Table` type we created earlier) and perform necessary task to persist our data.

CREATE PROCEDURE [dbo].[sp_SaveSomething]
	@udt_Something [dbo].[udt_Something] READONLY
AS
BEGIN
    INSERT INTO [dbo].[Something]
        SELECT *
        FROM @udt_Something
END
GO

4. Create extension method to convert `IEnumerable<T>` object to a `DataTable` object. In order to use SQL TVP, we have to pass our parameter as a `DataTable`. This method will help convert our data to `DataTable` type.
Helper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace QC
{
    public static class Helper
    {
        public static DataTable ToDataTable<T>(this IEnumerable<T> enumerable, IEnumerable<string> orderedColumnNames)
        {
            var dataTable = new DataTable();

            // Get all properties of the object
            PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            PropertyInfo[] readableProperties = properties.Where(w => w.CanRead).ToArray();

            // Get column
            var columnNames = (orderedColumnNames ?? readableProperties.Select(s => s.Name)).ToArray();

            // Add columns to data table
            foreach (string name in columnNames)
            {
                dataTable.Columns.Add(name, readableProperties.Single(s => s.Name.Equals(name)).PropertyType);
            }

            // Add rows to data table from object
            foreach (T obj in enumerable)
            {
                dataTable.Rows.Add(columnNames.Select(s => readableProperties.Single(s2 => s2.Name.Equals(s)).GetValue(obj)).ToArray());
            }

            return dataTable;
        }
    }
}

5. For the purpose of this example, let’s say we want to save a collection of objects. This is our object definition.
Something.cs

namespace QC
{
    public class Something
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Status { get; set; }
    }
}

6. Using EF, called stored procedure we created and pass in `SqlParameter`, which is a collection of objects that we converted to `DataTable`. Don’t forget to specify parameter type as `User Defined Table Type`.
DataAccess.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QC
{
    public class DataAccess
    {
        public void Save(IEnumerable<Something> data)
        {
            // Columns for ordering, the order of the columns must be the same as user defined table type
            var orderedCols = new[] { "Name", "Status" };

            // SQL parameter to pass to stored procedure
            var param = new SqlParameter("@udt_Something", SqlDbType.Structured);
            param.Value = data.ToDataTable(orderedCols);
            param.TypeName = "dbo.udt_Something";

            try
            {
                // QCDB is our EF entities
                using (var db = new QCDB())
                {
                    // Call stored procedure and pass in table valued parameter
                    db.Database.ExecuteSqlCommand("EXEC dbo.sp_SaveSomething @udt_Something", param);
                }
            }
            catch
            {
                throw;
            }
        }
    }
}

7. Example of usage.
OpsController.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QC
{
    public class OpsController : ApiController
    {
        public void SaveSomething()
        {
            var data = new List<Something>();
            data.Add(new Something { Id = 1, Name = "Chap", Status = "A" });
            data.Add(new Something { Id = 2, Name = "Stick", Status = "D" });

            var dataAccess = new DataAccess();
            dataAccess.Save(data);
        }
    }
}
Advertisements
 
Leave a comment

Posted by on January 18, 2019 in General

 

Tags: , , , , , , , , ,

WebApi .Net – Add DelegatingHandler to Get Request Body

Out of the box, WebApi pipeline bind request body to parameter, therefore WebApi Controller (which inherit from ApiController) `Request.Content` object is empty.

For example, passing this json in the request body:

[
    {
        "Name": "Test",
        "Status": "C",
    }
]

Will bind it to WebApi Controller’s parameter, however, `Request.Content` object is empty.

screenshot message handler

Overcome this issue by creating `DelegatingHandler` to add request body back.

RequestHandler.cs

using System.Net.Http;
using System.Threading;
using System.Threading.Tasks;

namespace QC
{
    public class RequestHandler : DelegatingHandler
    {
        protected override async Task SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
        {
            // Request body stream is emptied when WebApi bind request body to parameter, here we add original request body back so we can access it
            if (request.Content != null)
            {
                string body = await request.Content.ReadAsStringAsync();
                request.Properties["body"] = body;
            }

            return await base.SendAsync(request, cancellationToken);
        }
    }
}

Register in WebApiConfig.

WebApiConfig.cs

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        config.MessageHandlers.Add(new RequestHandler());
    }
}

Viola! `Request.Content` contains request body.

screenshot message handler2

Code is from here.

 
Leave a comment

Posted by on January 18, 2019 in General

 

Tags: , , , , ,

Web API .Net with Basic Authentication

Github project.

Notes:
1. This work on .Net framework 4.6.1.
2. Authorization part is not covered.

The core authentication code is in `Security/BasicAuthAttribute.cs`. This class inherit from following:

ActionFilterAttribute
So we can use it as attribute to decorate controllers or actions.

IAuthenticationFilter
To invoke WebApi’s authentication pipeline. Some developer like to use `IActionFilter` for authentication, while it may work, it is not a best practice as `IActionFilter` execute later in the WebApi stack.

IAuthenticationFilter implement 2 methods:
1. `AuthenticateAsync`. Run first. This is code to authentication user. Caller pass in credential in request header. First we begin by parsing the header and user name/password credential caller passed in. Then authenticate user, in Github project, I add user to generic principal but in production app, you should validate credential against security provider (ie: ADFS, Auth0), etc.
2. `ChallengeAsync`. Run after `AuthenticateAsync`. This is where authentication failed and we can challenge caller to prove them selves, which is done by passing `Authorization Basic` in response header.

Usage
There are 3 ways to use this attribute in WebApi.
1. Globally. Every actions will require authentication.

WebApiConfig.cs

public static void Register(HttpConfiguration config)
{
    // Add global authentication
    config.Filters.Add(new BasicAuthAttribute());

    // Web API routes
    config.MapHttpAttributeRoutes();

    config.Routes.MapHttpRoute(
        name: "DefaultApi",
        routeTemplate: "api/{controller}/{id}",
        defaults: new { id = RouteParameter.Optional }
    );
}

2. In entire controller. Every actions under that controller will require authentication. Notice the `[BasicAuth]` decoration.

ValuesController.cs

[BasicAuth]
public class ValuesController : ApiController
{
    public IEnumerable Get()
    {
        return new string[] { "value1", "value2" };
    }
            
    public string Get(int id)
    {
        return "value";
    }
}

3. In specific action. Notice the `[BasicAuth]` decoration.

ValuesController.cs

public class ValuesController : ApiController
{
    public IEnumerable Get()
    {
        return new string[] { "value1", "value2" };
    }

    [BasicAuth]
    public string Get(int id)
    {
        return "value";
    }
}
 
Leave a comment

Posted by on January 11, 2019 in General

 

Tags: , , , ,

WebApi ExceptionHandling.ExceptionHandler Sometimes Does Not Handle Global Exception. What ?!

See previous post on how to implement ExceptionHandler.

ExceptionHandler supposed to handle exception globally, but sometimes it does not… what ?!

There are few cases where ExceptionHandler will not capture exception. One of them is when CORS is enabled, see this.

To ensure ExceptionHandling to always capture exception, try:
1. If inherit from ExceptionHandler, override its method `ShouldHandle()`.

        public override bool ShouldHandle(ExceptionHandlerContext context)
        {
            return true;
        }

2. Instead of ExceptionHandling.ExceptionHandler, inherit and implement it’s interface `ExceptionHandling.IExceptionHandler`.

 
Leave a comment

Posted by on November 2, 2018 in General

 

Tags: , , , ,

 
%d bloggers like this: