C# 返回一个 SqlDataReader

Returning a SqlDataReader

提问by James Wilson

I have this code:

我有这个代码：

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;
        }
    }
}

How do I reference this?

我如何参考这个？

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" ) )

Edit:

编辑：

I am now getting the following error:

我现在收到以下错误：

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

异常详细信息：System.NullReferenceException：未将对象引用设置为对象的实例。

Here is the updated code:

这是更新后的代码：

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();
}

采纳答案by Joel Coehoorn

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

问题是离开函数（通过 return 语句）会将您踢出using块，因此您正在使用的SqlDataReader和SqlConnections被处理掉。要解决此问题，请尝试像这样更改函数签名：

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();

回答by jp2code

By definition, a usingstatement is supposed to Dispose of an object after it is called.

根据定义，using语句应该在调用后处理对象。

Therefore, after you return your data reader, it gets disposed of.

因此，在您归还数据读取器后，它会被处理掉。

回答by EuroMarkus

The problem is you are creating the database connection withinyour method.

问题是你创建的数据库连接中你的方法。

If you are going to sharedatabase resources between many methods, move SqlConnectionoutside this scope.

如果您要在许多方法之间共享数据库资源，请移至SqlConnection此范围之外。

This way you can return the Reader from this function and it will persist.

这样你就可以从这个函数返回 Reader 并且它会持续存在。

Also, don't .Read()within this function, just return the object.

另外，不要.Read()在这个函数内，只返回对象。

回答by A Ghazal

Add your connection string to AppSettings section in app.config or web.config.

将您的连接字符串添加到 app.config 或 web.config 中的 AppSettings 部分。

   public string GetSqlConnection()
    {
        return  System.Configuration.ConfigurationManager.AppSettings["SqlConnectionString"];
    }

//Function to return SqlDataReader results

//返回SqlDataReader结果的函数

    public SqlDataReader executeReader(string sql, SqlParameter[] parameters=null)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = GetSqlConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = sql;
        if (parameters != null)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter p in parameters)
            {
                cmd.Parameters.Add(p);
            }
        }
        else
        {
            cmd.CommandType = CommandType.Text;
        }
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return reader;
    }

To use the function:

要使用该功能：

        string query = @"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";
       //you can add more parameters by adding commas
       var parameters = new SqlParameter[] {
            new SqlParameter("@value", RecID )
           };

        SqlDataReader dr = executeReader(query, parameters);
        while (dr.Read())
        {
            //fill your controls with data 
        }
        dr.Close();

回答by Timo

I believe this StackOverflow answerdeserves mentioning. A very simple and pleasant-to-use solution.

我相信这个 StackOverflow 答案值得一提。一个非常简单且易于使用的解决方案。