how can i set the parameters for the sql query opt

问题:

I build a Web Service in ASP.Net which sends me a list of rooms.

The parameters are id's which are separated by a comma.

I saved them to a string and build a sql select query.

When I send all 4 parameters I everything works fine and I get a result. But when I send less then 4 I get an error.

System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

How can I set my the parameters optional in the sql query to select just the values I entered?

Here is my code so far:

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

    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 FROM RAUM r WHERE RAUMKLASSE_ID IN (" + raumklasseid + ") AND STADT_ID IN (" + stadtid + ") AND GEBAEUDE_ID IN (" + gebaudeid + ") AND REGION_ID IN (" + regionid + ")", con))
    {
        con.Open();

        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;
}

Thanks in advance for your help.

回答1:

Imagine the parameter REGION_ID is an empty string. That part of your query will be something like:

...AND REGION_ID IN ()...

Because in AND REGION_ID IN (" + regionid + ")" the regionid variable will be replaced with an empty string. This is not valid SQL syntax so you'll get that exception.

Declare a function like this:

private static void AppendConstrain(StringBuilder query, string name, string value)
{
    if (String.IsNullOrWhiteSpace(value))
        return;

    if (query.Length > 0)
        query.Append(" AND ");

    query.AppendFormat("{0} IN ({1})", name, value);
}

Then change your code to build the query in this way:

StringBuilder constrains = new StringBuilder();
AppendConstrain(contrains, "RAUMKLASSE_ID", RAUMKLASSE_ID);
AppendConstrain(contrains, "GEBAEUDE_ID", GEBAEUDE_ID);
AppendConstrain(contrains, "STADT_ID", STADT_ID);
AppendConstrain(contrains, "REGION_ID", REGION_ID);

StringBuilder query =
    new StringBuilder("SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r");

if (constrains.Length > 0)
{
    query.Append(" WHERE ");
    query.Append(constrains);
}

using (SqlCommand cmd = new SqlCommand(query.ToString(), con))
{
    // Your code...
}

回答2:

It always be a better approach to write the stored procedures and pass the parameters. But in your approach you should split your query because of not sure the values. So, your code be something like that..

Test it yourself, i didnt check it

string raumklasseid = RAUMKLASSE_ID;
        string gebaudeid = GEBAEUDE_ID;
        string stadtid = STADT_ID;
        string regionid = REGION_ID;
        string whereClause = string.Empty;

if (!string.IsNullorEmpty(raumklasseid))
{
   whereClause = "RAUMKLASSE_ID IN (" + raumklasseid + ")";
}
if (!string.IsNullorEmpty(stadtid ))
{
   if(string.IsNullorEmpty(whereClause)
      whereClause = "STADT_ID IN (" + stadtid + ")";
   else 
      whereClause += "AND RSTADT_ID IN (" + stadtid + ")";
}
if (!string.IsNullorEmpty(stadtid ))
{
   if(string.IsNullorEmpty(whereClause)
      whereClause = "STADT_ID IN (" + stadtid + ")";
   else 
      whereClause += "AND RSTADT_ID IN (" + stadtid + ")";
}
if (!string.IsNullorEmpty(regionid))
{
   if(string.IsNullorEmpty(whereClause)
      whereClause = "REGION_ID IN (" + regionid + ")";
   else 
      whereClause += "AND REGION_ID IN (" + regionid + ")";
}

if(!string.IsNullorEmpty(whereClause)
whereClause = "WHERE " + whereClause ;

// now your cmd should be like that

using (SqlCommand cmd = new SqlCommand(@"SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r " + whereClause , con))