Using Statements and Database Connections C# with Example

The using keyword ensures that the resource defined within the statement only exists within the scope of the 
statement itself. Any resources defined within the statement must implement the IDisposable interface. 
These are incredibly important when dealing with any connections that implement the IDisposable interface as it 
can ensure the connections are not only properly closed but that their resources are freed after the using 
statement is out of scope. 
Common IDisposable Data Classes 
Many of the following are data-related classes that implement the IDisposable interface and are perfect 
candidates for a using statement : 
SqlConnection,SqlCommand,SqlDataReader, etc. 
OleDbConnection,OleDbCommand,OleDbDataReader, etc. 
MySqlConnection, MySqlCommand, MySqlDbDataReader, etc. 
DbContext 
 

All of these are commonly used to access data through C# and will be commonly encountered throughout building 
data-centric applications. Many other classes that are not mentioned that implement the same 
FooConnection,FooCommand,FooDataReader classes can be expected to behave the same way. 
Common Access Pattern for ADO.NET Connections 
A common pattern that can be used when accessing your data through an ADO.NET connection might look as 
follows : 
// This scopes the connection (your specific class may vary) 
using(var connection = new SqlConnection("{your-connection-string}") 
{ 
// Build your query 
var query = "SELECT * FROM YourTable WHERE Property = @property"); 
// Scope your command to execute 
using(var command = new SqlCommand(query, connection)) 
{ 
// Open your connection 
connection.Open(); 
// Add your parameters here if necessary 
// Execute your query as a reader (again scoped with a using statement) 
using(var reader = command.ExecuteReader()) 
{ 
// Iterate through your results here 
} 
} 
} 
Or if you were just performing a simple update and didn't require a reader, the same basic concept would apply : 
using(var connection = new SqlConnection("{your-connection-string}")) 
{ 
var query = "UPDATE YourTable SET Property = Value WHERE Foo = @foo"; 
using(var command = new SqlCommand(query,connection)) 
{ 
connection.Open(); 
// Add parameters here 
// Perform your update 
command.ExecuteNonQuery(); 
} 
} 
Using Statements with DataContexts 
Many ORMs such as Entity Framework expose abstraction classes that are used to interact with underlying 
databases in the form of classes like DbContext. These contexts generally implement the IDisposable interface as 
well and should take advantage of this through using statements when possible : 
using(var context = new YourDbContext()) 
{ 
// Access your context and perform your query 
var data = context.Widgets.ToList(); 
}