C# 将布尔参数传递给 SQL Server 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17536093/
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 a boolean parameter to a SQL Server stored procedure
提问by gg17
I asked this question earlier and I thought I found what the problem was, but I didn't. I'm having a problem passing a boolean parameter to a stored procedure. Here's my c# code:
我之前问过这个问题，我以为我找到了问题所在，但我没有。我在将布尔参数传递给存储过程时遇到问题。这是我的 C# 代码：
public bool upload = false;
protected void showDate(object sender, EventArgs e)
{
if (Radio1.Checked)
{
upload = true;
Radio2.Checked = false;
date_div.Visible = true;
date_div2.Visible = false;
}
}
protected void getMonthList()
{
selectedYear = year.SelectedValue.ToString();
SqlConnection connection = new SqlConnection(connString);
SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
cmd.CommandText = "getMonth";
cmd.Parameters.Add("@year", SqlDbType.Int, 0).Value = Convert.ToInt32(selectedYear);
cmd.Parameters.AddWithValue("@upload", upload);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
month.DataSource = dt;
month.DataTextField = "month";
month.DataValueField = "monthValue";
month.DataBind();
month.Items.Insert(0, new ListItem("Select", "0"));
}
And this is the stored procedure getMonth:
这是存储过程getMonth：
ALTER PROCEDURE [dbo].[getMonth]
@year int,
@upload Bit
AS
BEGIN
IF @upload = 1
BEGIN
SELECT distinct datename(mm, Upload_date) month
,month (upload_date) monthValue
FROM dbo.RESOLVED
WHERE datepart(yyyy, upload_date) = @year
ORDER by 2
END
ELSE
BEGIN
SELECT distinct datename(mm, substring(CREATE_DT,1,2) + '.' + substring(CREATE_DT,3,2) + '.' + substring(CREATE_DT,5,4)) month
,month (substring(CREATE_DT,1,2) + '.' + substring(CREATE_DT,3,2) + '.' + substring(CREATE_DT,5,4)) monthValue
FROM dbo.RESOLVED
WHERE datepart(yyyy, substring(CREATE_DT,1,2) + '.' + substring(CREATE_DT,3,2) + '.' + substring(CREATE_DT,5,4)) = @year
ORDER by 2
END
The stored procedure is supposed to populate dropdownlist. It supposed to execute the IF statement, but IF is skipped and ELSE is executed instead.
存储过程应该填充下拉列表。它应该执行 IF 语句，但 IF 被跳过而 ELSE 被执行。
回答by Mr Moose
I'd be inclined to specify the type for the boolean parameter also. Maybe something like;
我也倾向于指定布尔参数的类型。也许是这样的；
SqlParameter param = new SqlParameter();
param.ParameterName = "@upload";
param.Value = upload;
param.DbType = System.Data.DbType.Boolean
cmd.Parameters.Add(param);
Maybe also check using a breakpoint or even System.Diagnostics.Debug.Write("@Upload is " + upload)to ensure you are passing in what you think you are passing in.
也许还可以使用断点进行检查，甚至System.Diagnostics.Debug.Write("@Upload is " + upload)确保您传入的是您认为传入的内容。
Lastly, I'd suggest putting your SqlConnectionand SqlCommandstatments in a usingblock to ensure the resources are cleaned up after this has run.
最后，我建议将您的SqlConnection和SqlCommand语句放在一个using块中，以确保在运行后清理资源。
