Friday, 16 May 2014

DevExpress GridView Customize with MVC

To implemente DevExpress GridView we need to use DevExpress function @Html.DevExpress().GridView(). This consume lots of time as we need to write some same code again and again. So I have implemented DevExpress GridView's Overload Method which reduce the nuber of lines of codes and also reduce the complexity of code.

Here I am Adding one Overload Method for DevExpress GridView in MVC. Here I am creating one Extension Method to Get and Set Property for the Grid which Varies in Different Grid.

Here I am going to add GridView name, Keyfield name, Route information, and grid with popup's width and height settings.

Properties Class :

public class CommonGridSettingPopupEntity
    {
        public string GridName { get; set; }
        public string KeyFieldName { get; set; }
        public string RouteArea { get; set; }
        public string RouteController { get; set; }
        public string RouteAction { get; set; }
        public bool GridWidth_Height_In_Pixel { get; set; }
        public double GridWidth { get; set; }
        public double GridHeight { get; set; }
        public string GridLayoutName { get; set; }
        public string PopupEditFormCaption { get; set; }
        public int PopupWidth_Pixel { get; set; }
        public int PopupHeight_Pixel { get; set; }
        public bool PopupWidth_Height_In_Perc { get; set; }


        public CommonGridSettingPopupEntity()
        {
            GridWidth = 100;
            GridHeight = 100;
            PopupWidth_Pixel = 450;
            PopupHeight_Pixel = 300;
            GridWidth_Height_In_Pixel = false;
            PopupWidth_Height_In_Perc = false;

        }


Now I am creating Extension Method by which we can easily generate our requuire type's column.

Column's Extensions :

namespace DevExpress.Web.Mvc
{

    #region Grid Columns
    public static class GridColumn
    {

        #region String


        /// <summary>
        ///
        /// </summary>
        /// <param name="settings"></param>
        /// <param name="FieldName"></param>
        /// <param name="DisplayName">(Optional) pass Column Caption name(If you dont want to pass caption then pass ""(blank) )</param>
        /// <param name="Width">(Optional) Pass column width (If you dont want to pass width then pass 0).</param>
        /// <param name="IsCentre">(Optional) If you want to create column centre then pass true otherwise false</param>
        public static void GetStringColumn(GridViewSettings settings, string FieldName, string DisplayName = "", int Width = 0, bool IsCentre = false)
        {
            settings.Columns.Add(gridViewColumn =>
             {
                 if (DisplayName != "")
                     gridViewColumn.Caption = DisplayName;

                 if (Width != 0)
                     gridViewColumn.Width = Unit.Pixel(Width);

                 if (IsCentre == true)
                 {
                     gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Center;
                     gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                 }
                 else
                 {
                     gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Left;
                     gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
                 }

                 gridViewColumn.FieldName = FieldName;
                 gridViewColumn.Settings.AutoFilterCondition = AutoFilterCondition.Contains;
                 gridViewColumn.HeaderStyle.Wrap = DefaultBoolean.True;
             });
        }

        #region String
        public static void GetHiddenColumn(GridViewSettings settings, string FieldName)
        {
            settings.Columns.Add(gridViewColumn =>
            {
                gridViewColumn.FieldName = FieldName;
                gridViewColumn.Visible = false;
            });
        }

        #endregion


        //public static void GetStringColumn(GridViewSettings settings, string FieldName, string DisplayName, int Width)
        //{
        //    //return new MVCxGridViewColumnCollection<TModel>(Columns);
        //    settings.Columns.Add(gridViewColumn =>
        //     {
        //         gridViewColumn.Caption = DisplayName;
        //         gridViewColumn.FieldName = FieldName;
        //         gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Left;
        //         gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
        //         gridViewColumn.Width = Unit.Pixel(Width);
        //         gridViewColumn.HeaderStyle.Wrap = DefaultBoolean.True;
        //         gridViewColumn.Settings.AutoFilterCondition = AutoFilterCondition.Contains;
        //     });



        //}

        #endregion

        #region DateTime
        public static void GetDateColumn(GridViewSettings settings, string FieldName, string DisplayName, string DisplayFormatString = "d")
        {
            settings.Columns.Add(gridViewColumn =>
             {
                 gridViewColumn.Caption = DisplayName;
                 gridViewColumn.FieldName = FieldName;
                 gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Center;
                 gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                 gridViewColumn.ColumnType = MVCxGridViewColumnType.DateEdit;
                 gridViewColumn.Settings.AutoFilterCondition = AutoFilterCondition.Equals;
                 gridViewColumn.PropertiesEdit.DisplayFormatString = DisplayFormatString;
                 gridViewColumn.HeaderStyle.Wrap = DefaultBoolean.True;
             });



        }

        public static void GetDateColumn(GridViewSettings settings, string FieldName, string DisplayName, int Width, string DisplayFormatString = "d")
        {
            settings.Columns.Add(gridViewColumn =>
             {
                 gridViewColumn.Caption = DisplayName;
                 gridViewColumn.FieldName = FieldName;
                 gridViewColumn.Width = Unit.Pixel(Width);
                 gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Center;
                 gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                 gridViewColumn.ColumnType = MVCxGridViewColumnType.DateEdit;
                 gridViewColumn.Settings.AutoFilterCondition = AutoFilterCondition.Equals;
                 gridViewColumn.PropertiesEdit.DisplayFormatString = DisplayFormatString;
                 gridViewColumn.HeaderStyle.Wrap = DefaultBoolean.True;

             });
        }

        #endregion

        #region Numeric
        public static void GetNumericColumn(GridViewSettings settings, string FieldName, string DisplayName)
        {
            settings.Columns.Add(gridViewColumn =>
             {
                 gridViewColumn.Caption = DisplayName;
                 gridViewColumn.FieldName = FieldName;
                 gridViewColumn.ColumnType = MVCxGridViewColumnType.SpinEdit;
                 gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Center;
                 gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                 gridViewColumn.Settings.AutoFilterCondition = AutoFilterCondition.Equals;
                 gridViewColumn.HeaderStyle.Wrap = DefaultBoolean.True;
             });

        }

        //"{0:c2}"
        public static void GetNumericColumn(GridViewSettings settings, string FieldName, string DisplayName, string DisplayFormatString, int Width = 0)
        {
            settings.Columns.Add(gridViewColumn =>
             {
                 gridViewColumn.Caption = DisplayName;
                 gridViewColumn.FieldName = FieldName;
                 gridViewColumn.ColumnType = MVCxGridViewColumnType.SpinEdit;
                 gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Center;
                 gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                 gridViewColumn.Settings.AutoFilterCondition = AutoFilterCondition.Equals;
                 gridViewColumn.HeaderStyle.Wrap = DefaultBoolean.True;
                 gridViewColumn.PropertiesEdit.DisplayFormatString = DisplayFormatString;

                 if (Width != 0)
                     gridViewColumn.Width = Unit.Pixel(Width);
             });
        }


        public static void GetNumericColumn(GridViewSettings settings, string FieldName, string DisplayName, int Width)
        {

            settings.Columns.Add(gridViewColumn =>
         {
             gridViewColumn.Caption = DisplayName;
             gridViewColumn.FieldName = FieldName;
             gridViewColumn.ColumnType = MVCxGridViewColumnType.SpinEdit;
             gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Center;
             gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
             gridViewColumn.Settings.AutoFilterCondition = AutoFilterCondition.Equals;
             gridViewColumn.HeaderStyle.Wrap = DefaultBoolean.True;
             gridViewColumn.Width = Unit.Pixel(Width);
         });

        }

        #endregion


        #region LinkColumn

        public static void GetLinkColumn(HtmlHelper htmlhelper, GridViewSettings settings, string FieldName, string DisplayName, int Width, string GridName)
        {

            settings.Columns.Add(gridViewColumn =>
             {
                 gridViewColumn.FieldName = FieldName;
                 gridViewColumn.Caption = DisplayName;
                 gridViewColumn.Width = Unit.Pixel(Width);
                 gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Left;
                 gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
                 gridViewColumn.SetDataItemTemplateContent(container =>
                 {

                     htmlhelper.ViewContext.Writer.Write(string.Format("<a href = 'javascript:void(0)' style='width:{3}px;' onclick='{0}.StartEditRow({1});'>{2}</a>", GridName, (container as GridViewDataItemTemplateContainer).VisibleIndex, container.Text, Width));


                 });
                 gridViewColumn.Settings.AutoFilterCondition = AutoFilterCondition.Contains;

             });
        }

        public static void GetLinkColumn(HtmlHelper htmlhelper, GridViewSettings settings, string FieldName, string DisplayName, string GridName)
        {

            settings.Columns.Add(gridViewColumn =>
             {
                 gridViewColumn.FieldName = FieldName;
                 gridViewColumn.Caption = DisplayName;
                 gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
                 gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Left;
                 gridViewColumn.SetDataItemTemplateContent(container =>
                 {

                     htmlhelper.ViewContext.Writer.Write(string.Format("<a href = 'javascript:void(0)'  onclick='{0}.StartEditRow({1});'>{2}</a>", GridName, (container as GridViewDataItemTemplateContainer).VisibleIndex, container.Text));


                 });
                 gridViewColumn.Settings.AutoFilterCondition = AutoFilterCondition.Contains;

             });
        }

        #endregion

        #region Link Column To Redirect BY ID

        public static void GetLinkRedirectColumn(HtmlHelper htmlhelper, GridViewSettings settings, string FieldName, string DisplayName, int Width, string URL)
        {
            settings.Columns.Add(gridViewColumn =>
            {
                gridViewColumn.FieldName = FieldName;
                gridViewColumn.Caption = DisplayName;
                gridViewColumn.Width = Unit.Pixel(Width);
                gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Left;
                gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
                gridViewColumn.SetDataItemTemplateContent(container =>
                {
                    htmlhelper.ViewContext.Writer.Write("<a href='" + URL + "'>" + container.Text + "</a>");

                });
                gridViewColumn.Settings.AutoFilterCondition = AutoFilterCondition.Contains;
            });
        }

        public static void GetLinkRedirectColumn(HtmlHelper htmlhelper, GridViewSettings settings, string FieldName, string DisplayName, string URL)
        {

            settings.Columns.Add(gridViewColumn =>
            {
                gridViewColumn.FieldName = FieldName;
                gridViewColumn.Caption = DisplayName;
                gridViewColumn.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
                gridViewColumn.CellStyle.HorizontalAlign = HorizontalAlign.Left;
                gridViewColumn.SetDataItemTemplateContent(container =>
                {

                    htmlhelper.ViewContext.Writer.Write("<a href='" + URL + "'>" + container.Text + "</a>");


                });
                gridViewColumn.Settings.AutoFilterCondition = AutoFilterCondition.Contains;

            });
        }

        #endregion

        #region DeleteButton

        public static void GetDeleteColumn(GridViewSettings settings, string ButtonID, string ButtonText, string ButtonURL, string fnCustomButtonClick, int width)
        {
            GridViewCommandColumnCustomButton deleteButton = new GridViewCommandColumnCustomButton()
            {
                ID = ButtonID,
                Text = ButtonText
            };
            deleteButton.Image.Url = ButtonURL;
            deleteButton.Image.AlternateText = "Delete";
            deleteButton.Image.IsResourcePng = true;
            settings.CommandColumn.Visible = true;
            settings.CommandColumn.ButtonType = System.Web.UI.WebControls.ButtonType.Image;
            settings.CommandColumn.CustomButtons.Add(deleteButton);
            settings.CommandColumn.Width = System.Web.UI.WebControls.Unit.Pixel(width);
            settings.CommandColumn.VisibleIndex = 30;
            settings.CommandColumn.SetHeaderTemplateContent(c =>
            {
            });
            settings.ClientSideEvents.CustomButtonClick = string.Format("function(s, e) {{ {0}(s, e); }}", fnCustomButtonClick);
        }

        public static void GetDeleteColumn(GridViewSettings settings, string ButtonID, string ButtonText, string ButtonURL, string fnCustomButtonClick)
        {
            GridViewCommandColumnCustomButton deleteButton = new GridViewCommandColumnCustomButton()
            {
                ID = ButtonID,
                Text = ButtonText
            };
            deleteButton.Image.Url = ButtonURL;
            deleteButton.Image.AlternateText = "Delete";
            deleteButton.Image.IsResourcePng = true;
            settings.CommandColumn.Visible = true;
            settings.CommandColumn.ButtonType = ButtonType.Image;
            settings.CommandColumn.CustomButtons.Add(deleteButton);
            settings.CommandColumn.Width = Unit.Pixel(16);
            settings.CommandColumn.VisibleIndex = 30;
            settings.CommandColumn.SetHeaderTemplateContent(c =>
            {
            });
            settings.ClientSideEvents.CustomButtonClick = string.Format("function(s, e) {{ {0}(s, e); }}", fnCustomButtonClick);
        }
        #endregion
    }
}


Now Creating GridView's Overload which makes you easier to build your DevExpress Grid. Here I am going to write code for it.

GridView Overload :

namespace DevExpress.Web.Mvc
{
  public static class GridViewExtensions
  {
    public static GridViewExtension GridViewDefaultPopup(this ExtensionsFactory extensionsFactory,
                                                                                    ViewContext viewContext,
                                                                                   CommonGridSettingPopupEntity objCommonGrid,
                                                                                    Action<GridViewSettings> action)
        {
            GridViewSettings gridViewSettings = new GridViewSettings();

            gridViewSettings.Name = objCommonGrid.GridName;
            gridViewSettings.KeyFieldName = objCommonGrid.KeyFieldName;


            #region Grid Height and Width

            if (!objCommonGrid.GridWidth_Height_In_Pixel)
            {
                gridViewSettings.Width = System.Web.UI.WebControls.Unit.Percentage(objCommonGrid.GridWidth);

                gridViewSettings.Height = System.Web.UI.WebControls.Unit.Percentage(objCommonGrid.GridHeight);

                gridViewSettings.Settings.HorizontalScrollBarMode = ScrollBarMode.Auto;
            }
            else
            {
                gridViewSettings.Width = System.Web.UI.WebControls.Unit.Pixel(Convert.ToInt32(objCommonGrid.GridWidth));

                if (objCommonGrid.GridHeight != 0)
                    gridViewSettings.Height = System.Web.UI.WebControls.Unit.Pixel(Convert.ToInt32(objCommonGrid.GridHeight));
            }

            // if (objCommonGrid.GridWidth != 0)

            #endregion

            #region Call Back Route (Controller , View , Action)
            if (!string.IsNullOrWhiteSpace(objCommonGrid.RouteController))
                gridViewSettings.CallbackRouteValues = new { Area = objCommonGrid.RouteArea, Controller = objCommonGrid.RouteController, Action = objCommonGrid.RouteAction };

            #endregion


            #region Pager and Filter Settings
            gridViewSettings.Settings.ShowFilterRow = true;
            gridViewSettings.Settings.ShowFilterRowMenu = true;
            gridViewSettings.SettingsPager.ShowEmptyDataRows = true;
            gridViewSettings.SettingsPager.Position = System.Web.UI.WebControls.PagerPosition.Bottom;
            gridViewSettings.SettingsPager.FirstPageButton.Visible = true;
            gridViewSettings.SettingsPager.LastPageButton.Visible = true;
            gridViewSettings.SettingsPager.PageSizeItemSettings.Visible = true;
            gridViewSettings.SettingsPager.PageSizeItemSettings.Items = new string[] { "10", "20", "50" };

            #endregion

            #region Empty Row Template
            gridViewSettings.SetEmptyDataRowTemplateContent(c =>
            {
                viewContext.Writer.Write("No data to display");
            });

            #endregion

            #region Get and Save Layout Code

            if (!string.IsNullOrWhiteSpace(objCommonGrid.GridLayoutName))
            {
                gridViewSettings.DataBinding = (sender, e) =>
                {
                    //Set Session Value
                    HttpContext.Current.Session["GridLayout"] = null;
                };

                gridViewSettings.ClientLayout = (sender, e) =>
                {

                    if (e.LayoutMode == ClientLayoutMode.Loading)
                    {

                        iFacilities.Controllers.CommonController obj = new iFacilities.Controllers.CommonController();
                        string strLayout = obj.getLayout(objCommonGrid.GridLayoutName,
                            iFacilities.Controllers.CommonController.getLoginUserInfoByEnum(CommonFunctions.UserLoginData.UserID, HttpContext.Current.Request.Cookies[System.Web.Security.FormsAuthentication.FormsCookieName]));

                        if (strLayout.Length > 0)
                            e.LayoutData = strLayout;
                    }
                    else
                        if (e.LayoutMode == ClientLayoutMode.Saving)
                        {
                            HttpContext.Current.Session["GridLayout"] = String.Format("{0}#$#{1}", e.LayoutData, objCommonGrid.GridLayoutName);
                        }
                };
            }

            #endregion

            #region Column Chooser
            gridViewSettings.SettingsBehavior.EnableCustomizationWindow = true;
            #endregion

            #region Popup Edit code

            gridViewSettings.SettingsEditing.Mode = GridViewEditingMode.PopupEditForm;
            gridViewSettings.SettingsPopup.EditForm.Modal = true;
            gridViewSettings.SettingsPopup.EditForm.HorizontalAlign = PopupHorizontalAlign.WindowCenter;
            gridViewSettings.SettingsPopup.EditForm.VerticalAlign = PopupVerticalAlign.WindowCenter;
            if (objCommonGrid.PopupWidth_Height_In_Perc)
            {
                gridViewSettings.SettingsPopup.EditForm.Width = Unit.Percentage(objCommonGrid.PopupWidth_Pixel);
                gridViewSettings.SettingsPopup.EditForm.Height = Unit.Percentage(objCommonGrid.PopupHeight_Pixel);
            }
            else
            {
                gridViewSettings.SettingsPopup.EditForm.Width = Unit.Pixel(objCommonGrid.PopupWidth_Pixel);
                gridViewSettings.SettingsPopup.EditForm.Height = Unit.Pixel(objCommonGrid.PopupHeight_Pixel);
            }

            gridViewSettings.SettingsPopup.EditForm.ResizingMode = ResizingMode.Live;


            gridViewSettings.SettingsText.PopupEditFormCaption = objCommonGrid.PopupEditFormCaption;

            gridViewSettings.CommandColumn.SetHeaderTemplateContent(c =>
            {

            });
            #endregion

            action(gridViewSettings);
            return new GridViewExtension(gridViewSettings, viewContext);
        }
  }
}



Lastly, I am showing you Example which uses the overload's we have created.

Grid View Example with Custom Overload :

 This is our MVC View :

@{
 CommonGridSettingPopupEntity objGridSettings = new CommonGridSettingPopupEntity(); objGridSettings.GridName = "grdContractBuildingInfo";
 objGridSettings.RouteArea = "Contract";
 objGridSettings.RouteController = "ContractInfo";
 objGridSettings.RouteAction = "PartialContractBuildingFormGrid";
 objGridSettings.KeyFieldName = "KeyID";
 objGridSettings.PopupEditFormCaption = "Contract Building Info"; objGridSettings.GridWidth_Height_In_Pixel = true;
 objGridSettings.GridHeight = 600;
 objGridSettings.GridWidth = 1000;
 objGridSettings.PopupHeight_Pixel = 500;
 objGridSettings.PopupWidth_Pixel = 800;
}
@Html.DevExpress().GridViewDefaultPopup(Html.ViewContext, objGridSettings, settings => { 

settings.ClientSideEvents.BeginCallback = string.Format("function(s, e) {{ }}");

GridColumn.GetHiddenColumn(settings, "KeyID");
GridColumn.GetHiddenColumn(settings, "BuildingID");

DevExpress.Web.Mvc.GridColumn.GetLinkColumn(this.Html, settings, "Building", "Building", 320, settings.Name);

GridColumn.GetStringColumn(settings, "JobNo", "Job No", 110, true);

 GridColumn.GetStringColumn(settings, "Manager", "Manager", 140);

 GridColumn.GetStringColumn(settings, "Director", "Director", 140);

 GridColumn.GetStringColumn(settings, "Supervisor", "Supervisor", 140);

 GridColumn.GetDateColumn(settings, "TerminateDate", "Terminate Date", 110);

 GridColumn.GetDateColumn(settings, "SuspendDate", "Suspend Date", 110);

 settings.SetEditFormTemplateContent(c => {
Html.RenderAction("PartialContractBuildingFormEdit");
});

 }).BindToLINQ("iFacilities.MODEL", "tblSQL", (s, e) =>
{
 e.KeyExpression = "KeyID";
 DBEntities DBS = new DBEntities();
 IQueryable ContractBuilding = from tbl in DBS.tblSQL select tbl;
  e.QueryableSource = ContractBuilding;
}).GetHtml()

Now try this to implement in your project. And See how much time you can save to implemet DevExpress Grid.

ENJOY !!

Posted by at 1 comment:

Thursday, 8 May 2014

SQL - Paging Logic Stored Procedure and Between Dates Filter Query

Here is the store procedure by which you can fetch your require data with paging and filter you need

Paging Logic
--EXEC dbo.[Demo_Paging_Logic] 1,10,'LookupValue ASC','LookupValue like''%123%''
CREATE PROCEDURE dbo.[Demo_Paging_Logic] @PageNumber INT = 1
 ,@PageSize INT = 5
 ,@SortExpression NVARCHAR(20) = 'LookupValue DESC'
 ,@WhereCondition NVARCHAR(MAX) = ' 1=1 '
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @FirstRec INT
  ,@LastRec INT
  ,@TotalRows INT

 SET @FirstRec = (@PageNumber - 1) * @PageSize
 SET @LastRec = (@PageNumber * @PageSize + 1)
 SET @TotalRows = @FirstRec - @LastRec + 1

 DECLARE @SortExpression1 NVARCHAR(MAX)
 DECLARE @Query NVARCHAR(MAX)
 DECLARE @Query1 NVARCHAR(MAX)

 SET @Query = 'SELECT *
     FROM(SELECT Cast(ROW_NUMBER() OVER (ORDER BY  ' + @SortExpression + ') As int) AS ROWNUM, COUNT(*) OVER(PARTITION BY 1) TotalRecord,*
     FROM Master.tblLookupValues WHERE  ' + @WhereCondition + ') AS  tbl
     WHERE ROWNUM > ' + CONVERT(NVARCHAR(10), @FirstRec) + ' AND ROWNUM < ' + CONVERT(NVARCHAR(10), @LastRec) + ' ORDER BY ROWNUM ASC'

 PRINT @Query

 EXEC sp_Executesql @Query
END
GO


Filter between two dates in SQL

 
DECLARE @StartDate AS VARCHAR(10) = '10-03-2014'
DECLARE @EndDate AS VARCHAR(10) = '26-03-2014'

SELECT *
FROM tABLE
WHERE Cast(convert(DATETIME, Cast(CreatedDate AS DATETIME), 103) AS DATE) BETWEEN Cast(convert(DATETIME, @StartDate, 103) AS DATE)
AND Cast(convert(DATETIME, @EndDate, 103) AS DATE)
Posted by at No comments:
Subscribe to: Posts (Atom)