C# 将多个参数作为单个字符串变量传递给 sql 过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2152396/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
passing multiple parameters to sql procedure as a single string variable
提问by Swati
From front end(studio 2008) I am passing values to sql procedure as :
从前端（工作室 2008）我将值传递给 sql 过程：
string a = "hello" + "098765" + "world" + "90.0909"
字符串 a =“你好”+“098765”+“世界”+“90.0909”
These are 4 different values that I've concatenated into a string a;
这些是我连接成字符串 a 的 4 个不同值；
now i pass this string ato the sql procedure using c# sqlCommand object.
现在我使用 c# sqlCommand 对象将此字符串 a传递给 sql 过程。
Now, how do I retrieve these 4 values in sql procedure as I've created the procedure as:
现在，我如何在 sql 过程中检索这 4 个值，因为我已将过程创建为：
create procedure Proc_name (@concatenated_string varchar(100))
as
insert into table1 values(**how can i get those 4 values here**).
I used arrays but it didn't work.
我使用了数组，但没有用。
采纳答案by David Hall
The standard way to do this would be to use four parameters on the procedure:
执行此操作的标准方法是在过程中使用四个参数：
create procedure Proc_name (@param1 varchar(100),
@param2 varchar(100),
@param3 varchar(100),
@param4 varchar(100))
as
insert into table1 values(@param1, @param2, @param3, @param4)
Then from your code (giving a c# example using ADO.NET)
然后从您的代码（使用 ADO.NET 给出 ac# 示例）
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create the command and set its properties.
SqlCommand command = new SqlCommand();
SqlCommand command = new SqlCommand
("Proc_name", connection);
command.CommandType = CommandType.StoredProcedure;
// Add the input parameters and set the properties.
SqlParameter parameter1 = new SqlParameter();
parameter.ParameterName = "@Param1";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = param1;
SqlParameter parameter2 = new SqlParameter();
parameter.ParameterName = "@Param2";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = param2;
// Same for params 3 and 4...
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter1);
command.Parameters.Add(parameter2);
command.Parameters.Add(parameter3);
command.Parameters.Add(parameter4);
// Open the connection and execute the reader.
connection.Open();
SqlDataReader reader = command.ExecuteNonQuery();
reader.Close();
}
回答by pedro
use several parameters instead of 1, e.g.:
使用多个参数而不是 1，例如：
CREATE PROCEDURE [dbo].[addUser]
@idRole int,
@userName varchar(255),
@password varchar(255)
AS
BEGIN
set nocount on
insert into userTbl ( idRole , userName , password )
VALUES ( @idRole , @userName , @password )
return scope_identity();
END
GO
回答by gbn
If you want to pass an array into SQL Server to deal with "multirow" updates on one table, read this famous article(s).
如果您想将数组传递到 SQL Server 以处理一个表上的“多行”更新，请阅读这篇著名的文章。
If you want a generic stored proc to update any table, then don't as per other comments
如果你想要一个通用的存储过程来更新任何表，那么不要按照其他评论
回答by Peter Gfader
You could concatenate the 4 strings with a comma between and split it in the database back.
您可以用逗号连接 4 个字符串，然后将其拆分到数据库中。
E.g.
例如
declare @values as nvarchar(1000)
set @values = 'hello,098765,world,90.0909'
SELECT * FROM split(@values)
---------------- SPLIT FUNCTION --------------
CREATE FUNCTION [dbo].[split]
(
@csv nvarchar(max)
)
RETURNS
@entries TABLE
(
entry nvarchar(100)
)
AS
BEGIN
DECLARE @commaindex int
SELECT @commaindex = CHARINDEX(',', @csv)
IF @commaindex > 0
BEGIN
INSERT INTO @entries
-- insert left side
SELECT LTrim(RTrim(LEFT(@csv, @commaindex-1)))
-- pass right side recursively
UNION ALL
SELECT entry
FROM dbo.split(RIGHT(@csv, LEN(@csv) - @commaindex))
END
ELSE
INSERT INTO @entries
SELECT LTrim(RTrim(@csv))
RETURN
END
回答by Kane
If you are using SQL Server 2005 then you might want to look at sending your data through to your stored procedure as an XML parameter. This linkexplains the process perfectly
如果您使用的是 SQL Server 2005，那么您可能希望将数据作为 XML 参数发送到存储过程。这个链接完美地解释了这个过程
Here's a sample section of how your code might look using .NET 3.5 and C#
这是使用 .NET 3.5 和 C# 时代码的示例部分
// sample object
// 样本对象
[Serializable]
internal class MyClass
{
internal string Property1 { get; set; }
internal string Property2 { get; set; }
internal int Property3 { get; set; }
internal string Property4 { get; set; }
}
// sample serialization
// 样本序列化
internal static string SerializeObject<T>(T objectGraph)
{
StringBuilder sb = new StringBuilder();
XmlWriterSettings writerSettings = new XmlWriterSettings();
writerSettings.OmitXmlDeclaration = true;
writerSettings.Indent = true;
using (XmlWriter xmlWriter = XmlWriter.Create(sb, writerSettings))
{
XmlSerializer xs = new XmlSerializer(typeof(T));
XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
ns.Add(String.Empty, String.Empty);
xs.Serialize(xmlWriter, objectGraph, ns);
}
return sb.ToString();
}
// sample stored procedure
// 示例存储过程
Create PROCEDURE [dbo].[MyProc]
@myClassXML XML
AS
BEGIN
INSERT INTO [dbo].[MyTable]
(
P1,
P2,
P3,
P4
)
SELECT
Container.ContainerCol.value('Property1[1]', 'varchar(50)') AS P1,
Container.ContainerCol.value('Property2[1]', 'varchar(50)') AS P2,
Container.ContainerCol.value('Property3[1]', 'int') AS P3,
Container.ContainerCol.value('Property4[1]', 'varchar(50)') AS P4,
FROM @myClassXML.nodes('//MyClass') AS Container(ContainerCol)
END
I am assuming that you've read the advice of other answers here and are not creating a generic "Insert Anything" stored procedure as this is one of the worst things that you could do.
我假设您已经阅读了其他答案的建议，并且没有创建通用的“插入任何内容”存储过程，因为这是您可以做的最糟糕的事情之一。
Note: This code was written in Notepad++ and thus hasn't been tested.
注意：此代码是用 Notepad++ 编写的，因此尚未经过测试。
回答by Unsliced
If you really do just want to use one parameter, then maybe consider an XML parameter rather than a string.
如果您真的只想使用一个参数，那么可以考虑使用 XML 参数而不是字符串。
回答by ChandM
public List<T> updateSiteDetails<T>(int SiteId, int CategoryId, string[] values)
{
int temp = values.Count();
int Counter = 0;
List<T> SiteDetails = null;
var parameterData = new string[temp];
var para = new string[temp];
foreach (string value in values)
{
Counter =Counter++;
parameterData[Counter] = "@,value"+Counter;
para[Counter] = string.Format(","+value);
}
//string ParameterDatas=string.Join(",",parameterData);
string parameterValue = string.Join(",",para);
using (SBDEntities db = new SBDEntities())
{
SiteDetails = db.Database.SqlQuery<T>("Sp_Update_Data @SiteId,@CategoryId" + string.Join(",", parameterData),string.Join(",",para)
//new Object[] { new SqlParameter("@SiteId", SiteId),
// new SqlParameter("@CategoryId",CategoryId)}
).ToList();
}
return SiteDetails;
}
in case you are using stored procedure with Entity framework
如果您在实体框架中使用存储过程
