从ASP.NET向SQL Server中的存储过程发送多个参数 [英] Sending multiple parameters from ASP.NET to my stored procedure in SQL server
问题描述
我在Asp.Net中有一个列表框，用户从中选择一个或多个参数并将其发送到存储过程。所选参数的数量完全取决于用户，因此我不知道用户将从列表框中选择多少参数。我还希望在单击提交按钮并在gridview上显示时，使用这些参数从表中检索数据。我遇到的问题是我可以发送一个参数并从我的存储过程中检索数据，但我真的不知道如何从列表框中将多个参数发送到我的存储过程。
我尝试过：
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
推荐答案
将多个值传递给a的最佳选项单个存储过程参数是使用表值参数：
表值参数| Microsoft Docs [ ^ ]
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[^]
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用法：
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();
}
或者，对于简单的字符串列表，您可以传递以逗号分隔的值列表，并使用 SPLIT_STRING函数 [ ^ ] （SQL 2016），或自定义字符串拆分功能 [ ^ ]如果您使用的是旧版本版本，将值拆分回表变量或临时表。
还有 XML方法 [ ^ ]，但性能往往很差。
< hr>
另一方面，如果要将多个参数传递给文本查询，则需要构建一个正确参数化的查询：
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:
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();
}
我不在我的开发机器上，因此无法使用存储过程编写示例。但是这里有一个使用动态查询：
I'm not on my dev machine so I can't write a sample using stored procedures. But here's one using dynamic query:
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);
}
这是另一个带参数化查询的解决方案，以防止SQL注入：
Here's another solution with parameterize query to "prevent" SQL Injection:
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：从未对参数化解决方案进行实际测试，但这应该会让您对如何进行测试有所了解。
PS:Never tested on the paramertize solution in actual but that should give you some idea on how to do it.
每当您从列表中选择项目时，您需要使用＃进行单独的字符串分隔传递给您的参数。然后将该字符串拆分为sql使用适当的返回任何你想要的基于拆分字符串
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
这篇关于从ASP.NET向SQL Server中的存储过程发送多个参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
