将参数传递给C#中的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15569860/
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 parameter to stored procedure in C#
提问by whoadityanawandar
I have a stored procedure that returns a variable @result set to 1 or 0 (datatype bit). I am accessing it in my C# with the following code. Its throwing an error saying too many parameters.
我有一个存储过程，它返回一个变量 @result 设置为 1 或 0（数据类型位）。我正在使用以下代码在我的 C# 中访问它。它抛出一个错误，说参数太多。
protected void btnRegister_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);
con.Open();
SqlCommand Cmd = new SqlCommand("usp_CheckEmailMobile", con);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = "Registration";
Cmd.Parameters.AddWithValue("@Name", txtName.Text);
Cmd.Parameters.AddWithValue("@Email", txtEmailAddress.Text);
Cmd.Parameters.AddWithValue("@Password", txtPassword.Text);
Cmd.Parameters.AddWithValue("@CountryCode", ddlCountryCode.Text);
Cmd.Parameters.AddWithValue("@Mobile", txtMobileNumber.Text);
//Cmd.Parameters.Add("@Result", DbType.Boolean);
SqlParameter sqlParam = new SqlParameter("@Result", DbType.Boolean);
//sqlParam.ParameterName = "@Result";
//sqlParam.DbType = DbType.Boolean;
sqlParam.Direction = ParameterDirection.Output;
Cmd.Parameters.Add(sqlParam);
Cmd.ExecuteNonQuery();
con.Close();
Response.Write(Cmd.Parameters["@Result"].Value);
}
the stored procedure: (this I think is fine...) And please correct my CS code...
存储过程：（我认为这很好...）请更正我的 CS 代码...
ALTER PROCEDURE [dbo].[usp_CheckEmailMobile]
( @Name VARCHAR(50),
@Email NVARCHAR(50),
@Password NVARCHAR(50),
@CountryCode INT,
@Mobile VARCHAR(50),
@Result BIT OUTPUT)
AS
BEGIN
IF EXISTS (SELECT COUNT (*) FROM AUser WHERE [Email] = @Email AND [Mobile] = @Mobile)
Begin
Set @Result=0; --Email &/or Mobile does not exist in database
End
ELSE
Begin
--Insert the record & register the user
INSERT INTO [AUser] ([Name], [Email], [Password], [CountryCode], [Mobile]) VALUES (@Name, @Email, @Password, @CountryCode, @Mobile)
Set @Result=1;
End
END
采纳答案by Arshad
you can try this code :
你可以试试这个代码：
bool result=false;
SqlCommand scCommand = new SqlCommand("usp_CheckEmailMobile", sqlCon);
scCommand.CommandType = CommandType.StoredProcedure;
scCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtName.Text;
scCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = txtEmailAddress.Text;
scCommand.Parameters.Add("@Password ", SqlDbType.NVarChar, 50).Value = txtPassword.Text;
scCommand.Parameters.Add("@CountryCode", SqlDbType.VarChar.50).Value =ddlCountryCode.SelectedText;
scCommand.Parameters.Add("@Mobile", SqlDbType.NVarChar, 50).Value = txtMobileNumber.Text;
scCommand.Parameters.Add("@Result ", SqlDbType.Bit).Direction = ParameterDirection.Output;
try
{
if (scCommand.Connection.State == ConnectionState.Closed)
{
scCommand.Connection.Open();
}
scCommand.ExecuteNonQuery();
result = Convert.ToBoolean(scCommand.Parameters["@Result"].Value);
}
catch (Exception)
{
}
finally
{
scCommand.Connection.Close();
Response.Write(result);
}
回答by Rowland Shaw
Why do you set:
为什么要设置：
Cmd.CommandText = "Registration";
this will replace your stored procedure name, so it won't call the stored procedure you indicated in:
这将替换您的存储过程名称，因此它不会调用您在以下位置指示的存储过程：
SqlCommand Cmd = new SqlCommand("usp_CheckEmailMobile", con);
It can be useful to use a SQL profiler to debug that the SQL going "over the wire" is as expected.
使用 SQL 分析器来调试“通过网络”运行的 SQL 是否符合预期会很有用。
