Where to use connection, dataset, datareader ?

[Doru Roman]

Hi,

I have some difficulties in understanding how to use the connection,
dataset, datareader in a form.
Let's suppose I want to create a datareader and when I push a button to
bring another row from the dataset.
Supposedly I create under MyForm_Load event the connection, then a command
and the reader.

SqlConnection conn = new SqlConnection(...);
conn.open();
SqlCommand cmd = conn.CreateCommnad();
cmd.CommandType = ...
cmdCommnadText = ....
SqlReader dr = cmd.ExecuteReader();

If I try to use dr.Read() in side a ButtonRead_Click event, dr is not
recognized.
So, what is the approach here, where I create and use the components to make
them work?
Thanks,
Doru

 

[Marina Levit [MVP]]

This is an issue of scope. Variables declared locally in a function cannot
be referenced outside that function. If something needs to be accessed in
multiple functions, it should be declared as a member variable of the class.

In the case of a data reader, I would not recommend having it be a class
level variable. For it to be of any use it would need to maintain an open
database connection, and that is not advisable. I would say just open the
connection in your click event as needed, run your query, get your data,
close the connection - all within the click event.

The problem you are describing is not actually related to ADO.NET in any
way, but in not understanding variable scoping. I recommend you pick up a
book on C# and read up on its structure and some coding techniques.

 

[Doru Roman]

I realize it is a matter of scope, that is why I asked for a solution in
case I need to navigate through the records at a push of a button. I gather
that this is not possible.

 

[Marina Levit [MVP]]

I told you how you can do it - declare the variables not inside a particular
function, but at the class level. So it's not impossible. I just warned
against having an open data reader just sitting around on a form, that is a
bad database access practice.

 

[Doru Roman]

I declared the datareader as a variable of the Form class and it works.

 

[Sahil Malik [MVP C#]]

Hey Marina,

Are you the Marina we've been seeing in this NG for so long now? If so, I'm
happy you got MVP . Congrats and well deserved indeed.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[W.G. Ryan eMVP]

Marina:

I noticed the same thing Sahil did. It's an honor to have you on board!

 

[Cor Ligthert [MVP]]

Marina,

I am happy for the recognition of your work.

Although that you was for me of course already onboard of these newsgroups
is it always nice to see as others as well award you for that.

My congratulations,

Cor

 

[Marina Levit [MVP]]

Thanks Sahil and William

I told you how you can do it - declare the variables not inside a
particular function, but at the class level. So it's not impossible. I
just warned against having an open data reader just sitting around on a
form, that is a bad database access practice.

 

[Marina Levit [MVP]]

Thank you Cor

Marina,

I am happy for the recognition of your work.

Although that you was for me of course already onboard of these newsgroups
is it always nice to see as others as well award you for that.

My congratulations,

Cor

 

[Marina Levit [MVP]]

Oh, and yeah, same Marina

Hey Marina,

Are you the Marina we've been seeing in this NG for so long now? If so,
I'm happy you got MVP . Congrats and well deserved indeed.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx