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

-----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-----

Heath Stewart

First, in either example, the DataTable is not disposed. Only the SqlDataAdapter is being disposed. Second, your problem in both cases is that you're passing a stored proc name for the selectCommand parameter - read the documentation: this won't work. It must be a SQL command and only a SQL command. Instead, try this:

public static DataTable GetTable()
{
SqlCommand cmd = myConnection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "StoredProcedure";
DataTable dt = new DataTable("Table");
SqlDataAdapter sda = new SqlDataAdapter(cmd);
using (sda) // Or using (SqlDataAdapter dsa = new SqlDataAdapter(cmd);
{
sda.Fill(dt);
return dt;
}
}

Also, if you're using a strongly-typed DataSet or DataTable, you'll also need to set up the SqlDataAdapter.TableMappings property.

-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----

Bill Priess

Hey Heath, Thanks for the quick reply... but... ;) According to the docs: selectCommandText A String that is a Transact-SQL SELECT statement or stored procedure to be used by the SelectCommand property of the SqlDataAdapter. I forgot to put in the fact that I use sda.SelectCommand.CommandType = CommandType.StoredProcedure. Anyhow... I've tried it a number of different ways, all with the same problem.. the DataTable never has rows in it. I understand what you are saying, but, if the data adapter gets disposed *after* the fill, why would the data table still return no rows? I did this and had the same results. public static DataTable getTable() { DataTable dt = new DataTable("table"); using (Config config = new Config()) //custom configuration class. { using (SqlConnection conn = new SqlConnection(config.SqlConnectionString)) { using (SqlDataAdapter sda = new SqlDataAdatper("Select * from table", conn)) { sda.Fill(dt); } } } return dt; } return dt; Now, from what I have read about the using statement, this pretty much translates to: public static DataTable getTable() { DataTable dt = new DataTable("table"); Config config = new Config(); try { SqlConnection conn = new SqlConnection(config.SqlConnectionString); try { SqlDataAdapter sda = new SqlDataAdapter(SqlDataAdatper("Select * from table", conn)); try { sda.Fill(dt); } finally { if (sda!= null) ((IDisposable)sda).Dispose(); } } finally { if (conn!= null) ((IDisposable)conn).Dispose(); } } finally { if (config != null) ((IDisposable)config).Dispose(); } return dt; } Believe it or not, when I run that code above, as is, it works fine. My table is populated and everyone is happy. Sorry to carry on, I am just trying to figure out the scope usage that MS wrote into the using statement. Any ideas? TIA, Bill P. Oakland, CA * * * -----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----- * * *

Heath Stewart

First of all, you don't need to worry about disposing managed objects all the time. The garbage collector will take care of that eventually. Disposing it just cleans it up immediately. Second, the using statement merely causes the Dispose method to be called on objects that implement IDisposable. The DataTable is not tied to a SqlDataAdapter (i.e., not a field or anything else that gets disposed). Because you're giving the DataTable a name, the result have to match that name to be filled. Use a TableMapping mapping or don't give the DataTable and name and see what you get. And yes, had you mentioned that you were setting the CommandType, I wouldn't have mentioned that. The default, however, is CommandText so it was worth mentioning from my perspective with the knowledge you provided.

-----BEGIN GEEK CODE BLOCK----- Version: 3.21 GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++ -----END GEEK CODE BLOCK-----

Bill Priess

Got it.. thanks for the assist. Unfortunatly, I have to worry about early disposal of my objects because I am running this on a shared server in the company and I need to really, really, REALLY manage my memory usage (***sigh grumble***)... Thanks Heath... :) Bill P. Oakland, CA

-----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-----