Using IEnumerable<IDataRecord> to return data

Question

I am trying to return data using IEnumerable with given fields, where I am calling the the method I want to reference the data with given field name and return that.

Example, here is the function

    public IEnumerable<IDataRecord> GetSomeData(string fields, string table, string where = null, int count = 0)
    {
        string sql = "SELECT @Fields FROM @Table WHERE @Where";

        using (SqlConnection cn = new SqlConnection(db.getDBstring(Globals.booDebug)))
        using (SqlCommand cmd = new SqlCommand(sql, cn))
        {
            cmd.Parameters.Add("@Fields", SqlDbType.NVarChar, 255).Value = where;

            cn.Open();

            using (IDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    yield return (IDataRecord)rdr;
                }
            }
        }
    }

Calling:

IEnumerable<IDataRecord> data = bw.GetSomeData("StaffCode, Perms", "BW_Staff", "StaffCode = 'KAA'");

What must I do to return the data this way or what way ?

string staffCode = data["StaffCode"].ToString();
string perms = data["Perms"].ToString();

Thanks for any help

Answer 1

your data variable is a collection of rows. You need to iterate over the collection to do something interesting with each row.

foreach (var row in data)
{
    string staffCode = row["StaffCode"].ToString();
    string perms = row["Perms"].ToString();
}

Update:

Based on your comment that you only expect GetSomeData(...) to return a single row, I'd suggest 1 of two things.

Change the signature of GetSomeData to return an IDataRecord. and remove "yield" from the implementation.

public IDataRecord GetSomeData(string fields, string table, string where = null, int count = 0)
    {
        string sql = "SELECT @Fields FROM @Table WHERE @Where";

        using (SqlConnection cn = new SqlConnection(db.getDBstring(Globals.booDebug)))
        using (SqlCommand cmd = new SqlCommand(sql, cn))
        {
            cmd.Parameters.Add("@Fields", SqlDbType.NVarChar, 255).Value = where;

            cn.Open();

            using (IDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    return (IDataRecord)rdr;
                }
            }
        }
    }
}

Or

var row = data.FirstOrDefault();
if (row != null)
{
    string staffCode = row["StaffCode"].ToString();
    string perms = row["Perms"].ToString();
}

Remarks:

Your implementation of GetSomeData is incomplete. You are not even using several of the parameters, most importantly the fields parameter. And conceptually in SQL you can't parameterize which fields get returned or which table gets used (etc.), but rather you need to construct a dynamic query and execute it.

Update 2

Here is an implementation of GetSomeData that constructs a proper query (in C# 6, let me know if you need it in an earlier version).

public IEnumerable<IDataRecord> GetSomeData(IEnumerable<string> fields, string table, string where = null, int count = 0)
{
    var predicate = string.IsNullOrWhiteSpace(where) ? "" : " WHERE " + where;
    string sql = $"SELECT { string.Join(",", fields) } FROM {table} {predicate}";

    using (SqlConnection cn = new SqlConnection(db.getDBstring(Globals.booDebug)))
    using (SqlCommand cmd = new SqlCommand(sql, cn))
    {
        cn.Open();

        using (IDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                yield return (IDataRecord)rdr;
            }
        }
    }
}

And here is how you would use it.

IEnumerable<IDataRecord> data = bw.GetSomeData(new[] { "StaffCode", "Perms" }, "BW_Staff", "StaffCode = 'KAA'");

You can either enumerate it or call .FirstOrDefault, it's your choice. Each time you call GetSomeData, it will run the query.

Update 3

GetSomeData implemented with earlier versions of C#

public IEnumerable<IDataRecord> GetSomeData(IEnumerable<string> fields, string table, string where = null, int count = 0)
{
    var predicate = string.IsNullOrEmpty(where) ? "" : " WHERE " + where;
    string sql = string.Format("SELECT {0} FROM {1} {2}", string.Join(",", fields), table, predicate);

    using (SqlConnection cn = new SqlConnection(db.getDBstring(Globals.booDebug)))
    using (SqlCommand cmd = new SqlCommand(sql, cn))
    {
        cn.Open();

        using (IDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                yield return (IDataRecord)rdr;
            }
        }
    }
}