How to implement Ajax.BeginForm in MVC OR How to validate MVC model data-annotation asynchronously

Here I am going to implement Ajax.BeginForm in MVC. It is replacement of Html.BeginForm html helper.

Html.BeginForm : Html helper post model to controller not asynchronously means your page post back and data-annotation validation fire.
Ajax.BeginForm : Ajax helper post model to controller asynchronously means your page doesn’t post back and data-annotation validation fire.

Step 1 : Add jquery.unobtrusive-ajax.min.js jquery reference to your view. And add 

 <add key="ClientValidationEnabled" value="true" />  
 <add key="UnobtrusiveJavaScriptEnabled" value="true" />  

This 2 key add to web.config under appSettings section.

Step 2 : Create first partialview ValidationSummary for show validation summary on the page.

 @using System.Web.Mvc.ViewUserControl  
 @Html.ValidationSummary("Please correct the errors and try again.")  

Step 3 : Create second partialview UserRegisterForm for registration form fields like Name, Age, Username, Password etc.

 @model EntityModel.UserModel  
 <div id="validationSummary">  
    @Html.RenderPartial("ValidationSummary");  
 </div>  
  <table width="455" border="0" cellspacing="0" cellpadding="10">  
               <tr>  
                 <td width="190">Username :<font class="Mandt">*</font></td>  
                 <td width="265">  
                   @Html.TextBoxFor(m => m.USR_Username, new { @class = "TextBox250", @autofocus = "autofocus" })</td>  
               </tr>  
               <tr>  
                 <td width="190">Password :<font class="Mandt">*</font></td>  
                 <td width="265">  
                   @Html.PasswordFor(m => m.USR_Password, new { @class = "TextBox250" })</td>  
               </tr>  
               <tr>  
                 <td width="190">Confirm Password :<font class="Mandt">*</font></td>  
                 <td width="265">  
                   @Html.PasswordFor(m => m.USR_Confirm_Password, new { @class = "TextBox250" })</td>  
               </tr>  
               <tr>  
                 <td>Email :</td>  
                 <td>  
                   @Html.TextBoxFor(m => m.USR_Email, new { @class = "TextBox250" })</td>  
               </tr>  
               <tr>  
                 <td>Phone :</td>  
                 <td>  
                   @Html.TextBoxFor(m => m.USR_Contact_No, new { @class = "TextBox250" })</td>  
               </tr>  
             </table>  

Step 4: Create view UserRegistration for User registration

 @using (Ajax.BeginForm("register",null, new AjaxOptions { HttpMethod = "POST", UpdateTargetId = "myForm" }, new { id = "myForm" }))  
 @Html.RenderPartial("UserRegisterForm");  

You can get better idea of AjaxOptions from below link
http://msdn.microsoft.com/en-us/library/system.web.mvc.ajax.ajaxoptions(v=vs.118).aspx


Step 5: Create Controller method register
 

 [AcceptVerbs(HttpVerbs.Post)]  
 public ActionResult Register(UserModel user)  
 {  
   if (Request.IsAjaxRequest())  
   {  
     return PartialView("UserRegisterForm");  }  
   else  
   {  
     return View();  
   }  
 }  

If Ajax request then return partialview in UpdateTargetId html element otherwise whole view will be sent back.

How to create custom search & optimize paging stored procedure (SP)

Here I am going to implement SP which provides Optimize Paging for tabular data representation and also custom search.

First step to create one sql table Users by below sql script

 CREATE TABLE [dbo].[Users](  
      [USR_User_ID] [bigint] IDENTITY(1,1) NOT NULL,  
      [USR_Department_ID] [bigint] NOT NULL,  
      [USR_FirstName] [nvarchar](100) NOT NULL,  
      [USR_MiddleName] [nvarchar](100) NULL,  
      [USR_LastName] [nvarchar](100) NOT NULL,  
      [USR_Email] [nvarchar](100) NULL,  
      [USR_Username] [nvarchar](100) NOT NULL,  
      [USR_Password] [nvarchar](200) NOT NULL,  
      [USR_Contact_No] [nvarchar](20) NULL,  
      [USR_Last_Login] [datetime] NOT NULL,  
      [USR_Status] [bit] NOT NULL,  
      [USR_Created_Date] [datetime] NOT NULL,  
      [USR_Created_By] [bigint] NOT NULL,  
      [USR_Updated_Date] [datetime] NULL,  
      [USR_Updated_By] [bigint] NULL,  
      [USR_IsDeleted] [bit] NOT NULL,  
      [USR_IsAdmin] [bit] NOT NULL,  
  CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED   
 (  
      [USR_User_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]  

I am going to add search parameters for
•    Username
•    Name
•    Status
•    DepartmentId

 /* Optional Filters for Dynamic Search*/  
   @Username NVARCHAR(50) = NULL,  
   @Name NVARCHAR(50) = NULL,  
   @Status tinyint=2,  
   @DepartmentId bigint,  

I am going to add pagination parameters
•    PageNo
•    PageSize

 /*– Pagination Parameters */  
   @PageNo INT = 0,  
   @PageSize INT = 10,  

Now adding dynamic filter expression

   @filterExpression NVARCHAR(max) = NULL,  

Now adding column sort parameters
•    SortColumn
•    SortOrder

 /*– Sorting Parameters */  
   @SortColumn NVARCHAR(50) = 'USR_User_ID',  
   @SortOrder NVARCHAR(4)='DESC',  

Now adding export flag parameter for return all the data

 /*– Export to excel flag */  
   @Export BIT = 0,  

Now adding total count output parameters for total no of records after applying filter parameters

 /*– Output Parameters */  
   @TotalCount INT OUTPUT  

Declare variables

 DECLARE @SQLQuery NVARCHAR(MAX)='';   
 DECLARE @SQLCountQuery NVARCHAR(MAX)='';  
 DECLARE @WhereClause NVARCHAR(MAX) = '';  
 DECLARE @OffsetRows INT;  

Set values to variables

 SET @OffsetRows = @PageNo * @PageSize;  
 SET @SQLQuery = 'SELECT * FROM Users U WHERE USR_IsDeleted=0 ';  
 SET @SQLCountQuery = 'SELECT COUNT(*) FROM Users U WHERE USR_IsDeleted=0 ';  

Set filter expression to where clause

 IF(@filterExpression IS NOT NULL AND @filterExpression !='')  
           SET @WhereClause = @WhereClause + @filterExpression;  

Set search parameters to where clause

 IF(@Username IS NOT NULL AND @Username !='')  
           SET @WhereClause = @WhereClause + ' AND USR_Username LIKE ''%' + @Username +'%''';                 
      IF(@Name IS NOT NULL AND @Name !='')  
           SET @WhereClause = @WhereClause + ' AND (U.USR_FirstName LIKE ''%' + @Name + '%'' OR U.USR_LastName LIKE ''%' + @Name + '%'' OR U.USR_FirstName + '' '' + U.USR_LastName LIKE ''%' + @Name + '%'')';  
      IF(@Status != 2)  
           SET @WhereClause = @WhereClause + ' AND U.USR_Status = ' + CAST(@Status AS NVARCHAR(10)) + ''  
      IF(@DepartmentId != 0)  
           SET @WhereClause = @WhereClause + ' AND U.USR_Department_ID  
  = ' + CAST(@DepartmentId AS NVARCHAR(50));  

Build both sql queries

 SET @SQLQuery = @SQLQuery + @WhereClause  
 SET @SQLCountQuery = @SQLCountQuery + @WhereClause  

Execute build sql query base on column sort parameters, export parameter & paging parameters
 

 IF(@Export = 1)  
           BEGIN  
                SET @SQLQuery = @SQLQuery + ' ORDER BY '+ @SortColumn + ' ' + @SortOrder ;  
           END  
      ELSE  
           BEGIN  
                SET @SQLQuery = @SQLQuery + ' ORDER BY '+ @SortColumn + ' ' + @SortOrder +' OFFSET '+ CAST(@OffsetRows AS NVARCHAR(10)) +' ROWS FETCH NEXT '+ CAST(@PageSize AS NVARCHAR(10)) +' ROWS ONLY';  
           END  

 DECLARE @RowCount TABLE (Value int);  
      INSERT INTO @RowCount  
      EXECUTE(@SQLCountQuery)  
      SELECT @TotalCount = Value FROM @RowCount;  
 EXECUTE(@SQLQuery)