|
Rebind a grid without a round-trip to data server -- shelleybobelly --
Hi, I am working with .NET 1.3 (2003), using c#. A simple lookup app. The user puts in certain parameters to search, then the Click event goes and gets an (up to 1000-row) dataset from a SQL server table that contains 3,000,000 records. There is a default sort order for the main query; it pulls a dataset which is then bound to a datagrid. What I want to do is to be able to sort that dataset and rebind it to the grid WITHOUT having to go back to the database. Seems like all the examples for sorting/paging all have it going back to the DB. Any help? Thanks |
|
-- BradleyPeter --
A few points are worth mentioning, I think: 1. I don't know of any way in which you can sort without going back to the server. The data doesn't exist on the client in any form other than an html table with some text in it. 2. The best way to do what you want, in my opinion, would be by firing off another query using a different ORDER BY clause. Of course you'll have to make sure you have indexes on all the columns you offer to sort by. In a large table this makes a huge (and I mean huge) difference. 3. Please be very, very careful of any application that allows users to enter profile strings. Unless you take very extensive measures to thwart SQL injection attacks you may get a nasty shock one day. If you don't know how to do this, please contact me off list and I'll try to sort out copies of our documentation for you - or at least give you a list of the highlights. 4. What on earth are users going to do with 1000 rows? We usually restrict our users to queries that return a maximum of a couple of hundred rows at the outside. 5. The best way we have found of getting over the SQL injection + large result set problem is by giving the users a selection of fields they can search on by picking from a dropdown list or lists. So, for example if a user wants to pick a country from all the several thousand possible ones for which we have addresses on file we might populate a dropdown list with something like: SELECT DISTINCT COUNTRY FROM ADDRESS ORDER BY COUNTRY It's easy to scroll down a list in alphabetical order, even if it's very large. And it restricts the user's profile to acceptable values. This solution can be extended to creating a profile from many dropdown lists. The value of each dropdown can be passed to a stored procedure as a parameter in the procedure's parameter list. That way you are guaranteed safe from SQL injection attacks (unless you do something silly like convert the parameter into a string in a query built and then executed in the SP). Just some thoughts that I hope are helpful. Peter -----Original Message----- From: DotNetDevelopment on behalf of shelleybobelly Sent: Mon 7/24/2006 8:57 PM To: DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web Services,.NET Remoting Cc: Subject: Rebind a grid without a round-trip to data server Hi, I am working with .NET 1.3 (2003), using c#. A simple lookup app. The user puts in certain parameters to search, then the Click event goes and gets an (up to 1000-row) dataset from a SQL server table that contains 3,000,000 records. There is a default sort order for the main query; it pulls a dataset which is then bound to a datagrid. What I want to do is to be able to sort that dataset and rebind it to the grid WITHOUT having to go back to the database. Seems like all the examples for sorting/paging all have it going back to the DB. Any help? Thanks |
|
-- shelleybobelly --
I have no idea what they want with 1000 rows. Just following the requirements, since I got tired of trying to apply logic (but I was just "following orders..."). I have already deployed with a dynamic SQL stored procedure that contains all the variable order-by possiblities. It is fast enough, but it just seems as if you could do something like re-order the existing dataset rather than a round-trip every time. Since you can change actual field data in a dataset while disconnected before returning to a server, it would just seem that you could otherwise manipulate the data. But there I go trying to apply logic again, sigh. Also, I have form validation to prevent injection attacks. And many possible input parameters for the where clause. And indexes on all, with clustered on the default sort field. Thanks anyhow. |
|
-- BradleyPeter --
I think you misunderstand a little. You can write code to reorder your DataSet, if you want to. Here's an article about it: http://aspnet101.com/aspnet101/tutorials.aspx?id=31 But this is all done on the server (the Web server). There is no way you can do it client-side that I know of. Peter -----Original Message----- From: DotNetDevelopment on behalf of shelleybobelly Sent: Tue 7/25/2006 6:45 PM To: DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web Services,.NET Remoting Cc: Subject: Re: Rebind a grid without a round-trip to data server I have no idea what they want with 1000 rows. Just following the requirements, since I got tired of trying to apply logic (but I was just "following orders..."). I have already deployed with a dynamic SQL stored procedure that contains all the variable order-by possiblities. It is fast enough, but it just seems as if you could do something like re-order the existing dataset rather than a round-trip every time. Since you can change actual field data in a dataset while disconnected before returning to a server, it would just seem that you could otherwise manipulate the data. But there I go trying to apply logic again, sigh. Also, I have form validation to prevent injection attacks. And many possible input parameters for the where clause. And indexes on all, with clustered on the default sort field. Thanks anyhow. |