SqlDataReader未从SQL Server存储过程返回任何数据

本文关键字：返回 任何 数据 存储过程 Server 未从 SQL SqlDataReader | 更新日期: 2023-09-27 17:59:27

我的SQL Server数据库中有以下存储过程，它执行良好：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LoadStates]
AS
BEGIN
    SELECT stateabbrev 
    FROM states 
    ORDER BY stateabbrev
END
GO

这是我的C#代码；sdrData已初始化，看起来是正确的，但结果集为空。请帮忙。

using (SqlCommand sqlCmd = new SqlCommand("LoadStates", sqlConn))
{
    sqlCmd.CommandType = CommandType.StoredProcedure;
    // set up the parameters that the Stored Procedure expects
    //sqlCmd.Parameters.Add("@States", SqlDbType.Char, 2).Direction = ParameterDirection.Output;
    using (SqlDataReader sdrData = sqlCmd.ExecuteReader())
    {
        while (sdrData.Read())
        {
            string strDBNme = sdrData.ToString();
            //string strDBNme = (string)sdrData["States"];
            cmbxACState.Items.Add(strDBNme);
        }
        sdrData.Close();
    }
}

SqlDataReader未从SQL Server存储过程返回任何数据

       SqlDataReader sdrData  = null;
        // create a connection object
        SqlConnection conn = new SqlConnection(create your sql connection string here );
// create a command object
  SqlCommand cmd  = new SqlCommand("LoadStates", conn);
    sqlCmd.CommandType = CommandType.StoredProcedure;
        try
        {
            // open the connection
            conn.Open();                
            sdrData = cmd.ExecuteReader();
            while (sdrData.Read())
                    {
                        //string strDBNme = sdrData.ToString();
                        string strDBNme = (string)sdrData["States"];
                        cmbxACState.Items.Add(strDBNme);
                    }
        }
        finally
        {
            // 3. close the reader
            if (sdrData != null)
            {
                sdrData.Close();
            }
            // close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }   

string strDBNme = (string)sdrData["stateabbrev"];

string connectionString = ConfigurationManager.ConnectionStrings["StackDemo"]
                 .ConnectionString;
         using (SqlConnection connection = new SqlConnection(connectionString))
         {
             connection.Open();
             using (SqlCommand cmd = new SqlCommand("LoadStates", connection))
             {
                 cmd.CommandType = CommandType.StoredProcedure;

                 using (SqlDataReader sdrData = cmd.ExecuteReader())
                 {
                     while (sdrData.Read())
                     {
                       Console.WriteLine( (string)sdrData["stateabbrev"]);
                     }
                     sdrData.Close();
                 }
             }
         }
相关文章：