Hi,
>> But if anyone one else has some idea on how to load a few hundred records at a time into a data grid view and keep track of what’s loaded there, and how to fetch the next or previous 100 rows.<<
In my view, we can use stored procedures and paging to achieve this goal. I created a general sample to achieve this goal,
Stored procedure:
CREATE PROCEDURE dbo.pagination
(
@PageSize int = 10,
@CurrentPageIndex int=1
)
AS
declare @index int
if(@CurrentPageIndex=1)
begin
EXEC( 'SELECT TOP ' + @PageSize + '* from Orders ')
end
else
begin
set @index= (@CurrentPageIndex-1) * @PageSize
EXEC( 'SELECT TOP ' + @PageSize + '* from Orders where OrderId not in '
+'(select top '+ @index +'OrderId from Orders ) ')
end
RETURN
VB.NET Code:
Private Const pageSize As Integer = 10
Private dtInfo As DataTable = Nothing
Protected Function ConnectStr() As String
Dim _connnectionStr As String = [String].Format("Data Source=localhost;AttachDbFilename={0};Integrated Security=True", "F:\xyt\MyProject\DataGridView\DataGridViewAndCheckBox\DataGridViewAndCheckBox\bin\Debug\b2bdb_Data.MDF")
'connection string
Return _connnectionStr
End Function
Private Function LoadData(PageIndex As Integer) As DataTable
Dim con As New SqlConnection(Me.ConnectStr())
Dim cmd As New SqlCommand("pagination", con)
cmd.CommandType = CommandType.StoredProcedure
con.Open()
cmd.Parameters.Add(New SqlParameter("@PageSize", SqlDbType.Int, 4))
cmd.Parameters("@PageSize").Value = pageSize
cmd.Parameters.Add(New SqlParameter("@CurrentPageIndex", SqlDbType.Int, 4))
cmd.Parameters("@CurrentPageIndex").Value = PageIndex
Dim dt As New DataTable()
Dim adapter As New SqlDataAdapter(cmd)
adapter.Fill(dt)
con.Close()
Return dt
End Function
Private Sub Form1_Load(sender As Object, e As EventArgs)
dtInfo = LoadData(1)
Me.dataGridView1.DataSource = dtInfo
Me.label1.Text = Convert.ToString(1)
End Sub
Private Sub btnNext_Click(sender As Object, e As EventArgs)
Me.label1.Text = (Convert.ToInt32(Me.label1.Text) + 1).ToString()
dtInfo = LoadData(Convert.ToInt32(Me.label1.Text))
If dtInfo.Rows.Count = 0 Then
'This is the last page.
Me.label1.Text = (Convert.ToInt32(Me.label1.Text) - 1).ToString()
MessageBox.Show("This is the last page.")
Else
Me.dataGridView1.DataSource = Nothing
Me.dataGridView1.DataSource = dtInfo
End If
End Sub
Private Sub btnPrevious_Click(sender As Object, e As EventArgs)
If Me.label1.Text = "1" Then
MessageBox.Show("This is first page")
Return
Else
Me.label1.Text = (Convert.ToInt32(Me.label1.Text) - 1).ToString()
dtInfo = LoadData(Convert.ToInt32(Me.label1.Text))
Me.dataGridView1.DataSource = Nothing
Me.dataGridView1.DataSource = dtInfo
End If
End Sub
If you want to keep track of what’s loaded there, you can write log for it. But IO operation will reduce this application’s performance. I suggest logging the record which failed to display or something else.
Have a nice time!
Sincerely,
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click
HERE to participate the survey.
datagridview slow to draw
