The Connection Object

Before any interaction occurs with the database, a connection to it must be established.

The connection informs the rest of ADO.NET code about the database it communicates with. It manages low-level logic associated with the protocols of various databases. This simplifies the overall process for the developer who merely instantiates the connection object, opens the connection, and closes it when finished. The design of ADO.NET classes also allows some of those connection operations to be avoided in certain situations.

Connections prove very simple, but still require an understanding of connections for correct use in data access routines. Connections are valuable resources. Users of a fairly small database number in the hundreds, and manipulate large data sets. Every connection carries overhead, with only a finite number of connections permitted at any one time.

CREATION

An SqlConnection is an object like any other in C#. It only requires declaration and instantiation in most cases. Review an example below:

SqlConnection conn = new SqlConnection (“Data Source =(local);Initial
Catalog=WorkingDatabase;Integrated Security=SSPI”;

The object employs a constructor with a single string-type argument, also known as a connection string. Review the common parts of a connection string below:

Parameter Name	Description
Data Source	This identifies the server as local, a domain name, or an IP address.
Initial Catalog	This specifies the database by name.
Integrated Security	When set to SSPI, this connects with a user's Windows login.
User ID	This provides the username for SQL Server.
Password	This provides the password associated with the SQL Server username.

The following example demonstrates how to form a connection string with a user ID and password:

SqlConnection conn = new SqlConnection (“Data Source=DatabaseServer;
Initial Catalog=MyDatabase;User ID=MyID;Password=MyPassword”);

USAGE

Connection objects enable the rest of ADO.NET code to work with the database, and other objects (e.g., SqlCommand and SqlDataAdapter) use the connection object as a parameter. An SqlConnection requires a certain sequence of events during the lifetime of the connection:

Instantiation – Review the procedure in examples above.
Open – Review an example below.
```
conn.Open();
```
Pass – Pass it to the command object by using it as a parameter. All operations using the object use the connection.
```
SqlCommand cmd = new SqlCommand(“select * from Clients”, conn);
```
Operate – Use the command object.
Close – Close the connection. Always close the connection when finished. Failing to close may have serious consequences.
```
conn.Close();
```

OTHER CONNECTIONS

Though this guide focuses on SQL Server, review the examples below demonstrating connection to different sources:

OleDb – The connection string format conforms to the standard connection string format with the exception of requiring a Provider keyword, and the lack of support for URL, Remote Provider, and Remote Server keywords. Review its syntax below.
```
using (OleDbConnection connection = new
OleDbConnection(connectionString))
{
	connection.Open();
}
```

Odbc – Review its syntax below.

using(OdbcConnection connection = new
OdbcConnection(connectionString))
{
	connection.Open();
}

Oracle – The connection string format conforms to the OleDb format. Review its syntax below.

using(OracleConnection connection = new
OracleConnection(connectionString))
{
	connection.Open();
}
OracleConnection dbConn = new OracleConnection(“Data
Source=MyServer;Integrated Security=yes;”);
dbConn.Open()

« Previous