Last post Mar 04, 2019 04:21 AM by Ackerly Xu
Mar 03, 2019 05:30 AM|human2x|LINK
I have a Gridview that shows records from a database table containing apprx. 300,000 records.
This page is opened by many times from different viewers.
I found that the gridview shows the same records with the same sequence for all users.
I want to show different records for all users. I don't want to show the same records for all users.
That is all . Thank you.
Mar 03, 2019 06:13 AM|yogyogi|LINK
You can use the ROW_NUMBER() method of SQL to create different orders based on different columns
Select * From (Select ROW_NUMBER() Over (Id) AS 'RowNum',* From TableName)t
Select * From (Select ROW_NUMBER() Over (Id Desc) AS 'RowNum',* From TableName)t
Select * From (Select ROW_NUMBER() Over (Names) AS 'RowNum',* From TableName)t
Select * From (Select ROW_NUMBER() Over (Names Desc) AS 'RowNum',* From TableName)t
So I just show 4 different queries. You can simply use a stored procedure that accepts 2 values 'column name' and 'order' and then you generate different ordered queries.
Mar 03, 2019 06:22 AM|human2x|LINK
Actually I want to show different records on every open, even on page refresh.
The above select you sent is for MSSQL , while I am using MySQL.
I appreciate your care.
Mar 03, 2019 02:44 PM|yogyogi|LINK
There may be some little syntax difference since you are using mysql see MySQL ROW_NUMBER Function
The logic will remain the same. You create a stored procedure that accepts 2 input parameters - 'column name' and 'order' and then you generate different ordered queries.
See stored procedures tutorial.
The stored procedure structure should be something like this:
CREATE PROCEDURE [dbo].[sp_MyProc]@ColumnName Varchar(50),@Order Varchar(10)
ASSet @Query = 'Select * From (Select ROW_NUMBER() Over ('+@ColumnName + ' ' + @Order +') AS 'RowNum',* From TableName)t' EXEC sp_executesql @queryGO;
Finally from your C# you will execute this stored prodcedure by passing different value for @ColumnName @order for different users.
Mar 04, 2019 04:21 AM|Ackerly Xu|LINK
If you are not going to show all records of your table, you could use a random number to let your record start with random index using limit query.
Below is my code. the first number after limit is the starting line number, the second is the number of rows your want to show.
int start = new Random().Next(6); //use a random, max value is 6
using (MySqlDataAdapter adapter = new MySqlDataAdapter("select * from student limit @start,1", constr))
DataTable table = new DataTable();
GridView1.DataSource = table;
<asp:GridView ID="GridView1" runat="server"></asp:GridView>