CTE - Common table expression

CTE - Common table expression

It is difficult to write and maintain queries that has number of joins and very complex structure. These queries can made easier to write, read and maintain by the help of CTE.

CTE is common table expression. It is a temporary result set which allow you to define subquery by assigning a alias name and use it further as a temporary table. You can create as many as CTE alias result set and use it to display output.

Example -

select * from(select id, name, class from student) S
where S.class > 6 order by S.id

The query looks bit complex and difficult to understand. But by using CTE. We can create a temporary result set which is easy to understand and maintain. And can used at time to display output.

With T(id, name, class)
As
(
select id, name, class from student
)
select * from T where class > 6

Here you can see above CTE is very simple, easy to understand and maintain.

Syntax -

1) WITH keyword is followed by the CTE name.
2) Column list in parenthesis is optional.
3) Query is written after AS keyword in parenthesis.

A CTE can be used to:

• Create a recursive query. 
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement.

CTE - Common table expression

CTE - Common table expression

It is difficult to write and maintain queries that has number of joins and very complex structure. These queries can made easier to write, read and maintain by the help of CTE.

CTE is common table expression. It is a temporary result set which allow you to define subquery by assigning a alias name and use it further as a temporary table. You can create as many as CTE alias result set and use it to display output.

Example -

select * from(select id, name, class from student) S
where S.class > 6 order by S.id

The query looks bit complex and difficult to understand. But by using CTE. We can create a temporary result set which is easy to understand and maintain. And can used at time to display output.

With T(id, name, class)
As
(
select id, name, class from student
)
select * from T where class > 6

Here you can see above CTE is very simple, easy to understand and maintain.

Syntax -

1) WITH keyword is followed by the CTE name.
2) Column list in parenthesis is optional.
3) Query is written after AS keyword in parenthesis.

Save image in database and render image from database

Firstly, We can save image in database as a byte array. Here, We will create a webapi which accept image as a base64 string. And further, we will convert the base64 string image into byte array and save it in mysql database. 

Datatype for MYSQL to save byte array is LONGBLOB.

Example

Properties class

public class UserInfo
{
    [Required]
    public string FirstName { get; set; }

    [Required]
    public string LastName { get; set; }

    [Required]
    [EmailAddress]
    public string Email { get; set; }

    public string UserImage { get; set; }//Base64 string

    public string ImageMimeType { get; set; }
}

API controller

[RoutePrefix("api/Account")]
public class AccountController : ApiController
{
    [HttpGet]
    [Route("SaveUserInfo")]
    [AppAuthorization]
    public IHttpActionResult SaveUserInfo(UserInfo model)
    {
        if (model != null)
        {
            try
            {
                if (ModelState.IsValid)
                {                    
                    var res = _userRepository.InsertUserInfo(model);
                    return Ok(res);
                }
                else
                {
                    return Content<object>(HttpStatusCode.InternalServerError, new { Status = false, Message = "Invalid model." });
                }
            }
            catch (Exception ex)
            {
                return Content<object>(HttpStatusCode.InternalServerError, new { Status = false, Message = ex.Message });
            }
        }
        else
        {
            return Content<object>(HttpStatusCode.InternalServerError, new { Status = false, Message = "Please provide model." });
        }
    }  
}

Business logic class

public class UserRepository : IUserRepository
{
    Dictionary<string, object> _sqlParams;

    public object InsertUserInfo(UserInfo model)
    {
       if (model.UserPic != null && model.UserPic != "")
       {
           _sqlParams = new Dictionary<string, object>();
           _sqlParams["FirstName "] = model.FirstName;
           _sqlParams["LastName "] = model.LastName;
           _sqlParams["Email"] = model.Email;
           _sqlParams["ImageBytes"] = Convert.FromBase64String(model.UserImage);
           _sqlParams["ImageName"] = model.FirstName;
           _sqlParams["MimeType"] = model.PicMimeType;     

          long userId = DBUtility.ExecuteInsertSql(SqlQueryConstants.InsertUserInfo, _sqlParams);

           return new {Status = true, Data = http://www.url.com:8098/UserImage/" + userId };//This url will render image
        }
        else
        {
           return new {Status = false, Message = "Please provide your image." };
        }
     }
}

Now, (http://www.url.com/Images/UserImage/" + userId) user will get this url to render the user image. 
This url basically hits the image controller and userImage action which will read image byte array from database and render image.

Image Controller

[AllowCrossSiteJsonAttribute]
public class ImagesController : Controller
{
   [Route("UserImage/{userId}")]
   public ActionResult UserImage(int userId)
   {
      var user = _userRepository.GetUserInfo(userId);//Get user info
      return new FileContentResult(user.ImageBytes, user.MimeType);          
   }
}

MVC Authorization filter

MVC supports 4 types of filter

• Authorization
• Action
• Result
• Exception

Authorization filter

It is executed after the user is authenticated in MVC life-cycle. It is basically used to authorize user from resources of your application. You can create your own custom authorization filter. A class which extend AuthorisationFilterAttribute class and overrides its OnAuthorization() method is authorization filter.

Example - Suppose you need to access user data from WEB API by providing authorization token. 

Controller

[RoutePrefix("api/Account")]

public class AccountController : ApiController

{

    [HttpGet]

    [Route("GetUserAppProfile")]

    [AppAuthorization]

    public IHttpActionResult GetUserProfile()

    {

        try

        {

            long userId = Convert.ToInt64(Request.Headers.GetValues("UserId").FirstOrDefault());

            var res = _userRepository.GetUserAppProfile(userId);

            return Ok(res);

        }

        catch (Exception ex)

        {

            return Content<object>(HttpStatusCode.InternalServerError, new { Status = false, Message = ex.Message });

        }

    }

}

Custom filer
        public override void OnAuthorization(System.Web.Http.Controllers.HttpActionContext actionContext)
        {
            if (!actionContext.Request.Headers.Contains("AuthToken"))
            {
                actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized, new { Status = false, Message = "Token is missing" });
            }
            else
            {
                string authToken = actionContext.Request.Headers.GetValues("AuthToken").FirstOrDefault();

                if (string.IsNullOrEmpty(authToken))
                {
                    actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized, new { Status = false, Message = "Token is missing" });
                }
                else
                {
                    long userId = userService.AuthorizeAppUser(authToken);

                    if (userId != null)
                    {
                        actionContext.Request.Headers.Add("UserId", userId.ToString());
                    }
                    else
                    {
                        actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized, new { Status = false, Message = "Invalid token" });
                    }
                }               
            }
            base.OnAuthorization(actionContext);
        }//End
    }

Before execution of action method, This custom authorization filter will execute and check for authentication token in header if token doesn't exist. It will straight forward return the status and message. And will not execute the action method.

C# Classes And Objects

C# classes, It is basically a wrapper or a construct which group together methods, variables or properties. And Object is instance of class. We can create as many instance of class with new keyword. Object has attributes and behavior and these attribute and behavior is defined by a class.

For example : - Suppose you have a TV(Television). It is an object and its size, color is its attributes and its functions like volume up down or number of channels is its behavior. 

Same in case of c# classes and object. Classes instance is an object and its attribute and behaviour is defined by a class.

Class A

{

   int a= 4;

   int b=5;

   int c=0;

   public void Add()

  {

     c= a+b;

  }

   main//Main method

  {

     A instance1 = new A(); //Here can create as many as objects/instance you need

     A instance2 = new A();

  }

}