I get a null SqlDataReader object as from a utility method, which seems to be OK. how do I fix it? The code returning the reader object is given below.

Question

0.00/5 (No votes)

See more:

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;

namespace DBTools {
    public class DBUtility {
        private static string connString = "Data Source=MK;Initial Catalog=students;Integrated Security=True";

        public static SqlDataReader ExecuteSelect(string cmdName, CommandType cmdType, params SqlParameter[] pars) {
            SqlDataReader reader = null;
            using (SqlConnection conn = new SqlConnection(connString)) {
                using (SqlCommand cmd = conn.CreateCommand()) {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = cmdName;
                    if (pars.Length > 0) {
                        cmd.Parameters.AddRange(pars);
                    }
                    conn.Open();
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    return reader;
                }
            }

        }

        public static Int32 ExecuteNonQuery(string cmdName, CommandType cmdType, params SqlParameter[] pars) {
            using (SqlConnection conn = new SqlConnection(connString)) {
                using (SqlCommand cmd = new SqlCommand(cmdName, conn)) {
                    // There're three command types: StoredProcedure, Text, TableDirect. The TableDirect
                    // type is only for OLE DB.
                    cmd.CommandType = cmdType;
                    if (pars.Length > 0)
                        cmd.Parameters.AddRange(pars);

                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

The reader object in the method above 'ExecuteSelect' method (on tracing the execution) shows that it contains a valid reference. But when I receive the vale in another file for displaying the data, as in the statement below, it shows a null reference that has not been initialized:

SqlDataReader reader = DBUtility.ExecuteSelect("SELECT * FROM stud_master WHERE StudId = @ID", CommandType.Text, new SqlParameter[] { new SqlParameter("@ID", studId) } );

Posted 8-Oct-15 1:55am

Mudit Khetan

Updated 8-Oct-15 3:31am

v3

Add a Solution

Comments

Sreekanth Mothukuru 8-Oct-15 8:06am

If it is OK then what do you want to fix and why?

Nathan Minier 8-Oct-15 8:28am

With Integrated Security it will use the execution context unless you're executing in an impersonated context, but I don't see any evidence of that.

Since you're using System.Web you must have an app pool. Does that App pool identity have permissions on the database server?

Mudit Khetan 8-Oct-15 9:26am

I am a newbie and trying to learn things. So I do not get yor description. It is working fine when i changed the code to use DataTable. the updated code is given below:
<pre>
DataTable table = DBUtility.ExecuteSelectTable("SELECT * FROM stud_master WHERE StudId = @ID", CommandType.Text, new SqlParameter[] { new SqlParameter("@ID", studId) });
</pre>

ZurdoDev 8-Oct-15 8:33am

It's not a good idea to pass Readers around. But what exactly is your question?

Mudit Khetan 8-Oct-15 9:30am

The basic premise of a return vale from a method call is to allow the caller reference be updated with the object sent as return value.
For example:

class Sample{
...
}
someMethod(){
Sample ref = methodReturningSampleObject();
}

If assuming the method methodReturningSampleObject() does not return a null reference at all, then why is my ref null in the code above?

ZurdoDev 8-Oct-15 9:44am

The only time I've seen reader be null right after calling ExecuteReader() is if an exception is thrown.

Mudit Khetan 8-Oct-15 11:51am

There were no exceptions thrown in ExecuteSelect() method. The call cmd.ExecuteReader() went fine and during trace, the local reader object had data in it.

ZurdoDev 8-Oct-15 11:53am

If it had data in it, it wasn't null, so now I'm confused.

Mudit Khetan 8-Oct-15 12:24pm

Same here. My only confusion is, if the data was present in the ExecuteSelect() method's local reader object, which is returned, why is it not available in the returned object?

ZurdoDev 8-Oct-15 12:38pm

It will be. You'll have to debug it and see where it becomes null.

I still recommend not passing DataReaders around.

Mudit Khetan 8-Oct-15 12:33pm

I have posted another related problem below. I did not know where to post it as the earlier one and the new are in same file and project. Sorry for posting it in the solution section!

Richard Deeming 8-Oct-15 14:37pm

You can't dispose of the SqlCommand and SqlConnection objects until you have finished using the SqlDataReader object. Since you've put both in a using block, the SqlDataReader you return from the method cannot be used.

You should either return a DataSet, or use an iterator method to return each record.

Mudit Khetan 8-Oct-15 23:42pm

Thank you very much. That solves the problem. Thanks to RyanDev as well for suggesting the use of data tables in place of readers.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mudit Khetan · Answer 1 · 2015-10-08T06:30:00

Another problem related to the ExecuteNonQuery() method of the above DBUtility class.
In the code below, i have two methods - btnUpdate_Click() and btnInsert_Click() - both doing almost same work, except that one uses and SQl "UPDATE" statement while the latter uses the "INSERT" sql.
Not the funny part is, code for INSERT executes and its data is reflected in the DB. The Code for UPDATE also executes, it returns that one row was affected by the statement, but the changes do not reflect in the DB at all.

protected void btnUpdate_Click(object sender, EventArgs e) {		
	string updateCommand = "UPDATE stud_master SET firstName = @Fname, lastName = @Lname, emailId = @Email, city = @City WHERE StudID = @ID";
	SqlParameter[] parameters = new SqlParameter[] {
		new SqlParameter("@ID", txtID.Text),
		new SqlParameter("@Fname", txtFName.Text),
		new SqlParameter("@Lname", txtLName.Text),
		new SqlParameter("@Email", txtEmail.Text),
		new SqlParameter("@City", txtCity.Text)
	};

	DBUtility dbUtility = new DBUtility();
	if (DBUtility.ExecuteNonQuery(updateCommand, CommandType.Text, parameters) > 0) {
		lblStatus.Text = "Record Successfully Updated.";
	}
	else {
		lblStatus.Text = "Cannot add new Record. Operation failed.";
	}
}

protected void btnInsert_Click(object sender, EventArgs e) {
	string insertCommand = "INSERT INTO stud_master VALUES(@ID, @Fname, @Lname, @Email, @City)";
	SqlParameter[] parameters = new SqlParameter[] {
		new SqlParameter("@ID", txtID.Text),
		new SqlParameter("@Fname", txtFName.Text),
		new SqlParameter("@Lname", txtLName.Text),
		new SqlParameter("@Email", txtEmail.Text),
		new SqlParameter("@City", txtCity.Text)
	};

	Int32 rowsAffected = dbUtility.ExecuteNonQuery(insertCommand, CommandType.Text, parameters);
	if (rowsAffected > 0) {
		lblStatus.Text = "Record Successfully added.";
	}
	else {
		lblStatus.Text = "Cannot add new Record. Operation failed.";
	}
}