Last post Feb 27, 2012 07:32 AM by Qin Dian Tang - MSFT
Feb 23, 2012 03:13 PM|ChrisPalko|LINK
My project has a Search page and a Maintenance page. When the user enters criteria and clicks the Search button on the Search page, I'm using the code behind on the Search page to call a stored procedure which first determines if there is at least one record
that is returned. If yes, then I use a "Response.Redirect" passing url parameters to the Maintenance page. The Maintenance page has both a formview and a gridview control to display master/detail. The gridview shows the results of the search and the formview
shows the details of the record that is selected in the gridview. After the Search page logic is completed and a redirect is done, the PAGE_LOAD (of the Maintenance page) uses the url parameters that were passed to call the formview's datasource (stored procedure)
and binds. The same url parameters are used to call the gridview's datasource (another stored procedure) and binds.
For example, in the Search page's code behind:
protected void SearchButton_Click(object sender, EventArgs e)
if ((lstSearchBy1.SelectedValue == matterNumber) && (!String.IsNullOrEmpty(txtSearchValue1.Text)))
int return_type = sBLL.SearchCount_By_Matter_Number(txtSearchValue1.Text);
case 0: //Matter record found, send to Maintenance Page
I display "No results found" message here.
In the code behind on the Maintenance page, the PAGE_LOAD does this when searching by a matter number:
sender, EventArgs e)
FormView1DataSource.SelectCommand = "sp_FMVWSearch_By_Matter_Number";
//Populate data table for gridview
dt = mBLL.spSearch_By_Matter_Number(matter_nbr);
dt.DefaultView.Sort = "matter_nbr ASC";
gvPracticeDetail.DataSource = dt;
gvPracticeDetail.SelectedIndex = 0;
This is what the formview's SqlDataSource mark-up looks like on the page:
<asp:SqlDataSource ID="FormView1DataSource" runat="server" ConnectionString="<%$ ConnectionStrings:PDMConnectionString %>"
OnSelecting="FormView1DataSource_OnSelecting" SelectCommandType="StoredProcedure" ></asp:SqlDataSource>
Is this approach an acceptable way to utilize the asp.net controls/framework? There may be broad searches, that's why I am putting the results into a data table and putting the data table into the app cache (for paging/sorting). I would like to ask if there
is any way to improve performance for this type of search? If I run each stored procedure independently outside of the project, each runs between 4-6 seconds.
Feb 23, 2012 03:27 PM|adamturner34|LINK
You're making 2 roundtrips to the database for the same record(s).
My suggestion would be to make 1 roundtrip to the database in the search screen, store the result in an xml file, and call your maintenance page via XMLHTTP object and pass the xml.
Feb 23, 2012 03:53 PM|ChrisPalko|LINK
Thank you for the quick reply. Could you provide me with a simplified example of how to do this using the structure I show in my post?
Feb 27, 2012 07:32 AM|Qin Dian Tang - MSFT|LINK
Why not still use a SqlDataSource for GridView. You can add QueryStringParameter into SqlDataSource to use values from url to select data from table. It may improve the performance. Furthermore, you can refer the way for efficient paging: