sp提供输出，但不提供SqlDataReader [英] sp giving output but SqlDataReader not

问题描述

我在C#项目中使用SP来使用SqlDataReader检索输出.下面是代码.

I am using SP in C# project to retrieve the output using SqlDataReader. Below is the code.

 public List<LMTUsage> GetCompanyID(string userID, int roleId, String Organisation, String BusinessArea)
    {
        List<LMTUsage> objLMT = new List<LMTUsage>();
        LMTUsage _oELMTUsage;

        SqlConnection oCon = new SqlConnection(ConfigurationManager.ConnectionStrings["LMTConnectionString"].ConnectionString);
        oCon.Open();

        try
        {
            using (SqlCommand _oCmd = new SqlCommand())
            {
                _oCmd.Connection = oCon;

                _oCmd.CommandType = CommandType.StoredProcedure;
                _oCmd.CommandText = "[SC_GetDropdownValues]";

                _oCmd.Parameters.Add(new SqlParameter("@UserId", userID));
                _oCmd.Parameters.Add(new SqlParameter("@RoleId", roleId));

                if (Organisation == "")
                    _oCmd.Parameters.Add(new SqlParameter("@Organisation", DBNull.Value));
                else
                    _oCmd.Parameters.Add(new SqlParameter("@Organisation", Organisation));

                if (BusinessArea == "")
                    _oCmd.Parameters.Add(new SqlParameter("@BusinessArea", DBNull.Value));
                else
                    _oCmd.Parameters.Add(new SqlParameter("@BusinessArea", BusinessArea));

                _oCmd.Parameters.Add(new SqlParameter("@Type", 3));


                using (SqlDataReader _oRdr = _oCmd.ExecuteReader())
                {
                   // _oRdr.Close();
                    while (_oRdr.Read())
                    {
                        _oELMTUsage = new LMTUsage();
                        _oELMTUsage.Company = _oRdr["Company"].ToString();

                        objLMT.Add(_oELMTUsage);
                    }
                    _oRdr.Close();
                }
            }
            return objLMT;
        }
        catch (Exception Ex)
        {
            throw Ex;
        }
        //finally
        //{
        //    oCon.Close();
        //    oCon.Dispose();
        //}
    }

这是带有select语句的非常简单的SP.从SQL 2014执行时，SP返回输出，但以上述方法实现时，它不返回任何输出.以下是参考屏幕.

It is very Simple SP with the select statement. SP return the output when executed from SQL 2014 but when implemented in the above method it doesn't return any output. Below is the screen for reference.

请指导.

推荐答案

除非您的存储过程被专门命名为[SC_GetDropdownValues](包括方括号)，否则无需在SqlCommand的 CommandText中定义方括号..试试:

Unless your stored procedure is specifically named [SC_GetDropdownValues] (including the square brackets), you don't need to define square brackets in your SqlCommand's CommandText. Try:

_oCmd.CommandText = "SC_GetDropdownValues";

这篇关于sp提供输出，但不提供SqlDataReader的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！