how to pass dynamic parameters over webservice to sql query

Question

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.

here is my roomdownloader in iOS:

    -(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:&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<[raumeArray count]; n++)
    {


        NSLog(@"AUSGABE: %@",[[raumeArray objectAtIndex:n] objectForKey:@"RaumName"]);



    }


}

you can see that i have 4 static parameter which i send to the webservice:

[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    [WebMethod]
    public List<RaumHelper.RAUM> Raum(string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID)
    {
        return RaumHelper.Raum(RAUMKLASSE_ID, STADT_ID, GEBAEUDE_ID, REGION_ID);
    }

and the helper class uses the static parameters in the sql query and send the rooms back to the iPhone:

internal static List<RAUM> Raum( string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID)
    {
        List<RAUM> strasseObject = new List<RAUM>();


        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 && rdr["ID"] != DBNull.Value)
                    {

                        strasseObject.Add(new RAUM()
                        {
                            RaumName = rdr["BEZEICHNUNG"].ToString(),
                            RaumID = rdr["ID"].ToString()

                        });
                    }

                }
            }
        }
        return strasseObject;
    }

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 :)

Answer 1

I'm not going to read all that code because the solution is simple. Rather than taking strictly a single string for each query param you accept a comma separated list. When the request comes in you break those into arrays and query the database for all combinations of parameters.

Make the client side code flexible as well so it can accept a list of things rather than just one.

Perhaps limit the total amount of db queries so someone doesn't send you three lists with five items each which would require 125 queries and would be way too much data to return to the phone all at once.