The Connection: Connecting to a Data Source
As you saw in Figure 4-1, the first object that interacts with a data source is the Connection object. You can bypass a Connection object by using a DataAdapter directly, but in that case a DataAdapter uses the Connection internally. The Connection class has a different name depending upon the data provider. The Connection class for OleDb, Sql, and Odbc are OleDbConnection, SqlConnection, respectively.
Creating a Connection
The SqlConnection class provides two forms of constructors. The first doesn't take any parameters, and the second takes a parameter of a connection string that points to the database to which you want to attach. The connection string contains the server name, database name, user ID, password, and more.
Listing 4-1 creates SqlConnection objects using both constructors.
Listing 4-1: Creating SqlConnection Objects
 |
Dim connString As String = "user id=sa;password=password;" & _ "initial catalog=northwind;data source=Northwind;Connect Timeout=30" Dim conn1 As SqlConnection = New SqlConnection(connString) Dim conn2 As SqlConnection = New SqlConnection() conn2.ConnectionString = connString
|
After creating a connection, you call its Open method, and when you're done with the connection, you call the Close method.
Understanding the Connection Properties and Methods
The Connection class (SqlConnection in this case) has a connection string that contains a server and a database name. The connection string will vary depending upon the provider used. The connection string will typically contain a group of property-value pairs to describe how to connect to a database. For the OleDbConnection class, you have properties such as Provider and DataSource. For the SqlConnection class, you have the server name, initial catalog, user ID, and password. Table 4-2 describes the Connection class properties. (Based on the data provider, some of these properties may not be applicable.)
| PROPERTY | DESCRIPTION |
|---|---|
| ConnectionString | The connection string. |
| ConnectionTimeOut | Waiting time while establishing a connection. |
| DataBase | Name of the current database. |
| DataSource | Filename of the data source. |
| Provider | Name of the OLE DB provider. This property is not available for the Sql and ODBC data providers. |
| State | Current state of the connection of type ConnectionState. |
| PacketSize | Size of network packets. This is only available to Sql data providers. |
| ServerVersion | SQL Server version. This is only available to Sql data providers. |
| WorkStationId | Database client ID. This is only available to Sql data providers. |
A typical connection for SQL Server may also contain the security type, the workstation ID, and even the network packet size. The following code is a connection string for a SQL Server called MCB:
connectionString = "data source=MCB;initial catalog=Northwind;" + "integrated security=SSPI;persist security info=False;" + "workstation id=MCB;packet size=4096";
The Connection can have different states such as open, closed, connecting, and so on. The ConnectionType enumeration defines the members of the ConnectionState. Table 4-3 describes its members.
| MEMBER | DESCRIPTION |
|---|---|
| Broken | Connection is broken after it was opened. This may be caused by network failure. |
| Closed | Connection is closed. |
| Connecting | Opening a new connection. |
| Executing | The connection is executing a command. |
| Fetching | Retrieving data from a data source. |
| Open | Connection is open and ready to use. |
Table 4-4 describes the Connection class methods. You'll see some of these methods throughout this chapter. The purpose of this table is to give you an idea of available methods.
| METHOD | DESCRIPTION |
|---|---|
| BeginTransaction | Begins a database transaction. |
| ChangeDatabase | Changes databases for an open connection. |
| Close | Closes an open connection. |
| CreateCommand | Creates and returns a Command objects depends on the data provider. For example, OleDbConnection returns OleDbCommand, and SqlConnection returns SqlCommand. |
| Open | Opens a new connection. |
| ReleaseObjectPool | Represents that the connection pooling can be cleared when the provider is released. This is only available for OleDb data providers. |
Opening and Closing a Connection
As you can see from Listing 4-1, the code creates two Connection objects: conn1 and conn2. It creates conn1 with no connection string. If you create a Connection object with no connection string, you must set its ConnectionString property before you call the Open method of the Connection object. The code creates the conn2 object with a connection string as an argument. As you can see from the connection string, it consists of a provider name and data source.
| Note | All the providers construct their connections in the same way. The thing that makes the connection construction different from other different providers is the ConnectionString. For example, the SqlClient namespace doesn't need to specify a provider string because Sql Server is always the database when using this class. |
After creating a Connection object, you call its Open method to open a connection. The Open method doesn't take any arguments. The following code opens a connection:
conn.Open()
When you're done with the connection, you call its Close method to release the connection. The Close method also doesn't take any arguments. The following code closes a connection:
conn.Close()
Listing 4-2 opens a connection with the Access 2000 Northwind database that resides in the C:\ directory. As you can see, you check the connection state to see if the connection is already opened (which is impossible in this code) or closed.
Listing 4-2: Connecting to an OleDb Database and Reading the OleDbConnection Properties
|
' Create a Connection Object Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\\Northwind.mdb" Dim conn As OleDbConnection = New OleDbConnection(ConnectionString) ' Open the connection If conn.State <> ConnectionState.Open Then conn.Open() MessageBox.Show("Connection state: " + conn.State.ToString()) End If ' Show the connection properties MessageBox.Show("Connection String :" + conn.ConnectionString & _ ", DataSource :" + conn.DataSource.ToString() & _ ", Provider :" + conn.Provider.ToString() & _ "," + conn.ServerVersion.ToString() & _ "," + conn.ConnectionTimeout.ToString()) ' Close the connection If conn.State = ConnectionState.Open Then conn.Close() MessageBox.Show("Connection state: " + conn.State.ToString()) End If ' Dispose the connection If (Not conn Is Nothing) Then conn.Dispose() End If |
| Caution | To utilize connection resources properly, it's always recommended to close open connections and dispose of connections explicitly if you don't intend to use them again. If you don't dispose of connections, the garbage collector will take care of it; however, the garbage collector waits for a certain interval of time. To force the garbage collector to dispose of connections, you call the Dispose method of the SqlConnection. The call to the Dispose method destroys the connection resource immediately. |
You can also use the OleDb data provider to connect to SQL Server or other databases if you have an OLE DB data provider installed for that database. Listing 4-3 shows the connection for a SQL Server using the OleDb data provider.
Listing 4-3: Connecting to a SQL Server Using the OleDb Data Provider
|
' Create a Connection Object Dim ConnectionString As String = "Provider=SQLOLEDB.1;" & _ "Integrated Security=SSPI;" & _ "Persist Security Info=false;" & _ "Initial Catalog=Northwind;" & _ "Data Source=MCB;" Dim conn As OleDbConnection = New OleDbConnection(ConnectionString) ' Open the connection If conn.State <> ConnectionState.Open Then conn.Open() MessageBox.Show("Connection state: " + conn.State.ToString()) End If |
Now you'll connect to a SQL Server database using the Sql data provider. For this example, you'll use the SQL Server 2000 Northwind database. Listing 4-4 shows the connection with the SQL Server database. As you can see, the code first constructs a connection string with the SQL Server name MCB and the database name Northwind. It uses Windows security in this connection, but depending on your SQL Server setup you could alternatively use a SQL Server login and password. After constructing a connection string, it calls the Open method to open the connection. After that it reads the connection properties and displays them in a message box. In the end, it calls the Close method to close the connection and the Dispose method to get rid of the connection resources.
Listing 4-4: Connecting to a SQL Server and Reading the SqlConnection Properties
|
' Create a Connection Object Dim ConnectionString As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) ' Open the connection If conn.State <> ConnectionState.Open Then conn.Open() MessageBox.Show("Connection state: " + conn.State.ToString()) End If ' Show the connection properties MessageBox.Show("Connection String :" + conn.ConnectionString & _ ", Workstation Id:" + conn.WorkstationId.ToString() & _ ", Packet Size :" + conn.PacketSize.ToString() & _ ", Server Version " + conn.ServerVersion.ToString() & _ ", DataSource :" + conn.DataSource.ToString() & _ ", Server Version:" + conn.ServerVersion.ToString() & _ ", Connection Time Out:" + conn.ConnectionTimeout.ToString()) ' Close the connection If conn.State = ConnectionState.Open Then conn.Close() MessageBox.Show("Connection state: " + conn.State.ToString()) End If ' Dispose the connection If (Not conn Is Nothing) Then conn.Dispose() End If |
You can even pass a user ID and password in a connection string. For example, the following connection string uses a user ID and password:
Dim ConnectionString As String = "user id=sa;password=pass;" & _ "Initial Catalog=Northwind;Data Source=MCB;"
| Note | As you can see, we used MCB as the data source name. You can use localhost as your local server. |
Understanding Connection Pooling
Connection pooling is a mechanism to utilize and share connection resources already available in a connection pool. In general, if an application needs a connection, it creates a new connection. There's a whole process of establishing a new connection. When the application is done using a connection, it closes the connection. Connection pooling plays a vital role in a three-tier development. For example, in a three-tier development, say you're using a Web server. The opening and closing of a connection consumes time and server resources in this scenario. Now say the application executes only a SELECT statement, which returns only a few records from the database. It may be possible that establishing a connection with the server may take more time than executing the query. This situation gets worse when there are hundreds of users accessing the same server. Another scenario is that a user logs into a server and then stays connected, never logging out. So, having a connection open until a user logs out is not a feasible solution.
So how does connection pooling help in this scenario? In the .NET Framework, when an application closes a connection, the connection goes to a pool of available connections and stays active for a certain interval of time even after you close the connection. When a user connects, the provider compares the connection string to the connection strings in the pool. If the string matches, then a connection is returned from the pool instead of creating and opening a new connection.
The connection pooling mechanism works differently for different data providers. The Connection class defines members that allow you to pool connection resources manually.
OLE DB provides automatic connection pooling (also known as session pooling), which is handled by OLE DB core components through its providers.
In ADO, you set the value of the OLE DB Services parameter, which includes connection pooling as well. This is an ADO connection string passed to the Open method of an OLEDBConnection:
Dim conn As OLEDBConnection = Nothing Conn.Open("DSN=LocalServer;UID=sa;PWD=;OLE DB Services=-1") Table 4-5 describes the values for the OLE DB Services parameter.
| SERVICES ENABLED | VALUE |
|---|---|
| All services (default) | "OLE DB Services = –1;" |
| All services except pooling | "OLE DB Services = –2;" |
| All services except pooling and auto enlistment | "OLE DB Services = –4;" |
| All services except client cursor | "OLE DB Services = –5;" |
| All services except client cursor and pooling | "OLE DB Services = –6;" |
| No services | "OLE DB Services = 0;" |
For example, the following string disables the connection pooling:
Dim connString As String = "Provider = SQLOLEDB OLE DB Services = -2;" & _ "Data Source = MCB; Integrated Security = SSPI "
ADO .NET manages connection pooling automatically when you call the Close or Dispose method of a Connection object. Once a pool is created, you can add connections to this pool until the pool reaches its maximum size. You can define the maximum size of a connection pool using the connection string. If a pool reaches its maximum size, the next added connection will join a queue and wait until the pool releases an existing connection.
You create a pool when you call the Open method of a connection based on the connection string. If you're using the same database for two Connection objects, but the connection string is different (including spaces and single characters), each connection will be added to a different pool. For example, Listing 4-5 creates two connections: conn1 and conn2. The ConnectionString1 and ConnectionString2 connection strings are different for both connections. Because these connections have different connection strings, they'll be added to two different pools.
Listing 4-5: Creating Two Connections with Different Strings
|
' Create a Connection Object Dim ConnectionString1 As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;" & _ "Data Source=MCB;" Dim conn1 As SqlConnection = New SqlConnection(ConnectionString1) ' Create a Connection Object Dim ConnectionString2 As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Pubs;" & _ "Data Source=MCB;" Dim conn2 As SqlConnection = New SqlConnection(ConnectionString2) ' Open connections conn1.Open() conn2.Open() MessageBox.Show("Connection1 " + conn1.State.ToString()) MessageBox.Show("Connection2 " + conn2.State.ToString()) ' some code conn1.Close() conn2.Close() MessageBox.Show("Connection1 " + conn1.State.ToString()) MessageBox.Show("Connection2 " + conn2.State.ToString()) |
| Caution | You must always call the Close or Dispose method of Connection to close the connection. Connections that are not explicitly closed are not added or returned to the pool. |
Even though the .NET Framework automatically manages connection pooling, you can customize the connection pooling properties programmatically by passing them in a connection string or by setting them using the Connection properties. A typical connection string with connection pooling parameters looks like the following:
Dim ConnectionString As String = "user id=sa;password=pass;" & _ "Initial Catalog=Northwind;Data Source=MCB;" & _ "Pooling='true';Connection Reset='false';" & _ "Connection Lifetime=5;Min Pool Size=3;Max Pool Size=20;"
Some of the pooling settings are in the form of key-value pairs (see Table 4-6).
| KEY | DESCRIPTION |
|---|---|
| Connection Lifetime | Connection creation time is compared with the current time, and if time span exceeds the Connection Lifetime value, the object pooler destroys the connection. The default value is 0, which will give a connection the maximum timeout. |
| Connection Reset | Determines whether a connection is reset after it was removed from the pool. The default value is True. |
| Max Pool Size | Maximum number of connection allowed in the pool. The default value is 100. |
| Min Pool Size | Minimum number of connections allowed in the pool. The default value is 0. |
| Pooling | When True (the default), the connection is drawn from the pool or created if necessary. |
The OleDbConnection class provides a ReleaseObjectPool method you can use to free resources reserved for a connection. You call this method when this connection won't be used again. To call ReleaseObjectPool, first you call the Close method. Listing 4-6 shows how to use ReleaseObjectPool.
Listing 4-6: Calling ReleaseObjectPool
|
' Connection and SQL strings Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\\Northwind.mdb" Dim SQL As String = "SELECT OrderID, Customer, CustomerID FROM Orders" ' Create connection object Dim conn As OleDbConnection = New OleDbConnection(ConnectionString) conn.Open() ' do something conn.Close() OleDbConnection.ReleaseObjectPool() ' Dispose the connection If (Not conn Is Nothing) Then conn.Dispose() End If
|
Using the CreateCommand and ChangeDatabase Methods
The SqlConnection class provides two more useful methods: the CreateCommand and ChangeDatabase methods. The CreateCommand method creates a SqlCommand object, which will be associated with the Connection object. In general, when you create a SqlCommand object, you need to pass a SqlConnection argument to connect to a connection, but if you use the CreateCommand method of SqlConnection, you don't need to do so.
The following code creates a SqlCommand using the CreateCommand method of an already existing SqlConnection object:
Dim cmd As SqlCommand = conn.CreateCommand() cmd.CommandText = "SELECT * FROM Customers" ' Do something with the command
We discuss SqlCommand in more detail in the following section.
The ChangeDatabase method of the SqlConnection object is a relief to developers who frequently have to change the database to which they're connected. This feature was missing in previous technologies, and the only way out of this problem was to disconnect the connection and then reconnect to the new database. The ChangeDatabase method does the same thing, but it does so internally so that developers can continue using the same SqlConnection object. The following code opens a connection to the Northwind database and later changes the database source using the ChangeDatabase:
' Create a Connection Object Dim ConnectionString As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) ' Open the connection conn.Open() conn.ChangeDatabase("pubs") ' Do something with the command MessageBox.Show(conn.Database.ToString()) ' Close the connection and call Dispose conn.Close() conn.Dispose() 
EAN: 2147483647
Pages: 214