Click here to Skip to main content
15,867,762 members
Articles / Database Development / SQL Server

Artisan Way of Data Reply

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
10 Jun 2021CPOL10 min read 13.1K   108   3   2
A way to deliver more details about an error or exception from an SQL Server database through an ASP.NET web application to a web client.
This article is a continuation of Artisan.Orm project in which you will learn how to deliver more details about an error or exception from an SQL Server database through an ASP.NET web application to a web client.

This article is a continuation and development of Artisan.Orm project:

The method described here can also be used completely independently.

Introduction

Suppose we have a web application of a three-tier structure:

  • Web SPA client, which requests data from a web server with Ajax
  • Web server and ASP.NET application
  • SQL Server database

Once a web client requests to get or save data, a data reply travels back from the database to UI through the way that might look like this:

Image 1

On this way, all sorts of surprises can happen. An error may raise in a database on SQL Server or an exception may occur in an ASP.NET application on a web server. The final recipient, a web client, should somehow be informed about what had happened: was the request executed correctly or something went wrong.

This article is an attempt to make a convenient and universal format of data reply that provides a possibility to pass the details of exceptional case through the above pipeline from a database to a web client.

Background

Some thoughts and propositions before starting.

Two Categories of Exceptions

Errors and exceptions can be divided into two categories: expected and unexpected.

  • Unexpected exception is the result of a bug in code or equipment failure, the things we hope never should happen in a perfect word.
    Usual reaction on such exceptions: inform a user and an admin about fatal application error.
  • Expected exception is the result of inconsistent data saving, untimely request or other activities where a user is able to solve the issue by himself.
    The examples:
    • Data validation on web server or in database, such as uniqueness of user's login or email.
    • Data concurrency, when two users edit the same record in database at the same time.
    • Data missing, when the first user deletes a record before the second one saves it.
    • Data access denial, when a data reply depends on user access level calculated in database.

Status Delivery via Http

How can a web client distinguish what kind of exception occurs? It is necessary to pass the status to it.

How to deliver this status from a web server to a web client? The first idea that comes to mind is to use the http Status Codes...

And this idea turns out to be worthless... It is like asking the airplane captain to inform your mom that you are in trouble via official radio frequency used for landing and taking off, although you just forgot your lunch box on the kitchen table.

Http Status Codes are used to inform recipients about a transmission status. Browsers use that status to react. And mixing a transmission status and a data reply status sooner or later leads us to a problem of unexpected browser reaction or impossibility to find appropriate code for your needs.

The better idea, I guess, is make a universal format of data reply, something like a wrapping object where Data and Status are the properties.

Seven Steps

The task of the exception details delivery through the data reply pipeline can be divided into several steps:

  1. In a database, find an exceptional case and output the necessary data.
  2. In a repository, recognize the exceptional case and read the data about it.
  3. In a repository, throw an exception, so that a data service can handle it in C# good practice way.
  4. In a data service, gets normal data or catch an exception, and create a universal data reply.
  5. In a ASP.NET Web API controller, serialize a data reply into JSON format.
  6. In Web client data services, get JSON data, define a status of a data reply, take appropriate actions.
  7. In SPA controllers, get a data reply, define a status of a data reply, take appropriate actions.

Universal Format of DataReply

DataReply in JSON

The desirable data wrapper or DataReply object, after ASP.NET Web API controller serializes it to JSON, should have a form of:

dataReply: {
    status: "ok",
    data: {...}, 
    messages: [...]
}

Therefore, a C# object for serialization must have the same public properties. After series of experiments, I found the optimal, at least for me, structure of DataReply class in C#.

DataReply in C#

The DataReply base class has only two properties: Status and Messages.
The derived DataReply<TData> adds the Data property.

Here is the diagram of DataReply class and its properties:

Image 2

And here is the C# code for:

  • DataReplyStatus
  • DataReplyMessage
  • DataReply
  • DataReply<TData>

(Click on tabs.)

C#
public enum DataReplyStatus
{
    Ok          ,
    Fail        ,
    Missing     ,
    Validation  ,
    Concurrency ,
    Denial      ,
    Error
}
C#
[DataContract]
public class DataReplyMessage
{
    [DataMember]
    public String Code;

    [DataMember(EmitDefaultValue = false)]
    public String Text;

    [DataMember(EmitDefaultValue = false)]
    public Int64? Id;

    [DataMember(EmitDefaultValue = false)]
    public Object Value;
}
C#
[DataContract]
public class DataReply {

    [DataMember]
    public DataReplyStatus Status { get; set; }

    [DataMember(EmitDefaultValue = false)]
    public DataReplyMessage[] Messages { get; set; }
        
    public DataReply()
    {
        Status = DataReplyStatus.Ok;
        Messages = null;
    }

    public DataReply(DataReplyStatus status)
    {
        Status = status;
        Messages = null;
    }

    public DataReply(DataReplyStatus status, string code, string text)
    {
        Status = status;
        Messages = new [] { new DataReplyMessage { Code = code, Text = text } };
    }
        
    public DataReply(DataReplyStatus status, DataReplyMessage message)
    {
        Status = status;
        if (message != null)
            Messages = new [] { message };
    }

    public DataReply(DataReplyStatus status, DataReplyMessage[] messages)
    {
        Status = status;
        if (messages?.Length > 0)
            Messages = messages;
    }

    public DataReply(string message)
    {
        Status = DataReplyStatus.Ok;
        Messages = new [] { new DataReplyMessage { Text = message } };
    }

    public static DataReplyStatus? ParseStatus (string statusCode)
    {
        if (IsNullOrWhiteSpace(statusCode))
            return null;
    
        DataReplyStatus status;

        if (Enum.TryParse(statusCode, true, out status))
            return status;
    
        throw new InvalidCastException(
                $"Cannot cast string '{statusCode}' to DataReplyStatus Enum. " +
                $"Available values: {Join(", ", Enum.GetNames(typeof(DataReplyStatus)))}");
    }
}
C#
[DataContract]
public class DataReply<TData>: DataReply {

    [DataMember(EmitDefaultValue = false)]
    public TData Data { get; set; }

    public DataReply(TData data)
    {
        Data = data;
    }

    public DataReply()
    {
        Data = default(TData);
    }

    public DataReply(DataReplyStatus status, string code, string text, TData data) 
           :base(status, code, text) 
    {
        Data = data;
    }

    public DataReply(DataReplyStatus status, TData data) :base(status) 
    {
        Data = data;
    }

    public DataReply(DataReplyStatus status) :base(status) 
    {
        Data = default(TData);
    }

    public DataReply(DataReplyStatus status, string code, string text) 
           :base(status, code, text) 
    {
        Data = default(TData);
    }

    public DataReply(DataReplyStatus status, DataReplyMessage replyMessage) 
           :base(status, replyMessage)
    {
        Data = default(TData);
    }

    public DataReply(DataReplyStatus status, DataReplyMessage[] replyMessages) 
           :base(status, replyMessages)
    {
        Data = default(TData);
    }
}

DataReplyStatus

This Enum contains the statuses I found useful in my projects, and there are no restrictions to reduce the list or extend it.

The meaning of statuses:

Code Usage
Ok The default status when everything had executed as expected
Fail When a query goal was not achieved
Missing When a query did not find a record with Id parameter
Validation When a query found a threat to data integrity
Concurrency When two or more users updated the same record in the same time
Denial When data access is calculated in database, user was not authorised to see the requested data, and you want to inform user about the reasons
Error All unexpected errors and exceptions

DataReplyMessage

The messages are for delivering of additional information about exceptional case.

DataReplyMessage has the following properties:

Code Usage
Code A string identifier of a message
Id An integer identifier of problem record in database that caused an exceptional case
Text Any human readable information for logs or other needs
Value A value that caused an exceptional case

The DataReply object has an array of DataReplyMessages that is enough for describing the exceptional case details of any kind.

How the DataReplyMessages Can Be Used?

Imagine that a user submits a form with many fields. Client validation finds no errors, but server validation does.

For example, server validation finds that login and email are not unique. Then DataReply will have Status = DataReplyStatus.Validation and an array of DataReplyMessages will contain two items:

Code Id Text Value
NON_UNIQUE_LOGIN 15 Login already exists Admin
NON_UNIQUE_EMAIL 15 Email already exists admin@mail.com

A data service is able to log this exception and UI is able to handle it and use this info for error highlighting and appropriate actions.

The DataReplyMessage class has four properties but only Code is mandatory. So if to decorate the other properties with [DataMember(EmitDefaultValue = false)], they will not be serialized to JSON.

Going Through the Steps

Step # 1

In a database, find an exceptional case and output the necessary data.

The database operations may raise errors and throw SqlException in C# code. An SQL Server raiserror command is able to output ErrorNumber, ErrorMessage, ServerState. That is good but not enough. Very often, a client wants to know more details about an error.

In order to output an error details in a DataReply format, it is required to create a special user-defined table type for DataMessages.

SQL
create type dbo.DataReplyMessageTableType as table
(
    Code     varchar(50)     not null ,
    [Text]   nvarchar(4000)      null ,
    Id       bigint              null ,
    [Value]  sql_variant         null ,

    unique (Code, Id)
);

Then in a stored procedure, we can output a status of exceptional case together with its details. As an example, here is a part of SaveUser procedure with check for data concurrency and validity:

SQL
declare 
    @UserId             int             ,
    @Login              varchar(20)     ,
    @Name               nvarchar(50)    ,
    @Email              varchar(50)     ,
    @Concurrency        varchar(20)     = 'Concurrency',
    @Validation         varchar(20)     = 'Validation',
    @DataReplyStatus    varchar(20)     ,
    @DataReplyMessages  dbo.DataReplyMessageTableType;

begin transaction;

if exists -- concurrency 
(
    select * from dbo.Users u with (tablockx, holdlock)
    inner join @User t on t.Id = u.Id and t.[RowVersion] <> u.[RowVersion]
)
begin
    select DataReplyStatus = @Concurrency;

    rollback transaction;
    return;
end
      
begin -- validation

    begin -- check User.Login uniqueness
        select top 1 
            @UserId = u.Id,
            @Login  = u.[Login]
        from
            dbo.Users u
            inner join @User t on t.[Login] = u.[Login] and t.Id <> u.Id;

        if @Login is not null 
        begin 
            set @DataReplyStatus = @Validation;

            insert into @DataReplyMessages
            select Code ='NON_UNIQUE_LOGIN', 'Login is not unique', @UserId, @Login;
        end;
    end;

    begin -- check User.Email uniqueness
        select top 1
            @UserId = u.Id,
            @Email = u.Email
        from
            dbo.Users u
            inner join @User t on t.Email = u.Email and t.Id <> u.Id

        if @Email is not null 
        begin 
            set @DataReplyStatus = @Validation;

            insert into @DataReplyMessages
            select Code ='NON_UNIQUE_EMAIL', 'User email is not unique', @UserId, @Email;
        end;
    end;
            
    select DataReplyStatus = @DataReplyStatus;
                        
    if @DataReplyStatus is not null  
    begin
        select * from @DataReplyMessages;

        rollback transaction;
        return;
    end

end;

-- save the user

-- output the saved user

Note the output pattern for an exceptional case:

SQL
select DataReplyStatus = @DataReplyStatus;
                    
if @DataReplyStatus is not null
begin
    select * from @DataReplyMessages;

    rollback transaction;
    return;
end

The first is status output. If status is not empty, then messages output, rollback and return.

Step # 2

In a repository, recognize the exceptional case and read the data about it

In repository method, we follow the above output pattern and throw DataReplyException with Status and Messages from a stored procedure:

C#
public User SaveUser(User user)
{
    return GetByCommand(cmd =>
    {
        cmd.UseProcedure("dbo.SaveUser");

        cmd.AddTableRowParam("@User", user);

        return cmd.GetByReader(dr => 
        {
            var statusCode = dr.ReadTo<string>(getNextResult: false);

            var dataReplyStatus = DataReply.ParseStatus(statusCode);

            if (dataReplyStatus != null )
            {
                if (dr.NextResult())
                    throw new DataReplyException(dataReplyStatus.Value, 
                                                 dr.ReadToArray<DataReplyMessage>());

                throw new DataReplyException(dataReplyStatus.Value);
            }

            dr.NextResult();
            
            var savedUser = reader.ReadTo<User>()

            return savedUser;
        });
    });
}

The above repository method is written with Artisan.Orm ADO.NET extension methods. But the code may be rewritten to use the regular ADO.NET methods.

Step # 3

In a repository, throw an exception, so that a data service can handle it in C# good practice way.

The DataReplyException in the above code sample is a custom exception that has two additional properties, Status and Messages:

C#
public class DataReplyException: Exception
{
    public DataReplyStatus Status { get; } = DataReplyStatus.Error;

    public DataReplyMessage[] Messages { get; set; }

    public DataReplyException(DataReplyStatus status, DataReplyMessage[] messages)
    {
        Status = status;
        Messages = messages;
    }
}

So the SaveUser repository method:

  • in normal case returns a User object
  • in expected exceptional case returns DataReplyException with status and messages
  • in unexpected exceptional case returns the regular SqlException

Step # 4

In a data service, gets normal data or catch an exception, and create universal data reply.

A data service is a layer where all the exceptions from a repository are intercepted, logged and transformed into appropriate format for Web API controller.

And data service is the place where data from a repository method is wrapped with a DataReply.

C#
public DataReply<User> SaveUser(User user)
{
    try 
    {
        var user = repository.SaveUser(user);

        return new DataReply<User>(user);
    }
    catch (DataReplyException ex)
    {
        // log exception here, if necessary

        return new DataReply<User>(ex.Status, ex.Messages);
    }
    catch (Exception ex) 
    {
        var dataReplyMessages = new [] 
        {
            new DataReplyMessage { Code = "ERROR_MESSAGE"  , Text = ex.Message },
            new DataReplyMessage { Code = "STACK_TRACE"    , 
                                   Text = ex.StackTrace.Substring(0, 500) }
        };        

        // log exception here, if necessary

        return new DataReply<User>(DataReplyStatus.Error, dataReplyMessages);
    }
}

The above SaveUser method:

  • in normal case, returns a DataReply object with Status = DataReplyStatus.Ok and Data = User;
  • in expected exceptional case, returns DataReply with a Status from DataReplyStatus Enum list and Messages from a stored procedure;
  • in unexpected exceptional case, returns a DataReply object with Status = DataReplyStatus.Error and Messages containing the original exception Message and StackTrace.

Of course, it is a bad idea to send StackTrace to a web client and your should never do this in production stage, but in development - it is useful thing.

Step # 5

In an ASP.NET Web API controller, serialize the data respond into JSON format.

SaveUser method in ASP.NET Web API Controller looks like this:

C#
[HttpPost]
public DataReply<User> SaveUser(User user) 
{ 
    using (var service = new DataService()) 
    {
        return service.SaveUser(user);
    } 
}

Because of the annotation attribute [DataMember(EmitDefaultValue = false)] for Data and Messages properties of DataReply, if they are Null, they are omitted. So:

  • in normal case, the JSON string is:

    JavaScript
    {
        "status" : "ok",
        "data"   : {"id":1,"name":"John Smith"} 
    }
  • in expected exceptional case with Status = DataReplyStatus.Validation, the JSON string is:

    JavaScript
    {
        "status"  : "validation",
        "messages" : [
            {"code":"NON_UNIQUE_LOGIN", "text":"Login is not unique", 
             "id":"1","value":"admin"},
            {"code":"NON_UNIQUE_EMAIL", "text":"User email is not unique", 
             "id":"1","value":"admin@mail.com"}
        ]
    }     
  • in unexpected exceptional case with Status = DataReplyStatus.Error and Messages containing the original exception Message and StackTrace, the JSON string is:

    JavaScript
    {
        "status": "error",
        "messages" : [
            {"code":"ERROR_MESSAGE", "text":"Division by zero"},
            {"code":"STACK_TRACE", "text":"Tests.DAL.Users.Repository.
                     <>c__DisplayClass8_0.<SaveUser>b__0(SqlCommand cmd) ..."}
      ]
    }       

Step # 6

In Web client data services, get JSON data, define a status of a data reply, take appropriate actions.

Below is the example of JavaScript and AngularJs code for a web client dataService. Because all the data requests and replies go through the single dataService and its methods, it is easy to handle the data replies with certain statuses using unified approach.

JavaScript
(function () {

    "use strict";

    angular.module("app")

    .service('dataService', function ( $q ,  $http ) {

        var error = {status : "error"};

        var allowedStatuses = 
            ["ok", "fail", "validation", "missing", "concurrency", "denial"];

        this.save = function (url, savingObject) {          

            var deferred = $q.defer();

            $http({
                method: 'Post',
                url: url,
                data: savingObject
            })
                .success(function (reply, status, headers, config) {
                    if ($.inArray((reply || {}).status, allowedStatuses) > -1) {
                        deferred.resolve(angular.fromJson(reply) );
                    } else {
                        showError(reply, status, headers, config);
                        deferred.resolve(error);
                    }
                })
                .error(function (data, status, headers, config) {
                    showError(data, status, headers, config);
                    deferred.resolve(error);
                });

            return deferred.promise;
        };

        function showError(data, status, headers, config) {
            
           // inform a user about unexpected exceptional case
        
        };

    });

})();

Step # 7

In SPA controllers, get a data reply, define a status of a data reply, take appropriate actions.

And finally, here is the example of how the DataReply can be handled in a web client controller:

JavaScript
function save() {

    userService.save('api/users' $scope.user)
        .then(function (dataReply) {
            if (dataReply.status === 'ok') {
                var savedUser = dataReply.data;
                $scope.user = savedUser;
            }
            else if (dataReply.status === 'validation' ){
                for (var i = 0; i < dataReply.messages.length; i++) {
                    
                    if (dataReply.messages[i].code === 'NON_UNIQUE_LOGIN') {
                        // highlight the login UI control
                    }
                    else if (dataReply.messages[i].code === 'NON_UNIQUE_EMAIL') {
                        // highlight the email UI control
                    }
                }
            }
            else if (dataReply.status === 'concurrency' ){
                // message about concurrency
            }
        });
}

Conclusion

The DataReply idea was dictated by the urgent need to transmit more details about exceptional cases to a web client during complex object graphs saving.

While saving complex object, the inconsistency or other problems may occur in any part of it. The task to collect and deliver all the possible issues about data at once required an appropriate transport. The DataReply became such a solution.

Having tried this idea on several real projects, it can be said that it proved its universality, effectiveness and right to life.

DataReply in Artisan.Orm

DataReply, DataReplyStatus Enum, DataReplyMessages and DataReplyException are now part of Artisan.Orm.

The initial article about Artisan.Orm is here.

If you are interested in the project, please visit Artisan.Orm GitHub page and its documentation wiki.

GitHub + Wiki

Artisan.Orm is also available as NuGet Package.

NuGet

About the Source Code

The attached archive contains a copy of the Artisan.Orm solution from GitHub, created in Visual Studio 2015, which consists of three projects:

  • Artisan.Orm - DLL project which contains the Artisan.Orm classes
  • Database - SSDT project to create database for SQL Server 2016 to provide data for testing
  • Tests - Test project with examples of the code use

In order to install the database and run the tests, change the connection string in file Artisan.publish.xml and in App.config to yours.

History

  • 16th April, 2017
    • Initial publication
    • Artisan.Orm source code of version 1.1.0

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Russian Federation Russian Federation
"It is best to erase all personal history, because that would make us free from the encumbering thoughts of other people."

Journey to Ixtlan by Carlos Castaneda

Comments and Discussions

 
QuestionApiController Pin
jamie hennings16-Apr-17 21:50
jamie hennings16-Apr-17 21:50 
AnswerRe: ApiController Pin
Vadim Loboda17-Apr-17 0:41
Vadim Loboda17-Apr-17 0:41 
An MVC controller is good to get HTML.
Although it can do JSON, File, Redirect, Session and other cool stuff.

An API controller is good to get data in JSON format.
Although it can do HTML, XML and other cool stuff.

If you build SPA and your HTML is static, you don't need MVC controller.

If you build regular ASP.NET MVC application and do not use AJAX, you don't need API controller.

If you build SPA, generate HTML dynamically (for example, different templates for different roles) and use AJAX, then take the best from two world - use and MVC controller, and API controller.

modified 18-Apr-17 0:10am.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.