How to display HTML code in your Blogger Post

Convert the code to display in a blog post using this tool:

http://www.accessify.com/tools-and-wizards/developer-tools/quick-escape/default.php

Sorting and Paging a GridView with Custom DataSource in ASP.Net

So it's pretty simple to use a GridView in ASP.Net you can bind it to all sorts of data, but what I like doing is binding it to collections of my own custom objects. But this causes issues, because then you can't use all the nice in built paging and sorting without extra cosing. If you use the GridView wizard are hook it up to a SQLdatasource directly or a data set then all the paging and sorting is handled for you. But what if like me you want to use your own custome objects.

Well there is a way, it does require a little extras coding but it's nothing too complicated. Firstly lets look at the code to set up a standard GridView:

Client Side Code:

<asp:gridview id="GridView1" runat="server" autogeneratecolumns="False" onrowdatabound="GridView1_RowDataBound" onselectedindexchanging="GridView1_SelectedIndexChanging">

   <columns>

       <asp:boundfield datafield="ProjectID" headertext="ProjectID"/>

       <asp:boundfield datafield="ProjectName" headertext="ProjectName"/>

       <asp:boundfield datafield="Sector" headertext="Sector"/>

       <asp:boundfield datafield="ProjectStatus" headertext="Project Status"/>

   </columns>

</asp:gridview>

Server Side Code:

IList<project> projectList = Project.GetAllProjects();
GridView1. DataSource = projectList;
GridView1.DataBind();

Project Class:

public class Project
{

public Project()
{
}

public int ProjectID { get;set;}

public string ProjectName {get;set;}

public string Sector {get;set;}

public string ProjectStatus {get;set;}

static public Project GetAllProjects()
}

So this will display my list of custom objects in a simple GridView but I can't do paging or sorting, bummer. So what do we have to change to get it to work?

Well first on the ASP.Net page we add an ObjectDataSource control and point that to our datasource. This will help make our custome data collection more compatible with the GridView. We can then point out GridView at this ObjectDataSource.

We can then set paging on the GridView and Sorting. But for the sorting to work we still need some extra code. We need to make our custom data class comparable. So in the GetAllProjects method we add a parameter that will handle the sortExpression, we add this into the code of our GridView as well.

Then we add a switch statement in here to handle this sort expression. This requires us also to create a comparer class, we will call it ProjectComparer and in there we place code to handle the comparisons for each project value.


Client Side Code:

<asp:ObjectDataSource ID="dsProjects" runat="server" DataObjectTypeName="Business.DomainLogic.Project" TypeName="Business.DomainLogic.Project" SelectMethod="GetAllProjects" SortParameterName="sortExpression"></asp:ObjectDataSource>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
       OnRowDataBound="GridView1_RowDataBound" DataSourceID="dsProjects"
       AllowPaging="True" PageSize="8" AllowSorting="True"
       OnSelectedIndexChanging="GridView1_SelectedIndexChanging"
       OnPageIndexChanging="GridView1_PageIndexChanging">
       <Columns>
           <asp:BoundField DataField="ProjectID" HeaderText="ProjectID"  />
           <asp:BoundField DataField="ProjectName" HeaderText="ProjectName" SortExpression="ProjectName" />
           <asp:BoundField DataField="Sector" HeaderText="Sector" SortExpression="Sector" />
           <asp:BoundField DataField="ProjectStatus" HeaderText="Project Status" SortExpression="ProjectStatus" />
       </Columns>
   </asp:GridView>

Server Side Code:

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
       {
           GridView1.PageIndex = e.NewPageIndex;
           GridView1.DataBind();
       }

Project Class:

public List<project> GetAllProjects(string sortExpression)
{
      List<project> projects = new List<project>();

      switch (sortExpression)
           {
               case "ProjectName":
               case "ProjectName ASC":
                   projects.Sort(ProjectComparer.CompareByName);
                   break;

               case "ProjectName DESC":
                   projects.Sort(ProjectComparer.CompareByNameDesc);
                   break;

               case "Sector":
               case "Sector ASC":
                   projects.Sort(ProjectComparer.CompareBySector);
                   break;

               case "Sector DESC":
                   projects.Sort(ProjectComparer.CompareBySectorDesc);
                   break;

               case "ProjectStatus":
               case "ProjectStatus ASC":
                   projects.Sort(ProjectComparer.CompareByProjectStatus);
                   break;

               case "ProjectStatus DESC":
                   projects.Sort(ProjectComparer.CompareByProjectStatusDesc);
                   break;
           }
}

ProjectComparer Class:

public class ProjectComparer
   {
       //COMPARE BY NAME

       private static IComparer<project> _compareByName = new _sortName(false);
       public static IComparer<project> CompareByName { get { return _compareByName; } }

       private static IComparer<project> _compareByNameDesc = new _sortName(true);
       public static IComparer<project> CompareByNameDesc { get { return _compareByNameDesc; } }

       private class _sortName : IComparer<project>
       {
           bool _reverse;
           public _sortName(bool reverse)
           {
               this._reverse = reverse;
           }

           public int Compare(Project x, Project y)
           {
               if (_reverse) return y.ProjectName.CompareTo(x.ProjectName);
               else return x.ProjectName.CompareTo(y.ProjectName);
           }
       }


//COMPARE BY SECTOR

       private static IComparer<project> _compareBySector = new _sortSector(false);
       public static IComparer<project> CompareBySector { get { return _compareBySector; } }

       private static IComparer<project> _compareBySectorDesc = new _sortSector(true);
       public static IComparer<project> CompareBySectorDesc { get { return _compareBySectorDesc; } }

       private class _sortSector : IComparer<project>
       {
           bool _reverse;
           public _sortSector(bool reverse)
           {
               this._reverse = reverse;
           }

           public int Compare(Project x, Project y)
           {
               if (_reverse) return y.Sector.CompareTo(x.Sector);
               else return x.Sector.CompareTo(y.Sector);
           }
       }


//COMPARE BY Project Status

       private static IComparer<project> _compareByProjectStatus = new _sortProjectStatus(false);
       public static IComparer<project> CompareByProjectStatus { get { return _compareByProjectStatus; } }

       private static IComparer<project> _compareByProjectStatusDesc = new _sortProjectStatus(true);
       public static IComparer<project> CompareByProjectStatusDesc { get { return _compareByProjectStatusDesc; } }

       private class _sortProjectStatus : IComparer<project>
       {
           bool _reverse;
           public _sortProjectStatus(bool reverse)
           {
               this._reverse = reverse;
           }

           public int Compare(Project x, Project y)
           {
               if (_reverse) return y.ProjectStatus.CompareTo(x.ProjectStatus);
               else return x.ProjectStatus.CompareTo(y.ProjectStatus);
           }
       }

}

And that will give you paging and sorting on your GridView bound to a collection of custom objects. If i have missed any thing or it doesn't make any sense, plese just drop me a comment.

SQL Server Collation

This error sucks the first time you see it because you may have no idea what it means:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

Well you can do a search to find the solution but basically if your doing a join between two tables from two seperate databases then this might happen. One has the wrong collation. You can Check like this:

use DB1
print 'My database [' + db_name() + '] collation is: ' + cast( DATABASEPROPERTYEX ( db_name(), N'Collation' ) as varchar(128) )

use DB2
print 'My database [' + db_name() + '] collation is: ' + cast( DATABASEPROPERTYEX ( db_name(), N'Collation' ) as varchar(128) )

If they are different then on your join just add this to the end of the code to convert the collation.

JOIN
Table T ON T.ID = P.ID COLLATE Latin1_General_CI_AS