Sending multiple parameters from ASP.NET to my stored procedure in SQL server

Question

5.00/5 (1 vote)

See more:

I have a list box in Asp.Net from where the user selects one or multiple parameters and send it to a stored procedure. The selected of number of parameters depends completely on the user so I don't know how many parameters the user is going to choose from the list box. I also want to retrieve data back from the table with those parameters when I click on the Submit button and display on a gridview. The issue I am having is I can send one parameter and retrieve data back from my stored procedure but I really don't know how to send multiple parameters from the list box to my stored procedure.

What I have tried:

Below is the code for single parameter in Asp.Net

protected void Button_Click(object sender, EventArgs e)
{
    string s = "Submit";
    SqlCommand cmd = new SqlCommand(s, con);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = lbCT.SelectedItem.Value;

    con.Open();

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);

    gvDS.DataSource = ds; 
    gvDS.DataBind();

    con.Close();
}
Below is my stored procedure in SQL Server

USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [Submit]
@Name varchar(12)
as 
begin

    select *
    from Employee 
    where Name = @Name
end

Posted 28-Jun-18 10:59am

Member 13863605

Updated 4-Jul-18 6:00am

Add a Solution

Comments

Richard Deeming 29-Jun-18 10:30am

Your stored procedure doesn't accept multiple values. Are you planning to rewrite it, or are you just trying to call it multiple times?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2018-06-29T06:18:00

The best option to pass multiple values to a single stored procedure parameter is to use a table-valued parameter:
Table-Valued Parameters | Microsoft Docs[^]

SQL

CREATE TYPE dbo.StringListTable As Table
(
    Value varchar(50) NOT NULL
);
GO
CREATE PROCEDURE dbo.ListEmployees
(
    @Names dbo.StringListTable READONLY
)
As
BEGIN
    SELECT
        *
    FROM
        Employee As e
    WHERE
        Exists
        (
            SELECT 1
            FROM @Names As n
            WHERE n.Value = e.Name
        )
    ORDER BY
        Name
    ;
END

Usage:

C#

var names = new DataTable();
names.Columns.Add("Value", typeof(string));

foreach (ListItem item in lbCT.Items)
{
    if (item.Selected)
    {
        names.Rows.Add(item.Value);
    }
}

using (var connection = new SqlConnection("... YOUR CONNECTION STRING HERE ..."))
using (var command = new SqlCommand("dbo.ListEmployees", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    var pNames = command.Parameters.AddWithValue("@Names", names);
    pNames.SqlDbType = SqlDbType.Structured;
    pNames.TypeName = "dbo.StringListTable";
    
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);

    gvDS.DataSource = ds; 
    gvDS.DataBind();
}

Alternatively, for a simple list of strings, you could pass a comma-separated list of values, and use the SPLIT_STRING function[^] (SQL 2016), or a custom string splitting function[^] if you're using an older version, to split the values back out into a table variable or temp table.

There's also the XML approach[^], but the performance tends to be quite poor.

On the other hand, if you want to pass multiple parameters to a text query, you'll need to build a properly parameterized query:

C#

using (var connection = new SqlConnection("... YOUR CONNECTION STRING HERE ..."))
using (var command = new SqlCommand("", connection))
{
    int index = 0;
    var sb = new StringBuilder("SELECT * FROM Employee");
    foreach (ListItem item in lbCT.Items)
    {
        if (item.Selected)
        {
            string name = "@p" + index;
            command.Parameters.AddWithValue(name, item.Value);
            sb.Append(index == 0 ? " WHERE Name In (" : ", ");
            sb.Append(name);
            index++;
        }
    }
    
    if (index != 0)
    {
        sb.Append(")");
    }
    
    sb.Append(" ORDER BY Name;");
    cmd.CommandText = sb.ToString();
    
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);

    gvDS.DataSource = ds; 
    gvDS.DataBind();
}

Vincent Maverick Durano · Answer 2 · 2018-06-28T15:44:00

I'm not on my dev machine so I can't write a sample using stored procedures. But here's one using dynamic query:

C#

private string GenerateDynamicQuery(string baseSql, StringCollection sc){
	StringBuilder sb = new StringBuilder(string.Empty);

	foreach (string item in sc){	
		sb.AppendFormat("{0}('{1}'); ", baseSql, item);
	}
		
	return sb.ToString();
}
	
private void InsertRecords(StringCollection sc){
	
	const string sqlStatement = "INSERT INTO Employee (Name) VALUES";
        string dynamicQuery = GenerateDynamicQuery(sqlStatement,sc);
		
        using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
           using(SqlCommand cmd = new SqlCommand(dynamicQuery,connection)){
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
        }
			
	//bind GridView after
	BindGrid(sc);	

}
		
private void BindGrid(StringCollection sc){
	StringBuilder sb = new StringBuilder(string.Empty);
	foreach (string item in sc){	
	        sb.AppendFormat("'{0}',", item);
	}

	string param = sb.ToString().TrimEnd(',');
	string sqlStatement = string.Format("SELECT * FROM Employee WHERE Name IN ({0});",param);
		
    	using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
        	using(SqlCommand cmd = new SqlCommand(sqlStatement,connection)){

                DataTable dt = new DataTable();
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(dt);

                if (dt.Rows.Count > 0) { //check if the query returns any data
                       GridView1.DataSource = dt;
                       GridView1.DataBind();
                }
                else
                {
                     //No records found
                }
        }

    }
}
		
protected void Button1_Click(object sender, EventArgs e)
{
        StringCollection sc = new StringCollection();
	foreach (ListItem item in ListBox1.Items)
	{
		if (item.Selected){
			sc.Add(item.Text);
		}
	}

       InsertRecords(sc);
    }

Here's another solution with parameterize query to "prevent" SQL Injection:

C#

private void InsertRecords(StringCollection sc){
	const string sqlStatement = "INSERT INTO Employee (Name) VALUES (@param1)";
   
	foreach (string item in sc){	
        	using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
           		using(SqlCommand cmd = new SqlCommand(sqlStatement ,connection)){
               	 		cmd.CommandType = CommandType.Text;
                		cmd.Parameters.AddWithValue("@param1", item)
                		cmd.ExecuteNonQuery();
        		}
		}
        }
			
	//bind GridView after
	BindGrid(sc);	

}
		
private void BindGrid(StringCollection sc){
	string[] strArray = new string[sc.Count];
	sc.CopyTo(strArray,0);

	var parms = strArray.Select((s, i) => "@param1" + i.ToString()).ToArray();
  	var inclause = string.Join(",", parms);

	string sqlStatement = "SELECT * FROM Employee WHERE Name IN ({0})";
		
    	using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
        	using(SqlCommand cmd = new SqlCommand(string.Format(sqlStatement, inclause),connection)){
 		
    			for (var i = 0; i < valuearray.Length; i++)
    			{
      				cmd.Parameters.AddWithValue(parms[i], strArray[i]);
    			}
                	DataTable dt = new DataTable();
                	SqlDataAdapter ad = new SqlDataAdapter(cmd);
                	ad.Fill(dt);

                	if (dt.Rows.Count > 0) { //check if the query returns any data
                       		GridView1.DataSource = dt;
                       		GridView1.DataBind();
                	}
                	else
                	{
                     	//No records found
                	}
        	}

    	}
}

protected void Button1_Click(object sender, EventArgs e)
{
        StringCollection sc = new StringCollection();
	foreach (ListItem item in ListBox1.Items)
	{
		if (item.Selected){
			sc.Add(item.Text);
		}
	}

       InsertRecords(sc);
 }

PS:Never tested on the paramertize solution in actual but that should give you some idea on how to do it.

Member 12893295 · Answer 3 · 2018-07-04T06:00:00

Solution 3

Whenever you select the item from list you need to make sepeation with # into single string the pass to your parameter. then split that string into sql the use appropriate the return whatever you want on the basis of splited string

Posted 4-Jul-18 6:00am

Member 12893295

Comments

CHill60 10-Jul-18 5:53am

Absolutely the wrong way to go about it

Sending multiple parameters from ASP.NET to my stored procedure in SQL server

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0