I have a search page that is tasked with searching 3.5 million records for individuals based on their name, customer ID, address, etc. The queries range from complex to simple.
Currently, this code relies on a SqlDataSource and a GridView. When a user types a serach term in and presses enter, the TextBoxChanged even runs a Search(term, type) function that changes the query that the SqlDataSource uses, adds the parameters, and rebinds the GridView.
It works well, but I've become obsessed with rewriting the code more efficiently. I want the paging to be done by SQL Server instead of the inefficiencies of a SqlDataSource in DataSet mode.
Enter the ObjectDataSource. Caveat: I have never used one before today.
I have spent the better part of the day putting together this class:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// Summary description for MultiSearchData
/// </summary>
public class MultiSearchData
{
private string _connectionString = string.Empty;
private string _sortColumns = string.Empty;
private string _selectQuery = string.Empty;
private int _lastUpdate;
private int _lastRowCountUpdate;
private int _lastRowCount;
private SqlParameterCollection _sqlParams;
public MultiSearchData()
{
}
private void UpdateDate()
{
_lastUpdate = (int)(DateTime.UtcNow - new DateTime(1970, 1, 1)).TotalSeconds;
}
private string ReplaceFirst(string text, string search, string replace)
{
int pos = text.IndexOf(search);
if (pos < 0)
{
return text;
}
return text.Substring(0, pos) + replace + text.Substring(pos + search.Length);
}
public string SortColumns
{
get { return _sortColumns; }
set { _sortColumns = value; }
}
public SqlParameterCollection SqlParams
{
get { return _sqlParams; }
set { _sqlParams = value; }
}
public string ConnectionString
{
get { return _connectionString; }
set { _connectionString = value; }
}
public string SelectQuery
{
get { return _selectQuery; }
set
{
if (value != _selectQuery)
{
_selectQuery = value;
UpdateDate();
}
}
}
public DataTable GetFullDataTable()
{
return GetDataTable(AssembleSelectSql());
}
public DataTable GetPagedDataTable(int startRow, int pageSize, string sortColumns)
{
if (sortColumns.Length > 0)
_sortColumns = sortColumns;
return GetDataTable(AssemblePagedSelectSql(startRow, pageSize));
}
public int GetRowCount()
{
if (_lastRowCountUpdate == _lastUpdate)
{
return _lastRowCount;
}
else
{
string strCountQuery = _selectQuery.Remove(7, _selectQuery.IndexOf("FROM") - 7);
strCountQuery = strCountQuery.Replace("SELECT FROM", "SELECT COUNT(*) FROM");
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(strCountQuery, conn))
{
if (_sqlParams.Count > 0)
{
foreach (SqlParameter param in _sqlParams)
{
cmd.Parameters.Add(param);
}
}
_lastRowCountUpdate = _lastUpdate;
_lastRowCount = (int)cmd.ExecuteScalar();
return _lastRowCount;
}
}
}
}
public DataTable GetDataTable(string sql)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
using (SqlCommand GetCommand = new SqlCommand(sql, conn))
{
conn.Open();
if (_sqlParams.Count > 0)
{
foreach (SqlParameter param in _sqlParams)
{
GetCommand.Parameters.Add(param);
}
}
using (SqlDataReader dr = GetCommand.ExecuteReader())
{
dt.Load(dr);
conn.Close();
return dt;
}
}
}
}
private string AssembleSelectSql()
{
StringBuilder sql = new StringBuilder();
sql.Append(_selectQuery);
return sql.ToString();
}
private string AssemblePagedSelectSql(int startRow, int pageSize)
{
StringBuilder sql = new StringBuilder();
string originalQuery = ReplaceFirst(_selectQuery, "FROM", ", ROW_NUMBER() OVER (ORDER BY " + _sortColumns + ") AS ResultSetRowNumber FROM");
sql.Append("SELECT * FROM (");
sql.Append(originalQuery);
sql.Append(") AS PagedResults");
sql.AppendFormat(" WHERE ResultSetRowNumber > {0} AND ResultSetRowNumber <= {1}", startRow.ToString(), (startRow + pageSize).ToString());
return sql.ToString();
}
}
I don't know if it's pretty. It works. I give it a query in the ObjectCreating method:
protected void dataMultiSearchData_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
{
MultiSearchData info;
info = Cache["MultiSearchDataObject"] as MultiSearchData;
if (null == info)
{
info = new MultiSearchData();
}
info.SortColumns = "filteredcontact.fullname";
info.ConnectionString = "Data Source=SERVER;Initial Catalog=TheDatabase;Integrated Security=sspi;Connection Timeout=60";
info.SelectQuery = @"SELECT filteredcontact.contactid,
filteredcontact.new_libertyid,
filteredcontact.fullname,
'' AS line1,
filteredcontact.emailaddress1,
filteredcontact.telephone1,
filteredcontact.birthdateutc AS birthdate,
filteredcontact.gendercodename
FROM filteredcontact
WHERE fullname LIKE 'Griffin%' AND filteredcontact.statecode = 0";
e.ObjectInstance = info;
}
protected void dataMultiSearchData_ObjectDisposing(object sender, ObjectDataSourceDisposingEventArgs e)
{
MultiSearchData info = e.ObjectInstance as MultiSearchData;
MultiSearchData temp = Cache["MultiSearchDataObject"] as MultiSearchData;
if (null == temp)
{
Cache.Insert("MultiSearchDataObject", info);
}
e.Cancel = true;
}
Once the class has the query, it wraps it in paging friendly SQL and we're off to the races. I've implemented caching so that it can skip some expensive queries. Etc.
My problem is, this completely breaks my pretty little Search(term, type) world. Having ot set the query in the ObjectCreating method is completely harshing my vibe.
I've been trying to think of a better way to do this all day, but I keep ending up with a really messy...do it all in ObjectCreating model that just turns my stomach.
How would you do this? How can I keep the efficiency of this new method whilst have the organizational simplicity of my former model?
Am I being too OCD?