Monthly Archives: February 2013

Gridview Model binding with Control attribute as parameter

Asp.Net MVC had enjoyed the benefit of Model binding since its inception. It helped in few things such as

  • UI and data model separation.
  • Unit testing of the data binding method. (great! isn’t it?)

With Asp.Net 4.5, Model binding is now available for Web forms as well. Moreover it is integrated in a seamless manner.

so here we go!

UI Code:

New Gridview has two properties to be noticed.

  • ItemType:
  • It needs a type name that you are binding to. It can be a dll which contains the actual method.

  • SelectMethod:
  • It needs a method which returns an IEnumerable or IQueryable type.

    Similarly we have UpdateMethod which is not covered in this post.

    <asp:DropDownList ID="ddlNameFilter" runat="server" AutoPostBack="true" Width="500px" >
                    <asp:ListItem Value="" Text="-- Select word name starts with --" Selected="True" />
                    <asp:ListItem Value="KE" Text="KE" />
                    <asp:ListItem Value="TE" Text="TE" />
                </asp:DropDownList>
    
    </br></br>
                
             <asp:GridView ID="gvPersons" runat="server" 
                    AllowPaging="True" AutoGenerateColumns="False" PageSize="20"             
                 ItemType="WebFormSamples.Person" SelectMethod="GetPersonList" 
                 OnRowDataBound="gvPersons_RowDataBound"
    
                    OnPageIndexChanging="gvPersons_PageIndexChanging">
                    <Columns>
                        <asp:BoundField DataField="BusinessEntityID" HeaderText="Product"
                            SortExpression="BusinessEntityID" />
                        <asp:BoundField DataField="PersonType" HeaderText="PersonType"
                            ReadOnly="True" SortExpression="PersonType" />
                        <asp:BoundField DataField="FirstName" HeaderText="FirstName"
                            SortExpression="FirstName" />
                        <asp:BoundField DataField="LastName" HeaderText="LastName"
                            SortExpression="LastName" />
                    </Columns>
                    <PagerStyle HorizontalAlign="Right" />
                    <PagerSettings Mode="Numeric" />
                </asp:GridView>
    

    Code Behind:

     
            // ddlNameFIlter is a dropdown which user selects and selected value is passed as parameter to the below //<strong>SelectMethod</strong> of Gridview. 
            public IQueryable<Person> GetPersonList([Control("ddlNameFilter")] string searchName)
            {
                List<Person> persons = new List<Person>();
                if ( string.IsNullOrEmpty(searchName))
                {
                    persons = Person.GetPersonList();
                }
                else
                    persons = Person.GetPersonList().Where(p => p.FirstName.ToLower().StartsWith(searchName.ToLower())).ToList();
    
                return persons.AsQueryable();
            }

    There are few more Value providers introduced in Asp.Net 4.5. [QueryString],[Cookie] are also very useful.

    I hope it helps!

    Advertisements

    GridView with built in CustomPaging – Asp.Net 4.5

    Every web developer hates Gridview ViewState specially when its large in size. Solution to this is to create a custom paging solution. but it was too much of work. You need to Create a custom control and then handling all the combinations of click on page links. Styling of the pager was also not standard.

    Asp.Net 4.5 resolved this by providing a Gridview attribute AllowCustomPaging = “true” which allow us to utilize built in gridview paging with custom paging logic. There is one more property VirtualItemCount added to gridview which should be set to the total number of records.

    Below is the simple example. It seems so easy that you can do it right away in your existing code with minimal changes.

    UI Code:

    
     <asp:GridView ID="gvPersons" runat="server" 
      AllowCustomPaging="true"
      AllowPaging="True"           AutoGenerateColumns="False" PageSize="20"
      OnPageIndexChanging="gvPersons_PageIndexChanging">
    
     <Columns>
     <asp:BoundField DataField="BusinessEntityID" HeaderText="Product"
     SortExpression="BusinessEntityID" />
    
     <asp:BoundField DataField="PersonType" HeaderText="PersonType"
     ReadOnly="True" SortExpression="PersonType" />
    
     <asp:BoundField DataField="FirstName" HeaderText="FirstName"
     SortExpression="FirstName" />
    
     <asp:BoundField DataField="LastName" HeaderText="LastName"
     SortExpression="LastName" />
    
     </Columns>
     <PagerStyle HorizontalAlign="Right" />
     <PagerSettings Mode="Numeric" />
     </asp:GridView>
    
    

    Custom Paging Stored Procedure in Sql server 2012

    This query is the heart of this custom paging logic. Sql Server 2012 provides simply “Awesome” way of writing a very complex query (in earlier versions).

    
    CREATE PROCEDURE usp_GetPersonsPaged
    	@StartRowIndex  int,
    	@MaximumRows	int
    AS
    BEGIN
    	
    	SET NOCOUNT ON;
    	    DECLARE @lastRowIndex int
    		SET @lastRowIndex = (@StartRowIndex + @MaximumRows) - 1;
    		 
    
    	 SELECT   [BusinessEntityID]
    		 ,[PersonType]     
    		 ,[FirstName]     
    		 ,[LastName]  FROM [AdventureWorks2012].Person.Person
    	 ORDER BY BusinessEntityID
    	 OFFSET @StartRowIndex ROWS
    	 FETCH NEXT @lastRowIndex ROWS ONLY;	
    
    END
    
    

    Code Behind:

    Nothing changes here. Only your data access layer method returns Maximum rows equal to page size.

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    gvPersons.VirtualItemCount = Person.GetTotalPersonCount() ;
                    BindGrid(0,gvPersons.PageSize);                
                }
            }
    
            private void BindGrid(int pageIndex,int pageSize)
            {            
                gvPersons.DataSource = Person.GetPersonListPaged(pageIndex, gvPersons.PageSize);
                gvPersons.DataBind();
            }
    
            protected void gvPersons_PageIndexChanging(object sender, GridViewPageEventArgs e)
            { 
                gvPersons.PageIndex = e.NewPageIndex;
                BindGrid(gvPersons.PageIndex,gvPersons.PageSize);
            }
    

    Data Access Code:

    you can use AdventureWorks Sample DB for Sql server 2012 to check out performance.

    
    public class Person
        {
            public int BusinessEntityID { get; set; }
    
            public string PersonType { get; set; }
    
            public string FirstName { get; set; }
    
            public string LastName { get; set; }
    
    
            public static List<Person> GetPersonListPaged(int startIndex, int maxPazeSize)
            {
                List<Person> persons = new List<Person> ();
    
                SqlConnection con = new SqlConnection();
                SqlCommand cmd = new SqlCommand();
                try
                {
                    con.ConnectionString = "data source=.\\sqlserver2012;initial catalog=AdventureWorks2012;integrated security=True;";
    
                    cmd.Connection = con;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = "usp_GetPersonsPaged";
    
                    cmd.Parameters.AddWithValue("StartRowIndex", startIndex);
                    cmd.Parameters.AddWithValue("MaximumRows"  , maxPazeSize);
                    con.Open();
                    
                    SqlDataReader dr;
                    dr = cmd.ExecuteReader();
                    
                    while (dr.Read())
                    {
                        Person p = new Person() {BusinessEntityID=dr.GetInt32(0),PersonType=dr.GetString(1),FirstName=dr.GetString(2),LastName=dr.GetString(3) };
    
                        persons.Add(p);                    
                    }                
                                    
                    con.Close();
                }
                catch (Exception ex)
                {
    
                }
                finally
                {
                    con.Dispose();
                    cmd.Dispose();
                }
    
                return persons;
            }
    
    
    
            public static int GetTotalPersonCount()
            {
                int totalCount = 0;
    
                SqlConnection con = new SqlConnection();
                SqlCommand cmd = new SqlCommand();            
                try
                {
                    con.ConnectionString = "data source=.\\sqlserver2012;initial catalog=AdventureWorks2012;integrated security=True;";
                    
                    cmd.Connection = con;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = "select count(1) from [AdventureWorks2012].[Person].[Person];";
                    con.Open();
                    totalCount = (int)cmd.ExecuteScalar();
    
                    con.Close();
                }
                catch (Exception ex)
                {
    
    
                }
                finally
                {
                    con.Dispose();
                    cmd.Dispose();
                }
    
                return totalCount;
            }
        }
    }
    

    I hope it helps!!