Solved

Sorting a Datagrid Without Another Database Call using C#

Posted on 2006-07-03
4
827 Views
Last Modified: 2008-02-26
Hello,

I have a C# web app that populates a datagrid based on several user input criteria.
The database response to a DISTINCT clause and ORDER BY clause is very slow. To eliveate that I want to do the sorting on the datagrid itself. However, I don't want to git the database again because the criteria for populating it is complex and the datagrid is populated on a button submit event. I figure a dataview is the way to go but I don't know how to implement it in my app. Here is the basic concept of the app. There are a bunch of texboxes on the page with a submit button below the textboxes. The user enters search criteria in the texboxes and they are passed to a stored procedure. The stored procedure builds a dataset and poplulates the datagrid. A checkbox column is added to the first column of the datagrid. The user checks a few of the checkboxes and a report is created.

I need to be able to somehow capture the dataset and make a datatable and a dataview and set up a method to sort the datagrid without making another database call. I also need to be able to process the checkboxes after sorting a column. I don't care if the original checkboxes become unchecked after the initial sort but the sort must not affect the checkbox porcessing procedure that builds the report.

I realize this one may be a bit complex but I would appreciate any help.

Here is some code.

index.aspx
==================================================
<%@ Register TagPrefix="uc1" TagName="ucCustomFind" Src="ucCustomFind.ascx" %>
<%@ Page language="c#" Codebehind="index.aspx.cs" AutoEventWireup="false" Inherits="MLS2.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
      <HEAD>
            <title>WebForm1</title>
            <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
            <meta content="C#" name="CODE_LANGUAGE">
            <meta content="JavaScript" name="vs_defaultClientScript">
            <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
            <LINK href="styleOCN.css" type="text/css" rel="stylesheet">
            <script language="javascript">
                  function SelectAllCheckboxes(spanChk)
                  {
                        // Added as ASPX uses SPAN for checkbox
                        var oItem = spanChk.children;
                        var theBox=(spanChk.type=="checkbox")?spanChk:spanChk.children.item[0];
                        xState=theBox.checked;

                        elm=theBox.form.elements;
                        for(i=0;i<elm.length;i++)
                        if(elm[i].type=="checkbox" && elm[i].id!=theBox.id)
                        {
                              //elm[i].click();
                              if(elm[i].checked!=xState)
                              elm[i].click();
                              //elm[i].checked=xState;
                        }
                  }
                  


            </script>
      </HEAD>
      <body>
            <form id="frmMLS" name="frmMLS" method="post" runat="server">
                  <div class="heading" align="center">MLS Cherry Pick</div>
                  <table class="fromFieldLable" id="tblMain" style="MARGIN-LEFT: 8px; WIDTH: 1072px; HEIGHT: 686px"
                        cellSpacing="0" cellPadding="0" width="1072" border="0">
                        <tr>
                              <td class="fromFieldLableEmph" style="WIDTH: 157px; HEIGHT: 31px">Street Number</td>
                              <td class="fromFieldLableEmph" style="WIDTH: 180px; HEIGHT: 31px">Street Name</td>
                              <td style="WIDTH: 170px; HEIGHT: 31px">&nbsp;</td>
                              <td class="fromFieldLableEmph" style="WIDTH: 189px; HEIGHT: 31px">County Name</td>
                              <td style="WIDTH: 179px; HEIGHT: 31px"></td>
                              <td style="WIDTH: 167px; HEIGHT: 31px"><!-- send to paperwise --></td>
                              <td style="WIDTH: 23px; HEIGHT: 31px">&nbsp;</td>
                              <td style="WIDTH: 8px; HEIGHT: 31px">&nbsp;</td>
                        </tr>
                        <tr>
                              <td style="WIDTH: 157px; HEIGHT: 28px"><asp:textbox id="txtStreetNumber" runat="server" Width="96px"></asp:textbox></td>
                              <td style="WIDTH: 358px; HEIGHT: 28px" colSpan="2"><asp:textbox id="txtStreetName" runat="server" Width="296px"></asp:textbox></td>
                              <td style="WIDTH: 189px; HEIGHT: 28px"><asp:dropdownlist id="ddlCounties" runat="server"></asp:dropdownlist></td>
                              <td style="WIDTH: 179px; HEIGHT: 28px"></td>
                              <TD style="WIDTH: 167px; HEIGHT: 28px"><asp:checkbox id="chkSendToPaperWise" runat="server" Visible="False"></asp:checkbox></TD>
                              <TD style="WIDTH: 23px; HEIGHT: 28px">&nbsp;</TD>
                              <TD style="WIDTH: 8px; HEIGHT: 28px">&nbsp;</TD>
                        </tr>
                        <TR>
                              <TD class="fromFieldLableEmph" style="WIDTH: 157px; HEIGHT: 24px">Nhbrhood Code</TD>
                              <TD class="fromFieldLableEmph" style="WIDTH: 180px; HEIGHT: 24px">Year Built</TD>
                              <TD class="fromFieldLableEmph" style="WIDTH: 170px; HEIGHT: 24px">Sale Date</TD>
                              <TD class="fromFieldLableEmph" style="WIDTH: 189px; HEIGHT: 24px">Gross Sale Price</TD>
                              <TD class="fromFieldLableEmph" style="WIDTH: 179px; HEIGHT: 24px">Price PSF</TD>
                              <TD class="fromFieldLableEmph" style="WIDTH: 167px; HEIGHT: 24px">Grade</TD>
                              <TD style="WIDTH: 23px; HEIGHT: 24px"></TD>
                              <TD style="WIDTH: 8px; HEIGHT: 24px">&nbsp;</TD>
                        </TR>
                        <TR>
                              <TD style="WIDTH: 157px; HEIGHT: 22px"><asp:textbox id="txtNeighborhoodCode" runat="server" Width="96px"></asp:textbox></TD>
                              <TD style="WIDTH: 180px; HEIGHT: 22px"><asp:textbox id="txtYearBuilt" runat="server" Width="64px"></asp:textbox>&nbsp;to
                                    <asp:textbox id="txtYearBuiltTo" runat="server" Width="64px"></asp:textbox></TD>
                              <TD style="WIDTH: 170px; HEIGHT: 22px"><asp:textbox id="txtSaleDate" runat="server" Width="56px"></asp:textbox>&nbsp;to
                                    <asp:textbox id="txtSaleDateTo" runat="server" Width="56px"></asp:textbox></TD>
                              <TD style="WIDTH: 189px; HEIGHT: 22px"><asp:textbox id="txtGrossSalePrice" runat="server" Width="60px"></asp:textbox>&nbsp;to
                                    <asp:textbox id="txtGrossSalePriceTo" runat="server" Width="52px"></asp:textbox></TD>
                              <TD style="WIDTH: 179px; HEIGHT: 22px"><asp:textbox id="txtPricePSF" runat="server" Width="62px"></asp:textbox>&nbsp;to
                                    <asp:textbox id="txtPricePSFTo" runat="server" Width="62px"></asp:textbox></TD>
                              <TD style="WIDTH: 167px; HEIGHT: 22px"><asp:dropdownlist id="selGrade" runat="server">
                                          <asp:ListItem Selected="True"></asp:ListItem>
                                          <asp:ListItem Value="A">A</asp:ListItem>
                                          <asp:ListItem Value="B">B</asp:ListItem>
                                          <asp:ListItem Value="C">C</asp:ListItem>
                                          <asp:ListItem Value="D">D</asp:ListItem>
                                          <asp:ListItem Value="E">E</asp:ListItem>
                                          <asp:ListItem Value="F">F</asp:ListItem>
                                          <asp:ListItem Value="G">G</asp:ListItem>
                                          <asp:ListItem Value="L">L</asp:ListItem>
                                          <asp:ListItem Value="P">P</asp:ListItem>
                                          <asp:ListItem Value="S">S</asp:ListItem>
                                          <asp:ListItem Value="U">U</asp:ListItem>
                                          <asp:ListItem Value="V">V</asp:ListItem>
                                          <asp:ListItem Value="X">X</asp:ListItem>
                                    </asp:dropdownlist></TD>
                              <TD style="WIDTH: 23px; HEIGHT: 22px"></TD>
                              <td style="WIDTH: 8px; HEIGHT: 22px">&nbsp;</td>
                        </TR>
                        <tr>
                              <td class="fromFieldLableEmph" style="WIDTH: 157px; HEIGHT: 32px">Price PSF Imp</td>
                              <td class="fromFieldLableEmph" style="WIDTH: 180px; HEIGHT: 32px">House SF</td>
                              <td class="fromFieldLableEmph" style="WIDTH: 170px; HEIGHT: 32px">Assessment Ratio</td>
                              <td class="fromFieldLableEmph" style="WIDTH: 189px; HEIGHT: 32px">Year Remodled</td>
                              <td class="fromFieldLableEmph" style="WIDTH: 179px; HEIGHT: 32px">Key Map Page</td>
                              <td class="fromFieldLableEmph" style="WIDTH: 167px; HEIGHT: 32px">CDU&nbsp;</td>
                              <td class="fromFieldLableEmph" style="WIDTH: 23px; HEIGHT: 32px">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                              </td>
                              <td style="WIDTH: 8px; HEIGHT: 32px">&nbsp;</td>
                        </tr>
                        <tr>
                              <td style="WIDTH: 157px; HEIGHT: 26px"><asp:textbox id="txtPricePSFImp" runat="server" Width="56px"></asp:textbox>&nbsp;to
                                    <asp:textbox id="txtPricePSFImpTo" runat="server" Width="56px"></asp:textbox></td>
                              <td style="WIDTH: 180px; HEIGHT: 26px"><asp:textbox id="txtHouseSF" runat="server" Width="64px"></asp:textbox>&nbsp;to
                                    <asp:textbox id="txtHouseSFTo" runat="server" Width="64px"></asp:textbox></td>
                              <td style="WIDTH: 170px; HEIGHT: 26px"><asp:textbox id="txtAssessmentRatio" runat="server" Width="48px"></asp:textbox>&nbsp;to
                                    <asp:textbox id="txtAssessmentRatioTo" runat="server" Width="48px"></asp:textbox></td>
                              <TD style="WIDTH: 189px; HEIGHT: 26px"><asp:textbox id="txtYearRemodled" runat="server" Width="96px"></asp:textbox></TD>
                              <TD style="WIDTH: 179px; HEIGHT: 26px"><asp:textbox id="txtKeyMapPage" runat="server" Width="96px"></asp:textbox></TD>
                              <TD style="WIDTH: 167px; HEIGHT: 26px"><asp:textbox id="txtCDU" runat="server" Width="28px"></asp:textbox></TD>
                              <TD style="WIDTH: 23px; HEIGHT: 26px">&nbsp;&nbsp;&nbsp;&nbsp;</TD>
                              <td style="WIDTH: 8px; HEIGHT: 26px"></td>
                        </tr>
                        <tr>
                              <td style="WIDTH: 157px; HEIGHT: 17px"></td>
                              <td style="WIDTH: 180px; HEIGHT: 17px"></td>
                              <td style="WIDTH: 170px; HEIGHT: 17px"></td>
                              <td style="WIDTH: 189px; HEIGHT: 17px"></td>
                              <TD style="WIDTH: 179px; HEIGHT: 17px"></TD>
                              <TD style="WIDTH: 167px; HEIGHT: 17px"></TD>
                              <TD style="WIDTH: 23px; HEIGHT: 17px"></TD>
                              <TD style="WIDTH: 8px; HEIGHT: 17px"></TD>
                        </tr>
                        <TR>
                              <TD style="WIDTH: 1073px" colSpan="8"><asp:button id="cmdCustomFind" runat="server" Text="Custom Find"></asp:button></TD>
                        </TR>
                        <tr>
                              <td style="WIDTH: 1073px" colSpan="8"><br>
                                    <div style="VERTICAL-ALIGN: top; OVERFLOW: auto; WIDTH: 100.05%; HEIGHT: 400px">
                                          <asp:datagrid id="dgCustomFindResults" runat="server" Width="900" HorizontalAlign="Left" Font-Size="8pt"
                                                Font-Name="Arial" ShowHeader="True" AutoGenerateColumns="True" EnableViewState="True" AllowSorting="True"
                                                OnSortCommand="MLS_Sort">
                                                <Columns>
                                                      <asp:TemplateColumn>
                                                            <HeaderTemplate>
                                                                  <asp:CheckBox onclick="javascript:SelectAllCheckboxes(this);" id="chkAll" runat="server"></asp:CheckBox>
                                                            </HeaderTemplate>
                                                            <ItemTemplate>
                                                                  <asp:CheckBox ID="myCheckbox" EnableViewState="False" AutoPostBack="False" Runat="server" />
                                                            </ItemTemplate>
                                                      </asp:TemplateColumn>
                                                </Columns>
                                          </asp:datagrid></div>
                              </td>
                        </tr>
                        <tr>
                              <td style="WIDTH: 1073px" colSpan="8"><asp:label id="lblResultsCount" runat="server"></asp:label><br>
                                    <br>
                              </td>
                        </tr>
                        <tr>
                              <td style="WIDTH: 1073px" colSpan="8"><asp:button id="cmdGetSelections" runat="server" Visible="False" Text="Build Ratio Study Report"></asp:button>&nbsp;
                                    <asp:button id="cmdPrintRatioStudy" runat="server" Visible="False" Text=" Sales Report"></asp:button></td>
                        </tr>
                  </table>
            </form>
      </body>
</HTML>

==================================================================


index.aspx.cs
===================================
using System.Data.SqlClient;
using System.Configuration;

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Text;

namespace MLS2
{
      /// <summary>
      /// Summary description for WebForm1.
      /// </summary>
      public class WebForm1 : System.Web.UI.Page
      {
            protected System.Web.UI.WebControls.TextBox txtStreetNumber;
            protected System.Web.UI.WebControls.TextBox txtStreetName;
            protected System.Web.UI.WebControls.TextBox txtNeighborhoodCode;
            protected System.Web.UI.WebControls.TextBox txtYearBuilt;
            protected System.Web.UI.WebControls.TextBox txtCDU;
            protected System.Web.UI.WebControls.TextBox txtPricePSF;
            protected System.Web.UI.WebControls.TextBox txtPricePSFImp;
            protected System.Web.UI.WebControls.TextBox txtHouseSF;
            protected System.Web.UI.WebControls.TextBox txtYearRemodled;
            protected System.Web.UI.WebControls.TextBox txtKeyMapPage;
            protected System.Web.UI.WebControls.Button cmdCustomFind;
            protected System.Web.UI.WebControls.Button cmdPrintRatioStudy;
            protected System.Web.UI.WebControls.DataGrid dgCustomFindResults;
            protected System.Web.UI.WebControls.Button selBtn;
            protected System.Web.UI.WebControls.Button cmdGetSelections;
            protected System.Web.UI.WebControls.Label lblResultsCount;
            protected System.Web.UI.WebControls.CheckBox chkSendToPaperWise;
            protected System.Web.UI.WebControls.TextBox txtYearBuiltTo;
            protected System.Web.UI.WebControls.TextBox txtPricePSFImpTo;
            protected System.Web.UI.WebControls.TextBox txtHouseSFTo;
            protected System.Web.UI.WebControls.DropDownList DropDownList1;
            protected System.Web.UI.WebControls.CheckBox myCheckbox;
            protected System.Web.UI.WebControls.DropDownList selGrade;
            protected System.Web.UI.WebControls.TextBox txtAssessmentRatio;
            protected System.Web.UI.WebControls.TextBox txtSaleDate;
            protected System.Web.UI.WebControls.TextBox txtAssessmentRatioTo;
            protected System.Web.UI.WebControls.TextBox txtGrossSalePriceTo;
            protected System.Web.UI.WebControls.TextBox txtGrossSalePrice;
            protected System.Web.UI.WebControls.TextBox txtPricePSFTo;
            protected System.Web.UI.WebControls.DropDownList ddlCounties;
            protected System.Web.UI.WebControls.TextBox txtSaleDateTo;      

            private DataView dv;



            private void Page_Load(object sender, System.EventArgs e)
            {
                  string CONN = ConfigurationSettings.AppSettings["DB_CONN"];

                  if(!Page.IsPostBack)
                  {
                        // populate county drop down list.
                        string ddlCONN = ConfigurationSettings.AppSettings["DB_CONN"];
                        System.Data.SqlClient.SqlConnection objConn = new System.Data.SqlClient.SqlConnection();
                        objConn.ConnectionString = ddlCONN;
                        objConn.Open();
                        System.Data.SqlClient.SqlCommand objCmd = new System.Data.SqlClient.SqlCommand("MLS_GetCounties", objConn);
                        objCmd.CommandType = System.Data.CommandType.StoredProcedure;
                        ddlCounties.DataSource = objCmd.ExecuteReader();
                        ddlCounties.DataTextField = "cad_CADCode";
                        ddlCounties.DataValueField = "cad_CADID";
                        ddlCounties.DataBind();
                        objConn.Close();

                        BindGrid("txroll_NeighborhoodCode");

                  }

            }

            #region Web Form Designer generated code
            override protected void OnInit(EventArgs e)
            {
                  //
                  // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                  //
                  InitializeComponent();
                  base.OnInit(e);
                  
            }
            
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {    
                  this.txtNeighborhoodCode.TextChanged += new System.EventHandler(this.txtNeighborhoodCode_TextChanged);
                  this.cmdCustomFind.Click += new System.EventHandler(this.CustomFindEngage);
                  this.cmdGetSelections.Click += new System.EventHandler(this.BuildRatioStudyEngage);
                  this.cmdPrintRatioStudy.Click += new System.EventHandler(this.RatioStudyEngage);
                  this.Load += new System.EventHandler(this.Page_Load);

            }
            #endregion

            private void txtNeighborhoodCode_TextChanged(object sender, System.EventArgs e)
            {
            
            }



            public void CustomFindEngage(object sender, System.EventArgs e)
            {      
            

                  string strStreetNum = txtStreetNumber.Text.ToString();
                  string strStreetName = txtStreetName.Text.ToString();
                  string strKeymapPage = txtKeyMapPage.Text.ToString();
                  string strNeighborhoodCode = txtNeighborhoodCode.Text.ToString();
                  //string strCountyName = txtCountyName.Text.ToString();
                  string strCountyName = ddlCounties.SelectedItem.Text.ToString();
                  string intYearBuilt = txtYearBuilt.Text.ToString();
                  string intYearBuiltTo = txtYearBuiltTo.Text.ToString();
                  string strGrade = selGrade.SelectedItem.ToString();
                  string fltAssessmentRatio = txtAssessmentRatio.ToString();
                  string fltAssessmentRatioTo = txtAssessmentRatioTo.ToString();
                  string intSaleDate = txtSaleDate.ToString();
                  string intSaleDateTo = txtSaleDateTo.ToString();
                  string intGrossSalePrice = txtGrossSalePrice.ToString();
                  string intYearRemdl = txtYearRemodled.ToString();
                  string strCDU = txtCDU.Text.ToString();


                  string CONN = ConfigurationSettings.AppSettings["DB_CONN"];
                  SqlConnection connObj = new SqlConnection(CONN);
                  SqlCommand cmdObj = new SqlCommand("MLS_GetData", connObj);
                  cmdObj.CommandType = CommandType.StoredProcedure;

                  if (txtStreetNumber.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strStreetNum", strStreetNum));
                  }
                  if (txtStreetName.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strStreetName", strStreetName));
                  }
                  if (txtKeyMapPage.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strKeymapPage", strKeymapPage));
                  }
                  if (txtNeighborhoodCode.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strNeighborhoodCode", strNeighborhoodCode));
                  }
//                  if (txtCountyName.Text != "")
//                  {
//                        cmdObj.Parameters.Add(new SqlParameter("@strCountyName", strCountyName));
//                  }
                  if (ddlCounties.SelectedItem.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strCountyName", strCountyName));
                  }

                  if (txtYearBuilt.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intYearBuilt", intYearBuilt));
                  }
                  if (txtYearBuiltTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intYearBuiltTo", intYearBuiltTo));
                  }
                  if (selGrade.SelectedItem.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strGrade", strGrade));
                  }

                  // special signiture for datetime parse
                  if (txtSaleDate.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intSaleDate", SqlDbType.DateTime));
                        cmdObj.Parameters["@intSaleDate"].Value = DateTime.Parse(txtSaleDate.Text);
                  }
                  if (txtSaleDateTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intSaleDateTo", SqlDbType.DateTime));
                        cmdObj.Parameters["@intSaleDateTo"].Value = DateTime.Parse(txtSaleDateTo.Text);
                  }
                  if (txtGrossSalePrice.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intGrossSalePrice", SqlDbType.Money));
                        cmdObj.Parameters["@intGrossSalePrice"].Value = Convert.ToDecimal(txtGrossSalePrice.Text);
                  }
                  if (txtGrossSalePriceTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intGrossSalePriceTo", SqlDbType.Money));
                        cmdObj.Parameters["@intGrossSalePriceTo"].Value = Convert.ToDecimal(txtGrossSalePriceTo.Text);
                  }
                  if (txtYearRemodled.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intYearRemdl", SqlDbType.Int));
                        cmdObj.Parameters["@intYearRemdl"].Value = Convert.ToInt32(txtYearRemodled.Text);
                  }
                  if (txtAssessmentRatio.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@fltAssessmentRatio", SqlDbType.Decimal));
                        cmdObj.Parameters["@fltAssessmentRatio"].Value = Convert.ToDecimal(txtAssessmentRatio.Text);
                  }
                  if (txtAssessmentRatioTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@fltAssessmentRatioTo", SqlDbType.Decimal));
                        cmdObj.Parameters["@fltAssessmentRatioTo"].Value = Convert.ToDecimal(txtAssessmentRatioTo.Text);
                  }
                  //txtPricePSFImp
                  if (txtPricePSFImp.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intImp", SqlDbType.Decimal));
                        cmdObj.Parameters["@intImp"].Value = Convert.ToDecimal(txtPricePSFImp.Text);
                  }
                  if (txtPricePSFImpTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intImpTo", SqlDbType.Decimal));
                        cmdObj.Parameters["@intImpTo"].Value = Convert.ToDecimal(txtPricePSFImpTo.Text);
                  }
                  if (txtHouseSF.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intHouseSF", SqlDbType.Int));
                        cmdObj.Parameters["@intHouseSF"].Value = Convert.ToInt32(txtHouseSF.Text);
                  }
                  if (txtHouseSFTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intHouseSFTo", SqlDbType.Int));
                        cmdObj.Parameters["@intHouseSFTo"].Value = Convert.ToInt32(txtHouseSFTo.Text);
                  }
                  if (txtCDU.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strCDU", strCDU));
                  }
                  if (txtPricePSF.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intPricePSF", SqlDbType.Money));
                        cmdObj.Parameters["@intPricePSF"].Value = Convert.ToDecimal(txtPricePSF.Text);
                  }
                  if (txtPricePSFTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intPricePSFTo", SqlDbType.Money));
                        cmdObj.Parameters["@intPricePSFTo"].Value = Convert.ToDecimal(txtPricePSFTo.Text);
                  }


                        SqlDataAdapter da = new SqlDataAdapter ( cmdObj ) ;
                        DataSet dsMLS = new DataSet("MLS");
                        da.SelectCommand.CommandTimeout = 0;
                        da.Fill(dsMLS, "MLS");
                        dgCustomFindResults.DataSource =  dsMLS.Tables["MLS"].DefaultView;
                        dgCustomFindResults.DataBind();

                        cmdGetSelections.Visible = true;
                        cmdPrintRatioStudy.Visible = true;

                        lblResultsCount.Text = "Number of records returned =  " + dsMLS.Tables["MLS"].Rows.Count.ToString();

      }

            //string myList = "'02843010110000,04210107260000,02472811250000,aaa'";

            // this is the sales report button method
            private void RatioStudyEngage(object sender, System.EventArgs e)
            {
                  if (chkSendToPaperWise.Checked == true)
                  {
                        Session["SendToPaperWise"] = "SendYes";
                  }
                  else
                  {
                        Session["SendToPaperWise"] = "SendNo";
                  }

                  string strCadNumbersList = "aaa,"; // the cadid list used for stored proc
                  string lastSelectedCADID = ""; // used to build the name of the CR Report for PaperWise

                  foreach(DataGridItem dgi in dgCustomFindResults.Items)
                  {
                        CheckBox chkSelected = (CheckBox)dgi.FindControl("myCheckbox");

                        if(chkSelected.Checked == true)
                        {
                              strCadNumbersList += dgi.Cells[1].Text.ToString() + ",";
                              lastSelectedCADID = dgi.Cells[1].Text.ToString();
                        }
                  }
                  strCadNumbersList += "aaa";
                  strCadNumbersList = "'" + strCadNumbersList + "'";
                  Session["strCadNumbersList"] = strCadNumbersList.ToString();
                  Session["lastSelectedCADID"] = lastSelectedCADID.ToString();
                  //lblResultsInfo.Text = strCadNumbersList.ToString();
                  //lblResultsInfo.Text = lastSelectedCADID.ToString();


                  // ****************************************************
                  //  open popup window and display Neighborhood report
                  // ****************************************************
                  StringBuilder sb = new StringBuilder();
                  sb.Append("<script>");
                  sb.Append("window.open('dsp_SalesReport.aspx', '', '');");
                  sb.Append("</script>");
                  Page.RegisterStartupScript("SalesReportPage", sb.ToString());

            }


          // this is the acutal ratio study button method.
            private void BuildRatioStudyEngage(object sender, System.EventArgs e)
            {
                  if (chkSendToPaperWise.Checked == true)
                  {
                        Session["SendToPaperWise"] = "SendYes";
                  }
                  else
                  {
                        Session["SendToPaperWise"] = "SendNo";
                  }

                  string strCadNumbersList = "aaa,"; // the cadid list used for stored proc
                  string lastSelectedCADID = ""; // used to build the name of the CR Report for PaperWise

                  foreach(DataGridItem dgi in dgCustomFindResults.Items)
                  {
                        CheckBox chkSelected = (CheckBox)dgi.FindControl("myCheckbox");

                        if(chkSelected.Checked == true)
                        {
                              strCadNumbersList += dgi.Cells[1].Text.ToString() + ",";
                              lastSelectedCADID = dgi.Cells[1].Text.ToString();
                        }
                  }
                  strCadNumbersList += "aaa";
                  strCadNumbersList = "'" + strCadNumbersList + "'";
                  Session["strCadNumbersList"] = strCadNumbersList.ToString();
                  Session["lastSelectedCADID"] = lastSelectedCADID.ToString();
                  //lblResultsInfo.Text = strCadNumbersList.ToString();
                  //lblResultsInfo.Text = lastSelectedCADID.ToString();

                  // ****************************************************
                  //  open popup window and display Neighborhood report
                  // ****************************************************
                  StringBuilder sb = new StringBuilder();
                  sb.Append("<script>");
                  //sb.Append("window.open('dspSelectedProperties.aspx', '', '');");
                  //sb.Append("window.open('dsp_Test1.aspx', '', '');");
                  sb.Append("window.open('dspSelectedProperties.aspx', '', '');");
                  sb.Append("</script>");
                  Page.RegisterStartupScript("SelectedPropertiesPage", sb.ToString());

            }

            private void chkSendToPaperWise_CheckedChanged(object sender, System.EventArgs e)
            {
            
            }

            private void txtGroup_TextChanged(object sender, System.EventArgs e)
            {
            
            }

            private void DropDownList1_SelectedIndexChanged(object sender, System.EventArgs e)
            {
            
            }

            public void MLS_Sort(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
            {
                  //
                  BindGrid(e.SortExpression);
            }

            public void BindGrid(String sortfield)
            {

                  /////////////
                  
            }




      }
}
========================================================


GetData stored procedure:
=====================================
CREATE PROCEDURE dbo.MLS_GetData

      @strStreetNum varchar (6) = NULL,
      @strStreetName varchar (30) = NULL,
      @strKeymapPage  nvarchar (6) = NULL,
      @strNeighborhoodCode varchar (10) = NULL,
      @strCountyName varchar (255) = NULL,
      @intYearBuilt int  = NULL,
      @intYearBuiltTo int = NULL,
             @strGrade char (1) = NULL,
             @intSaleDate datetime = NULL,
             @intSaleDateTo datetime = NULL,
      @intGrossSalePrice money = NULL,
      @intGrossSalePriceTo money = NULL,
      @intYearRemdl int = NULL,
      @fltAssessmentRatio numeric (6,4) = NULL,
      @fltAssessmentRatioTo numeric (6,4) = NULL,
      @intImp money = NULL,
      @intImpTo money = NULL,
      @intHouseSF int = NULL,
      @intHouseSFTo int = NULL,
      @strCDU varchar (50) = NULL,
      @intPricePSF money = NULL,
      @intPricePSFTo money = NULL,
      @debug     bit = 0

 AS

DECLARE @sql     nvarchar(4000),                                  
                 @paramlist  nvarchar(4000)                    
                                                                       
   SELECT @sql =            

      'SELECT TOP 1000 a.cad_account_number,  b.txroll_year, a.date_sold, a.sale_price, a.year_remdl, a.condi_desire_util,
             b.txroll_streetNumber, b.txroll_StreetName, b.txroll_YearBuilt, b.Txroll_Keymap,
             b.txroll_NeighborhoodCode, b.txroll_GBA, b.txroll_NoticedLandValue, b.txroll_NoticedTotalValue,
                    b.txroll_Grade,
            (b.txroll_NoticedTotalValue/a.sale_price) AS ratio,
           (a.sale_price - b.txroll_NoticedLandValue)/b.txroll_GBA  AS imp,
                  a.sale_price /  b.txroll_GBA  AS price_PSF
      
      FROM dbo.MLS a
      INNER JOIN dbo.tbl_TaxRoll b
         ON a.cad_account_number = b.txroll_CadAccountNumber AND a.cad_id = b.txroll_CadID
      WHERE b.txroll_Year = 2006 AND b.txroll_NeighborhoodCode IS NOT NULL AND b.txroll_GBA > 0 AND a.sale_price > 0'  



   IF @strStreetNum  IS NOT NULL                                                
      SELECT @sql = @sql + ' AND  b.txroll_streetNumber = @x_StreetNum'  

   IF @strStreetName  IS NOT NULL                                                
      SELECT @sql = @sql + ' AND  b.txroll_StreetName LIKE @x_StreetName + ''%'''  
 
  IF @strKeymapPage  IS NOT NULL                                                
      SELECT @sql = @sql + ' AND  b.txroll_Keymap = @x_KeymapPage'  

   IF @strNeighborhoodCode  IS NOT NULL                                                
      SELECT @sql = @sql + ' AND  b.txroll_NeighborhoodCode = @x_NeighborhoodCode'  

   IF @strCountyName  IS NOT NULL                                                
      SELECT @sql = @sql + ' AND  a.county_name = @x_CountyName'  

   IF @intYearBuilt  IS NOT NULL                                                
      SELECT @sql = @sql + ' AND  b.txroll_YearBuilt >= @x_YearBuilt'  

   IF @intYearBuiltTo  IS NOT NULL                                                
      SELECT @sql = @sql + ' AND  b.txroll_YearBuilt<= @x_YearBuiltTo'  

   IF @strGrade  IS NOT NULL                                                
      SELECT @sql = @sql + ' AND  b.txroll_Grade LIKE @x_Grade + ''%'''  

   IF @intSaleDate IS NOT NULL
       SELECT @sql = @sql + ' AND a.date_sold >= @x_DateSold'

   IF @intSaleDateTo IS NOT NULL
       SELECT @sql = @sql + ' AND a.date_sold <= @x_DateSoldTo'

   IF @intGrossSalePrice IS NOT NULL
       SELECT @sql = @sql + ' AND a.sale_price >= @x_GrossSalePrice'

   IF @intGrossSalePriceTo IS NOT NULL
       SELECT @sql = @sql + ' AND a.sale_price <= @x_GrossSalePriceTo'

   IF @intYearRemdl   IS NOT NULL                                                
      SELECT @sql = @sql + ' AND  a.year_remdl = @x_YearRemdl '  

   IF @fltAssessmentRatio IS NOT NULL
       SELECT @sql = @sql + ' AND b.txroll_NoticedTotalValue/a.sale_price >= @x_Ratio'

   IF @fltAssessmentRatioTo IS NOT NULL
       SELECT @sql = @sql + ' AND b.txroll_NoticedTotalValue/a.sale_price <= @x_RatioTo'

   IF @intImp IS NOT NULL
       SELECT @sql = @sql + ' AND ((a.sale_price - b.txroll_NoticedLandValue)/b.txroll_GBA) >= @x_Imp '

   IF @intImpTo IS NOT NULL
       SELECT @sql = @sql + ' AND ((a.sale_price - b.txroll_NoticedLandValue)/b.txroll_GBA) <= @x_ImpTo '

   IF @intHouseSF IS NOT NULL
       SELECT @sql = @sql + ' AND b.txroll_GBA >= @x_HouseSF'

   IF @intHouseSFTo IS NOT NULL
       SELECT @sql = @sql + ' AND b.txroll_GBA <= @x_HouseSFTo'

   IF @strCDU IS NOT NULL
       SELECT @sql = @sql + ' AND a.condi_desire_util = @x_CDU'

   IF @intPricePSF IS NOT NULL
        SELECT @sql = @sql + ' AND a.sale_price /  b.txroll_GBA > = @x_PricePSF'

   IF @intPricePSFTo IS NOT NULL
        SELECT @sql = @sql + ' AND a.sale_price /  b.txroll_GBA <= @x_PricePSFTo'



   --SELECT @sql = @sql + ' ORDER BY  b.txroll_NeighborhoodCode, a.date_sold DESC'                          
                                                                         
   IF @debug = 1                                                      
      PRINT @sql  

SELECT @paramlist =
                '@x_StreetNum   varchar(6),                                  
                        @x_StreetName   varchar (30),
                        @x_KeymapPage nvarchar (6),
                        @x_NeighborhoodCode varchar (10),
                 @x_CountyName varchar (255),
                 @x_YearBuilt int,
                 @x_YearBuiltTo int,
                  @x_Grade char (1),
                        @x_DateSold datetime,
                 @x_DateSoldTo datetime,
                        @x_GrossSalePrice money,
                 @x_GrossSalePriceTo money,
                 @x_YearRemdl int,
                 @x_Ratio numeric (6,4),
                 @x_RatioTo numeric (6,4),
                 @x_Imp money,
                        @x_ImpTo money,
                 @x_HouseSF int,
                 @x_HouseSFTo int,
                 @x_CDU varchar (50),
                 @x_PricePSF money,
                 @x_PricePSFTo money'                                                                  


EXEC sp_executesql @sql, @paramlist,                            
                                  @strStreetNum, @strStreetName, @strKeymapPage, @strNeighborhoodCode, @strCountyName, @intYearBuilt, @intYearBuiltTo, @strGrade,
                   @intSaleDate, @intSaleDateTo, @intGrossSalePrice, @intGrossSalePriceTo, @intYearRemdl, @fltAssessmentRatio, @fltAssessmentRatioTo, @intImp, @intImpTo, @intHouseSF, @intHouseSFTo, @strCDU,
                   @intPricePSF, @intPricePSFTo


RETURN
GO
===================================================


0
Comment
Question by:FastEddie___
  • 2
4 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 17039820
You would first want to save the results of your query in a DataSet (DataTable)..

This is quite easily done using a DataAdapter ... You do something similar here ..

                    SqlDataAdapter da = new SqlDataAdapter ( cmdObj ) ;
                    DataSet dsMLS = new DataSet("MLS");
                    da.SelectCommand.CommandTimeout = 0;
                    da.Fill(dsMLS, "MLS");
                    dgCustomFindResults.DataSource =  dsMLS.Tables["MLS"].DefaultView;
                    dgCustomFindResults.DataBind();

You would then want to cache those results ... as a simple example.

Cache[SessionId + ":MLSDATA"] = dsMLS;

Obviously on the other end you would want to check your cache ... so your code might look something like this

                    DataSet dsMLS = Cahce[SessionId + ":MLSDATA"] as DataSet;
                    if(dsMLS == null) {
                        SqlDataAdapter da = new SqlDataAdapter ( cmdObj ) ;
                        dsMLS = new DataSet("MLS");
                        da.SelectCommand.CommandTimeout = 0;
                        da.Fill(dsMLS, "MLS");
                    }
                    dgCustomFindResults.DataSource =  dsMLS.Tables["MLS"].DefaultView;
                    dgCustomFindResults.DataBind();

and you have cached the dataset ...


For the sorting you are right a dataview would be a good way of doing this for sorting you would use code similar to the following.

dsMLS.Tables["MLS"].DefaultView.Sort = "YourFirstColumn, YourSecondColumn";

Just like in SQL

I noticed that your bindgrid method is actually empty .. If you need further help that code would help.

Also here is a example I wrote a while ago that shows how the caching is done using session ... I personally lean towards using the cache (and handling the case that your dataset disappears but:))

Cheers,

Greg Young




            private void Page_Load(object sender, System.EventArgs e)
            {
                  if(Request["PageSize"] != null && Request["PageSize"] != "") {
                        this.dgRedirects.PageSize = int.Parse(Request["PageSize"]);
                  }
                  m_Dataset = Session["RedirectData"] as DataSet;
                  if(!this.IsPostBack) {
                        SortHelper.SetNewSortExpression("IncomingPath", ViewState);
                        LoadData();
                        BindData();
                  }
            }

            private void LoadData() {
                  string sql = "select pkUrlMapId, IncomingPath, RedirectPath from UrlMap";
                  m_Dataset = new DataSet();
                  using(SqlConnection conn = new SqlConnection(Config.ConnectionString)) {
                        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                        da.Fill(m_Dataset);
                        conn.Close();
                  }
                  Session["RedirectData"] = m_Dataset;
            }

            private void BindData() {
                  DataView view = new DataView(m_Dataset.Tables[0]);
                  view.Sort = SortHelper.BuildSortExpression(ViewState);
                  if(this.dgRedirects.CurrentPageIndex * this.dgRedirects.PageSize <= view.Table.Rows.Count) {
                        this.dgRedirects.CurrentPageIndex = this.dgRedirects.CurrentPageIndex >0 ? this.dgRedirects.CurrentPageIndex -1 : 0;
                  }
                  this.dgRedirects.DataSource = view;
                  this.dgRedirects.DataBind();
            }

0
 

Author Comment

by:FastEddie___
ID: 17044584
Hi gregoryyoung,

You have some great concepts here. (Using a cached dataset, Sorting using a DataView.)
This is new territory for me so please bear with me just a bit.

What do you mean by: "on the other end you would want to check your cache" ? Does the "other end" mean, putting :

                   DataSet dsMLS = Cahce[SessionId + ":MLSDATA"] as DataSet;
                    if(dsMLS == null) {
                        SqlDataAdapter da = new SqlDataAdapter ( cmdObj ) ;
                        dsMLS = new DataSet("MLS");
                        da.SelectCommand.CommandTimeout = 0;
                        da.Fill(dsMLS, "MLS");
                    }

in a different method other than   "public void CustomFindEngage(object sender, System.EventArgs e)" ?

On the sorting, you have some code refrencing Request["PageSize"]. I'm guessing your datagrid has multiple pages and you are checking which page number you are on? Is that correct? My datagrid doesn't use pagination so I won't have to worry about that... right?

On the PageLoad method you have code that check for !IsPostBack and LoadData and BindData methods.
My app has a submit button that runs the CustomFindEngage() method and builds the datagrid. Since it is a submit button, that would mean that the page will always be a postback, right? So I'm wondering if the !IsPostBack will ever be called.

Also, do I need to use the sessionid? This code:  Cache[SessionId + ":MLSDATA"] = dsMLS;  is giving me this error:  'SessionId' does not exist in the class or namespace 'MLS2.WebForm1'.

I really do appreciate all your comments and insight. Could you please elaborate just a bit more on this?

Here is the code I have so far:

///////////////////////////////////////////////////////////////////////

using System.Data.SqlClient;
using System.Configuration;

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Text;

namespace MLS2
{
      /// <summary>
      /// Summary description for WebForm1.
      /// </summary>
      public class WebForm1 : System.Web.UI.Page
      {
            protected System.Web.UI.WebControls.TextBox txtStreetNumber;
            protected System.Web.UI.WebControls.TextBox txtStreetName;
            protected System.Web.UI.WebControls.TextBox txtNeighborhoodCode;
            protected System.Web.UI.WebControls.TextBox txtYearBuilt;
            protected System.Web.UI.WebControls.TextBox txtCDU;
            protected System.Web.UI.WebControls.TextBox txtPricePSF;
            protected System.Web.UI.WebControls.TextBox txtPricePSFImp;
            protected System.Web.UI.WebControls.TextBox txtHouseSF;
            protected System.Web.UI.WebControls.TextBox txtYearRemodled;
            protected System.Web.UI.WebControls.TextBox txtKeyMapPage;
            protected System.Web.UI.WebControls.Button cmdCustomFind;
            protected System.Web.UI.WebControls.Button cmdPrintRatioStudy;
            protected System.Web.UI.WebControls.DataGrid dgCustomFindResults;
            protected System.Web.UI.WebControls.Button selBtn;
            protected System.Web.UI.WebControls.Button cmdGetSelections;
            protected System.Web.UI.WebControls.Label lblResultsCount;
            protected System.Web.UI.WebControls.CheckBox chkSendToPaperWise;
            protected System.Web.UI.WebControls.TextBox txtYearBuiltTo;
            protected System.Web.UI.WebControls.TextBox txtPricePSFImpTo;
            protected System.Web.UI.WebControls.TextBox txtHouseSFTo;
            protected System.Web.UI.WebControls.DropDownList DropDownList1;
            protected System.Web.UI.WebControls.CheckBox myCheckbox;
            protected System.Web.UI.WebControls.DropDownList selGrade;
            protected System.Web.UI.WebControls.TextBox txtAssessmentRatio;
            protected System.Web.UI.WebControls.TextBox txtSaleDate;
            protected System.Web.UI.WebControls.TextBox txtAssessmentRatioTo;
            protected System.Web.UI.WebControls.TextBox txtGrossSalePriceTo;
            protected System.Web.UI.WebControls.TextBox txtGrossSalePrice;
            protected System.Web.UI.WebControls.TextBox txtPricePSFTo;
            protected System.Web.UI.WebControls.DropDownList ddlCounties;
            protected System.Web.UI.WebControls.TextBox txtSaleDateTo;      



            private void Page_Load(object sender, System.EventArgs e)
            {
                  string CONN = ConfigurationSettings.AppSettings["DB_CONN"];

                  if(!Page.IsPostBack)
                  {
                        // populate county drop down list.
                        string ddlCONN = ConfigurationSettings.AppSettings["DB_CONN"];
                        System.Data.SqlClient.SqlConnection objConn = new System.Data.SqlClient.SqlConnection();
                        objConn.ConnectionString = ddlCONN;
                        objConn.Open();
                        System.Data.SqlClient.SqlCommand objCmd = new System.Data.SqlClient.SqlCommand("MLS_GetCounties", objConn);
                        objCmd.CommandType = System.Data.CommandType.StoredProcedure;
                        ddlCounties.DataSource = objCmd.ExecuteReader();
                        ddlCounties.DataTextField = "cad_CADCode";
                        ddlCounties.DataValueField = "cad_CADID";
                        ddlCounties.DataBind();
                        objConn.Close();

                        BindGrid("txroll_NeighborhoodCode");

                  }

            }

            #region Web Form Designer generated code
            override protected void OnInit(EventArgs e)
            {
                  //
                  // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                  //
                  InitializeComponent();
                  base.OnInit(e);
                  
            }
            
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {    
                  this.txtNeighborhoodCode.TextChanged += new System.EventHandler(this.txtNeighborhoodCode_TextChanged);
                  this.cmdCustomFind.Click += new System.EventHandler(this.CustomFindEngage);
                  this.cmdGetSelections.Click += new System.EventHandler(this.BuildRatioStudyEngage);
                  this.cmdPrintRatioStudy.Click += new System.EventHandler(this.RatioStudyEngage);
                  this.Load += new System.EventHandler(this.Page_Load);

            }
            #endregion

            private void txtNeighborhoodCode_TextChanged(object sender, System.EventArgs e)
            {
            
            }



            public void CustomFindEngage(object sender, System.EventArgs e)
            {      
            

                  string strStreetNum = txtStreetNumber.Text.ToString();
                  string strStreetName = txtStreetName.Text.ToString();
                  string strKeymapPage = txtKeyMapPage.Text.ToString();
                  string strNeighborhoodCode = txtNeighborhoodCode.Text.ToString();
                  //string strCountyName = txtCountyName.Text.ToString();
                  string strCountyName = ddlCounties.SelectedItem.Text.ToString();
                  string intYearBuilt = txtYearBuilt.Text.ToString();
                  string intYearBuiltTo = txtYearBuiltTo.Text.ToString();
                  string strGrade = selGrade.SelectedItem.ToString();
                  string fltAssessmentRatio = txtAssessmentRatio.ToString();
                  string fltAssessmentRatioTo = txtAssessmentRatioTo.ToString();
                  string intSaleDate = txtSaleDate.ToString();
                  string intSaleDateTo = txtSaleDateTo.ToString();
                  string intGrossSalePrice = txtGrossSalePrice.ToString();
                  string intYearRemdl = txtYearRemodled.ToString();
                  string strCDU = txtCDU.Text.ToString();


                  string CONN = ConfigurationSettings.AppSettings["DB_CONN"];
                  SqlConnection connObj = new SqlConnection(CONN);
                  SqlCommand cmdObj = new SqlCommand("MLS_GetData", connObj);
                  cmdObj.CommandType = CommandType.StoredProcedure;

                  if (txtStreetNumber.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strStreetNum", strStreetNum));
                  }
                  if (txtStreetName.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strStreetName", strStreetName));
                  }
                  if (txtKeyMapPage.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strKeymapPage", strKeymapPage));
                  }
                  if (txtNeighborhoodCode.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strNeighborhoodCode", strNeighborhoodCode));
                  }
//                  if (txtCountyName.Text != "")
//                  {
//                        cmdObj.Parameters.Add(new SqlParameter("@strCountyName", strCountyName));
//                  }
                  if (ddlCounties.SelectedItem.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strCountyName", strCountyName));
                  }

                  if (txtYearBuilt.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intYearBuilt", intYearBuilt));
                  }
                  if (txtYearBuiltTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intYearBuiltTo", intYearBuiltTo));
                  }
                  if (selGrade.SelectedItem.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strGrade", strGrade));
                  }

                  // special signiture for datetime parse
                  if (txtSaleDate.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intSaleDate", SqlDbType.DateTime));
                        cmdObj.Parameters["@intSaleDate"].Value = DateTime.Parse(txtSaleDate.Text);
                  }
                  if (txtSaleDateTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intSaleDateTo", SqlDbType.DateTime));
                        cmdObj.Parameters["@intSaleDateTo"].Value = DateTime.Parse(txtSaleDateTo.Text);
                  }
                  if (txtGrossSalePrice.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intGrossSalePrice", SqlDbType.Money));
                        cmdObj.Parameters["@intGrossSalePrice"].Value = Convert.ToDecimal(txtGrossSalePrice.Text);
                  }
                  if (txtGrossSalePriceTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intGrossSalePriceTo", SqlDbType.Money));
                        cmdObj.Parameters["@intGrossSalePriceTo"].Value = Convert.ToDecimal(txtGrossSalePriceTo.Text);
                  }
                  if (txtYearRemodled.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intYearRemdl", SqlDbType.Int));
                        cmdObj.Parameters["@intYearRemdl"].Value = Convert.ToInt32(txtYearRemodled.Text);
                  }
                  if (txtAssessmentRatio.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@fltAssessmentRatio", SqlDbType.Decimal));
                        cmdObj.Parameters["@fltAssessmentRatio"].Value = Convert.ToDecimal(txtAssessmentRatio.Text);
                  }
                  if (txtAssessmentRatioTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@fltAssessmentRatioTo", SqlDbType.Decimal));
                        cmdObj.Parameters["@fltAssessmentRatioTo"].Value = Convert.ToDecimal(txtAssessmentRatioTo.Text);
                  }
                  //txtPricePSFImp
                  if (txtPricePSFImp.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intImp", SqlDbType.Decimal));
                        cmdObj.Parameters["@intImp"].Value = Convert.ToDecimal(txtPricePSFImp.Text);
                  }
                  if (txtPricePSFImpTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intImpTo", SqlDbType.Decimal));
                        cmdObj.Parameters["@intImpTo"].Value = Convert.ToDecimal(txtPricePSFImpTo.Text);
                  }
                  if (txtHouseSF.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intHouseSF", SqlDbType.Int));
                        cmdObj.Parameters["@intHouseSF"].Value = Convert.ToInt32(txtHouseSF.Text);
                  }
                  if (txtHouseSFTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intHouseSFTo", SqlDbType.Int));
                        cmdObj.Parameters["@intHouseSFTo"].Value = Convert.ToInt32(txtHouseSFTo.Text);
                  }
                  if (txtCDU.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@strCDU", strCDU));
                  }
                  if (txtPricePSF.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intPricePSF", SqlDbType.Money));
                        cmdObj.Parameters["@intPricePSF"].Value = Convert.ToDecimal(txtPricePSF.Text);
                  }
                  if (txtPricePSFTo.Text != "")
                  {
                        cmdObj.Parameters.Add(new SqlParameter("@intPricePSFTo", SqlDbType.Money));
                        cmdObj.Parameters["@intPricePSFTo"].Value = Convert.ToDecimal(txtPricePSFTo.Text);
                  }

                  

                        SqlDataAdapter da = new SqlDataAdapter ( cmdObj ) ;
                        DataSet dsMLS = new DataSet("MLS");
                        da.SelectCommand.CommandTimeout = 0;
                        da.Fill(dsMLS, "MLS");
                        DataTable dt = dsMLS.Tables[0]; // make data table
                        dgCustomFindResults.DataSource =  dsMLS.Tables["MLS"].DefaultView;
                        dgCustomFindResults.DataBind();
                        // cache results
                      Cache[SessionId + ":MLSDATA"] = dsMLS;

                        cmdGetSelections.Visible = true;
                        cmdPrintRatioStudy.Visible = true;
                        lblResultsCount.Text = "Number of records returned =  " + dsMLS.Tables["MLS"].Rows.Count.ToString();

      }

            //string myList = "'02843010110000,04210107260000,02472811250000,aaa'";

            // this is the sales report button method
            private void RatioStudyEngage(object sender, System.EventArgs e)
            {
                  if (chkSendToPaperWise.Checked == true)
                  {
                        Session["SendToPaperWise"] = "SendYes";
                  }
                  else
                  {
                        Session["SendToPaperWise"] = "SendNo";
                  }

                  string strCadNumbersList = "aaa,"; // the cadid list used for stored proc
                  string lastSelectedCADID = ""; // used to build the name of the CR Report for PaperWise

                  foreach(DataGridItem dgi in dgCustomFindResults.Items)
                  {
                        CheckBox chkSelected = (CheckBox)dgi.FindControl("myCheckbox");

                        if(chkSelected.Checked == true)
                        {
                              strCadNumbersList += dgi.Cells[1].Text.ToString() + ",";
                              lastSelectedCADID = dgi.Cells[1].Text.ToString();
                        }
                  }
                  strCadNumbersList += "aaa";
                  strCadNumbersList = "'" + strCadNumbersList + "'";
                  Session["strCadNumbersList"] = strCadNumbersList.ToString();
                  Session["lastSelectedCADID"] = lastSelectedCADID.ToString();
                  //lblResultsInfo.Text = strCadNumbersList.ToString();
                  //lblResultsInfo.Text = lastSelectedCADID.ToString();


                  // ****************************************************
                  //  open popup window and display Neighborhood report
                  // ****************************************************
                  StringBuilder sb = new StringBuilder();
                  sb.Append("<script>");
                  sb.Append("window.open('dsp_SalesReport.aspx', '', '');");
                  sb.Append("</script>");
                  Page.RegisterStartupScript("SalesReportPage", sb.ToString());

            }


          // this is the acutal ratio study button method.
            private void BuildRatioStudyEngage(object sender, System.EventArgs e)
            {
                  if (chkSendToPaperWise.Checked == true)
                  {
                        Session["SendToPaperWise"] = "SendYes";
                  }
                  else
                  {
                        Session["SendToPaperWise"] = "SendNo";
                  }

                  string strCadNumbersList = "aaa,"; // the cadid list used for stored proc
                  string lastSelectedCADID = ""; // used to build the name of the CR Report for PaperWise

                  foreach(DataGridItem dgi in dgCustomFindResults.Items)
                  {
                        CheckBox chkSelected = (CheckBox)dgi.FindControl("myCheckbox");

                        if(chkSelected.Checked == true)
                        {
                              strCadNumbersList += dgi.Cells[1].Text.ToString() + ",";
                              lastSelectedCADID = dgi.Cells[1].Text.ToString();
                        }
                  }
                  strCadNumbersList += "aaa";
                  strCadNumbersList = "'" + strCadNumbersList + "'";
                  Session["strCadNumbersList"] = strCadNumbersList.ToString();
                  Session["lastSelectedCADID"] = lastSelectedCADID.ToString();
                  //lblResultsInfo.Text = strCadNumbersList.ToString();
                  //lblResultsInfo.Text = lastSelectedCADID.ToString();

                  // ****************************************************
                  //  open popup window and display Neighborhood report
                  // ****************************************************
                  StringBuilder sb = new StringBuilder();
                  sb.Append("<script>");
                  //sb.Append("window.open('dspSelectedProperties.aspx', '', '');");
                  //sb.Append("window.open('dsp_Test1.aspx', '', '');");
                  sb.Append("window.open('dspSelectedProperties.aspx', '', '');");
                  sb.Append("</script>");
                  Page.RegisterStartupScript("SelectedPropertiesPage", sb.ToString());

            }

            private void chkSendToPaperWise_CheckedChanged(object sender, System.EventArgs e)
            {
            
            }

            private void txtGroup_TextChanged(object sender, System.EventArgs e)
            {
            
            }

            private void DropDownList1_SelectedIndexChanged(object sender, System.EventArgs e)
            {
            
            }

            public void MLS_Sort(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
            {
                  //
            }

            public void BindGrid(String sortfield)
            {
                  //
            }




      }
}


///////////////////////////////////////////////////////////////////////


0
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 500 total points
ID: 17045394
What I would do here to help make things clearer is make your function that gets the data return a dataset ...

Then I can have code that is simple and clear such as ..

  public DataSet CustomFindEngage() {}

DataSet GetData() {
    DataSet MLSData = Cache[SessionID + ":MLSdata"] as DataSet;
    if(MLSData == null) {
          MLSData = CustomFindEngage();
    }
    return MLSData;
}

the last line of CustomFindEngagae before returning would probably be Cache[SessionID + ":MLSdata"] = MLSData (setting the data in the cache).

The you just call this whenever you need the dataset (i.e. to bind it to the grid). The nice thing about this is that it handles the case where you have lost you cache between postbacks (it will automatically reload your data for you if your data is not cached). It also seperates the convern of getting the data from the con cern of binding the data etc (making your code a bit easier to read).

When you wanted to change the data in this case you would simply re-read the data and save it in the cache by calling your load function directly as opposed to calling this function.

As for you questions about !IsPostBack, you are correct in that you will be things slightly differently. You be executing such code on your button postback as opposed to in the formload like I was doing. Basically your button postback would be loading the data and setting a flag etc saying the data should be displayed.

Cheers,

Greg
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now