21 Jun 2012

Getting distinct rows from datatable in .net 2.0


Dim dt As DataTable = readCODates()
Dim distinctValues As DataTable = dt.DefaultView.ToTable(True, "Date")
at No comments:

12 Jun 2012

ASP.NET - Multi Threading

A thread is defined as the execution path of a program. Each thread defines a unique flow of control. If your application involves complicated and time consuming operations like database access or some intense I/O operations, then it is often helpful to set different execution paths or threads, with each thread performing a particular job.
Threads are lightweight processes. One common example of use of thread is implementation of concurrent programming by modern operating systems. Use of threads saves wastage of CPU cycle and increase efficiency of an application.
So far we have written programs where a single thread runs as a single process which is the running instance of the application. However, this way the application can perform one job at a time. To make it execute more than one task at a time, it could be divided into smaller threads.
In .NET, the threading is handled through the System.Threading namespace. Creating a variable of the System.Threading.Thread type allows you to create a new thread to start working with. It allows you to create and access individual threads in a program.

Creating Thread:

A thread is created by creating a Thread object, giving its constructor a ThreadStart reference.
ThreadStart childthreat = new ThreadStart(childthreadcall);

The Thread Life Cycle:

The life cycle of a thread starts when an object of the System.Threading.Thread class is created and ends when the thread is terminated or completes execution.
Following are the various states in the life cycle of a thread :
  • The Unstarted State: it is the situation when the instance of the thread is created but the Start method has not been called.
  • The Ready State: it is the situation when the thread is ready to run and waiting CPU cycle.
  • The Not Runnable State: a thread is not runnable, when:
    • Sleep method has been called
    • Wait method has been called
    • Blocked by I/O operations
  • The Dead State: it is the situation when the thread has completed execution or has been aborted.

The Thread Priority:

The Priority property of the Thread class specifies the priority of one thread with respect to other. The .Net runtime selects the ready thread with the highest priority.
The priorities could be categorised as:
  • Above normal
  • Below normal
  • Highest
  • Lowest
  • Normal
Once a thread is created its priority is set using the Priority property of the thread class.
NewThread.Priority = ThreadPriority.Highest;

Thread Properties & Methods:

The Thread class has the following important properties:
PropertyDescription
CurrentContextGets the current context in which the thread is executing.
CurrentCultureGets or sets the culture for the current thread.
CurrentPrincipleGets or sets the thread's current principal (for role-based security).
CurrentThreadGets the currently running thread.
CurrentUICultureGets or sets the current culture used by the Resource Manager to look up culture-specific resources at run time.
ExecutionContextGets an ExecutionContext object that contains information about the various contexts of the current thread.
IsAliveGets a value indicating the execution status of the current thread.
IsBackgroundGets or sets a value indicating whether or not a thread is a background thread.
IsThreadPoolThreadGets a value indicating whether or not a thread belongs to the managed thread pool.
ManagedThreadIdGets a unique identifier for the current managed thread.
NameGets or sets the name of the thread.
PriorityGets or sets a value indicating the scheduling priority of a thread.
ThreadStateGets a value containing the states of the current thread.
The Thread class has the following important methods:
MethodsDescription
AbortRaises a ThreadAbortException in the thread on which it is invoked, to begin the process of terminating the thread. Calling this method usually terminates the thread.
AllocateDataSlotAllocates an unnamed data slot on all the threads. For better performance, use fields that are marked with the ThreadStaticAttribute attribute instead.
AllocateNamedDataSlotAllocates a named data slot on all threads. For better performance, use fields that are marked with the ThreadStaticAttribute attribute instead.
BeginCriticalRegionNotifies a host that execution is about to enter a region of code in which the effects of a thread abort or unhandled exception might jeopardize other tasks in the application domain.
BeginThreadAffinityNotifies a host that managed code is about to execute instructions that depend on the identity of the current physical operating system thread.
EndCriticalRegionNotifies a host that execution is about to enter a region of code in which the effects of a thread abort or unhandled exception are limited to the current task.
EndThreadAffinityNotifies a host that managed code has finished executing instructions that depend on the identity of the current physical operating system thread.
FreeNamedDataSlotEliminates the association between a name and a slot, for all threads in the process. For better performance, use fields that are marked with the ThreadStaticAttribute attribute instead.
GetDataRetrieves the value from the specified slot on the current thread, within the current thread's current domain. For better performance, use fields that are marked with the ThreadStaticAttribute attribute instead.
GetDomainReturns the current domain in which the current thread is running.
GetDomainIDReturns a unique application domain identifier.
GetNamedDataSlotLooks up a named data slot. For better performance, use fields that are marked with the ThreadStaticAttribute attribute instead.
Interrupt Interrupts a thread that is in the WaitSleepJoin thread state.
Join Blocks the calling thread until a thread terminates, while continuing to perform standard COM and SendMessage pumping. This method has different overloaded forms.
MemoryBarrierSynchronizes memory access as follows: The processor executing the current thread cannot reorder instructions in such a way that memory accesses prior to the call to MemoryBarrier execute after memory accesses that follow the call to MemoryBarrier.
ResetAbortCancels an Abort requested for the current thread.
SetDataSets the data in the specified slot on the currently running thread, for that thread's current domain. For better performance, use fields marked with the ThreadStaticAttribute attribute instead.
StartStarts a thread.
SleepMakes the thread pause for a period of time.
SpinWaitCauses a thread to wait the number of times defined by the iterations parameter
VolatileRead()Reads the value of a field. The value is the latest written by any processor in a computer, regardless of the number of processors or the state of processor cache. This method has different overloaded forms.
VolatileWrite()Writes a value to a field immediately, so that the value is visible to all processors in the computer. This method has different overloaded forms.
Yield Causes the calling thread to yield execution to another thread that is ready to run on the current processor. The operating system selects the thread to yield to.

Example:

The following example illustrates the uses of the Thread class. The page has a label control for displaying messages from the child thread. The messages from the main program is directly displayed using the Response.Write() method. So it appears on the top of the page.
The source file is as follows:
<%@ Page Language="C#" 
         AutoEventWireup="true" 
         CodeBehind="Default.aspx.cs" 
         Inherits="threaddemo._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h3>Thread Example</h3>
    </div>
    <asp:Label ID="lblmessage" runat="server" Text="Label">
    </asp:Label>
    </form>
</body>
</html>
The code behind file is as follows:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Threading;

namespace threaddemo
{
   public partial class _Default : System.Web.UI.Page
   {
      protected void Page_Load(object sender, EventArgs e)
      {
         ThreadStart childthreat = 
                          new ThreadStart(childthreadcall);
         Response.Write("Child Thread Started <br/>");
         Thread child = new Thread(childthreat);
         child.Start();
         Response.Write(
            "Main sleeping  for 2 seconds.......<br/>");
         Thread.Sleep(2000);
         Response.Write(
            "<br/>Main aborting child thread<br/>");
         child.Abort();
      }
      public void childthreadcall()
      {
      try{
         lblmessage.Text = "<br />Child thread started <br/>";
         lblmessage.Text += "Child Thread: Coiunting to 10";
         for( int i =0; i<10; i++)
         {
            Thread.Sleep(500);
            lblmessage.Text += "<br/> in Child thread </br>";
         }
         lblmessage.Text += "<br/> child thread finished";
      }
      catch(ThreadAbortException e)
      {
         lblmessage.Text += "<br /> child thread - exception";
      }
      finally{
         lblmessage.Text += "<br /> child thread 
                           - unable to catch the exception";
      }
      }
   }
}
Observe the following:
  • When the page is loaded, a new thread is started with the reference of the method childthreadcall(). The main thread activities are displayed directly on the web page.
  • The second thread runs and sends messages to the label control.
  • The main thread sleeps for 2000 ms, during which the child thread runs.
  • The child thread runs till it is aborted by the main thread. It raises the ThreadAbortException and is terminated.
  • Control returns to the main thread.
When executed the program sends the following messages:
ASP.NET Thread
at No comments:

9 Jun 2012

Custom Paging in ASP.Net GridView using SQL Server Stored Procedure


In this article I’ll explain how to implement Custom Paging in ASP.Net GridView control.
Need for Custom Pagination
Now the question arises why there’s need to take so much pain when ASP.Net GridView control has in built paging mechanism.
The answer is that ASP.Net GridView fetches all the records and then displays one page from the fetched records. Thus for example if your table has 1000 records and you need to display only 50 records per page, GridView will fetch all 1000 records discard the 950 records and display the 50 records based on the page index selected by the users.
Thus the above approach is quite inefficient in terms of both bandwidth and performance. With custom pagination we will fetch records per page based on the page index. Thus if our table has 1000 records and we need to display only 50 records per page, then we will fetch only 50 records based on page index. Thus this will boost the performance of the application.

Pagination Stored Procedure
SQL Server 2005 came up with the new ROW_NUMBER() keyword that allows us to give row numbers to the records that we select from the table. Using this row number we can implement our custom pagination logic within the SQL Server Stored Procedure.
I am using the Customers table Microsoft’s NorthWind Database for this article and below is the Stored Procedure that allows us to fetch records page wise.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE GetCustomersPageWise
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
      ,[CustomerID]
      ,[CompanyName]
      ,[ContactName]
     INTO #Results
      FROM [Customers]
     
      SELECT @RecordCount = COUNT(*)
      FROM #Results
           
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1)+ @PageSize) - 1
     
      DROP TABLE #Results
END
GO

Above I am passing the PageIndex, PageSize as input parameters so that we can fetch the records for the desired page index. And for populating the Pager in front end we will need the total number of records in the table which we are fetching using the RecordCount parameter.
HTML Markup
The HTML markup is quite simple it has a GridView, a DropDownLists selecting the Page Size and a Repeater which will be used for populating the pager.
<div>
PageSize:
<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
    <asp:ListItem Text="10" Value="10" />
    <asp:ListItem Text="25" Value="25" />
    <asp:ListItem Text="50" Value="50" />
</asp:DropDownList>
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderText="CustomerId" DataField="CustomerId" />
        <asp:BoundField HeaderText="ContactName" DataField="ContactName" />
        <asp:BoundField HeaderText="CompanyName" DataField="CompanyName" />
    </Columns>
</asp:GridView>
<br />
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
    <asp:LinkButton ID="lnkPage" runat="server" Text = '<%#Eval("Text") %>' CommandArgument = '<%# Eval("Value")%>' Enabled = '<%# Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</div>

Implementing the Custom Pagination
Now let’s start implementing the custom pagination in the code behind. First you will need to import the following namespaces
C#
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
 
VB.Net
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data

Binding the GridView with Data
Below is the method that will execute the stored procedure and bind the data to the ASP.Net GridView Control
C#
private void GetCustomersPageWise(int pageIndex)
{
    string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("GetCustomersPageWise", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", int.Parse(ddlPageSize.SelectedValue));
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            con.Open();
            IDataReader idr = cmd.ExecuteReader();
            GridView1.DataSource = idr;
            GridView1.DataBind();
            idr.Close();
            con.Close();
            int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
            this.PopulatePager(recordCount, pageIndex);
        }
    }
}

VB.Net
Private Sub GetCustomersPageWise(ByVal pageIndex As Integer)
    Dim constring As String = ConfigurationManager.ConnectionStrings("constring").ConnectionString
    Dim con As SqlConnection = New SqlConnection(constring)
    Dim cmd As SqlCommand = New SqlCommand("GetCustomersPageWise", con)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
    cmd.Parameters.AddWithValue("@PageSize", Integer.Parse(ddlPageSize.SelectedValue))
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4)
    cmd.Parameters("@RecordCount").Direction = ParameterDirection.Output
    con.Open()
    Dim idr As IDataReader = cmd.ExecuteReader
    GridView1.DataSource = idr
    GridView1.DataBind()
    idr.Close()
    con.Close()
    Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("@RecordCount").Value)
    Me.PopulatePager(recordCount, pageIndex)
End Sub

Populating the Pager
In the last line of the above method we are calling the method described below to populate the pager Repeater control based on the record count that we fetch from the database
C#
private void PopulatePager(int recordCount, int currentPage)
{
    double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPageSize.SelectedValue));
    int pageCount = (int)Math.Ceiling(dblPageCount);
    List<ListItem> pages = new List<ListItem>();
    if (pageCount > 0)
    {
        pages.Add(new ListItem("First", "1", currentPage > 1));
        for (int i = 1; i <= pageCount; i++)
        {
            pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
        }
        pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
    }
    rptPager.DataSource = pages;
    rptPager.DataBind();
}

VB.Net
Private Sub PopulatePager(ByVal recordCount As Integer, ByVal currentPage As Integer)
    Dim dblPageCount As Double = CType((CType(recordCount, Decimal) /Decimal.Parse(ddlPageSize.SelectedValue)), Double)
    Dim pageCount As Integer = CType(Math.Ceiling(dblPageCount), Integer)
    Dim pages As New List(Of ListItem)
    If (pageCount > 0) Then
        pages.Add(New ListItem("First", "1", (currentPage > 1)))
        Dim i As Integer = 1
        Do While (i <= pageCount)
            pages.Add(New ListItem(i.ToString, i.ToString, (i <> currentPage)))
            i = (i + 1)
        Loop
        pages.Add(New ListItem("Last", pageCount.ToString, (currentPage < pageCount)))
    End If
    rptPager.DataSource = pages
    rptPager.DataBind()
End Sub

Below is the event that is raised when the Page Size DropDownList is changed. This method simply calls theGetCustomersPageWise() method.
C#
protected void PageSize_Changed(object sender, EventArgs e)
{
    this.GetCustomersPageWise(1);
}

VB.Net
Protected Sub PageSize_Changed(ByVal sender As Object, ByVal e As EventArgs)
    Me.GetCustomersPageWise(1)
End Sub

Finally the below event is executed when the page number LinkButton is clicked. This event makes a database call to get new set of records based on the PageIndex and PageSize
C#
protected void Page_Changed(object sender, EventArgs e)
{
    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    this.GetCustomersPageWise(pageIndex);
}

VB.Net
Protected Sub Page_Changed(ByVal sender As Object, ByVal e As EventArgs)
    Dim pageIndex As Integer = Integer.Parse(CType(sender, LinkButton).CommandArgument)
    Me.GetCustomersPageWise(pageIndex)
End Sub
 
Screenshot
The below screenshot describes the working of the ASP.Net GridView with Custom Pagination using SQL Server Stored Procedure
Custom Paging in ASP.Net DataGrid using SQL server Stored procedure
Downloads
You can download the complete source code in VB.Net and C# using the link below.
at No comments:
Subscribe to: Posts (Atom)