我如何可以设置SQL查询参数可选? [英] how can i set the parameters for the sql query optional?

查看:408
本文介绍了我如何可以设置SQL查询参数可选?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我建立在ASP.Net Web服务,它发送我的房间列表。

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

该参数是用逗号分隔的ID。

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

我把它们保存到一个字符串,并建立一个SQL SELECT查询。

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

当我把所有4个参数我的一切工作正常,我也得到一个结果。但是,当我送不到4我得到一个错误。

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 ')'.

如何设置我的可选在SQL查询中只选择我输入的数值参数?

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

下面是我的code迄今:

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.

推荐答案

想象一下参数 REGION_ID 是一个空字符串。您的查询的那部分将是这样的:

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

...AND REGION_ID IN ()...

由于在键,REGION_ID IN(+ regionid +) regionid 变量将与更换空字符串。这不是有效的SQL语法,所以你会得到例外。

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

然后,修改code建立这样的查询:

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

这篇关于我如何可以设置SQL查询参数可选?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆