CODEWITHSUNDEEP
asp.netsql-serverdatabasedatalist
Mr A
Mr A

Reputation: 6768

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

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.

Upvotes: 5

Views: 1317

Answers (3)

Aristos
Aristos

Reputation: 66641

First you need somewhere to remember what is the last CustomterID that you show, and the Max rows of your customers.

Let say that the LastCustomerID -> CuLast and Max rows -> CuMax, then the SQL can be like. 

WITH CustomersNewOrder as (
 select *, CustomerID-CuLast as OrderMe from Customers where CustomerID > CuLast
union
 select *, CustomerID+CuMax as OrderMe from Customers where CustomerID <= CuLast 
) select * from CustomersNewOrder order by OrderMe

also When the CuLast > CuMax then make the CuLast = 0

What I do here is that the number is the last is starting from 0, and the one that you have all ready show starting after the maximum of the list.

This way you do not need to make update to database, and only one number needs to remember, the last one you show.

Because you say that you won to be the same for all users, and I suppose that you have one pool only, then a static value anywhere on global space can do the job that keeping the last one show.

Upvotes: 1

Pankaj
Pankaj

Reputation: 10095

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...

Upvotes: 1

Prabhavith
Prabhavith

Reputation: 486

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

Upvotes: 1

Related Questions