Thursday, August 4, 2016

Asp.net mvc check if username exists instantly using remote validation

check that particular username or email address  exists or not in our database instantly at client side . We can achieve particular perspective by hard coding at server side but at client side we can achieve using remote validation attribute in model . Let’s see example .

First create model for user name  using data annotations with remote attribute 

public class RegisterModel
{
        [Remote("CheckUserNameExists", "Common")]
        [Required(ErrorMessage="User name is required")]
        [Display(Name = "User Name")]
        public string UserName { get; set; }
}

Here we are using remote attribute to check instantly that user name  exists or not . If we check parameters of remote attribute , we will find that we need to provide controller and action name . We provided Controller name as Common and action name as CheckUserNameExists . Let’s create action name as “CheckUserNameExists” .

Create action for remote validation

public virtual JsonResult CheckUserNameExists(string userName)
{
    bool chkUser = false;
    //Check in database that particular user name is exist or not
    chkUser = CheckUserNameExistsFunction(userName);
    if (chkUser)
    {
         return Json("User name is already registered ", JsonRequestBehavior.AllowGet);
    }
    else
    {
         return Json(true, JsonRequestBehavior.AllowGet);
    }
}
By calling above method we can check that user name  exists or not instantly as it  returns Json Result . Now we we will see how we can implement at view side where it will show the message instantly .
@Html.TextBoxFor(m =>m.UserName, new { placeholder = "Enter user name", id = "txtUserName" })
@Html.ValidationMessageFor(m =>m.UserName, string.Empty, new { @class = "error-class" })

What is your opinion ?

By implementing above all things we can achieve instantly message that user name is already exist or not . I hope now you can easily implement remote validation . If you have any query you can comment or you can mail me.
Nice one, thanks for that Dilip Patel. @ahmet and Dilip, you could rewrite your function a little to simplify things, like so:
[AllowAnonymous]
        public virtual JsonResult CheckUserNameExists(string userName)
        {
            //Check in database via Usermanager that particular user name is exist or not
            bool userExists = UserManager.Users.Any(u => u.UserName == userName);
            return userExists
                ? Json(Help.User_name_already_registred, JsonRequestBehavior.AllowGet)
                : Json(true, JsonRequestBehavior.AllowGet);
        }
        [AllowAnonymous]
        public virtual JsonResult CheckEmailExists(string email)
        {
            //Check in database via Usermanager that particular email is exist or not
            bool userExists = UserManager.Users.Any(u => u.Email == email);
            return userExists
                ? Json(Help.Email_already_registred, JsonRequestBehavior.AllowGet)
                : Json(true, JsonRequestBehavior.AllowGet);
        }

db Backups task scheduler


USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases]    Script Date: 11/10/2015 6:10:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Microsoft
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================

ALTER PROCEDURE [dbo].[sp_BackupDatabases] 
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200)
AS

       SET NOCOUNT ON;
          
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
          
             -- Pick out only databases which are online in case ALL databases are chosen to be backed up
             -- If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name
          
            -- Filter out databases which do not need to backed up
            IF @backupType='F'
                  BEGIN
                  DELETE @DBs where DBNAME IN
('tempdb','master','model','msdb' )
                  END
            ELSE IF @backupType='D'
                  BEGIN
                  DELETE @DBs where DBNAME IN
('tempdb','master','model','msdb' )
                  END
            ELSE IF @backupType='L'
                  BEGIN
                  DELETE @DBs where DBNAME IN
('tempdb','master','model','msdb' )
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
          
            -- Declare variables
            DECLARE @BackupName varchar(500)
            DECLARE @BackupFile varchar(500)
            DECLARE @DBNAME varchar(500)
            DECLARE @sqlCommand NVARCHAR(1000)
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                 
                      
            -- Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs

      WHILE @Loop IS NOT NULL
      BEGIN

-- Database Names have to be in [dbname] format since some have - or _ in their name
      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),105),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') 

-- Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = 'F'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'D'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'L'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

-- Provide the backup a name for storing in the media
      IF @backupType = 'F'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
      IF @backupType = 'D'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
      IF @backupType = 'L'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed

       IF @backupType = 'F'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
                  END
       IF @backupType = 'D'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'       
                  END
       IF @backupType = 'L'
                  BEGIN
               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'       
                  END

-- Execute the generated SQL command
       EXEC(@sqlCommand)

-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop


END

Find nth highest and lowest salary of an Employee

This question has been asked many times in interview to candidate that find or fetch nth highest and lowest salary of an Employee like 2nd, 3rd, 4th, 5th… highest and lowest salary of an Employee from Employee table. This question some times also asked as get nth highest and lowest salary of an Employee without using TOP and sub query. Let’s see solutions for all this questions in this article.
Suppose we have following table of Employee with Name and Salary of Employee.
Query to get nth(3rd) highest salary of an Employee
Suppose we want to get 3rd highest salary of an Employee so query should be like.
Let me illustrate above query as here we want to fetch nth(3rd) highest salary of an Employee so first inside sub query we find all top n(3) distinct records using order by descending which result in to top n(3) records with highest salary and than we fetch top 1 record among all these records with order by ascending which full fill our requirement to find nth(3rd) highest salary of an Employee.

Query to get nth(3rd) lowest salary of an Employee

See here we want to find nth(3rd) lowest salary of an Employee so our logical of fetching data will be reversed so first inside sub query we find all top n(3) distinct records using order by ascending which result in to top n(3) records with lowest salary and than we fetch top 1 record among all these records with order by descending which full fill our requirement to find nth(3rd) lowest salary of an Employee.

Query to get nth(3rd) highest salary of an Employee without using TOP and Sub Query

This question also can twisted to find or fetch nth(3rd) highest salary of an Employee without using TOP and Sub Query so solutions can be using With CTE(Common Table Expressions) as below query.
in above query we find salary in descending order using With CTE(Common Table Expression) than we used RowNum = n(3) which results into nth(3rd) highest salary of an Employee without using TOP and Sub Query.

Query to get nth(3rd) lowest salary of an Employee without using TOP and Sub Query

To find or fetch nth(3rd) lowest salary of an Employee without using TOP and Sub Query so solutions can be using With CTE(Common Table Expressions) as below query.
in above query we find salary in ascending order using With CTE(Common Table Expression) than we used RowNum = n(3) which results into nth(3rd) lowest salary of an Employee without using TOP and Sub Query.