[chooser169]

I currently have a program I'm using to export SQL data which allows the user to preview the data beforehand in a Datagridview then export it to a csv file (the csv is created from the populated datagrid data). Right now everything's working fine, but when the datagrid tries to display more than 100,000 records and 10-20 columns it slows down to a crawl. I'm wondering if A) a datagrid is the right way to go, B') if another option would be much faster to preview with, or C) scrap the preview and just push the select query out to a csv file (which our users would be unhappy with). I was also toying with the idea that when previewing, I change the SQL query to SELECT TOP 100, but when exporting it would push the csv directly from the SQL database. Any thoughts or suggestions? I've added the code I use to populate the datagrid below.

  Dim connectionString As String
        Dim cnn As SqlConnection
        connectionString = "Data Source=SERVER;Initial Catalog=CATALOG;User ID=uname;Password=password"
        cnn = New SqlConnection(connectionString)
        Dim command As New System.Data.SqlClient.SqlCommand("SELECT Query", cnn)
        Dim dt As New DataTable
        Dim adpt As New Data.SqlClient.SqlDataAdapter(command)
        SuspendLayout()
        Try
            adpt.Fill(dt)
            Form3.DataGridView1.DataSource = dt
        Catch ex As SqlException
            MessageBox.Show("An error occurred while loading data !" & vbCrLf & ex.ToString())
        End Try
        ResumeLayout()
        Form3.Show()

[BobRodes]

I can't imagine people being able to browse through 100,000 records effectively in the first place, but there you are. You might consider some sort of system of user-selectable filters. The user can apply the filters and then you can display the result in the datagrid. You would use the datatable's Select method to return an array, and then bind your array to the datagrid. The user could browse through various subsets of the data, and when ready to commit to the export, you can export the underlying datatable. This would minimize the number of records shown in the datagrid and should solve your bottleneck. Of course, if the user wants to see all 100,000 records, then it's on him. You might want to allow the user to cancel out of a query if it's too slow.

[doc_guru]

Seeing your question and the answer below it, I think the answer is right, google step by step by yourself. As I search two things for you.
1. a similar discussion with this :convert-datatable-to-csv-stream.
2. A program guide based on a 3d tools for data to CSV..
Each way helps a lot.

[chooser169]

Thank you both for your help. I think I'll implement both of your examples. First by filtering, then tack on some of that csv stream exporting. Thanks again!