返回 SqlDataReader [英] Returning a SqlDataReader

问题描述

我有这个代码:

public static SqlDataReader GetGeneralInformation ( int RecID )
{
    using ( var conn = new SqlConnection( GetConnectionString() ) )
    using ( var cmd = conn.CreateCommand() )
    {
        conn.Open();
        cmd.CommandText =
        @"SELECT cs.Status, cs.Completed
          FROM NC_Steps s
          INNER JOIN NC_ClientSteps cs
              ON cs.RecID = s.RecID
          WHERE cs.ClientID = 162
          AND s.RecID = @value";
        cmd.Parameters.AddWithValue( "@value", RecID );
        using ( var reader = cmd.ExecuteReader() )
        {
            if ( reader.Read() )
            {
                return reader;
            }
            return null;
        }
    }
}

如何引用这个?

我试过了，但它不起作用.

I tried this but it does not work.

SqlDataReader reader = GeneralFunctions.GetGeneralInformation();

另外我将如何从读者那里阅读?

Also how would I read from the reader?

Reader.GetString( reader.GetOrdinal( "Status" ) )

编辑:

我现在收到以下错误:

异常详细信息:System.NullReferenceException:没有对象引用设置为对象的实例.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

这是更新后的代码:

public static IEnumerable<IDataRecord> GetGeneralInformation ( int ClientID )
{
    using ( var conn = new SqlConnection( GetConnectionString() ) )
    using ( var cmd = conn.CreateCommand() )
    {
        conn.Open();
        cmd.CommandText =
        @"SELECT i.GoLiveDate, i.FirstBonusRun, i.TechFName, i.TechLName, i.TechEmail, i.TechPhone, i.WebISPFName, i.WebISPLName, 
          i.WebISPEmail, i.WebISPPhone, i.FullFillFName, i.FullFillLName, i.FullFillEmail, i.FullFillPhone, d.FName,
          d.LName, d.HomePhone, d.Email
          FROM NC_Information i
          INNER JOIN Distributor d
            ON d.DistID = i.ClientID
          WHERE clientID = @value";
        cmd.Parameters.AddWithValue( "@value", ClientID );
        using ( var reader = cmd.ExecuteReader() )
        {
            while ( reader.Read() )
            {
                yield return reader;
            }
            yield return null;
        }
    }
}

protected void Page_Load(object sender, EventArgs e)
{
    IEnumerable<IDataRecord> reader = GeneralFunctions.GetGeneralInformation( (int)Session[ "DistID" ] );

    //string result = GeneralFunctions.GetGeneralInformation( Globals.GeneralInformation ).First()[ "Status" ].ToString();
}

推荐答案

问题是离开函数(通过 return 语句)把你踢出 using 块，所以 SqlDataReader 和 SqlConnections 已被释放.要解决此问题，请尝试更改函数签名，如下所示:

The problem is that leaving the function (via the return statement) kicks you out of the using blocks, and so the SqlDataReader and SqlConnections you are using are disposed. To get around the problem, try changing the function signature like this:

public static IEnumerable<IDataRecord> GetGeneralInformation ( int RecID )

然后像这样更新函数的中间:

and then update the middle of the function like this:

using ( var reader = cmd.ExecuteReader() )
{
    while ( reader.Read() )
    {
        yield return reader;
    }
}

对于最后的我如何阅读它?"部分，它可能看起来像这样:

For the final "How do I read from it?" part, it might look like this:

int RecID = 12345;
string result = GetGeneralInformation(RecID).First()["Status"].ToString();

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