DataTable is getting null

Question

0.00/5 (No votes)

See more:

.NET3.5

Hi,
I'm preety new to .NET technology. Currently I'm building an .NET 3.5 application which has a three tier architecture. I'm facing a problem with a datatable. I'm using a datatable to bind it with a dropdown list. The problem is although the datatable is getting populated in DataAccess layer by the SP I've executed but while returning it to Business I'm getting null hence causing a null reference exception. My code is preety straight forward.

DataAccess Code:

public System.Data.DataTable FillData(string strConnString)
        {
            loginDataAccess loginDa = new loginDataAccess();
            SqlConnection Conn = loginDa.createConnection(strConnString);
            SqlCommand Cmd = new SqlCommand("proc_FillData", Conn);
            Cmd.CommandType = System.Data.CommandType.StoredProcedure;

            System.Data.DataTable DT = new System.Data.DataTable();
            SqlDataAdapter Sda = new SqlDataAdapter();

            try
            {
                Conn.Open();

                Sda.SelectCommand = Cmd;
                Sda.Fill(DT);

                if (DT.Rows.Count > 0)
                {
                   /* Here the datatable is populating but while returning to the business it is getting null*/
                    return DT;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                Cmd = null;

                if (Conn != null)
                {
                    Conn.Close();
                    Conn.Dispose();
                }
                if (Sda != null)
                {
                    Sda = null;
                    Sda.Dispose();
                }
             }
        }

SO, the DT is populating properly I've verified it by putting a debug point on the code region. but when I'm collecting this DataTable in business layer It is giving me an Null Reference Object exception. My Business layer code is as below.

 public System.Data.DataTable FillData(string strConnString)
        {
            
            
          qualityDataAccess fillDa = new qualityDataAccess();
            DataTable getauditReasonDt =new DataTable();

            try
            {
/* From here I'm calling the DataAccess layer method and getting a null back while in DataAccess the DataTable is getting populated perfectly*/
                getauditReasonDt = fillDa.FillData(strConnString);

                if (getauditReasonDt.Rows.Count > 0)
                {
                    return getauditReasonDt;
                }
                else
                {
                    return null;
                }
            }
            

            
            catch (Exception ex)
            {
                throw ex;
            }
            
            finally
            {
                fillDa = null;
                if (getauditReasonDt != null)
                {
                    getauditReasonDt = null;
                    getauditReasonDt.Dispose();

                }
          }          
        }

Please help me to resolve this issue as this is very urgent and need to be resolved ASAP.

Posted 6-Aug-10 2:33am

Bodan_

Updated 6-Aug-10 2:35am

v2

Add a Solution

Comments

[no name] 6-Aug-10 8:40am

It is very rude to ask for urgent help and for your problem to be resolved ASAP. This is a volunteer site and people will answer on their own time, not yours.

Bodan_ 6-Aug-10 9:04am

Hi Mark,

My apology if I hurt anybody's sentiment here. My intention was not to behave rude, It was urgent for me that's why I've mentioned like that. Please forgive me if it hurts anybody here as it was clearly not my intention.
Thank you for your solution.
Regards,
Subhadeep

3 solutions

Solution 2

The reason that Sda is beocomin null is because of this line

finally
{
....
if (Sda != null)
{
Sda = null;
Sda.Dispose();
}
}

Now Sda reffers to the same element as you are returning and finally always gets executed. So before returning the reference you are setting it to null and then disposing it hence your poor business layer is getting a null

Thus please remove these lines and your code will work.

Let me know how things turn out for u

Posted 6-Aug-10 7:50am

gaurav_verma_mca

Solution 3

So, gaurav is somewhat right, but not in the right spot of your code. Your problem is actually that you dispose of getauditReasonDT.

Here's what happens in memory (as an example) with your code.

You create getauditReasonDT which creates a pointer.

Address	Type	Data
0x1	Pointer	Null

You then set it to New Table() (which is total unnecessary since you later set it to something else.

Address	Type	Data
0x1	Pointer	0x2
0x2	DataTable	Blank DataTable

Then, you pass getauditReasonDT back which is really only passing the location of the data in memory (so in this case, it's just passing 0x2.

Then, you set dispose which disposes of the datatable in memory so those pointers have nowhere to point.

At least that's how I understand it.

And as far as I know, dispose of the DataAdapter doesn't dispose of the DataTable that it filled. I do this in my code all of the time and never have any problems.

Posted 6-Aug-10 11:35am

William Winner

Comments

Bodan_ 9-Aug-10 9:49am

Hi All,

Thank you for your tips to resolve this issue. My issue was resolved following Mark's suggetion. I don't know the exact reason why this was happend but currently the issue has been resolved. Thank you all for your support.

cheers,
Subhadeep

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)

Not Active · Accepted Answer · 2010-08-06T02:45:00

You have much more code than is necessary to accomplish the task. Try this

using(SqlConnection conn = new SqlConnection(connString))
{
   using(SqlCommand cmd = new SqlCommand("proc_FillData", conn) )
   {
      cmd.CommandType = CommandType.StoredProcedure;
      conn.Open();
      DataTable dt = new DataTable();
      dt.Load(cmd.ExecuteReader());

      return dt;
   }
}