Running DataReader from separate class file in webforms c#

Question

I placed my DB reader in a separate class file because I didn't want to keep rewriting it but I keep getting the error:

Object reference not set to an instance of an object. db was null

This is my DataReader:

namespace ProjectName
{
    public class DBReader
    {
        string dsn = ConfigurationManager.ConnectionStrings["database"].ConnectionString.ToString();

        public SqlDataReader SqlReader(string sql, string retDebug = "")
        {
            try
            {
                SqlConnection conn;
                SqlCommand cmd;
                SqlDataReader dr_user;

                conn = new SqlConnection(dsn);
                conn.Open();

                try
                {
                    cmd = new SqlCommand(sql, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandTimeout = 180;

                    dr_user = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    return dr_user;
                }
                catch (SqlException ex)
                {
                    retDebug = ex.Message;
                    return null;
                }

                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                retDebug = ex.Message;
            }

            return null;
        }
    }
}

This is where I'm catching the error...at

SqlDataReader reader = db.SqlReader(query, "");

in the code shown here:

<!DOCTYPE html>
<script runat="server">
    ProjectName.DBReader db;
    string projectName;

    protected void Page_Load(object sender, EventArgs e)
    {
        LoadProjects();
    }

    public void LoadProjects()
    {
        string query = @"SELECT * FROM projects where project_type = 3;

        SqlDataReader reader = db.SqlReader(query, "");

        while (reader.Read())
        {
            //code does something here
        }
    }
</script>

I want to be able to reuse this because I know I will be using it many times in this project.

Any help/direction would be appreciated!

You need to instantiate the object, db = new ProjectName.DBReader() — Nikita Chayka, Oct 31 '22 at 17:43
Duplicate: [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Ňɏssa Pøngjǣrdenlarp, Oct 31 '22 at 17:44
Now it says reader was null, but not db was null :) So you need to debug your SqlReader method logic, cause there you handling exceptions and explicitly returning null in some cases. So most likely you have some other exception there — Nikita Chayka, Oct 31 '22 at 17:55
Not a direct answer to your question, but a strong suggestion - learn to use Entity Framework Core. You'll find it way easier to work with than handling raw connections and writing SQL. — Avrohom Yisroel, Oct 31 '22 at 21:39

Albert D. Kallal · Accepted Answer · 2022-11-01T03:12:07.987

As others pointed out, like any class, you have to create a instance of that class before using.

HOWEVER, if you don't have any public vars (at least ones that will change with different users on the web site), then you can also have public members of that class, and create the class as static. (but, those public members MUST be the same for all logged on users)

So, your choice.

Always create an instance of the class before using.

eg this:

    public void LoadProjects()
    {
        string query = @"SELECT * FROM projects where project_type = 3";
        DBReader MyDB = new DBReader();

        SqlDataReader reader = MyDB.SqlReader(query, "");

        while (reader.Read())
        {
            //code does something here
        }
    }

Or, you can declare the class as static, like this:

(air code warning).

public static class DBReader
{
    static readonly string dsn = ConfigurationManager.ConnectionStrings["database"].ConnectionString.ToString();

    static public SqlDataReader SqlReader(string sql, string retDebug = "")
    {
        SqlDataReader dr_user = null;

        using (SqlConnection conn = new SqlConnection(dsn))
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 180;
                conn.Open();
                dr_user = cmd.ExecuteReader();
            }
        }
        return dr_user;
    }
}

So, now in code you don't have to create a instance.

eg:

    public void LoadProjects()
    {
        string query = @"SELECT * FROM projects where project_type = 3";

        SqlDataReader reader = MyCode.DBReader.SqlReader(query, "");

        while (reader.Read())
        {
            //code does something here
        }
    }

Running DataReader from separate class file in webforms c#

1 Answers1