C#:如何为 SQL Server 存储过程使用参数? [英] C# : how to use parameters for SQL Server stored procedure?
问题描述
我想使用 C# 中的存储过程从 SQL Server 表中获取数据.我想用我将作为参数传递的 id 打印城市,但我不知道在 C# 中的存储过程中使用类似参数的正确语法.
I want to use stored procedures in C# to get data from a SQL Server table. I want to print the city with the id that I'll pass as a parameter, but I don't know the correct syntax of using parameters like that for a stored procedure in C#.
这是我使用的 C# 代码:
This is the C# code that I'm using:
string connString = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand command = new SqlCommand("SP_GetCityByID where id = 2", conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = command.Parameters.Add("@ID", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
command.ExecuteNonQuery();
Console.WriteLine(param.Value);
}
推荐答案
SqlCommand command = new SqlCommand("SP_GetCityByID ", conn);
在调用存储过程时,不要放置 where
条件.where
条件需要在存储过程的主体内,它应该将您的城市表的 id
列与您传递给的 @ID
参数进行比较存储过程.其次,您在最后编写的 ExecuteNonQuery
函数不会满足您的目的.使用 ExecuteScalar
函数代替,如下所示:
You don't put a where
condition when you call a stored procedure. where
condition needs to be inside the body of stored procedure which should compare the id
column of your city table with @ID
parameter you are passing to stored procedure. Secondly, ExecuteNonQuery
function which you have written at the end will not serve your purpose. Use ExecuteScalar
function instead as given below:
String cityName= command.ExecuteScalar();
我假设您的存储过程接受参数 @ID
并以表格的形式返回匹配的城市名称.
I am assuming your stored procedure accepts parameter @ID
and returns matching city name in the form of table.
这篇关于C#:如何为 SQL Server 存储过程使用参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!