What is the recommended way to work with multiple (nested) SQL Server queries using C#? -

i have scenario query sql server db, obtain results, , based on results, make subsequent queries db again. following how i've structured code same:

what i'm interested in knowing is, correct way deal such scenarios?

should doing else alternatively? like, make first call db, load results in dictionary, make next calls , use result stored in dictionary make these next calls

(if feel need context on code - want add uniqueness constraint , index on columns cola, colb, , colc on mytable, can't directly apply uniqueness constraint. there existing violations on these columns. first resolve these violations changing value of colc entries cause violation, , after fixing violations, add constraint)

void main() {      using(sqlconnection connection = new sqlconnection(@"data source=localhost; initial catalog=mydatabase; integrated security=true; multipleactiveresultsets=true"))      {         connection.open();          //check if index exists on columns cola_colb_colc without uniqueness constraint         sqlcommand mycommand = new sqlcommand(@"select 1 sys.indexes                                  name = 'uq_cola_colb_colc'                                  , object_id = object_id('mytable')                                 , is_unique = 0");         sqldatareader myreader = mycommand.executereader();         if(myreader.hasrows)         {             try {                  //get unique values exist (cola,colb,colc) tuple                 mycommand = new sqlcommand(@"select count(*) count,                                                         cola,colb,colc                                                          [apimanagement.local].[dbo].[mytable]                                                          group cola,colb,colc ", connection);                  sqldatareader myreader = mycommand.executereader();                 while (myreader.read()) {                      //for each of unique values, rows have value                     sqlcommand mycommand2 = new sqlcommand(@"select id,cola,colb,colc mytable                     cola=@cola , colb=@colb , colc=@colc", connection);                     mycommand2.parameters.addwithvalue("@cola", myreader["cola"].tostring());                     mycommand2.parameters.addwithvalue("@colb", myreader["colb"].tostring());                     mycommand2.parameters.addwithvalue("@colc", myreader["colc"].tostring());                      int index = 2;                     sqldatareader myreader2 = mycommand2.executereader();                     myreader2.read(); //read first row off results                      //if more rows exist, have violations uniqueness constraint on (cola,colb,colc)                     //fix these violations appending indices colc value                     while (myreader2.read()) {                         sqlcommand mycommand3 = new sqlcommand(@"update mytable                                                                  set colc=@newcolc                                                                 id=@id", connection);                          mycommand3.parameters.addwithvalue("@id", myreader2["id"].tostring());                         mycommand3.parameters.addwithvalue("@newcolc", myreader2["colc"].tostring()+index);                          bool changedsuccessfully = false;                         while(!changedsuccessfully)                         {                             try                             {                                 mycommand3.executenonquery();                                 index++;                                 break;                             }                             catch(sqlexception e)                             {                                 if((uint)e.hresult == 0x80131904)                                 {                                     index++;                                 }                                 else                                 {                                     throw e;                                 }                              }                         }                     }                 }                  //after violations fixed, create index on (cola,colb,colc) uniqueness constraint                 mycommand = new sqlcommand(@"drop index uq_cola_colb_colc on [mytable];                 create unique nonclustered index [uq_cola_colb_colc] on [mytable]([cola] asc, [colc] asc, [colb] asc) [colb] != 3");                  mycommand.executenonquery();              } catch (exception e) {                 console.writeline(e.tostring());             }         }      } } 

well - i'd sqldatareader handling wrong.
wrap them in usingto avoid connection leaks:

using (sqldatareader myreader = mycommand.executereader()) { //do stuff myreader here. } //using clause ensure dispose  //do remainder stuff outside here. 

also have connection open short time possible. connection pooling limit of overhead opening/closing connections while leaving free not worry about. keeping connections open long can hinder performance.

outside tips - "recommended" way structure extremely subjective , logic dependant.
- comes down how data needs moved around between server , application. letting database work optimized , letting code work .net optimized - meaning how of can/should kept in database , how data should moved code layer.

a lot of comes experience , trying out , performance tuning see best.

edit: saw comment 1 time thing you'll not run again. wouldn't worry @ , easiest way , move on.
because it's cost effective fiddle , time better spend on actual problems :)