CHAPTER 21 ADO.NET PART I: THE CONNECTED LAYER
In the preceding code, you pass your connection object as a parameter to a new static helper
method in the Program class named ShowConnectionStatus(), which you implement as follows:
static void ShowConnectionStatus(SqlConnection cn)
{
// Show various stats about current connection object.
Console.WriteLine("***** Info about your connection *****");
Console.WriteLine("Database location: {0}", cn.DataSource);
Console.WriteLine("Database name: {0}", cn.Database);
Console.WriteLine("Timeout: {0}", cn.ConnectionTimeout);
Console.WriteLine("Connection state: {0}\n", cn.State.ToString());
}
While most of these properties are self-explanatory, the State property is worth special mention.
You can assign this property any value of the ConnectionState enumeration, as seen here:
public enum ConnectionState
{
Broken, Closed,
Connecting, Executing,
Fetching, Open
}
However, the only valid ConnectionState values are ConnectionState.Open and
ConnectionState.Closed (the remaining members of this enum are reserved for future use). Also, it is
always safe to close a connection where connection state is currently ConnectionState.Closed.
Working with ConnectionStringBuilder Objects
Working with connection strings programmatically can be cumbersome because they are often
represented as string literals, which are difficult to maintain and error-prone at best. The Microsoftsupplied ADO.NET data providers support connection string builder objects, which allow you to establish
the name/value pairs using strongly typed properties. Consider the following update to the current
Main() method:
static void Main(string[] args)
{
Console.WriteLine("***** Fun with Data Readers *****\n");
// Create a connection string via the builder object.
SqlConnectionStringBuilder cnStrBuilder =
new SqlConnectionStringBuilder();
cnStrBuilder.InitialCatalog = "AutoLot";
cnStrBuilder.DataSource = @"(local)\SQLEXPRESS";
cnStrBuilder.ConnectTimeout = 30;
cnStrBuilder.IntegratedSecurity = true;
using(SqlConnection cn = new SqlConnection())
{
cn.ConnectionString = cnStrBuilder.ConnectionString;
cn.Open();
833