Using (SqlDataReader rdr = SqlHelper. ExecuteReader (SqlHelper. ConnectionStringLocalTransaction, CommandType. Text, SQL _SELECT_ITEMS_BY_PRODUCT, parm )){
// Scroll through the results
While (rdr. Read ()){
ItemInfo item = new ItemInfo (rdr. getString (0), rdr. getString (1), rdr. getInt32 (2), rdr. getDecimal (3), rdr. getString (4), rdr. getString (5), rdr. getString (6), rdr. getString (7 ));
// Add each item to the arraylist
ItemsByProduct. Add (item );
}
}
Public static SqlDataReader ExecuteReader (string connectionString, CommandType cmdType, string cmdText, params SqlParameter [] commandParameters ){
SqlCommand cmd = new SqlCommand ();
SqlConnection conn = new SqlConnection (connectionString );
// We use a try/catch here because if the method throws an exception we want
// Close the connection throw code, because no datareader will exist, hence
// CommandBehaviour. CloseConnection will not work
Try {
PrepareCommand (cmd, conn, null, struct type, plain text, commandParameters );
SqlDataReader rdr = cmd. ExecuteReader (CommandBehavior. CloseConnection );
Cmd. Parameters. Clear ();
Return rdr;
}
Catch {
Conn. Close ();
Throw;
}
}
First, initialize an object in the using statement and save its reference in a variable. then, access the variable in the braces matching the using statement. when this code is compiled, the compiler automatically generates a try block and a finally block. in the finally block, the compiler will generate code to transform the variable into an IDisposable interface and call the Dispose method on the interface. obviously, the using statement can only be used for the types that implement the IDisposable interface.
The preceding Code initializes a SqlDataReader object in the using statement, which is returned by the ExecuteReader () method. This method contains such a CommandBehavior. closeConnection does not close conn if no exception occurs. However, after the using statement is executed, the SqlDataReader's Dispose () method is automatically called. In this method, SqlDataReader is disabled, when SqlDataReader is disabled, CommandBehavior. closeConnection, the corresponding conn will also be closed, without causing a waste of resources.