如何将多个列表框项目传递到一个存储过程参数IN Clause？ [英] How do I pass multiple listbox items into one stored procedure parameter IN Clause?

问题描述

我有一个列表框，例如所选项目是GREEN，RED，BLUE，我想将这3个选定的项目传递给存储过程，以便在where条件IN子句中使用，然后将使用数据表填充我的网格视图。



对于单个选定的项目，它工作正常，但是对于多个选择它不会显示任何记录。非常感谢您的帮助。在此先感谢:)



ASPX.CS

I have a list box for example the selected items are GREEN, RED, BLUE and I would like to pass this 3 selected items to the stored procedure to be used in the where condition IN clause which the data table will then be used to populate my grid view.

For a single selected item, it is working fine, however for multiple selection it will not display any record. Your help in kindly appreciated. Thanks in advance:)

ASPX.CS

public void Bind()
       {
               using (SqlConnection conn = new SqlConnection(dbConn))
           {
               using (SqlCommand cmd = new SqlCommand(spretrieve, conn))
               {

                   string selectedValue = "";

                   foreach (ListItem item in ListBox1.Items)
                   {
                       if (item.Selected)
                       {
                           selectedValue += item.Text + ',';
                       }
                   }
                   selectedValue = selectedValue.Substring(0, selectedValue.Length - 1);

                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.Parameters.Add("@param", SqlDbType.VarChar).Value = selectedValue;


                   conn.Open();
                   SqlDataAdapter da = new SqlDataAdapter(cmd);
                   DataSet ds= new DataSet();
                   da.Fill(ds);
                   GRIDVIEW.DataSource = ds.Tables[0];
                   GRIDVIEW.DataBind();

               }
           }

存储程序

STORED PROCEDURE

 ALTER PROCEDURE [dbo].[SP]
-- Add the parameters for the stored procedure here

@param varchar(512)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;SELECT * FROM TABLENAME WHERE COLUMN IN (@param);
END

推荐答案

继续解决方案1，使用在您的存储过程中，您需要更改代码（在SP中）以使用函数的输出...

Further to solution 1, to use that in your Stored procedure you will need to change the code (in the SP) to use the output from the function ...

ALTER PROCEDURE [dbo].[SP]

@param varchar(512)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SELECT * FROM TABLENAME WHERE [COLUMN] IN 
           (SELECT splitdata FROM dbo.fnSplitString(@param, ','))
END

（我使用的源自 fnSplitString 函数sql-server / 2013/09/9 / how-to-split-a-string-by-delimited-char-in-sql-server /> sqlservercentral [ ^ ]）

确保逗号sep中没有空格您传入的评估列表，例如

(I am using my fnSplitString function sourced from sqlservercentral[^])
Ensure that there are no spaces in the comma separated list that you pass in e.g.

EXEC SP 'Indigo,Blue'

将列出'Indigo'和'Blue'，但

will list both 'Indigo' and 'Blue' but

EXEC SP 'Indigo, Blue'

只会列出'Indigo'。在C＃代码中生成该参数的一种更简洁的方法是使用System.Linq 和

will only list 'Indigo'. A neater way of producing that parameter in your C# code is to include using System.Linq and

List<string> sItems = listBox1.SelectedItems.Cast<string>().ToList();
string parm = string.Join(",", sItems.ToArray());

另一种方法是将单个参数作为正确格式的IN子句传递，例如'Blue'，'Violet'（注意单引号应包含在字符串中）。例如。在sql中称为

An alternative approach would be to pass in the single parameter as a properly formatted IN clause e.g. 'Blue','Violet' (Note the single quotes are intended to be included in the string). E.g. called in sql as

exec SP '''Indigo'',''Blue'''

SP需要动态生成SQL，例如

The SP would need to dynamically generate the SQL e.g.

ALTER PROCEDURE [dbo].[SP]

@param varchar(512)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max)
    SET @sql = 'SELECT * FROM TABLENAME WHERE [COLUMN] IN (' + @param + ')'
    EXEC sp_executesql @sql;
END

如果你想在C＃中快速创建该参数，你可以这样做这个

If you wanted to quickly create that parameter in your C# you can do it like this

List<string> sItems = listBox1.SelectedItems.Cast<string>().ToList();
parm = string.Format("'{0}'", 
     string.Join("','", sItems.Select(i => i.Replace("'", "''")).ToArray()));

您可以将选定的值作为逗号分隔（'，'）传递并创建一个函数来获取它回到sql端

You can pass the selected values as comma seperated (',') and create a function to get it back on the sql side

input -> apples,oranges,bananas
output -> |Value|
          apples
          oranges
          bananas

ALTER FUNCTION [dbo].[fnSimpleSplit] 
( @InputString NVARCHAR(MAX)
, @Delimiter   NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
  WITH cte AS
  (
    SELECT CAST(NULL AS NVARCHAR(MAX)) val , @InputString + @Delimiter s , CHARINDEX(@Delimiter,@InputString) offset
  UNION ALL
    SELECT SUBSTRING(s,1,offset-1) , SUBSTRING(s,offset+1,LEN(s)) , CHARINDEX(@Delimiter,s,offset+1)-offset
    FROM cte
    WHERE offset>0
  )
  SELECT val
  FROM cte
  WHERE val IS NOT NULL
)

这篇关于如何将多个列表框项目传递到一个存储过程参数IN Clause？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！