block scope when using 'using' statement

[Bill Priess]

Hey gang,

Ok, I'm stumped on this one... I am using the using statement to wrap a
SqlDataAdapter that I am using to fill a DataTable. Now, what I need to know
is, just how much block-scope applies to objects created in the using scope.

For example:

<code>
static DataTable getTable()
{
using (SqlDataAdapter sda = new SqlDataAdapter("StoredProcedure", new
SqlConnection("ConnectionString")))
{
DataTable dt = new DataTable("table");
sda.Fill(dt);
return dt;
}
}
</code>

I know this would return a table with zero rows becuase it is created and
destroyed within the scope of the using statement. But...
<code>
static DataTable getTable()
{
DataTable dt = new DataTable("table");
using (SqlDataAdapter sda = new SqlDataAdapter("StoredProcedure", new
SqlConnection("ConnectionString")))
{
sda.Fill(dt);
}
return dt;
}
</code>

Now, when I call this, I also get back zero rows in my table, yet when I run
the query in SQL, it comes back fine. Anyone have any ideas or experience
with the using statement?


TIA,
Bill P.

-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCM/MU/B dpu s--:-- a32 C++++$ ULH+++ P+++ L++ E+ W+++$ N++ o K? w++++$ O--
M V-- PS+ PE+ Y++ PGP++ t++@ 5++@ X++ R+@ tv b++ DI++ D+++>++++ G++ e++
h---- r+++ y++++
-----END GEEK CODE BLOCK-----

 

[Miha Markic]

Hey gang,

Ok, I'm stumped on this one... I am using the using statement to wrap a
SqlDataAdapter that I am using to fill a DataTable. Now, what I need to know
is, just how much block-scope applies to objects created in the using

scope.


Hi Bill,

I think that both examples should return filled table.
Check your adapter it is configured properly...

 

[100]

Hi Bill,
SqlDataAdapter's Dispose method is called as soon as using block is exited
(doesn't matter how - return statement, exception thrown or just the block
ends). This means that your adapter is dsiposed just before you return it.
If you want to return the adapter don't use using statement nor dispose the
object. Let the caller decide what to do with it.

HTH
B\rgds
100

 

[100]

Ok. I was wrong . I miss to see that you are not returning the adapter
itself. Next time I'll look more carefully ;(

B\rgds
100

 

[Jerry Negrelli]

Does it work if you DON'T use the "using" statement? I
suspect that the issue isn't necessarily with
your "using" syntax but with something else...

JER

 

[Eric Newton]

try restructuring your code, where the SqlConnection is the target of the
using instead of the DataAdapter which doesnt actually need to be
disposed...

ie,
using( SqlConnection conn = new SqlCOnnection("ConnectionString") )
{
SqlDataAdapter da = new SqlDataAdapter( blah blah blah )

conn.Open();
da.Fill( dataset );
conn.Close();
} // calls conn.Dispose() even if exception occurs