Pivot Table with Grand Total in SQL

At first crerate a table By following query

CREATE TABLE [dbo].[tblProduct](

          [Vendor] [varchar](50) NULL,

          [Product] [varchar](50) NULL,

          [TotalSell] [int] NULL

)

Insert records in Table by this queries:

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Dell', N'LED', 50)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Dell', N'Mouse', 10)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'Processor', 101)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'MotherBoard', 50)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'iBall', N'cabinet', 12)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'iBall', N'Keyboard', 60)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Microsoft', N'OS', 1005)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'OS', 20)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Microsoft', N'Mouse', 100)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'iBall', N'LED', 10)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'cabinet', 20)

GO

Now You can get result with sell count of each item by its vendor with Total and Grand Total

SELECT Vendor, ISNULL(LED,0) AS LED,ISNULL(Mouse,0) AS Mouse,

                    ISNULL(Processor,0) AS Processor,ISNULL(MotherBoard,0) AS MotherBoard,

                    ISNULL(cabinet,0) AS cabinet,ISNULL(Keyboard,0) AS Keyboard,

                    ISNULL(OS,0) AS OS, Total

FROM

(

          SELECT *, SUM(TotalSell) OVER (PARTITION BY Vendor) AS Total FROM tblProduct

)a

PIVOT

  (SUM(TotalSell) FOR Product IN (LED,Mouse,Processor,MotherBoard,cabinet,Keyboard,OS)

)b

UNION

SELECT 'Grand Total', SUM(ISNULL(LED,0)) AS LED, SUM(ISNULL(Mouse,0)) AS Mouse,

                            SUM(ISNULL(Processor,0)) AS Processor,

                            SUM(ISNULL(MotherBoard,0)) AS MotherBoard,

                            SUM(ISNULL(cabinet,0)) AS cabinet,

                            SUM(ISNULL(Keyboard,0)) AS Keyboard,

                            SUM(ISNULL(OS,0)) AS OS,SUM(Total) as Total

FROM

(

          SELECT *, SUM(TotalSell) OVER (PARTITION BY Vendor) AS Total FROM tblProduct

)a

PIVOT

          (SUM(TotalSell) FOR Product IN (LED,Mouse,Processor,MotherBoard,cabinet,Keyboard,OS)

)b

ORDER BY Total

Record will like below:

Add multiple where condition dynamically in Linq to Entity framework

How to use LinqKit for Linq to Entity framework

Use LinqKit predicateBuilder class in our code.

Add LinqKit dll in your application reference and add library in your page like :

using LinqKit;

Write the following code to achieve your requirement for multiple dynamic where condition

var predicate = LinqKit.PredicateBuilder.True<tblInfo>();

predicate = predicate.And(p => p.Name == "Ajay");

predicate = predicate.And(p => p.City == 2);

var Res = objDB.tblInfoes.AsExpandable().Where(predicate);

AsExpandable() is must required for Linq to Entity framework

Download Linqkit from Here. You can read more about LinqKit from this blog

Add multiple condition dynamically in Linq to sql

What to do if you want to add multiple where condition dynamically in linq. Here is solution for that. This is working fine for Linq to Sql. You have to use LinqKit for Linq to Entity.

public static class PredicateBuilder

    {

        public static Expression<Func<T, bool>> True<T>() { return f => true; }

        public static Expression<Func<T, bool>> False<T>() { return f => false; }

        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,

                                                                           Expression<Func<T, bool>> expr2)

        {

          var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());

            return Expression.Lambda<Func<T, bool>>

                  (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);

        }

       public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,

                                                                            Expression<Func<T, bool>> expr2)

        {

          var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());

          return Expression.Lambda<Func<T, bool>>

                                (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);

        }

    }

Use this predicate class in our code.

using (DataSQLDataContext db = new DataSQLDataContext())

            {

               Expression<Func<tblInfoe, bool>> predicate = PredicateBuilder.True<tblInfoe>();

    // tblInfoe = Tables from DB

                predicate = predicate.And(p => p.Name == "Ajay");

                predicate = predicate.And(p => p.City == 2);

                var d = db.tblInfoes.Where(predicate);

            }

Use of Generic with Repository pattern

Hello All,

          Here is an example for generic repository pattern.  Now what is Generic ?  And the answer is common called generic. Yes .net provide generic by same way. You can create a generic class and it will be independent from datatype. Means, Your datatype will desided by your code to use generic. You can see in following example.  Where  T = Generic Datatype. It will same for whole class when you create object of the class.

Let’s see the Generic class and make it in use.

Generic Class :

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Entity;

using System.Linq;

using System.Linq.Expressions;

namespace Web_test

{

    public class GenericRepository<T>

        where T : class

    {

        internal MyDB objMyDB;  //  MyDB = Datacontext  in Linq to Entity

        internal DbSet<T> dbSet;

        public GenericRepository(MyDB objMyDB)

        {

            this.objMyDB = objMyDB;

            dbSet = objMyDB.Set<T>();

        }

        public IEnumerable<T> GetList(Expression<Func<T, bool>> filter = null,

            Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null, string includeProperties = "")

        {

            IQueryable<T> query = dbSet;

            if (filter != null)

            {

                query = query .Where(filter);

            }

            foreach (var includeProperty in includeProperties.Split(new char[] { ',' },

                        StringSplitOptions.RemoveEmptyEntries))

            {

                query = query.Include(includeProperty);

            }

            if (orderBy != null)

            {

                return orderBy(query).ToList();

            }

            else

            {

                return query.ToList();

            }

        }

        public T GetDetailByID (object ID)

        {

            return dbSet.Find(ID);           

        }

        public void Insert (T entity)

        {

            dbSet.Add(entity);

        }

        public void Delete (int ID)

        {

            T entityToDelete = dbSet.Find(ID);

            dbSet.Remove(entityToDelete);           

        }

        public void Update(T entity)

        {

            dbSet.Attach(entity);

            objMyDB.Entry(entity).State = EntityState.Modified;

        }

        public void Save()

        {

            objMyDB.SaveChanges();

        }

        private bool disposed = false;

        protected virtual void Dispose(bool disposing)

        {

            if (!this.disposed)

            {

                if (disposing)

                {

                    objMyDB.Dispose();

                }

            }

            this.disposed = true;

        }

        public void Dispose()

        {

            Dispose(true);

            GC.SuppressFinalize(this);

        }

    }

}

We are using Linq to Entity framework for repository. Now Let’s see how only one class can do all operations for all entities.

using System.Collections.Generic;

using System.Linq;

namespace Web_test

{

    public class CRUDOperations

    {

        private MyDB objDB = new MyDB();

        private GenericRepository<tblInfo> objInfo;

        public void Insert()

        {

            tblInfo objInfo = new tblInfo();    // We have table with name = tblInfo

            objInfo.City = 1;

            objInfo.Contact = "7777978360";

            objInfo.Name = "Ajay Patel";

            objInfoProp.Insert(objInfo);
            objInfoProp.save();

        }

        public void Update()

        {

            tblInfo objInfo = objInfoProp.GetDetailByID(1);   // We have table with name = tblInfo

            objInfo.City = 2;

            objInfo.Contact = "7777978360";

            objInfo.Name = "AP";

            objInfoProp.Update(objInfo);

        }

        public void Delete()

        {

            objInfoProp.Delete(1);

        }

        public void GetList()

        {

            List<tblInfo> lstInfo = objInfoProp.GetList(p => p.Name == "AP").ToList();

        }

        public GenericRepository<tblInfo> objInfoProp

        {

            get

            {

                if (objInfo == null)

                {

                    objInfo = new GenericRepository<tblInfo>(objDB);

                }

                return objInfo;

            }

        }

    }

}

This is the use of generic. I am not good to explain but hope you all have got this if not then contact me

Thanks