Hi all. Thank you for your help with this issue. I was wondering if there was a better way to do this. This method works but with almost 3,000 rows it take a really long time to load. Thank you.
Dim objConn As System.Data.SqlClient.SqlConnection
Try
objConn = globalfunctions.OpenConnection
Dim ds As New DataSet()
Dim myAdapter As New SqlDataAdapter("GetMissingAttributeValues", objConn)
myAdapter.Fill(ds)
Dim DV As DataView = ds.Tables(0).DefaultView
Dim myDataView As New DataView(ds.Tables(0))
myDataView.Sort = Session("SortBy") & " " & Session("SortOrder")
gvProducts.DataSource = myDataView
gvProducts.DataBind()
For i As Integer = 0 To gvProducts.Rows.Count - 1
If ds.Tables(0).Rows(i).Item("Attribute_Value") Is DBNull.Value Then
Dim Path As TextBox = DirectCast(gvProducts.Rows(i).FindControl("txtAttribute_Value"), TextBox)
Path.Visible = True
Else
gvProducts.Rows(i).Cells(7).Visible = False
Dim Path As TextBox = DirectCast(gvProducts.Rows(i).FindControl("txtAttribute_Value"), TextBox)
Path.Visible = False
Dim Path2 As Label = DirectCast(gvProducts.Rows(i).FindControl("lbAttribute_Value2"), Label)
Path2.Visible = True
Path2.Text = String.Format("{0:0.000}", ds.Tables(0).Rows(i).Item("Attribute_Value"))
Dim Path3 As DropDownList = DirectCast(gvProducts.Rows(i).FindControl("ddlattribute_values"), DropDownList)
Path3.Visible = False
End If
Next
Catch ex As Exception
Finally
If Not IsNothing(objConn) Then
'close the connection
objConn.Close()
objConn = Nothing
End If
End Try
With 3000 rows you should definitely look if you can't use paging. Even without this code for each row, it can get seriously slow. Especially on PostBack.
Typically it is not a good idea to look up data per row. Each Open of SqlConnection costs time. No problem for 1 row. No problem for 10 rows. But definitely a problem for 3000 rows. I get the feeling you have already taken care of this through the OpenConnection
thing.
And even if you do the Open only once, having a separate query per row can be costly. Could you make a single query for the entire GridView that gathers all the data? (For instance with a join.) A single select will be way faster than doing things per row.
Even if putting the data in the GridView is more work.
Superguppie.
Please remember to click “Mark as Answer” on the post that helps you. This can be beneficial to other community members reading the thread.
When all you've got is a Hammer, Every Problem looks like a Nail. Michael Swain.
Thank you. Yes. I took care of the open connection. OOPS. However, there are certain times when the row will have an edit option and certain times when it won't that is why I did this row by row. If there is already data it does not need to be edited however
if not data exists then I am adding a dropdown and a textbox for that row only where the user can add the missing data. Is there a better way of accomplishing this same result?
If loading peformance is your main concern then you may want to consider implementing Custom Paging in your GridView. Loading thousands of data in the page is not a good idea and definitely not user-friendly. You can also limit the amount of data to be returned
in your query or use sql paging for more effecient way.
Thank you vinz. I think I will try this avenue. I have one more quick question. So, do you know of a better way to do this operation in general because each row has a condition. So if there is data don't show the textbox or dropdown and if there is no
data then show it.
techgeeksqua...
Member
28 Points
42 Posts
Speed Issue-Posing data to each row of gridview
Aug 17, 2012 09:25 PM|LINK
Hi all. Thank you for your help with this issue. I was wondering if there was a better way to do this. This method works but with almost 3,000 rows it take a really long time to load. Thank you.
Dim objConn As System.Data.SqlClient.SqlConnection Try objConn = globalfunctions.OpenConnection Dim ds As New DataSet() Dim myAdapter As New SqlDataAdapter("GetMissingAttributeValues", objConn) myAdapter.Fill(ds) Dim DV As DataView = ds.Tables(0).DefaultView Dim myDataView As New DataView(ds.Tables(0)) myDataView.Sort = Session("SortBy") & " " & Session("SortOrder") gvProducts.DataSource = myDataView gvProducts.DataBind() For i As Integer = 0 To gvProducts.Rows.Count - 1 If ds.Tables(0).Rows(i).Item("Attribute_Value") Is DBNull.Value Then Dim Path As TextBox = DirectCast(gvProducts.Rows(i).FindControl("txtAttribute_Value"), TextBox) Path.Visible = True Else gvProducts.Rows(i).Cells(7).Visible = False Dim Path As TextBox = DirectCast(gvProducts.Rows(i).FindControl("txtAttribute_Value"), TextBox) Path.Visible = False Dim Path2 As Label = DirectCast(gvProducts.Rows(i).FindControl("lbAttribute_Value2"), Label) Path2.Visible = True Path2.Text = String.Format("{0:0.000}", ds.Tables(0).Rows(i).Item("Attribute_Value")) Dim Path3 As DropDownList = DirectCast(gvProducts.Rows(i).FindControl("ddlattribute_values"), DropDownList) Path3.Visible = False End If Next Catch ex As Exception Finally If Not IsNothing(objConn) Then 'close the connection objConn.Close() objConn = Nothing End If End Tryoned_gk
All-Star
31529 Points
6440 Posts
Re: Speed Issue-Posing data to each row of gridview
Aug 17, 2012 10:09 PM|LINK
superguppie
All-Star
48225 Points
8679 Posts
Re: Speed Issue-Posing data to each row of gridview
Aug 20, 2012 07:20 AM|LINK
With 3000 rows you should definitely look if you can't use paging. Even without this code for each row, it can get seriously slow. Especially on PostBack.
Typically it is not a good idea to look up data per row. Each Open of SqlConnection costs time. No problem for 1 row. No problem for 10 rows. But definitely a problem for 3000 rows. I get the feeling you have already taken care of this through the OpenConnection thing.
And even if you do the Open only once, having a separate query per row can be costly. Could you make a single query for the entire GridView that gathers all the data? (For instance with a join.) A single select will be way faster than doing things per row. Even if putting the data in the GridView is more work.
Please remember to click “Mark as Answer” on the post that helps you. This can be beneficial to other community members reading the thread.
When all you've got is a Hammer, Every Problem looks like a Nail. Michael Swain.
techgeeksqua...
Member
28 Points
42 Posts
Re: Speed Issue-Posing data to each row of gridview
Aug 20, 2012 01:56 PM|LINK
Thank you. Yes. I took care of the open connection. OOPS. However, there are certain times when the row will have an edit option and certain times when it won't that is why I did this row by row. If there is already data it does not need to be edited however if not data exists then I am adding a dropdown and a textbox for that row only where the user can add the missing data. Is there a better way of accomplishing this same result?
vinz
All-Star
127011 Points
17934 Posts
MVP
Re: Speed Issue-Posing data to each row of gridview
Aug 20, 2012 02:14 PM|LINK
If loading peformance is your main concern then you may want to consider implementing Custom Paging in your GridView. Loading thousands of data in the page is not a good idea and definitely not user-friendly. You can also limit the amount of data to be returned in your query or use sql paging for more effecient way.
Here's one example: GridView Custom Paging with LINQ
MessageBox Controls for WebForms | Blog | Twitter | Linkedin
techgeeksqua...
Member
28 Points
42 Posts
Re: Speed Issue-Posing data to each row of gridview
Aug 20, 2012 02:28 PM|LINK
Thank you vinz. I think I will try this avenue. I have one more quick question. So, do you know of a better way to do this operation in general because each row has a condition. So if there is data don't show the textbox or dropdown and if there is no data then show it.