Audit Management - Used common table.

There are varies methods available for audit management. I would like to shared here one of method I followed in my recent project.

Approach is create a common table to records audit data in a Jason format. Implementation is  very simple and easy. bellow method will support bulk insert/update as well. 

Followed following steps:

01. Create common table

03. Create SP helper to generate trigger passing table Name and schema.  

01. Table Structure  :
Uid |TableName | OldValues | NewValues | CreatedDate |HostName |DbLogin

02. Create Helper SP to generate audit trigger,

ALTER PROCEDURE [dbo].[AuditTrigerHelper]
@SCHEMA as Nvarchar(128),
@TABLE_NAME as nvarchar(128)
AS
BEGIN
   DECLARE @sql varchar(8000);

    SELECT @sql = 'CREATE TRIGGER '+@SCHEMA +'.[trg_Audit_' + @TABLE_NAME + '] ON  ' + @SCHEMA + '.' + @TABLE_NAME + '
   AFTER UPDATE,INSERT,DELETE
AS
Declare
@TableName as nvarchar(128) ='''+ @SCHEMA +'.' + @TABLE_NAME +''',
@KeyValue as Nvarchar(128),
@OldValue as Nvarchar(max),
@NewValue as Nvarchar(max);
BEGIN
--get only delete records
IF EXISTS(SELECT 1 FROM deleted A
LEFT JOIN inserted B on A.Uid = B.Uid
where B.Uid IS NULL)
BEGIN
INSERT INTO [Audit].[Audit](
TableName
,KeyValue
,OldValues
,NewValues
,CreatedDate
,HostName
,DbLogin)
SELECT
   @TableName,
    A.Uid,
AA.JSON_Data as OldValue
,null as newValues
,GETDATE(),
    HOST_NAME(),
    SYSTEM_USER
from deleted A
Cross APPLY (select JSON_Data = (
select AB.* from deleted AB
where A.Uid = AB.Uid for JSON Path)) AA
END

--check insert and update records
;with CTE_inserted AS (
SELECT A.Uid, AA.JSON_Data as NewValue
from inserted A
Cross APPLY (select JSON_Data = (
select AB.* from inserted AB
where A.Uid = AB.Uid
for JSON Path)) AA
)
,CTE_deleted AS (
SELECT A.Uid, AA.JSON_Data as OldValue
from deleted A
Cross APPLY (select JSON_Data = (
select AB.* from deleted AB
where A.Uid = AB.Uid
for JSON Path)) AA
)

INSERT INTO [Audit].[Audit](
TableName
,KeyValue
,OldValues
,NewValues
,CreatedDate
,HostName
,DbLogin)
SELECT
   @TableName,
   c.Uid,
   D.OldValue,
   C.NewValue,  
   GETDATE(),
   HOST_NAME(),
   SYSTEM_USER
from CTE_inserted C
LEFT JOIN  CTE_deleted D on C.uid = D.UID
END'
--select @sql
EXEC(@sql)
END

04.  Used Audit TriggerHelper to generate sql table trigger passing the table Name and schema
-- ex:   Exec EXEC AuditTrigerGenerationHelper 'schemaName' ,'TableName'

05. Trigger will be generated as below,

Create TRIGGER [schemaName].[trg_Audit_TableName] ON  [schemaName].[TableName]
   AFTER UPDATE,INSERT,DELETE
AS
Declare
@TableName as nvarchar(128) =schemaName.TableName',
@KeyValue as Nvarchar(128),
@OldValue as Nvarchar(max),
@NewValue as Nvarchar(max);
BEGIN
--get only delete records
IF EXISTS(SELECT 1 FROM deleted A
LEFT JOIN inserted B on A.Uid = B.Uid
where B.Uid IS NULL)
BEGIN
INSERT INTO [Audit].[Audit](
TableName
,KeyValue
,OldValues
,NewValues
,CreatedDate
,HostName
,DbLogin)
SELECT
   @TableName,
    A.Uid,
AA.JSON_Data as OldValue
,null as newValues
,GETDATE(),
    HOST_NAME(),
    SYSTEM_USER
from deleted A
Cross APPLY (select JSON_Data = (
select AB.* from deleted AB
where A.Uid = AB.Uid for JSON Path)) AA
END

--check insert and update records
;with CTE_inserted AS (
SELECT A.Uid, AA.JSON_Data as NewValue
from inserted A
Cross APPLY (select JSON_Data = (
select AB.* from inserted AB
where A.Uid = AB.Uid
for JSON Path)) AA
)
,CTE_deleted AS (
SELECT A.Uid, AA.JSON_Data as OldValue
from deleted A
Cross APPLY (select JSON_Data = (
select AB.* from deleted AB
where A.Uid = AB.Uid
for JSON Path)) AA
)

INSERT INTO [Audit].[Audit](
TableName
,KeyValue
,OldValues
,NewValues
,CreatedDate
,HostName
,DbLogin)
SELECT
   @TableName,
   c.Uid,
   D.OldValue,
   C.NewValue,  
   GETDATE(),
   HOST_NAME(),
   SYSTEM_USER
from CTE_inserted C
LEFT JOIN  CTE_deleted D on C.uid = D.UID

END

Clean Code for C# developer

01. Naming Convention 

Names should be readable, meaningful and better to use camel case notation,  avoid using bad names,  Hungarian notation and Misleading Names,

Ex: 

    //Bad Code

            int a;  // bad naming

            int iNumber; string strEmployeeName ;// Hungarian notation

            var dbData = db.entityService.GetAllEmployee(); // misleading Naming

           

            var employeesalary;  //missing camelCase notation  

            public double GetTotalAmount(decimal unitprice, int quantity)

            {

                    // some logic

            }

           

  //Good Code

            int numberOfDays  // readable meaningful name

            var employeeList = factory.employeeService.GetAllEmployee();

           

             var employeeSalary;  //use camlecase Notation  

             public double GetTotalAmount(decimal unitPrice, int quantity)

             {

                    // some logic

             }

02. Avoid nesting too deeply 

Too many if else statements can make the code hard to follow.  Do not used if else statements,  avoid else statement, Explicit is better than implicit.

ex 1: 

//Bad

public bool IsValidDay(string day)

{

    if (!string.IsNullOrEmpty(day))

    {

        day = day.ToLower();

        if (day == "friday")

        {

            return true;

        }

        else if (day == "saturday")

        {

            return true;

        }

        else if (day == "sunday")

        {

            return true;

        }

        else

        {

            return false;

        }

    }

    else

    {

        return false;

    }

}

//Good

public bool IsValidDay(string day)

{

    if (string.IsNullOrEmpty(day))

    {

        return false;

    }

    var openingDays = new[] { "friday", "saturday", "sunday" };

    return openingDays.Any(d => d == day.ToLower());

}

03. Avoid Magic String

keep string on centralized pace and used it.  

            //Bad Code

            if(userRole == "Admin"){

            }

            //Good Code

            const string ADMIN_ROLE = "Admin"

            if (userRole == ADMIN_ROLE)

            {

                // logic in here

            }

04. Don't repeat class name in variable 

//Bad code

public class Account{

    public string AccountName{get;set;}

    public string AccountCurrency {get;set;}

    public string AccountCode {get;set;}

}

//Good code

public class Account{

    public string Name{get;set;}

    public string Currency {get;set;}

    public string Code {get;set;}

}

05. Use default variable than short circuit or conditional 

//Bad code

public void ProcessOrder(DateTime orderDate){

    var date = orderDate != null? orderDate: Datetime.Today()

}

//Good Code

public void ProcessOrder(DateTime orderDate =  DateTime.Today()){

    var orderDate = orderDate

}

06.  Avoid mental mapping , keep it easy to read 

        //bad code     

        var l = new []{"USA","Sri Lanka","India","Singapore"}

            for(int i=0;i<l.Count();i++){

                //code logic here

                var country = l[i];

                //code logic here

                 DoSomething(country);

            }

    //good code

            var countryList = new []{"USA","Sri Lanka","India","Singapore"}

            foreach(var country in countryList){

                //code logic here,

                DoSomething(country);

            }

07. Use Searchable name 

 //bad code

            var data = new { Name = "Kamal", Age = 34 };

           

    //good code

            var person = new Person

            {

                Name = "Kamal",

                Age = 42

            };

08.  Avoid Type Checking 

Always try to used strongly type object, 

public Path TravelToTexas(object vehicle)

{

    if (vehicle.GetType() == typeof(Bicycle))

    {

        (vehicle as Bicycle).PeddleTo(new Location("texas"));

    }

    else if (vehicle.GetType() == typeof(Car))

    {

        (vehicle as Car).DriveTo(new Location("texas"));

    }

}

public Path TravelToTexas(Traveler vehicle)

{

    vehicle.TravelTo(new Location("texas"));

}

//OR pattern matching

public Path TravelToTexas(object vehicle)

{

    if (vehicle is Bicycle bicycle)

    {

        bicycle.PeddleTo(new Location("texas"));

    }

    else if (vehicle is Car car)

    {

        car.DriveTo(new Location("texas"));

    }

}

09. Method should have single responsibility

//Bad Code

public void SendEmailToListOfClients(string[] clients)

{

    foreach (var client in clients)

    {

        var clientRecord = db.Find(client);

        if (clientRecord.IsActive())

        {

            Email(client);

        }

    }

}

//Good Code

public void SendEmailToListOfClients(string[] clients)

{

    var activeClients = GetActiveClients(clients);

    // Do some logic

}

public List<Client> GetActiveClients(string[] clients)

{

    return db.Find(clients).Where(s => s.Status == "Active");

}

10.   Avoid many function argument 

//bad Code

public void SendEmail(string title, string body, string ccAddress, string ToAddress, string Sender)

{

    // ...

}

public class EmailDate

{

    public string Title { get; set; }

    public string Body { get; set; }

    public string ToAddress { get; set; }

    public bool CCAddress { get; set; }

}

var config = new EmailDate

{

    Title = "ERROR CODE 001",

    Body = "TEST EMAIL BODY",

    ToAddress = "Baz",

    CCAddress = true

};

//good code

public void SendEmail(EmailDate emailData)

{

    // ...

}

11. Error Handling 

This is a another very important section, if system doesn't have proper error handling will cost for support and maintainability 

 Don't re throw same exception in the catch block, 

try

{

    // Do something..

}

catch (Exception ex)

{

    // Any action something like roll-back or logging etc.

    throw ex;

}

try

{

    // Do something..

}

catch (Exception ex)

{   

    throw ;

}

 

Don't ignore exception. 

//code

Use multiple catch block instead of if condition on the catch block 

//code

Don't leave out of comment on code base on leave com

you can find more details on below pdf 

Reference : Clean Code.pdf (itcollege.ee) 

How to pass web configuraition value to angular controller/service via angular constant

In my appliction, there are few constant value need to pass the angular controllers . So I need to create angular constant and inject it everyware. So I have done the following steps to achived this,

Create the dynaic java script file .

01. In my case some of constant values has to fetch from the web config file. So I have create mvc conroller and return dynamically generated java scripts file when page is loading 

Asp.net Mvc Controller 

 public class ScriptsController : Controller

    {

        public ActionResult Constants()

        {

            var script = new StringBuilder();

            script.Append("(function () {");

            script.Append("angular.module('app')");

            script.Append(".constant('Settings', {");

            script.AppendFormat("AppUrl: '{0}',", ConfigurationManager.AppSettings["AppUrl"]);

            script.Append("});");

            script.Append("})();");

            return JavaScript(script.ToString());

        }

    }

In layout page call the url and load the dynamic java scripts file 

 "script type="text/javascript" src="@Url.Action("Constants", "Scripts")"

setting file is available to used everyware to used 

---settings file is injected and used web confile value inside java scripts controller. 

var app = angular.module('app', ['ui.router']);

app.config(function ($stateProvider, $urlRouterProvider, $locationProvider, Settings) {

    var baseUrl = Settings.AppUrl;

    $urlRouterProvider.otherwise('/home');

    $stateProvider

        .state('home', {

            url: '/About',

            templateUrl: baseUrl+ '/Home/About'

        })

        .state('Contact', {

            url: '/Contact',

            templateUrl: baseUrl + '/Home/Contact' 

        })

   // $locationProvider.hashPrefix('!').html5Mode(true);

});

Excel Sheet Column Title

 public static string converToLetter(int n)
        {
            char[] letter = new char[10];
            int nextValue = n;
            int varBase = 26;
            int i = 0;
            while (nextValue > 0)
            {
                nextValue--;
                int reminder = nextValue % varBase;
                nextValue = nextValue / varBase;
                char id = (char)(65 + reminder);
                letter[i] = id;
                i++;
            }
            letter.Reverse();
            return   new string(letter.ToArray());
        }

MVC helper for jquery datePicker

MVC helper creation is very easy and its only requires static method which should return string. Below code is shown how to create textbox with attached jquery date time popup controller.  

public static MvcHtmlString DatePickerFor (this HtmlHelper htmlHelper, Expression<Func> expression, object htmlAttributes = null)

        {

            var body = (MemberExpression)expression.Body;

            var propertyName = body.Member.Name; // get controller id 

            string textbox = htmlHelper.EditorFor(expression).ToString();

           var script = "'<'script type=\"text/javascript\'>'"

$('#" + propertyName + "').datepicker();

            script += "'<'/script'>'"

            return MvcHtmlString.Create(textbox + "\n" + script);

        }

In the view you can access this helper as below

@Html.DatePickerFor (model => model.dateField)

 Above code is shown the basic steps of required html helper. Assume that want to add more html attributes, date format and other specific filter criteria inside the method and can be pass parameter from view page. This helper is useful you need to add business logic  and validation required before binding data to view. 

public static MvcHtmlString DatePickerFor(this HtmlHelper htmlHelper, Expression<Func> expression, object htmlAttributes = null)

        {

            var body = (MemberExpression)expression.Body;

            var propertyName = body.Member.Name; // get controller id 

            var attr = new RouteValueDictionary(htmlAttributes); // html attributes 

            string propertyDateFormat = ModelMetadata.FromLambdaExpression(expression, htmlHelper.ViewData).DisplayFormatString;

// logic and validation 

            string dateFormat = "";

            if (propertyDateFormat == "{0:dd/MM/yyyy}")

                dateFormat = "dd/mm/yy";

            else               

                dateFormat = "dd/mm/yy";

            string textbox = htmlHelper.EditorFor(expression).ToString(); // render html text box 

                        var script = "'<'script type=\"text/javascript\"'>'$('#" + propertyName + "').datepicker({ dateFormat: '" + dateFormat + "' }); $('#" + propertyName + "').removeClass('text-box');";

            if (attr["style"] != null)

                script += "$('#" + propertyName + "').attr('style','" + attr["style"] + "');";

            if (attr["class"] != null)

                script += "$('#" + propertyName + "').addClass('" + attr["class"] + "');";

            script += "'<'/script'>'";

            return MvcHtmlString.Create(textbox + "\n" + script);        }

without above helper we can used jquery date picker but if need to add some server side validation and server side controlled then this way is more easy.  

Dialog Popup with hyper link

Java script function for Dialog popup 

After click the link popup the data which is return form that link.

link
@Html.ActionLink("Name", "Action","Controller",new {//data need to pass as query string}, new {
// html attributes
class = "LinkClassName", dialog-title = "this is tiltle name ",  dialog-id  = "dailog id" ....
});

 $(".LinkClassName").live("click", function (e) {
            e.preventDefault();     
.append($loading) // this is for load image for fully loaded 
.addClass("dialog") // css class for loading 
.attr("id", $(this) // div id 
.attr("dialog-id")) // poup id
.attr("dialog-path", $(this).attr("data-dialog-path")) // after close page refresh 
.appendTo("body") // attached div content to the body 
.dialog({
   title: $(this).attr("data-dialog-title"), // popup dialog title 
   close: function () {  $(this).remove()   }, // close finction 
   modal: true, /
   resizable: false,
   width: '400px'
}).load(this.href); // href is the url
        });
more details available here.(http://jqueryui.com/)


Form submit using Ajax 


var form = $("#yourFormId");
$.ajax({
                url: '@Url.Action("ActionName","ControllerName")',
                data: form.serialize(),
                type: 'POST',
                success: function (data) {
                    $("#targetUpdateDiv").html(data);
                }
            });

ASP.net MVC3 Date Formats validation Issued (dd/mm/yyyy vs mm/dd/yyyy) 

Client side  passing date format as "dd/mm/yyyy" format. but model is return format is incorrect its cheeking mm/dd/yyyy. i have found that issue in mvc web application. 

solution

Reason is that web server culture info is used as "en-us" its validate format "mm/dd/yyyy".  then i found that we can change the culture dynamically in the following way .

Option 1: Page level

The Culture used by the individual Page can be set via the Culture property.

<%@ Page Culture="en-SG" Language="C#" %>

Option 2: Application wide (web.Config)

The Culture can be set Application wide by adding the  node to the web.Config file. The  node must be placed inside .

<system.web>

  <globalization culture="en-SG"/>

Option 3: Thread level

The Culture of the current Thread can be changed programatically be setting the System.Threading.Thread.CurrentThread.CurrentCulture property.
The following example demonstrates how to get the user-agent(browsers) preferred UserLanguage property on each Request and set the Threads CurrentCulture during the Application_BeginRequest Event.

protected void Application_BeginRequest(Object sender, EventArgs e)

{

    CultureInfo requestCulture;

    try

    {        

        requestCulture = CultureInfo.CreateSpecificCulture(Request.UserLanguages[0]);

    }

    catch

    {

        // Return server Culture if none available in HttpHeaders.

        requestCulture = CultureInfo.CurrentCulture;

    }

    System.Threading.Thread.CurrentThread.CurrentCulture = requestCulture;

}