Sunday, August 16, 2015

.Net Sound Timer

This tutorial show you how to set sound timer depend on the number of order. It plays sound for notification whenever the number of order change.
Sound Timer

Firstly, copy the following javascript to <head> tage.
<script type="text/javascript">
    function EvalSound(soundobj) {
        var thissound = document.getElementById(soundobj);
        thissound.play();
    }
</script>
Secondly, copy the following code to <form> tag.
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<asp:Timer ID="Timer1" runat="server" Interval="5000" OnTick="Timer1_Tick">
</asp:Timer>
<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
    <ContentTemplate>
        <asp:Label ID="lblOrder" runat="server" Text=""></asp:Label>
    </ContentTemplate>
    <Triggers>
        <asp:AsyncPostBackTrigger ControlID="Timer1" />
    </Triggers>
</asp:UpdatePanel>
<audio id="audio1" src="sound/Console_beep.mp3" preload="auto" autobuffer>
</audio>
In code-behind, the following snippet should be there.
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        string dt = DateTime.Now.ToLongTimeString();
        string count = DAL.GetOrderCount();
        lblOrder.Text = string.Format("There are {0} records at {1}.", count, dt);
        Session["count"] = count;
        
    }
}
protected void Timer1_Tick(object sender, EventArgs e)
{
    string dt = DateTime.Now.ToLongTimeString();
    string count = DAL.GetOrderCount();
    if (count != Session["count"].ToString())
    {
        lblOrder.Text = string.Format("There are {0} records at {1}.", count, dt);
        ScriptManager.RegisterStartupScript(Page, GetType(), "PlaySound", "EvalSound('audio1');", true);
    }
    Session["count"] = count;
}
It uses "Northwind" database and connection string in web.config seem like below:
<connectionStrings>
<add name="sqlConn" providerName="System.Data.SqlClient" connectionString="Server='(local)';uid='sa';pwd='p@ssw0rd';Database='Northwind';Pooling=False;" />
</connectionStrings>
It has the following code snippet in the DAL class. It uses the Microsoft.Practices.EnterpriseLibrary.Data.dll to shorten the code.
private static Database getDatabase()
{
    Database database = null;
    try
    {
        database = DatabaseFactory.CreateDatabase("sqlConn");
    }
    catch (Exception exception)
    {
               
    }
    return database;
}
public static string GetOrderCount()
{
    string count = "";
    try
    {
        count = getDatabase().ExecuteScalar("GetOrderCount").ToString();
    }
    catch (Exception exception)
    {
        
    }
    return count;
}
The "GetOrderCount" stored procedure is as shown below:
USE [Northwind]
GO
ALTER PROCEDURE [dbo].[GetOrderCount]
AS
BEGIN
    SELECT COUNT(*) FROM Orders;
END
This application demonstrates a simple use of the Timer control with UpdatePanel combining HTML5 audio element in a real world example.

Sunday, April 12, 2015

ASP.NET ListView Custom Paging with DataPager

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. 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.Custom paging solved this performance issue by requiring the page developer to tell the ListView 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.
ListView Custom Paging
Copy the following code to <head> tag.
<head runat="server">
    <title>ListView Test</title>
    <style type="text/css">
        .imgborder {
            border-color: #CCC;
            border-width: thin;
            border-style: solid;
            margin: 5px;
            padding: 5px;
        }
    </style>
</head>  
Here is server controls and copy to <form> tag.
<asp:ListView ID="ListView1" runat="server" GroupItemCount="4" GroupPlaceholderID="groupPlaceHolder1"
      ItemPlaceholderID="itemPlaceHolder1" DataSourceID="ObjectDataSource1" OnPagePropertiesChanging="ListView1_PagePropertiesChanging">
      <LayoutTemplate>
          <table>
               <asp:PlaceHolder runat="server" ID="groupPlaceHolder1"></asp:PlaceHolder>
          </table>
      </LayoutTemplate>
      <GroupTemplate>
          <tr>
              <asp:PlaceHolder runat="server" ID="itemPlaceHolder1"></asp:PlaceHolder>
          </tr>
      </GroupTemplate>
      <ItemTemplate>
           <td class="imgborder">
               <asp:Label ID="lblCustomerID" runat="server" Text='<%#Eval("CustomerID") %>' /></br>
               <asp:Label ID="lblCompanyName" runat="server" Text='<%#Eval("CompanyName") %>' /><br>
               <asp:Label ID="lblContactName" runat="server" Text='<%#Eval("ContactName") %>' />
           </td>
      </ItemTemplate>
      <EmptyDataTemplate>
          There is no data.
      </EmptyDataTemplate>
 </asp:ListView>
 <asp:DataPager ID="DataPager1" runat="server" PageSize="8" PagedControlID="ListView1">
      <Fields>
           <asp:NumericPagerField />
      </Fields>
 </asp:DataPager>
<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>
In code-behind, "ListView1_PagePropertiesChanging" event is as below:
protected void ListView1_PagePropertiesChanging(object sender, PagePropertiesChangingEventArgs e)
{
    DataPager1.SetPageProperties(e.StartRowIndex, e.MaximumRows, false);
    ListView1.DataBind();
}
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 (store procedure).
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 
That's it. Happy Myanmar New Year!!!