c# - Most efficient way to populate DropDownLists in a GridView? -

i've got code, , works. takes forever. ok, maybe 40 seconds, that's forever in online application.

protected void datagrid_open_rowdatabound(object sender, gridviewroweventargs e) {     oracleconnection conn = getconnection();     try     {         if (e.row.rowtype == datacontrolrowtype.datarow)         {             datarowview drv = e.row.dataitem datarowview;              //**********************************************             //*************  auditor names  ****************             //**********************************************              oraclecommand cmd4 = new oraclecommand();             cmd4.commandtype = commandtype.storedprocedure;             cmd4.commandtext = "cstapp_o.cst_amr_auditornames";             cmd4.connection = conn;              cmd4.parameters.add("cursor_", oracletype.cursor).direction = parameterdirection.output;              var searchadapter4 = new oracledataadapter(cmd4);             var ds4 = new datatable();             searchadapter4.fill(ds4);              dataview view = new dataview(ds4);             datatable distinctvalues = new datatable();             distinctvalues = view.totable(true, "auditor_name", "auditorid");              dropdownlist ddl = e.row.findcontrol("ei_open_auditorname") dropdownlist;              ddl.datasource = distinctvalues;             ddl.datatextfield = "auditor_name";             ddl.datavaluefield = "auditorid";             ddl.databind();             ddl.selectedvalue = drv["auditor_name"].tostring();              //**********************************************             //*****************  status  *******************             //**********************************************              oraclecommand cmd5 = new oraclecommand();             cmd5.commandtype = commandtype.storedprocedure;             cmd5.commandtext = "cstapp_o.cst_amr_auditstatus";             cmd5.connection = conn;              cmd5.parameters.add("cursor_", oracletype.cursor).direction = parameterdirection.output;              var searchadapter5 = new oracledataadapter(cmd5);             var ds5 = new datatable();             searchadapter5.fill(ds5);              dataview view5 = new dataview(ds5);             datatable distinctvalues5 = new datatable();             distinctvalues5 = view5.totable(true, "statusname", "statusid");              //                datarowview drv = e.row.dataitem datarowview;             dropdownlist ddl5 = e.row.findcontrol("ei_open_auditstatus") dropdownlist;              ddl5.datasource = distinctvalues5;             ddl5.datatextfield = "statusname";             ddl5.datavaluefield = "statusid";             ddl5.databind();             ddl5.selectedvalue = drv["overall_audit_status"].tostring();          }     }     catch (exception ex)     {      } } 

is there more efficient way this? can't believe re-running stored procedure every record in gridview best way fill dropdownlist, , i've got 2 dropdownlists fill.

you can use datatable temporarily store data , method data datatable , reuse don't hit database on every databound item.

datatable dt;  protected void datagrid_open_rowdatabound(object sender, gridviewroweventargs e) {     //check if current row datarow     if (e.row.rowtype == datacontrolrowtype.datarow)     {         //check if datatable exists, if not loaddatafromdb method         if (dt == null)         {             dt = loaddatafromdb();         }          dropdownlist ddl = e.row.findcontrol("ei_open_auditorname") dropdownlist;          ddl.datasource = dt;         ddl.datatextfield = "auditor_name";         ddl.datavaluefield = "auditorid";         ddl.databind();     } }  //method loading data datatable , return such public datatable loaddatafromdb() {     string query = "cstapp_o.cst_amr_auditornames";     datatable dt = new datatable();      using (sqlconnection connection = new sqlconnection(connectionstring))     using (sqldataadapter adapter = new sqldataadapter(query, connection))     {         try         {             adapter.fill(dt);         }         catch         {         }     }      return dt; }