title: how to pass dynamic parameters over webservice to sql query
body: <p>i build an app where i select a country a city and a street. after the selection it sends the id's to the webservice and i get all the room that are available for the selected place. but what if i want to select more than one city or more than one street? i am searching for a dynamic solution with no luck.</p>
  
  <p>here is my roomdownloader in iOS:</p>
  
  <pre><code>    -(void)raumDownloader
  {
      NSMutableArray *object=[[NSMutableArray alloc]init];
  
      NSLog(@"RaumklasseID: %@    StadtID: %@     GebäudeID: %@        RegionID: %@",raumklasseid, stadtid, gebaudeid, regionid);    
  
      NSString *emptyString = @"";
      [object addObject:raumklasseid];
  
      if (stadtid==nil)
      { 
          [object addObject:emptyString];
      }
      else {
           [object addObject:stadtid];
      }
  
  
      if (gebaudeid==nil)
      {
          [object addObject:emptyString];
      }else {
          [object addObject:gebaudeid];
      }
  
      if (regionid==nil)
      {
          [object addObject:emptyString];
      }else {
          [object addObject:regionid];  
  
      }
  
  
       NSMutableArray *key =[[NSMutableArray alloc]init];
      [key addObject:@"RAUMKLASSE_ID"];
      [key addObject:@"STADT_ID"];
      [key addObject:@"GEBAEUDE_ID"];
      [key addObject:@"REGION_ID"];
  
      NSDictionary* info = [[NSDictionary alloc]initWithObjects:object forKeys:key];
      NSData *jsonData = [NSJSONSerialization dataWithJSONObject:info 
                                                         options:NSJSONWritingPrettyPrinted error:nil];
  
      NSString *name = [[NSString alloc] initWithData:jsonData                                        
                                             encoding:NSUTF8StringEncoding];
  
      NSMutableURLRequest *request = [[NSMutableURLRequest alloc] initWithURL:[NSURL URLWithString:@"http://nexxtsolutions-entwicklung.de/Webdienst22/service1.asmx/Raum"]];
  
      [request setHTTPMethod: @"POST"];
      [request setValue:@"application/json" forHTTPHeaderField:@"Content-Type"];
  
      NSData *reqData = [NSData dataWithBytes:[name UTF8String] length:[name length]];
      [request setHTTPBody:reqData];
  
      NSURLResponse *response =[[NSURLResponse alloc]init];
      NSData *data = [NSURLConnection sendSynchronousRequest:request returningResponse:&amp;response error:nil];
  
      NSMutableDictionary *dict = [[NSMutableDictionary alloc]init ];
      dict = [NSJSONSerialization JSONObjectWithData:data options:kNilOptions error:nil];
  
      raumeArray =  [dict objectForKey:@"d"];
      for(int n=0; n&lt;[raumeArray count]; n++)
      {
  
  
          NSLog(@"AUSGABE: %@",[[raumeArray objectAtIndex:n] objectForKey:@"RaumName"]);
  
  
  
      }
  
  
  }
  </code></pre>
  
  <p>you can see that i have 4 static parameter which i send to the webservice:</p>
  
  <pre><code>[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
      [WebMethod]
      public List&lt;RaumHelper.RAUM&gt; Raum(string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID)
      {
          return RaumHelper.Raum(RAUMKLASSE_ID, STADT_ID, GEBAEUDE_ID, REGION_ID);
      }
  </code></pre>
  
  <p>and the helper class uses the static parameters in the sql query and send the rooms back to the iPhone:</p>
  
  <pre><code>internal static List&lt;RAUM&gt; Raum( string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID)
      {
          List&lt;RAUM&gt; strasseObject = new List&lt;RAUM&gt;();
  
  
          using (SqlConnection con = new SqlConnection(@"Data Source=Localhost\SQLEXPRESS;Initial Catalog=BOOK-IT-V2;Integrated Security=true;"))
          using (SqlCommand cmd = new SqlCommand(@"SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID, ra.BEZEICHNUNG AS raumBEZEICHNUNG FROM RAUM r, RAUMATTRIBUTE ra WHERE RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID) AND STADT_ID = ISNULL(@Stadt_ID, STADT_ID) AND GEBAEUDE_ID = ISNULL(@Gebaeude_ID, GEBAEUDE_ID) AND REGION_ID = ISNULL(@Region_ID, REGION_ID) INNER JOIN RAZUORDNUNG ON RAZUORDNUNG.RAUM_ID = RAUM.ID
  INNER JOIN RAUMATTRIBUTE ON RAZUORDNUNG.RAUMATTRIBUTE_ID = RAUMATTRIBUTE.", con))
          {
              con.Open();
              if (!StringExtensions.IsNullOrWhiteSpace(RAUMKLASSE_ID))
                  cmd.Parameters.AddWithValue("@Raumklasse_ID", RAUMKLASSE_ID);
              else
                  cmd.Parameters.AddWithValue("@Raumklasse_ID", DBNull.Value);
  
              if (!StringExtensions.IsNullOrWhiteSpace(STADT_ID))
                  cmd.Parameters.AddWithValue("@Stadt_ID", STADT_ID);
              else
                  cmd.Parameters.AddWithValue("@Stadt_ID", DBNull.Value);
  
              if (!StringExtensions.IsNullOrWhiteSpace(GEBAEUDE_ID))
                  cmd.Parameters.AddWithValue("@Gebaeude_ID", GEBAEUDE_ID);
              else
                  cmd.Parameters.AddWithValue("@Gebaeude_ID", DBNull.Value);
  
              if (!StringExtensions.IsNullOrWhiteSpace(REGION_ID))
                  cmd.Parameters.AddWithValue("@Region_ID", REGION_ID);
              else
                 cmd.Parameters.AddWithValue("@Region_ID", DBNull.Value);
  
  
  
              using (SqlDataReader rdr = cmd.ExecuteReader())
              {
  
  
  
                  while (rdr.Read())
                  {
  
  
  
                      if (rdr["BEZEICHNUNG"] != DBNull.Value &amp;&amp; rdr["ID"] != DBNull.Value)
                      {
  
                          strasseObject.Add(new RAUM()
                          {
                              RaumName = rdr["BEZEICHNUNG"].ToString(),
                              RaumID = rdr["ID"].ToString()
  
                          });
                      }
  
                  }
              }
          }
          return strasseObject;
      }
  </code></pre>
  
  <p>my question again is what if i select more than one city or street ?  the id's should be send dynamically and the sql query too. 
  thanks in advance for your help :) </p>