SQL GROUP BY And HAVING Clause

GROUP BY Clause

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Example - Suppose you want to check number of orders placed by each customer

Here, above we have 2 tables customer and order and we need to find number of orders by each customer.

Query - select c.name, c.phone from customer c join orders o on c.id = o.custId

Now, To get the count of repeated customers

Query - select c.name, count(o.custid) as totalorders from

             customer c join orders o on c.id =  o.custId

             group by c.name

Multiple columns in Group By

Query - select c.name, c.phone, count(o.custid) as totalorders from

             customer c join orders o on c.id =  o.custId

             group by c.name, c.phone

HAVING Clause

There is always some situation where you need to use aggregate function with WHERE clause. Using aggregate function with WHERE clause is not allowed. So with the help of HAVING clause we can use aggregate function with WHERE clause.

For example as above in GROUP BY clause if we would like to get those records having more than 1 order taht can be achived by using HAVING with WHERE clause.

GROUP BY Query as above to get customer detail and total number of oders placed by them

Query - select c.name, c.phone, count(o.custid) as totalorders from

             customer c join orders o on c.id =  o.custId

             group by c.name, c.phone

Using HAVING clause to get customer having more than 4 orders placed

Query - select c.name, c.phone, count(o.custid) as totalorders from

             customer c join orders o on c.id =  o.custId

             group by c.name, c.phone 

             having count(o.custid) > 4

Same using where clause

Query - select c.name, c.phone, count(o.custid) as totalorders from

             customer c join orders o on c.id =  o.custId

             where c.name = 'tarran'

             group by c.name, c.phone

             having count(o.custid) > 4

Window Service

Window service is a service which runs in a background and need no user interaction. It automatically started when computer boots.
Mostly, There is business requirement for long-running scheduled jobs based on some time interval. For example - Sending emails, Reminder messages etc after some time interval or on daily basis. So, Window service is best fit for this system.

To create window service
Add window service in your project

By default it will create following files as in below image in your window service project.

• Program.cs basically have code to start your service that is Service1.cs.
• Service1.cs will contain OnStart and OnEnd method of service.

Now we required installer class to install service. To add installer class, click on Service1.cs. It will open a window right click on it and select AddInstaller as in below image.

It will add installer class named PprojectInstaller.cs.

Now on serviceInstaller1 box right click and select properties. You will see property window on right side of screen. It contain basic properties of your service installation. Look at start-type which is manual. It means service will not start automatically after installation. You have to start it manually.

So far, We have 3 files in our window service project :

• Program.cs, Which contain code to run your Service1.cs file.
• Service.cs, It contain method OnStart and OnEnd which will trigger when service start and end.
• ProjectInstaller.cs, Which is your installer class and used to install service.

Now lets add a another class to logging triggers named Liberary.cs

Liberary.cs

 public static class Liberary

    {       

        public static void WriteLog(String msg)

        {

            StreamWriter sw = null;

            try

            {

                sw = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "\\LogFile.txt", true);

                sw.WriteLine(DateTime.Now.ToString() + ": " + msg);

                sw.Flush();

                sw.Close();

            }

            catch { }

        }              

    }

Service1.cs 

    public partial classService1 : ServiceBase

    {       

        public Service1()

        {

            InitializeComponent();     

        }

        protected override void OnStart(string[] args)

        {

            Liberary.WriteLog("Service started");

            Job();

        }

        public void Job()

        {

            Liberary.WriteLog(DateTime.Now + " : Test");

            double TimerInterVal = (double)30000; //After 30 seconds       

            System.Timers.Timer myTimer = new System.Timers.Timer();       

            myTimer.Interval = TimerInterVal;

            myTimer.AutoReset = true;

            myTimer.Elapsed += new System.Timers.ElapsedEventHandler(notify_Elapsed);

            myTimer.Enabled = true;

        }

        void notify_Elapsed(object sender, System.Timers.ElapsedEventArgs e)

        {

            Liberary.WriteLog(DateTime.Now + " : Elapse");

        }

        protected override void OnStop()

        {

            Liberary.WriteLog(DateTime.Now + " : Stop");

        }       

    }

In Service1.cs, We have created a job which will run after every 30 seconds and logged it in a file. Now rebuild project in debugging mode. It will create a exe file in bin > debug folder.

Install window service

To test your window service, we need to install it.

Go to Window > All program > Visual studio 2013 > Visual studio tools > Developer command prompt for VS2013

Now write following command :

InstallUtil.exe "../pathofproject/bin/debud/application.exe"

It will install the service. But remember it will run manually as setup in installer file start-type property. Now open the services app in all program. Here you will find your service and you can start it manually.

To debug window service without installing 

• Remove program.cs file which was added by default when you created the project.
• Add following in Service1.cs file.

        public static void Main()

        {

#if DEBUG

            Scheduler ser = new Scheduler();

            ser.OnStart(null);

#else

            ServiceBase.Run(new Scheduler());

#endif

        }

Form authentication in MVC

Tables
create table UserRole(Id int primary key identity(1,1), Name varchar(20) not null)

select * from UserRole

create table Users(ID int primary key identity(1,1), Email varchar(100), Password varchar(100), RoleId int not null foreign key references UserRole(id))

insert into Users values('tarvinder3012@gmail.com', 'test123', 1)

select * from Users

Now, Create MVC empty application

Now, In solution folder add separate project/class library for data source (Entity framework). Create database first approach and provide server detail. It will automatically create classes. Also add connection string in app config file.

You can use same connection string in your mvc project web config.

As we are going to do form authentication so we need to mention it in web config.

  <system.web>

    <compilation debug="true" targetFramework="4.5"/>

    <httpRuntime targetFramework="4.5"/>

    <authentication mode="Forms">      

    </authentication>

  </system.web>

Add controller - Account Controller

using EfModel;

using FormAuthentication.Models;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Web.Security;

namespace FormAuthentication.Controllers

{

    public class AccountController : Controller

    {

        public ActionResult Index()

        {

            return View();

        }

        [HttpPost]

        [ValidateAntiForgeryToken]

        public ActionResult Index(Login model)

        {

            if (ModelState.IsValid)

            {

               using(TestEntities _context = new TestEntities())

               {

                   User res = _context.Users.Where(p => p.Email == model.UserName && p.Password == model.Password).FirstOrDefault();

                   if (res == null)

                   {

                       ViewBag.Status = "Invalid credentials";

                   }

                   else

                   {

                       Session["User"] = model;

                       FormsAuthentication.SetAuthCookie(res.Email, false);

                       return RedirectToAction("index", "home");

                   }

               }

               return View();

            }

            else

            {

                return new HttpNotFoundResult();

            }

        }

        [HttpGet]

        public ActionResult Logout()

        {

            FormsAuthentication.SignOut();

            Session.Abandon();

            return RedirectToAction("index", "account");

        }       

    }

}

Add view - Index View(Account controller)

@model FormAuthentication.Models.Login

@using System.Web.Mvc.Html

@{

    ViewBag.Title = "Index";

}

<h2>Index</h2>

@using(Html.BeginForm("Index", "Account", FormMethod.Post))

{    

    @Html.AntiForgeryToken();

    

    <label>UserName</label>   

    @Html.TextBoxFor(u => u.UserName, new { placeholder = "Emial" });

    @Html.ValidationMessageFor(u=> u.UserName)

    

    <label>Password</label>    

    @Html.PasswordFor(u => u.Password, new { placeholder = "Password" });

    @Html.ValidationMessageFor(u=>u.Password)

    

    <input type="submit" value="Login" />

    <label>@ViewBag.Status</label>

}

Add controller - Home Controller

using FormAuthentication.Filter;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace FormAuthentication.Controllers

{

    [CheckAuthentication]

    public class HomeController : Controller

    {        

        public ActionResult Index()

        {

            return View();

        }

    }

}

Add view - Index View(Home controller)

@{

    ViewBag.Title = "Index";

}

<h2>Welcome</h2>

<a href="/account/logout">Logout</a>

Authorization filter - It will be use in controller to check user accessing resoureces is authorized. 

Add Class -  CheckAuthentication

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Web.Routing;

namespace FormAuthentication.Filter

{

    public class CheckAuthentication : AuthorizeAttribute

    {

        public override void OnAuthorization(AuthorizationContext filterContext)

        {

            if (HttpContext.Current.User.Identity.IsAuthenticated)

            {

                base.OnAuthorization(filterContext);

            }

            else

            {

                filterContext.Result = new RedirectToRouteResult(new RouteValueDictionary(new{ controller = "account", action = "index" }));

            }

        }

    }

}

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);          
   }
}