Tuesday, February 18, 2014

แก้ปัญหา Start Apache 2.2 ไม่ได้

ถ้าวันนึงไม่สามารถ start service apache ได้ลองทำตามนี้ดู
1.ลองดูให้เห็นกับตาว่า  start service apache ไม่ขึ้น ให้ไปที่ Service แล้วคลิ็กขวาเลือก All Tasks -> Start
2. ไม่ขึ้นจริงๆด้วย โดยจะขึ้นข้อความ “Windows could not start the Apache2.2 on Local Computer. For more information, review the System Event Log. If this is a non-Micorsoft service, contact the service vendor, and refer to service-specific error code 1.”
apache service.jpg
2.1 จากที่ลองค้นหาดูตามเวบต่างๆพบว่าที่ start ไม่ขึ้นนั้นเกิดจาก port 80 มีการใช้งานอยู่ ตรงนี้มันวิธีแก้ง่ายๆอยู่คือเปลี่ยน port ของ Apache ให้ไปใช้ port อื่น เช่น 8080 หรือ 8088 หรือ เลขอื่นๆแทนที่ไม่ชนกับที่มีอยุ่ โดย เข้าไปเปลี่ยนที่ไฟล์ C:\AppServ\Apache2.2\conf\httpd.conf แล้วแก้เลข port จาก “Listen 80″ เป็น port ที่ต้องการ เช่น “Listen 8080″ แล้ว start service อีกครั้งก็จะใช้งานได้
3.  จากที่แนะนำไปคือการเปปลี่ยนสามารเปลี่ยน default port ของ apache ถ้าไม่อยากเปลี่ยนให้ตรวจสอบสิ่งเหล่านี้
3.1 ดูว่า service ไหนใช้ port 80 อยู่ แล้วทำการปิด service นั้น โดยพิมพ์คำสั่ง netstat -nao ที่ Command prompt จะได้ output ประมาณนี้
1
2
3
4
5
6
7
8
9
10
11
12
Active Connections
Proto Local Address Foreign Address State PID
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING 4
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING 868
TCP 0.0.0.0:443 0.0.0.0:0 LISTENING 2616
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING 4
TCP 0.0.0.0:902 0.0.0.0:0 LISTENING 2416
TCP 0.0.0.0:912 0.0.0.0:0 LISTENING 2416
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING 508
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING 984
TCP 0.0.0.0:1027 0.0.0.0:0 LISTENING 328
จะเห็นว่ามี 0.0.0.0:80 ถูกใช้งานอยู่โดย PID(Process Identifier) เลข 4 ขั้นตอนต่อไปก็ดูว่า process นี้คืออะไร ปิดได้หรือไม่
3.2 ดู process id ว่าคือ service อะไรให้ไปที่ Task Manager คลิ้กที่ process แล้วเลือก view -> select column แล้วติ้กถูกที่ PID(Process Identifier)
3.3 จะเป็นว่า PID 4 เป็น process ของ system ไม่สามารถที่จะ kill ได้ หากเป็นของ program อืนๆที่เรารู้จักก็ให้ลองปิด program นั้นๆไปก่อนแล้วลอง start service อีกครั้ง Skype, NOD32 หรือ IIS
4. เมื่อพบว่าเป็นของ system ทำให้ไม่สามารถปิดได้ วิธีการก็คือไม่ให้มัน start ขึ้นมาแทน อ้างอิงจากที่นี่
1) Launch RegEdit
2) Go to: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\HTTP
3) Change the value of ’start’ to 4 (disabled)
4) Re-boot your computer
5. ลอง Start service Apache อีกครั้ง
source : http://langisser.wordpress.com/2012/05/15/%E0%B9%81%E0%B8%81%E0%B9%89%E0%B8%9B%E0%B8%B1%E0%B8%8D%E0%B8%AB%E0%B8%B2-start-apache-%E0%B9%84%E0%B8%A1%E0%B9%88%E0%B9%84%E0%B8%94%E0%B9%89/

Regedit เปิดไม่ได้ +CMD ใช้ไม่ได้

Regedit เปิดไม่ได้ +CMD ใช้ไม่ได้
  ปัญหานี้คือ เราจะเข้าไปแก้ค่าต่างๆกลับ หรือเอามันออกจาก Registry มันก็เลยทำไม่ให้เราเข้า Regedit โดย Admin ซะงั้นล่ะ ทั้งๆที่เราเองก็เป็น Admin
    เพื่อไม่ให้เป้นการเสียเวลา เรามาเรียกใช้บริการ Gpedit  อีกทีแล้วกันครับ รู้จักกันทั้งทีใช้ให้คุ้มไปเลยครับ
    ไปที่ Start => Run แล้วพิมพ์ Gpedit.msc แล้ว OK ครับ  จะมีตัวโปรแกรม Group Policy ขึ้นมา
     ตรง User Configuration => Adinistrative Templates => System ตรงด้านขวา Double Click ตรง Prevent access to registry editing tools เลือกเป้น Disable แล้ว OK ตามรูปครับ

Image

Image

Image 
    แถมอีกนิดครับไหนๆก้มาหน้านี้กันแล้ว ด้านบน Prevent access to registry editing tools จะเห็นว่ามี Prevent access to the command prompt ใครที่มีปัญหาใช้ CMD ไม่ได้ก็แก้ตรงนี้ได้เช่นเดียวกันครับ เข้ามาทีเดียวได้ 2 ต่อ ตามรูปครับ
 Image
source : http://www.i2-internet.com/index.php?option=com_content&task=view&id=107&Itemid=142

Sunday, February 9, 2014

Optimized Paging and Sorting using Object Data Source lazyloading

Title:       Optimized Paging and Sorting using Object Data Source
Author:      Muhammad Akhtar Shiekh 
Email:       akhhttar@gmail.com
Member ID:   2243665
Language:    C# 2.0
Platform:    Windows, .NET
Technology:  ASP.NET
Level:       Intermediate
Description: An article on optimized paging and sorting in GridView.
Section      ASP.NET
SubSection   GridView
License:     CPL

Introduction

Paging and sorting are most commonly used features of ASP.NET GridView. And it is very easy to use/implement these features in GridView with small chunk of lines. In this article I am going to demonstrate what are the performance drawbacks of using conventional way to page and sort your GridView and then I will demonstrate ‘An Optimized way to implement Paging and Sorting’.

What are conventional steps for Paging and Sorting?

Usually we do following steps to enable paging and sorting in our GridView,
  1. Set AllowPaging and AllowSorting Properties of GridView to True to enable paging and sorting respectively e.g
        <asp:GridView ID="GridView1" runat="server" AllowPaging="true" AllowSorting="true"  >
         </asp:GridView>
    
  2. Set PageSize property to mention how many records will be display on each page.
  3. Set SortExpression property of each column. By default each DataBound columns has bounded column name as default value for SortExpression property.
  4. Handle PageIndexChanging and Sorting Events of GridView to respond Paging and sorting actions respectively e.g
        <asp:GridView ID="GridView1" runat="server" AllowPaging="true" 
                    AllowSorting="true" onpageindexchanging="GridView1_PageIndexChanging" 
                    onsorting="GridView1_Sorting"  >
                </asp:GridView>
    
         protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            
    
        }
        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
    
        }
    
  5. Put Some Logic in Event Handler to do their jobs

    a. In PageIndexChanging Event Handler method, we usually get the data from database or somewhere from Cache and rebind our Grid with that data. And after rebinding we change PageIndex property of GridView to new page index to display the page that was selected by user.
         protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.DataSource = GetData(); // GetData() is a method that will get Data from Database/Cache/Session to display in Grid.
            GridView1.DataBind();
    
            GridView1.PageIndex = e.NewPageIndex;
        }
    
    
    b. In Sorting Event Handler method, we get the sorted data according to the sort expression from our data source (data source could be database/cache/session etc) and then rebind the Grid to display sorted records.
         protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            GridView1.DataSource = GetData(e.SortExpression); // GetData(string sortBy) is a method that will get Data from Database/Cache/Session to display in Grid.
    
            GridView1.DataBind();
        }
     
     
And that’s it.

Drawbacks

In conventional way of paging and sorting we get complete set of data instead of getting part of data that is required to display on current/requested page. As you can see that on each pageIndexChanging call, we are getting all the data from our data source and then bind it to GridView. Ideally we should get only that data that is required to display on requested page.

Hmmm…Sounds good but HOW??

The question that may arise in your mind could be “It seems good in theory that we should only get required data, but practically if we bind only one page data with GridView then it would assume that this is the only data that it needs to display, so how GridView even display page numbers and total records count? It is a genuine question, so let’s try to answer this question!!!

An Optimized Way of Paging and Sorting

As in the start of this article, we discuss conventional 5 steps to implement paging and sorting in ASP.NET GridView. In this purposed solution we will be using first 3 steps AS IT IS and instead of performing 4th and 5th steps by our self, we will use ObjectDataSource that will perform these steps for us in optimized way.

High Level Overview

We will optimize the code on both Database and Presentation layer
At the Database Level we will write a stored procedure in such a way that it would return only one page records. That stored procedure take Page Size, Page Index and Sort Expression as input parameters and return sorted record for particular page index.
At the Presentation layer, we will use ObjectDataSource’s virtual paging feature to optimize the paging. Virtual paging is not a term defined by Microsoft, I used it by myself because ObjectDataSource expose some properties and method that allow us to bind only one page data with GridView and to define total number of records in database (not in one page), so that GridView can extract out total number of pages that needs to be display in page area of GridView. In the next sections we will see what these properties and methods are? and how to use them?
If you are not familiar with ObjectDataSource then I would recommend that you should first read some article on that. Here are some articles

Implementation Details:

Database Layer:

We have an employee table in database with following schema
TableSchema.GIF 
and we wrote a following stored procedure that has two select statements. First select statement will return the total number of employee in Employee table and second dynamic select statement will return one page sorted record according to the provided start index, page size, and sortby by parameters
Create PROCEDURE spGetAllEmployee
 (
 @startIndex  int,
 @pageSize  int,
 @sortBy  nvarchar(30),
 @totalEmployees int OUTPUT  
 )
AS
 SET NOCOUNT ON 

 DECLARE
    @sqlStatement nvarchar(max),    
    @upperBound int

  IF @startIndex  < 1 SET @startIndex = 1
  IF @pageSize < 1 SET @pageSize = 1
  
  SET @upperBound = @startIndex + @pageSize
 

 Select @totalEmployees=Count(*) From Employee
  
  SET @sqlStatement = ' SELECT E.EmployeeID, E.EmployeeCode, E.Name, E.Department, E.Salary
                FROM (
                      SELECT  ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ') AS rowNumber, *
                      FROM    Employee
                     ) AS E
                WHERE  rowNumber >= ' + CONVERT(varchar(9), @startIndex) + ' AND
                       rowNumber <  ' + CONVERT(varchar(9), @upperBound)
  exec (@sqlStatement)
One thing that i want to explain in above stored procedure is ROW_NUMBER() function that make it possible for us to select only one page data. ROW_NUMBER() method is included in 2005 release of TSQL, it actually add an integer column in selected record set, that contain the record number for each record . It seems very simple but infact it very helpful while we are going to perform nested queries. As we did in our stored procedure, in nested query we select all employees record sorted by the provided sort expression and add a row number for each record using ROW_NUMBER()method, and in outer query we filter the resulted rows by using lower and upper bound indexes so that we return only those rows which lies in lower and upper bounds.

Data Access layer:

In Data Access Layer we will write a class that will be responsible to call spGetAllEmployee stored procedure to get employee records and return employee list to the business logic layer. To avoid the complexity and length of article i am only posting the code is used to fetch the records from database, I am not posting any helper code/classes, however complete code is available for download .
public List<EmployeeInfo> GetAllEmployee(int startIndex, int pageSize, string sortBy,ref int totalEmployees)        {
            IDbConnection connection=null ;
            IDbCommand selectCommand=null ;
            List<EmployeeInfo> employeeInfoList;
            DataSet employeeDS = new DataSet();
            IDbDataAdapter dataAdapter = DataObjectFactory.CreateDataAdapter();
            try
            {
                using (connection = DataObjectFactory.CreateConnectionObject())
                {
                    using (selectCommand = DataObjectFactory.CreateStoredProcedureCommand(connection, "spGetAllEmployee"))
                    {

                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@startIndex", SqlDbType.Int, startIndex));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@pageSize", SqlDbType.Int, pageSize));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@sortBy", SqlDbType.NVarChar, 30, sortBy));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@totalEmployees", SqlDbType.Int, 0));
                        ((SqlParameter)selectCommand.Parameters["@totalEmployees"]).Direction = ParameterDirection.Output;

                        if (connection.State != ConnectionState.Open)
                            connection.Open();

                        dataAdapter.SelectCommand = selectCommand;

                        dataAdapter.Fill(employeeDS);

                        totalEmployees = Convert.ToInt32(((SqlParameter)selectCommand.Parameters["@totalEmployees"]).Value);
                    }
                }
            }
            catch (SqlException ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                DALException mineException = new DALException();
                mineException.ConnectingString = connection.ConnectionString;
                mineException.StoredProcedureName = selectCommand.CommandText;
                mineException.Source = "GetAllEmployee";
                throw mineException;
                
            }

            employeeInfoList =  ConvertorUtility.ConvertToEmployeeInfoCollection(employeeDS);

            return employeeInfoList;
           
        }

One thing you might notice that last parameter totalEmployee of GetAllEmployee is by reference, it is because of the optimization, we don’t want to perform two seprate database calls to get employees records and total count. Because of the same reason we are returning both things (employee data and total count) from one stored procedure. In presentation layer it will be more clearer to you that what was the necessity of this approach and how it will be helping us.

Business Logic Layer:

Business logic layer will only be calling Data Access layer code to fetch the records. Source code is available indownload.

Presentation Layer:

As we discusses earlier in the artcile that we will use the virtual paging feature of ObjectDataSource. Object Data Source exposes some interesting properties which is used for virtual paging purpose. To get benefits from these properties you should set EnablePaging property of ObjectDataSource to True. These properties are
  • StartRowIndexParameterName: Specify the parameter name of Select method of ObjectDataSource’s bounded Type. ObjectDataSource will pass the value to this parameter when user changes the page index. For example, if Grid View page size is 10 and user click page number 3 to view 3rd page then theObjectDataSource will calculate the value of StartRowIndexParameter by using ( page Size * page Index) formula , in this case it will be 10 * 3= 30. Default value of this property is startRowIndex, which means if we don’t mention any value for this property then Select method should have startRowIndex parameter.
  • MaximumRowsParameterName: Specify the parameter name of Select method of ObjectDataSurce’s bounded Type. ObjectDataSource will pass the value to this parameter when user changes the page Index.ObjectDataSource source get its value from GridView’s PageSize property. Default value of this property ismaximumRow. which means if we don’t mention any value for this property then Select method should havemaximumRow parameter.
  • SelectCountMethod: Specify the method name of ObjectDataSource’s Type. This method will be called byObjectDataSource to get the total number of records in database. This count will help ObjectDataSource to create virtual paging in the Grid. For example, if GridView page size is 10, and SelectCountMethod return 100 as total number of employees in database then ObjectDataSource will display 10 page indexes in GridView page areas ( However we didn’t get all 100 records from data base)
  • SortParameterName: Specify the parameter name of Select method of ObjectDataSource’s bounded Type.ObjectDataSource will pass the value to this parameter when user click on any column header to sort the data according to that column. ObjectDataSource fetch the value from SortExpression property of particular GirdView column
Now if you see all these three properties togather then you will understand that SelectCountMethod will use to display virtual page indexes in GridView and on each page index change we will use values ofStartRowIndexParameterName and MaximumRowsParameterName parameters to query database to fetch desired page data only.
As you know that we already written stored procedures, data access and business logic code which accpet these parameters and return the only one page sorted data. So it's time to use them by passing these parameters values to them.
It’s enough in theory, now let’s talk something in C# and ASPX,

ASPX: Markup of GridView and ObjectDataSource


<asp:GridView ID="GridView1" DataKeyNames="EmployeeID" runat="server" AllowPaging="True" AutoGenerateColumns="False"
        CellPadding="4" DataSourceID="ObjectDataSource1" ForeColor="#333333" GridLines="None" AllowSorting="True" PageSize="5" >
        <RowStyle BackColor="#EFF3FB" />
        <Columns>
            <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="true" SortExpression="EmployeeID" />
            <asp:BoundField DataField="EmployeeCode" HeaderText="EmployeeCode" SortExpression="EmployeeCode" />
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            <asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department" />
            <asp:BoundField DataField="Salary" HeaderText="Salary" SortExpression="Salary" />
        </Columns>
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#2461BF" />
        <AlternatingRowStyle BackColor="White" />
    </asp:GridView>

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetAllEmployees" EnablePaging="true"
        TypeName="EmployeeData" StartRowIndexParameterName="startIndex" MaximumRowsParameterName="pageSize" SortParameterName="sortBy" SelectCountMethod="GetTotalEmployeesCount" >
    </asp:ObjectDataSource>


EmployeeData.cs: A class which is bound to ObjectDataSource

public class EmployeeData
{

    private static int employeesCount;
    public EmployeeData()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    [DataObjectMethod(DataObjectMethodType.Select)]
    public static List<EmployeeInfo> GetAllEmployees(int startIndex, int pageSize, string sortBy)
    {
        EmployeeBLL objEmployeeBLL = new EmployeeBLL();
        List<EmployeeInfo> result;
        int totalEmployees=0;

        if (string.IsNullOrEmpty(sortBy))
            sortBy = "EmployeeID";

        result = objEmployeeBLL.GetAllEmployee(startIndex, pageSize, sortBy, ref totalEmployees);
        employeesCount = totalEmployees;
        return result;

    }

    public static int GetTotalEmployeesCount()
    {
        return employeesCount;
    }


}

Nothing special here to explain because we are calling already explained code to get one page sorted data by passingObjectDataSource provided values. But one thing that I want to explain from optimization point of view, We are getting employeesCount in GetAllEmployees() method, and in GetTotalEmployeesCount() we only return that value, instead of fetching the count from database. What people usually do is , they send seprate database call to fetch the count which is an unnecessary step because we can write our stored procedure and data acess and business logic layer in such a way in which we can get the actual data and total count in one database call.

Comparison

I did comparison between conventional and optimized implementation of paging and sorting and there is a HUGE difference between them, Here are the results
One noticeable thing from this performance table is, as we are increasing the data up-optimized implementation is taking more time to fetch the records from database while there is no noticeable difference in optimized way.
No. of RecordsOptimized Paging ( in seconds)Un-optimized Paging  ( in seconds)
5000000.2107.41
10000000.2817.27
20000000.3236.28
30000000.3354.03

No. of RecordsOptimized Sorting ( in seconds)Un-optimized Sorting  ( in seconds)
50000001.6110.14
100000003.1323.22
200000009:3847.39
300000013.1270:25

* Paging time is calculated on GridView page index changed event.
* Sorting time is calculated on GridView sorting event.

Summary

In this article we learned an End to End solution to optimized paging and sorting in ASP.NET GridView. In Database Layer we used ROWNUMBER() method which help us to select only one page data. And In Presentation layer we usedObjectDataSource Virtual paging feature by using its properties StartRowIndexParameterName,MaximumRowsParameterNameSelectCountMethod,SortParameterName. Hope it helps.

source : http://www.codeproject.com/Articles/42043/Optimized-Paging-and-Sorting-using-Object-Data-Sou