c# - Using an ObjectDataSource with a GridView in a dynamic scenario -

i have search page tasked searching 3.5 million records individuals based on name, customer id, address, etc. queries range complex simple.

currently, code relies on sqldatasource , gridview. when user types serach term in , presses enter, textboxchanged runs search(term, type) function changes query sqldatasource uses, adds parameters, , rebinds gridview.

it works well, i've become obsessed rewriting code more efficiently. want paging done sql server instead of inefficiencies of sqldatasource in dataset mode.

enter objectdatasource. caveat: have never used 1 before today.

i have spent better part of day putting 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 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     {         { return _sortcolumns; }         set { _sortcolumns = value; }     }      public sqlparametercollection sqlparams     {         { return _sqlparams; }         set { _sqlparams = value; }     }      public string connectionstring     {         { return _connectionstring; }         set { _connectionstring = value; }     }      public string selectquery     {         { 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() on (order " + _sortcolumns + ") resultsetrownumber from");         sql.append("select * (");         sql.append(originalquery);         sql.append(") pagedresults");         sql.appendformat(" resultsetrownumber > {0} , resultsetrownumber <= {1}", startrow.tostring(), (startrow + pagesize).tostring());          return sql.tostring();     } } 

i don't know if it's pretty. works. give query in objectcreating method:

protected void datamultisearchdata_objectcreating(object sender, objectdatasourceeventargs e) {     multisearchdata info;     info = cache["multisearchdataobject"] 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,                               '' line1,                               filteredcontact.emailaddress1,                               filteredcontact.telephone1,                               filteredcontact.birthdateutc birthdate,                               filteredcontact.gendercodename                          filteredcontact                          fullname 'griffin%' , filteredcontact.statecode = 0";      e.objectinstance = info; } protected void datamultisearchdata_objectdisposing(object sender, objectdatasourcedisposingeventargs e) {     multisearchdata info = e.objectinstance multisearchdata;      multisearchdata temp = cache["multisearchdataobject"] multisearchdata;      if (null == temp)     {         cache.insert("multisearchdataobject", info);     }      e.cancel = true; } 

once class has query, wraps in paging friendly sql , we're off races. i've implemented caching can skip expensive queries. etc.

my problem is, breaks pretty little search(term, type) world. having ot set query in objectcreating method harshing vibe.

i've been trying think of better way day, keep ending messy...do in objectcreating model turns stomach.

how this? how can keep efficiency of new method whilst have organizational simplicity of former model?

am being ocd?

i determined can't done. furthermore, after benchmarking class found performed no better sqldatasource more difficult maintain.

thus abandoned project. hope finds code useful @ point though.