I have this class that tries to read from the sql reader

        using Microsoft.Data.SqlClient;
    using MinimalAPI.Models.ConnectUI;

    namespace ConnectAPI.SQL.BAL
    {
        public class ConnectData
        {
            public static Form Get_Froms_Data(int form_Id, string page_Id = null)
            {
                Form form = new Form();
                SqlDataReader reader = ConnectAPI.SQL.DAL.ConnectData.Get_Froms_Data(form_Id, page_Id);
                foreach(var row in reader) <-- where the error occurs
                {


                    var t = "";

                }
                reader.Close();
                return form;
            }
        }
    }

And this class to access dal call:

        using Microsoft.Data.SqlClient;
    using MinimalAPI.Models.ConnectUI;
    using System.Data;

    namespace ConnectAPI.SQL.DAL
    {
        public class ConnectData
        {
            static string sqlconnectionstring = "xxx";

            public static SqlDataReader Get_Froms_Data(int form_Id, string page_Id = null)
            {
                string procedurename = "getFormData";
                SqlDataReader reader = null;
                using (SqlConnection con = new SqlConnection(sqlconnectionstring))
                {
                    con.Open();

                    using (SqlCommand cmd = new SqlCommand(procedurename, con))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;

                        cmd.Parameters.Add("@formId", SqlDbType.Int).Value = form_Id;
                        cmd.Parameters.Add("@pageId", SqlDbType.NVarChar, 50).Value = page_Id;
                        reader = cmd.ExecuteReader();
                    }
                    con.Close();
                }

                return reader;
            }

        }
    }

but i keep getting the error:

System.InvalidOperationException: 'Invalid attempt to call FieldCount when reader is closed.'

CodePudding user response：

The reader needs an open connection. Therefore, closing the connection effectively closes the reader, too. To get around it, you can start by changing it to return IEnumerable<IDataRecord> via an iterator block, like this:

public class ConnectData
{
    static string sqlconnectionstring = "xxx";

    public static IEnumerable<IDataRecord> Get_Froms_Data(int form_Id, string page_Id = null)
    {
        string procedurename = "getFormData";

        using var con = new SqlConnection(sqlconnectionstring);
        using var cmd = new SqlCommand(procedurename, con);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.Add("@formId", SqlDbType.Int).Value = form_Id;
        cmd.Parameters.Add("@pageId", SqlDbType.NVarChar, 50).Value = page_Id;
        con.Open();

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

But there is still an issue here, in that we are yielding the same object over and over as it mutates. This can have some strange or unexpected consequences. We can fix this by making sure to construct a new object before returning to other code and yielding the new object. We can also improve this to reduce the amount of boilerplate needed for each method.

public class ConnectData
{
    static string sqlconnectionstring = "xxx";

    // notice this is private
    private static IEnumerable<IDataRecord> GetSQLData(string sql, Action<SqlParameterCollection> addParams, bool AsProcedure = true)
    {
        using var con = new SqlConnection(sqlconnectionstring);
        using var cmd = new SqlCommand(sql, con);
        if (AsProcedure) cmd.CommandType = System.Data.CommandType.StoredProcedure;
        if (addParams is object) addParams(cmd.Parameters);
        con.Open();

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

    // I don't know what kind of item you're returning here, had to make it up
    public static IEnumerable<FormItem> Get_Forms_Data(int form_Id, string page_Id = null)
    {        
        string procedurename = "getFormData";
        var data = GetSqlData(procedureName, p => {
                p.Add("@formId", SqlDbType.Int).Value = form_Id;
                p.Add("@pageId", SqlDbType.NVarChar, 50).Value = page_Id;
            }, true);

        foreach(var row in data)
        {
            // again: I don't know what your records look like
            yield return new FormItem( row["column"]);    
        }
    }
}