till now I always used a similar structure to get data from DB and fill a DataTable
public static DataTable GetByID(int testID)
{
DataTable table = new DataTable();
string query = @"SELECT * FROM tbl_Test AS T WHERE T.testID = @testID";
using (SqlConnection cn = new SqlConnection(Configuration.DefaultConnectionString))
{
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.Add("@testID", SqlDbType.Int).Value = testID;
cn.Open();
table.Load(cmd.ExecuteReader());
}
return table;
}
Now I saw some warnings in the build analysis:
TestService.cs (37): CA2000 : Microsoft.Reliability : In method 'TestService.GetByID(int)', object 'table' is not disposed along all exception paths. Call System.IDisposable.Dispose on object 'table' before all references to it are out of scope.
TestService.cs (42): CA2000 : Microsoft.Reliability : In method 'TestService.GetByID(int)', call System.IDisposable.Dispose on object 'cmd' before all references to it are out of scope.
Should I change my code in
public static DataTable GetByID(int testID)
{
DataTable table = new DataTable();
string query = @"SELECT * FROM tbl_Test AS T WHERE T.testID = @testID";
using (SqlConnection cn = new SqlConnection(Configuration.DefaultConnectionString))
{
using (SqlCommand cmd = new SqlCommand(query, cn))
{
cmd.Parameters.Add("@testID", SqlDbType.Int).Value = testID;
cn.Open();
table.Load(cmd.ExecuteReader());
}
}
return table;
}
What to do with DataTable object? Is it a good practice to place SqlCommand inside the using?
Thanks
Cheers
When you call "Dispose()" on a SqlConnection, internally, it calls "Close()", too. There is no worry - you are free to use Close() manually, or just let Dispose() do it for you. The contract of Dispose is basically that there is an understanding that calling it may render the object unusable for any future operations.
Net documentation for the OleDbCommand, Close() closes the connection and returns it to the connection pool, and Dispose() closes the connection and REMOVES it from the connection pool.
Deletes it from the memory.
You should also do this:
using (SqlDataReader reader =
cmd.ExecuteReader
(CommandBehavior.CloseConnection))
{
table.Load(reader);
}
when loading the table
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With