ASP.NET Gridview with custom paging and sorting.

When displaying large amounts of data it's often best to only display a portion of the data, allowing the user to step through the data ten or so records at a time. Additionally, the end user's experience can be enhanced if they are able to sort the data by one of the columns.
While default paging was easy to implement, it carried with it a performance cost since all records to be paged through were being returned from the database. That is, if the DataGrid was paging through a total of 1,000 records, showing 10 records per page, on each and every page request all 1,000 records would be returned from the database, but only the 10 appropriate ones would be displayed.
Custom paging solved this performance issue by requiring the page developer to tell the DataGrid exactly how many total records were being paged through as well as returning the precise subset of records to display on the page. The following example use ObjectDataSource for DAL and Microsoft.Practices.EnterpriseLibrary.Data.dll (download here) to shorten code.

Gridview with custom paging and sorting.

//Here is server control.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
    AllowPaging="True" AllowSorting="True" AlternatingRowStyle-Wrap="True" 
    DataSourceID="ObjectDataSource1" EmptyDataText="There is no data.">
    <RowStyle BackColor="#EFF3FB" />
    <Columns>
        <asp:BoundField HeaderText="No." DataField="No" />
        <asp:BoundField HeaderText="CustomerID" DataField="CustomerID" SortExpression="CustomerID"/>
        <asp:BoundField HeaderText="CompanyName" DataField="CompanyName" SortExpression="CompanyName" />
        <asp:BoundField HeaderText="ContactName" DataField="ContactName" SortExpression="ContactName" />
        <asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle" SortExpression="ContactTitle" />
    </Columns>
    <AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GridDataPage"
    TypeName="DAL" SelectCountMethod="DataRowCount" 
    SortParameterName="SortExpression" EnablePaging="True">
    <SelectParameters>
        <asp:Parameter Name="maximumRows" Type="Int32" />
        <asp:Parameter Name="startRowIndex" Type="Int32" />
        <asp:Parameter Name="SortExpression" Type="String" />
    </SelectParameters>
</asp:ObjectDataSource>

Here is the connection string in web.config and it use sample "Northwind" db (download here) and "Customers" table.

<connectionStrings>
    <add name="sqlConn" providerName="System.Data.SqlClient"  connectionString="Server='.\SQLEXPRESS';uid='xxxxxx';pwd='xxxxxxxx';Database='Northwind';Pooling=False;"/>
</connectionStrings>

Here is the underlying DAL class for ObjectDataSource.

using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
public class DAL
{
    // Methods
    private static Database getDatabase()
    {
        Database database = null;
        try
        {
            database = DatabaseFactory.CreateDatabase("sqlConn");
        }
        catch (Exception ex)
        {
            //handle exception here.
        }
        return database;
    }
    public static DataSet GridDataPage(int maximumRows, int startRowIndex, string SortExpression)
    {
        DataSet ds = new DataSet();
        if (startRowIndex >= maximumRows)
        {
            startRowIndex++;
        }
        try
        {
            ds = getGridData(maximumRows, startRowIndex, SortExpression);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                startRowIndex = (startRowIndex > 0) ? startRowIndex : 1;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    ds.Tables[0].Rows[i][0] = Convert.ToString((int)(i + startRowIndex));
                }
                HttpContext.Current.Items["rowCount"] = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
                return ds;
            }
            HttpContext.Current.Items["rowCount"] = 0;
        }
        catch (Exception ex)
        {
            //handle exception here.
        }
        return ds;
    }
    public static DataSet getGridData(int maximumRows, int startRowIndex, string SortExpression)
    {
        DataSet ds = new DataSet();
        try
        {
            ds = getDatabase().ExecuteDataSet("GetCustomers", new object[] { maximumRows, startRowIndex, SortExpression });
        }
        catch (Exception ex)
        {
            ds = null;
            //handle exception here.
        }
        return ds;
    }
    public static int DataRowCount(int maximumRows, int startRowIndex, string SortExpression)
    {
        return (int)HttpContext.Current.Items["rowCount"];
    }
}

//Here is transact-sql.

USE [Northwind]
GO
ALTER PROCEDURE [dbo].[GetCustomers]
    @pageSize INT = NULL,
    @pageStart INT = NULL,
    @orderBy nvarchar(200) = NULL
AS
BEGIN

DECLARE @sqlPopulate VARCHAR(2000)
IF @pageSize IS NULL or @pageSize = 0
    SET @pageSize = 10;
IF @pageStart IS NULL
    SET @pageStart = 0;
IF @orderBy IS NULL OR @orderBy = '' 
    SET @orderBy = 'CustomerID DESC';
IF @pageStart = 0
BEGIN
    SET @sqlPopulate = 'SELECT '' '' AS No, CustomerID, CompanyName, ContactName, ContactTitle ' +
        + ' FROM dbo.Customers WHERE CustomerID IN '+
        '(SELECT top '+ CAST(@pageSize AS VARCHAR(10)) +' CustomerID FROM dbo.Customers ';

    SET @sqlPopulate = @sqlPopulate + ' ORDER BY ' + @orderBy +
        ') ORDER BY ' + @orderBy;
END
ELSE
BEGIN
    SET @sqlPopulate = 'SELECT '' '' AS No, CustomerID, CompanyName, ContactName, ContactTitle ' +
        ' FROM Customers WHERE CustomerID IN '+
        '(SELECT top '+ CAST(@pageSize AS VARCHAR(10)) + ' CustomerID FROM Customers WHERE CustomerID NOT IN '+
            '(SELECT top '+ CAST((@pageStart-1) AS VARCHAR(10)) +' CustomerID FROM Customers ';

    SET @sqlPopulate = @sqlPopulate + ' ORDER BY ' + @orderBy +
            ') ORDER BY ' + @orderBy + ') ORDER BY ' + @orderBy;
END
EXEC(@sqlPopulate)

SET @sqlPopulate = '';
SET @sqlPopulate = 'SELECT COUNT(*) FROM Customers';
EXEC (@sqlPopulate)
END 

Useful link:
Microsoft Enterprise Library

Similar Article

That's it. Enjoy!

What is HTML5?

HTML5 will be the new standard for HTML, XHTML, and the HTML DOM.

The previous version of HTML came in 1999. The web has changed a lot since then.

HTML5 is still a work in progress. However, most modern browsers have some HTML5 support.

Some of the most interesting new features in HTML5:
    * The canvas element for drawing
    * The video and audio elements for media playback
    * Better support for local offline storage
    * New content specific elements, like article, footer, header, nav, section
    * New form controls, like calendar, date, time, email, url, search


Original link : http://www.w3schools.com/html5/html5_intro.asp

How to write log using log4net in asp.net.

The Apache log4net library is a tool to help the programmer output log statements to a variety of output targets. log4net is a port of the excellent Apache log4j™ framework to the Microsoft® .NET runtime.
This article describes the easy way to be followed for using File Appender of Log4net to write to a text file in a web application day by day.

First of all, get the latest version of log4net library and add reference of it in your project. You can download here : http://logging.apache.org/log4net/download_log4net.cgi.

Add below line in your AssemblyInfo.cs file.
[assembly: log4net.Config.XmlConfigurator(ConfigFile="Web.config", Watch=true)]

Folder where you put log4net

Add below section in Web.Config file.

<configuration>
  <configSections>
    <!-- log4net START here-->
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net"/>
    <!-- log4net STOP here-->
  </configSections>
  <!-- log4net START here-->
  <log4net>
    <root>
      <level value="ALL"/>
      <appender-ref ref="MyTxt"/>
    </root>
    <appender name="MyTxt" type="log4net.Appender.RollingFileAppender">
      <file value="G:\Log_"/>
      <!-- This is file path to save the log. Note : don't put file extension.-->
      <appendToFile value="true"/>
      <datePattern value="yyyy-MM-dd.\tx\t"/>
      <!-- This is date format for file name to save the log.-->
      <rollingStyle value="Date"/>
      <param name="StaticLogFileName" value="false"/>
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%d [%-5thread] %-5level : %logger : %message %timestampms %n"/>
      </layout>
    </appender>
  </log4net>
  <!-- log4net END here-->
  <appSettings>
    <add key="logging" value="Y"/>
    <!-- Here is boolean flag to log or not-->
  </appSettings>
  //Other sections may go here
</configuration>

Add one class Logging.cs file.
public class Logging
{
    public Logging()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public static void logMsg(string msg, ref log4net.ILog log)
    {
         if (ConfigurationManager.AppSettings["logging"].ToString().Equals("Y"))
        {
            //There are seven logging levels.
            log.Error("MyApp : " + msg);
            //log.Fatal("MyApp : " + msg);
            //log.Warn("MyApp : " + msg);
            //log.Info("MyApp : " + msg);
            //log.Debug("MyApp : " + msg);
        }
    }
}

In your code-behind,
//declare private static variable.
private static log4net.ILog log4 = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

protected void Page_Load(object sender, EventArgs e)
{
    try
    {
        String Qs = Request.QueryString["id"].ToString();
        //here cause error when it miss id querystring.
    }
    catch (Exception ex)
    {
        Logging.logMsg(ex.Message, ref log4);
    }
}

when it lead to error every times, it save the error in log file(here may be Log_2011-12-22.txt) and log statement may be similar as below :
2011-12-22 16:37:47,034 [4688 ] ERROR : WebAppBlog._Default : MyApp : Object reference not set to an instance of an object. 534ms

That's it. Thank for reading.

$(document).ready() vs pageLoad() vs Application.Init

jQuery’s $(document).ready()

    * Ideal for onetime initialization.
    * Optimization black magic; may run slightly earlier than pageLoad().
    * Does not re-attach functionality to elements affected by partial postbacks.

ASP.NET AJAX’s pageLoad()

    * Unsuitable for onetime initialization if used with UpdatePanels.
    * Slightly less optimized in some browsers, but consistent.
    * Perfect for re-attaching functionality to elements within UpdatePanels.

ASP.NET AJAX’s Application.Init

    * Useful for onetime initialization if only ASP.NET AJAX is available.
    * More work required to wire the event up.
    * Exposes you to the “sys is undefined” error if you aren’t careful. For this error, see this post
       http://www.aspmemo.net/2011/12/how-to-solve-sys-is-undefined-error.html


Original Post : http://encosia.com/document-ready-and-pageload-are-not-the-same/

What is jQuery?

jQuery is a library of JavaScript Functions.

jQuery is a lightweight "write less, do more" JavaScript library.

The jQuery library contains the following features:

    * HTML element selections
    * HTML element manipulation
    * CSS manipulation
    * HTML event functions
    * JavaScript Effects and animations
    * HTML DOM traversal and modification
    * AJAX
    * Utilities

More detail : http://www.w3schools.com/jquery/jquery_intro.asp