Cube and Rollup Operator in SQL Server 2008

CUBE operator is used with Group by clause. It use to get the subtotal and grand total of all permutation of columns provided by the CUBE operator.

ROLLUP operator is used with Group by clause. It use to get the subtotal and grand total of a set of columns provided by the ROLLUP operator.

For example,


Declare @tmpTable table(Product varchar(10), Shop varchar(10), Quantity int)
insert into @tmpTable (Product, Shop, Quantity) values ('Tea', 'Tea-Post',100)
insert into @tmpTable (Product, Shop, Quantity) values ('Tea', 'GH2',90)
insert into @tmpTable (Product, Shop, Quantity) values ('Puva', 'Tea-Post',10)
insert into @tmpTable (Product, Shop, Quantity) values ('Puva', 'GH2',25)
insert into @tmpTable (Product, Shop, Quantity) values ('Lassi', 'Anand',2)
insert into @tmpTable (Product, Shop, Quantity) values ('Lassi', 'Nadiad',20)
insert into @tmpTable (Product, Shop, Quantity) values ('Lassi', 'Khambhat',50)
select * from @tmpTable

SELECT Product,Shop,sum(Quantity) FROM @tmpTable GROUP BY ROLLUP (Product,Shop)

SELECT Product,Shop,sum(Quantity) FROM @tmpTable GROUP BY CUBE (Product,Shop)

Result would be

Cube_Rollup_SQL

Insert multiple rows into a table from another table and get identity values

Let me explain the case first, so you will get the idea when this is required to use. In the database, there is more than one tables which store the future values and will move on specific time to standard tables. For example,
Table “UserList2” contains the future values consider as temporary table. Table “UserList1” contains standard values consider as standard table. SQL SCRIPT as below:

GO
/****** Object: Table [dbo].[UserList2] Script Date: 11/26/2015 07:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserList2](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL,
CONSTRAINT [PK_UserList2] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[UserList2] ON
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (1, N'Prakash', N'Anand')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (2, N'Rupal', N'Baroda')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (3, N'Arpit', N'Anand')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (4, N'Vishal', N'Baroda')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (5, N'Ajay', N'Gandhinagar')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (6, N'Kamal', N'Rajkot')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (7, N'Akshay', N'Rajkot')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (8, N'Khimjibhai', N'Gandhinagar')
SET IDENTITY_INSERT [dbo].[UserList2] OFF
/****** Object: Table [dbo].[UserList1] Script Date: 11/26/2015 07:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserList1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL,
[UserList1Id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[UserList1] ON
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (1, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (2, N'Arpit', N'Anand', 3)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (3, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (4, N'Arpit', N'Anand', 3)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (5, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (6, N'Arpit', N'Anand', 3)
SET IDENTITY_INSERT [dbo].[UserList1] OFF

Want to move data from UserList2 to UserList1 and need to use UserList1 Identity column for further process, how to get all newly inserted which as below:


DECLARE @output TABLE (id int)

Insert into UserList1 (Name, City, UserList1Id)
OUTPUT inserted.Id INTO @output
SELECT Name, City, Id FROM UserList2 Where City='Anand'

select * from @output

OUTPUT clause introduced in MS SQL Server 2005. Want to know more about OUTPUT clause refer this link

Remove illegal characters from file name or file path in c#

File cannot be saved if file name has illegal characters. So how to remove the illegal characters by a single line code as below. I use LINQ Querty for that.


string fileName = "prakash C/O swami.xml";
fileName = System.IO.Path.GetInvalidFileNameChars().Aggregate(fileName, (current, c) => current.Replace(c.ToString(), string.Empty));

Now the fileName is “rakash CO swami.xml”. The code removes slash ‘/’ because it is a illegal characters. As same any illegal characters will remove using this above code.

SQL Server Transaction

BEGIN TRANSACTION
BEGIN TRY

    -- put SQL commands here    INSERT/UPDATE/Delete

    -- if successful - COMMIT the work
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    -- handle the error case (here by displaying the error)
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    -- in case of an error, ROLLBACK the transaction    
    ROLLBACK TRANSACTION

    -- if you want to log this error info into an error table - do it here 
    -- *AFTER* the ROLLBACK
END CATCH

Selected value not set on DropDownListFor() in MVC

Let me tell you small story.

There is a ‘title’ column name for salutation in database. Based on that, I create a view model so property name is ‘title’ and list name is ‘titles’. Due to property name is ‘title’, selected value is not set on DropdownListFor. Because ‘title’ is a reserved keyword. It is an attribute of any html element.

I renamed property name in viewmodel ‘title’ to ‘salutation’. All is working fine..

Enjoy day 🙂

How to display Progress bar while page is loading

<pre>

<code>

protected void Page_Load(object sender, EventArgs e)
{
showPageLoad();
//do somthing
    }
private void showPageLoad()
{
int i=0;
Response.Write(“<div id=’divTitle’>Loading(<span id=’message’>0%</span>)</div>”);
Response.Flush();
for(i=5;i<=100;i=i+5)
{
System.Threading.Thread.Sleep(200);// any codes can be typed here
outputflash(i.ToString() + “%”);
Response.Flush();
}
}
private void removePageLoad()
{
ScriptManager.RegisterStartupScript(Page, this.GetType(), “deleteLoading”, “var d = document.getElementById(‘divTitle’);d.parentNode.removeChild(d);”, true);
    }
private void outputflash(string value)
{
Response.Write(“<script type=’text/javascript’>document.getElementById(‘message’).innerHTML='” + value + “‘;</script>”);
    }

</code>

</pre>

Download / Upload Files on FTP Server

Require following ftp details where you want to upload the files:

FTP URL: “ftp://ftp.yoursite.com/”
FTP Username: username
FTP Password: password
FTP Port: 21

Upload File


public string UploadFile(string FtpUrl, string fileName, string userName, string password, string UploadDirectory = "")
        {
            string PureFileName = new FileInfo(fileName).Name;
            String uploadUrl = String.Format("{0}{1}/{2}", FtpUrl, UploadDirectory, PureFileName);
            FtpWebRequest req = (FtpWebRequest)FtpWebRequest.Create(uploadUrl);
            req.Proxy = null;
            req.Method = WebRequestMethods.Ftp.UploadFile;
            req.Credentials = new NetworkCredential(userName, password);
            req.UseBinary = true;
            req.UsePassive = true;
            byte[] data = File.ReadAllBytes(fileName);
            req.ContentLength = data.Length;
            Stream stream = req.GetRequestStream();
            stream.Write(data, 0, data.Length);
            stream.Close();
            FtpWebResponse res = (FtpWebResponse)req.GetResponse();
            return res.StatusDescription;
        }

Download File


 public static string DownloadFile(string FtpUrl,string FileNameToDownload,
                        string userName, string password,string tempDirPath)
    {
        string ResponseDescription = "";
        string PureFileName = new FileInfo(FileNameToDownload).Name;
        string DownloadedFilePath  =  tempDirPath+"/"+PureFileName;
        string downloadUrl = String.Format("{0}/{1}", FtpUrl, FileNameToDownload);
        FtpWebRequest req = (FtpWebRequest)FtpWebRequest.Create(downloadUrl);
        req.Method = WebRequestMethods.Ftp.DownloadFile;
        req.Credentials = new NetworkCredential(userName, password);
        req.UseBinary = true;
        req.Proxy = null;
        try
        {
            FtpWebResponse response = (FtpWebResponse)req.GetResponse();
            Stream stream = response.GetResponseStream();
            byte[] buffer = new byte[2048];
            FileStream fs = new FileStream(DownloadedFilePath, FileMode.Create);
            int ReadCount = stream.Read(buffer, 0, buffer.Length);
            while (ReadCount > 0)
            {
                fs.Write(buffer, 0, ReadCount);
                ReadCount = stream.Read(buffer, 0, buffer.Length);
            }
            ResponseDescription = response.StatusDescription;
            fs.Close();
            stream.Close();
        }
        catch(Exception e)
        {
            Console.WriteLine(e.Message);
        }
        return ResponseDescription;
    }

ASP.NET Identity reset password


var Db = new ApplicationDbContext();
var userDetail = Db.Users.FirstOrDefault(u => u.UserName.Equals(Username));

String userId = userDetail.Id;
String newPassword = "pa$$word";
ApplicationUser cUser = UserManager.FindById(userId);
String hashedNewPassword = UserManager.PasswordHasher.HashPassword(newPassword);
UserStore store = new UserStore();
cUser.PasswordHash = hashedNewPassword;
UserManager.UpdateAsync(cUser);
Db.SaveChanges();

UnitOfWork Generic Repository

The repository and unit of work patterns are intended to create an abstraction layer between the data access layer and the business logic layer of an application. Implementing these patterns can help insulate your application from changes in the data store and can facilitate automated unit testing or test-driven development (TDD).

UnitOfwork


  public class UnitOfWork : IUnitOfWork
    {
        public DataBaseEntities _context;

        #region Constructor
        public UnitOfWork()
        {
            _context = new DataBaseEntities ();
        }
        #endregion

        #region Implement Inteface Methods

        public void Commit()
        {
            _context.SaveChanges();
        }

        public void CommitAndRefreshChanges()
        {
            bool saveFailed = false;

            do
            {
                try
                {
                    _context.SaveChanges();
                    saveFailed = false;
                }
                catch (DbUpdateConcurrencyException ex)
                {
                    saveFailed = true;

                    ex.Entries.ToList()
                              .ForEach(entry =>
                              {
                                  entry.OriginalValues.SetValues(entry.GetDatabaseValues());
                              });

                }
            } while (saveFailed);
        }

        public void RollbackChanges()
        {
            // set all entities in change tracker 
            // as 'unchanged state'
            _context.ChangeTracker.Entries()
                              .ToList()
                              .ForEach(entry => entry.State = System.Data.Entity.EntityState.Unchanged);
        }

        public IEnumerable ExecuteQuery(string sqlQuery, params object[] parameters)
        {
            return _context.Database.SqlQuery(sqlQuery, parameters);
        }

        public int ExecuteCommand(string sqlCommand, params object[] parameters)
        {
            return _context.Database.ExecuteSqlCommand(sqlCommand, parameters);
        }

        private bool _disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this._disposed)
            {
                if (disposing)
                {
                    _context.Dispose();
                }
            }
            this._disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion

  public List ExecuteQueryForDropdown(string sqlQuery)
        {
            var result = db.Database.SqlQuery(sqlQuery).ToList();
            return result;
        }

 public IQueryable Filter(Expression<Func> filter, string orderByColumnName, bool ascending, out int total, int index = 0, int size = 50)
        {
            int skipCount = index * size;
            var resetSet = filter != null ? table.Where(filter).AsQueryable() : table.AsQueryable();
           
            total = resetSet.Count();


            if (!string.IsNullOrEmpty(orderByColumnName))
            {
                resetSet = resetSet.OrderBy(orderByColumnName, ascending);
                resetSet = skipCount == 0 ? resetSet.Take(size) : resetSet.Skip(skipCount).Take(size);
            }
            return resetSet.AsQueryable();
        }

        #region Repositories

        #endregion
    }

Interface of UnitOfWork


 public interface IUnitOfWork : IDisposable
    {
        /// 
        /// Commit all changes made in a container.
        /// 
        void Commit();

        /// 
        ///  If the entity have fixed properties and any optimistic concurrency problem exists,
        ///  then 'client changes' are refreshed - Client wins
        /// 
        void CommitAndRefreshChanges();

        /// 
        /// Rollback tracked changes. See references of UnitOfWork pattern
        /// 
        void RollbackChanges();

        /// 
        /// Execute specific query with underliying persistence store
        /// 
        /// Entity type to map query results
        /// 
        /// SELECT idCustomer,Name FROM dbo.[Customers] WHERE idCustomer > {0}
        /// 
        /// 
        /// 
        /// 
        IEnumerable ExecuteQuery(string sqlQuery, params object[] parameters);

        /// 
        /// Execute arbitrary command into underliying persistence store
        /// 
        /// 
        /// Command to execute
        /// 
        /// SELECT idCustomer,Name FROM dbo.[Customers] WHERE idCustomer > {0}
        /// 
        ///
        /// A vector of parameters values
        /// The number of affected records
        int ExecuteCommand(string sqlCommand, params object[] parameters);

 List ExecuteQueryForDropdown(string sqlQuery);

        IQueryable Filter(Expression<Func> filter, string orderByColumnName, bool ascending, out int total, int index = 0, int size = 20);
    }

Generic Repository


 public class RepositoryBase where T : class
    {
        internal TSPEntities _dataContext;
        internal DbSet _dbset;

        public RepositoryBase(TSPEntities context)
        {
            this._dataContext = context;
            this._dbset = context.Set();
        }

        /// 
        /// Paging Query with filter
        /// 
        /// 
        /// 
        /// 
        /// 
        public virtual List Get(Expression<Func> filter = null, Func<IQueryable, IOrderedQueryable> orderBy = null, string includeProperties = "")
        {
            IQueryable 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();
            }
        }

        /// 
        /// Get selected records with paging
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        public virtual IEnumerable GetPaged(int pageIndex, int pageCount, System.Linq.Expressions.Expression<Func> orderByExpression, bool ascending)
        {
            var set = _dbset;

            if (ascending)
            {
                return set.OrderBy(orderByExpression)
                          .Skip(pageCount * pageIndex)
                          .Take(pageCount);
            }
            else
            {
                return set.OrderByDescending(orderByExpression)
                          .Skip(pageCount * pageIndex)
                          .Take(pageCount);
            }
        }

        public virtual void Add(T entity)
        {
            _dbset.Add(entity);
        }

        public virtual void Delete(T entity)
        {
            _dbset.Remove(entity);
        }

        public virtual void Delete(Expression<Func> where)
        {
            IEnumerable objects = _dbset.Where(where).AsEnumerable();
            foreach (T obj in objects)
                _dbset.Remove(obj);
        }

        public virtual T GetById(long id)
        {
            return _dbset.Find(id);
        }

        public virtual T GetById(string id)
        {
            return _dbset.Find(id);
        }

        public virtual IEnumerable GetAll()
        {
            return _dbset.ToList();
        }

        public virtual IQueryable GetAllQueryable()
        {
            return _dbset.AsQueryable();
        }

        public virtual IEnumerable GetMany(Expression<Func> where)
        {
            return _dbset.Where(where).ToList();
        }

        public virtual IQueryable GetManyQueryable(Expression<Func> where)
        {
            return _dbset.AsQueryable().Where(where);
        }

        public T Get(Expression<Func> where)
        {
            return _dbset.Where(where).FirstOrDefault();
        }

 public IQueryable Filter(Expression<Func> filter, string orderByColumnName, bool ascending, out int total, int index = 0, int size = 50)
        {
            int skipCount = index * size;
            var resetSet = filter != null ? table.Where(filter).AsQueryable() : table.AsQueryable();
           
            total = resetSet.Count();


            if (!string.IsNullOrEmpty(orderByColumnName))
            {
                resetSet = resetSet.OrderBy(orderByColumnName, ascending);
                resetSet = skipCount == 0 ? resetSet.Take(size) : resetSet.Skip(skipCount).Take(size);
            }
            return resetSet.AsQueryable();
        }
    }