c# database (access) return results from a function

Hi

I am new to c# and have (through help on this website ;o) ) created a function which connects to a database and performs an sql query.

this is fine for updates and inserts, but when I need to return data it doesn't work like I want it, I get errors because the code in the function is closing the connection, so the returned data 'expires'

here is the code:


public OleDbDataReader data_sql(string strsql)
        {
            OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\mydata.mdb");
            connection.Open();
            OleDbCommand cmd = new OleDbCommand(strsql);
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = connection;
            OleDbDataReader reader = default(OleDbDataReader);
            reader = cmd.ExecuteReader();
            connection.Close();

            return reader;

        }

Open in new window


how can I get the returned data available outside of the function?

Cheers

CD
cycledudeAsked:
Who is Participating?
 
Dale BurrellConnect With a Mentor DirectorCommented:
Remove the line connection.Close(). And change ExecuteReader to be ExecuteReader(CommandBehavior.CloseConnection).
0
 
DanielSVCommented:
Hi,

You need to read out the data before you close the connection.

After you do ExecuteReader();, do something like.

while(reader.Read())
{
// Pull out the data you want here.
}

See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executereader(v=vs.71).aspx for an example.
0
 
cycledudeAuthor Commented:
Thanks

I have changed the function slightly to return the result, then close the connection... that appears to be working.

have also tried dale_burrell's advice, and that works too.

Thanks ;o)
0
 
cycledudeAuthor Commented:
many thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.