What to do from preventing SQLdatareader to return NULL?

Question

I have a problem with a method that looks like the following:

    public SqlDataReader statistiekenDocentBekijken(int docentid)
    {
        //Conectie met de database
        SqlConnection connectie = new SqlConnection("user id=bbbbb;" +
           "password=bbbbb;server=bbbbb;" +
           "Trusted_Connection=false;" +
           "database=bbbbb; ");

        SqlCommand statsDocenten = new SqlCommand("SELECT * FROM ENQUETE_ANTWOORD LEFT JOIN KDV ON ENQUETE_ANTWOORD.kdv_ID = KDV.kdv_ID LEFT JOIN DOCENT ON KDV.docent_ID = DOCENT.docent_ID LEFT JOIN VAK ON KDV.vak_ID = VAK.vak_ID WHERE DOCENT.docent_ID = "+ docentid +" ",connectie);
        SqlDataReader statsDocentenR;
        connectie.Open();
        statsDocentenR = statsDocenten.ExecuteReader();
        connectie.Close();
        return statsDocentenR;
    }

This method is located in a class Methods where I put all the methods in.

What I want is to return the content in the datareader and show it in a datagridview, located on a form that is called Mainform. My method currently returns null because I close the connection before I have returned anything. I don't really know what to do right now because I cannot close the connection in the Mainform that I have and I don't even know if this is the proper way to do this or that I maybe should return it in another way. I hope any of you guys can help me out with this. Thanks.

score 0 · Accepted Answer · answered Oct 16 '13 at 09:44

0

If I have read the problem correctly.

Add CommandBehavior.CloseConnection

Change the execute to read

statsDocentenR = statsDocenten.ExecuteReader(CommandBehavior.CloseConnection);

Remove connectie.Close();

The connection will close when the reader is closed.

answered Oct 16 '13 at 09:44

Fred

5,663
4
45
74

Does the reader closes itself when the value has been returned and the method is no longer in use? – Niek Jonkman Oct 16 '13 at 09:49
@NiekJonkman The GC will dispose of the reader and the connection will then be closed. However I would call the close method when I am finished with the reader `statsDocentenR.Close()` You never know how long the connection will remain open if you dont. – Fred Oct 16 '13 at 10:04

What to do from preventing SQLdatareader to return NULL?

1 Answers1