我正在尝试使用如下所示的存储过程从数据库中提取一条数据。
USE [PSD]
GO
/****** Object: StoredProcedure [dbo].[ocso_GetNextDTEventNumber] Script Date: 1/19/2017 10:12:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ocso_GetNextDTEventNumber]
@EventNumber varchar(15) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--increment last number by 1
UPDATE LU_EVENT_NUMBERS SET evn_last_number = evn_last_number + 1 where evn_year = Year(GetDate())
--return event number to caller
--Get the next event number for all incidents
SELECT @EventNumber = evn_comp_formatted_next_num from LU_EVENT_NUMBERS where evn_year = Year(GetDate())
END
我使用此SQL代码测试此过程
DECLARE @NewEventNumber varchar(15)
Execute dbo.ocso_GetNextDTEventNumber
@EventNumber = @NewEventNumber OUTPUT;
Print convert(varchar(15),@NewEventNumber)
我得到了我期望的结果。现在,当我尝试在我的C#代码中获取结果时,如下所示:
public string GetEventNumber(string enumber)
{
string results;
SqlConnection _con = new SqlConnection();
_con.ConnectionString =
"Data Source=ops-devsql;" +
"Initial Catalog=PSD;" +
"User id=****;" +
"Password=****;";
_con.Open();
{
using (SqlCommand _cmd = new SqlCommand("ocso_GetNextDTEventNumber", _con))
{
_cmd.Parameters.Add("@NewEventNumber", SqlDbType.VarChar, 15)
.Direction = ParameterDirection.Output;
_cmd.ExecuteNonQuery();
results = _cmd.Parameters["@NewEventNumber"].Value.ToString();
//results = (string)_cmd.Parameters["@NewEventNumber"].Value.ToString();
return results;
}
}
}
我得到一个空字符串。为什么这不起作用?
您忘记了告诉命令您要执行存储过程:
using (SqlCommand _cmd = new SqlCommand("ocso_GetNextDTEventNumber", _con))
{
_cmd.CommandType = CommandType.StoredProcedure;
//...
}
如果未指定,则采用默认值Text
(因此是简单的sql查询)。我猜您某处有一个空的,Try....Catch
因为这会导致异常。
您还应该使用相同的参数名称:
_cmd.Parameters.Add("@EventNumber", SqlDbType.VarChar, 15).Direction = ParameterDirection.Output;
_cmd.ExecuteNonQuery();
results = _cmd.Parameters["@EventNumber"].Value.ToString();
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句