Displaying customers from SQL Server database using datalist control in asp.net

Question

I have got a list of 1000 customers which I am displaying through a datalist control in asp.net. The list shows one customer per page.

The query I am using to bound the data list is :

static public DataTable GetAllCustomers()
{
    string sql = "Select * from [Customers]";
    SqlDataAdapter da = new SqlDataAdapter(sql, ConnectionString);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
}

What I want to do is once the customer is viewed it should go back to the bottom of the list, so that once the user logged in second time , he dont have to start from the beginning viewing the same customer, the viewed customers should go to bottom of the 1000 customer list, for instance if once customer 1 is viewed, the next time the customer 1 should become 1000 customer and the customer 2 should become customer 1, hope it make sense.

Any suggestion or assistance will be appreciated on how to achieve this scenario & what changes do i have to make in the db and query to achieve this.

Answer 1

When the customer is viewed capture the current datetime and use sorting, orderby datetime ascending order

Answer 2

Create a new column in the same table. Whenever you view the customer update the customer with the current date and time for .this newly created column

ALTER TABLE YourTableName 
ADD NewColumnName DATETIME DEFAULT getdate()

The newly created column should have all values initially updated by current date and time. This means the default value of this column will be GetDate()

When you bring all record from database sort it by column . Thus you will have unviewed customers at the top every time...

static public DataTable GetAllCustomers()
{
    string sql = "Select * from [Customers]";
    using (SqlDataAdapter da = new SqlDataAdapter(sql, ConnectionString))
    {
        using (DataTable dt = new DataTable())
        {
            da.Fill(dt);
            return dt;
        }
    }
}

Hope this will help you...