Making a Webservice (asp.NET) which accepts several Paramters seperated by comma

Question

I have the following Webservice and SQL-Query:

  public class DIENSTLEISTUNG
        {
            public string DienstleistungName { get; set; }
            public string DienstleistungArtBezeichnung { get; set; }
        }

        internal static List<DIENSTLEISTUNG> Dienstleistung(string RAUM_ID)
        {
            List<DIENSTLEISTUNG> BestuhlungObject = new List<DIENSTLEISTUNG>();

using (SqlConnection con = new SqlConnection(@"Data Source=Localhost\SQLEXPRESS;Initial Catalog=BOOK-IT-V2;Integrated Security=true;"))
            using (SqlCommand cmd = new SqlCommand(@"
                                       SELECT
                                       DISTINCT dl.NAME AS NAME, da.BEZEICHNUNG AS BEZEICHNUNG
                                       FROM RAUM r
                                       RIGHT JOIN DIENSTLEISTUNG_Raum bs ON bs.RAUM_ID = (" + Room_ID + ") 
                                       RIGHT JOIN DIENSTLEISTUNG ba ON bs.DIENSTLEISTUNG_ID = ba.ID 
                                       RIGHT JOIN DIENSTLEISTUNGSART da ON ba.DIENSTLEISTUNGSART_ID = da.ID
                                       RIGHT JOIN DIENSTLEISTER dl ON da.DIENSTLEISTER_ID = dl.ID", con))

            {
                con.Open();
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                  while (rdr.Read())
                    {
                        if (rdr["NAME"] != DBNull.Value && rdr["BEZEICHNUNG"] != DBNull.Value)
                        {

                            BestuhlungObject.Add(new DIENSTLEISTUNG()
                               {                                                                                                                                      
                                DienstleistungName = rdr["NAME"].ToString(),
                                DienstleistungArtBezeichnung = rdr["BEZEICHNUNG"].ToString()
                             });

                        }

                    }
                }
            }
            return BestuhlungObject;
        }

    }
}

This Webservice is working like charm. I can send one Room_ID = 219 and get the expected output. My Question is, how can i send more then one Room_ID seperated by comma. For Example: Room_ID = 219, 220, 221

Can someone please help me with that?

Thanks in advance

You have to send Room_ID as '219','220','221' and change this bs.RAUM_ID = (" + Room_ID + ") as bs.RAUM_ID IN (" + Room_ID + ") — Suave Nti, Jun 29 '12 at 18:06
SQL injection risk. If Room_ID was crafted correctly, you could be in trouble. Never concatenate strings to form SQL statements. Use parameters instead. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx — spender, Jun 29 '12 at 18:08
@ user1043021. Thats not working. Iam using the Browser to invoke the Webmethod and i send the Room-IDs like 219, 220, 221 — Paks, Jun 29 '12 at 18:14
@spender : Oops true , Paks Is the RAUM_ID an Integer type in your DB or varchar ? — Suave Nti, Jun 29 '12 at 18:16
Firstly as said by spender never conncatenate strings to your SQL statements , Use command.Parameters.Add .. secondly to work with your query you need to pass room ids as '219,'220','221'.. and use IN clause instead of '='.. — Suave Nti, Jun 29 '12 at 18:33
i used the IN clause but it doesn't work. Is there another solution to how i can customize the webservice? — Paks, Jun 29 '12 at 19:07

score 1 · Accepted Answer · edited May 23 '17 at 12:12

See T-SQL stored procedure that accepts multiple Id values

One of many ways (MS SQL 2005+), if you can pass parameters as xml:

var sql =
    @"SET ARITHABORT ON;
    SELECT ...  
    RIGHT JOIN DIENSTLEISTUNG_Raum bs ON bs.RAUM_ID IN
    (
        SELECT tbl.col.value('.', 'varchar(20)')
        FROM @xml.nodes('id') as tbl(col)
    ) ...";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.Add("@xml", SqlDbType.Xml).Value = "<id>111</id><id>222</id><id>333</id>";

Or using LIKE if you can pass parameters as ,aaa,bbb,ccc, (note the order of LIKE arguments to avoid an injection)

var sql =
    @"SELECT ...    
    RIGHT JOIN DIENSTLEISTUNG_Raum bs ON @ids LIKE '%,' + bs.RAUM_ID + ',%'
    ...";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.Add("@ids", SqlDbType.VarChar).Value = ",111,222,333,";

Making a Webservice (asp.NET) which accepts several Paramters seperated by comma

1 Answers1