Connecting to a Data Store

I l @ ve RuBoard

In the following sections, we'll use the Northwind database that ships with the .NET Framework SDK. To access this data source, we'll use the classes from the SqlClient namespace. We'll highlight any differences between the use of the classes in the SQL and OLE DB libraries where they are relevant.

Connecting to a Data Source (OLE DB and SQL)

To connect to a database, you must create a SqlConnection object. The simplest way to do this is to pass a connection string as a parameter to the SqlConnection constructor, as shown here:

 StringconString= "datasource=(LOCAL); initialcatalog=Northwind; integratedsecurity=SSPI; persistsecurityinfo=False; workstationid=(LOCAL);packetsize=4096"; SqlConnectioncon=newSqlConnection(conString); 

Many settings can be defined in the connection string. The most frequently used values are shown in Table 7-2. If you're using Visual Studio .NET, you can specify New Connection as the ConnectionString property of a connection (in the Properties window). The Data Link Properties dialog box that appears will, in effect, launch a wizard for creating a connection string.

Table 7-2. Elements of the ConnectionString Property for SQL Server Connections

Property	Description
Data source	The server name
Integrated security	The name of the authentication service to be used
Initial catalog	The database name
Persist security info	Indicates whether to persist security credentials
User ID	A SQL Server login
Connect timeout	The number of seconds within which a connection to the server must be established; an error is returned if the connection can not be made within this time

You can also create a SqlConnection object without passing any parameters. You can then set the ConnectionString property, which the SqlConnection object exposes, to specify the properties needed to connect to the database. Once you set the properties, you can connect to the database by calling the SqlConnection object's Open method. Note that the Open method might throw an InvalidConnectionException (which indicates that the connection is already open) or an SqlException (which is thrown when the underlying data provider returns an error or warning).

The following code example is taken from the sample file SimpleSqlConnection.jsl. As you can see, you should always invoke the Close method of the SqlConnection object. Alternatively, you can invoke the SqlConnection object's Dispose method, but as you'll see shortly, the Close method is the preferred approach.

 SqlConnectionconnection=null; //ConnecttoanSQLdatasource try { connection=newSqlConnection(); //WeneedtosettheConnectionStringproperty connection.set_ConnectionString("datasource=(LOCAL);" +  "initialcatalog=Northwind;" +  "integratedsecurity=SSPI;" +  "persistsecurityinfo=False;" +  "workstationid=(LOCAL);" +  "packetsize=4096"); //TheOpenmethodusesthepropertiesoftheconnectionstring connection.Open(); Console.WriteLine("Openedconnectionsuccessfullyto " + connection.get_DataSource()); } catch(System.Exceptionex) { Console.WriteLine("Failedtoopenconnection: " +ex); //Thefinallyblockwillclosetheconnection } finally { if(connection!=null) { //Finally,closetheconnection connection.Close(); } } 

Connections and Visual Studio .NET

Visual Studio .NET provides excellent support for ADO.NET components . To create a connection object and set its connection string properties, follow this simple procedure:

1. Drag an OleDbConnection or a SqlConnection control from the Data tab of the Toolbox onto the design area.

2. View the control's properties, and then select Connection String.

3. Select New Connection from the drop-down list to the right of the property name.

4. In the Data Link Properties dialog box, set the appropriate properties for the connection.

Some examples of connections created using Visual Studio .NET are provided in the sample project DataApplication. This sample has one OleDbConnection and two SqlConnection objects. One of the SqlConnection objects was created using the New Connection option, and the other was created by selecting an existing connection string from the list supplied in the Connection String drop-down list.

Connection Pooling

Pooling connections is one way to enhance the scalability of an application and improve its performance. Both the OLE DB and SQL providers support connection pooling, and in fact both provide connection pooling automatically. To disable connection pooling, you must add the following to the ConnectionString used to instantiate your connection:

 Pooling=false 

When you call the Open method of a connection, the connection pooler checks to see whether a connection pool exists for connections with exactly the same connection string as that of the connection you're working with. If no pool exists, the connection pooler creates one and fills it with connection objects. The number of connection objects added to the pool depends on the values of two properties: Max Pool Size and Min Pool Size . The default value for Max Pool Size is 100 , and the minimum is . You can change the defaults by passing values in the ConnectionString to the connection constructor as key/value pairs. For example, the following code creates a connection string that defines a maximum pool size of 50 connection objects and a minimum of 1:

 ("datasource=(LOCAL); initialcatalog=Northwind; integratedsecurity=SSPI; persistsecurityinfo=False; workstationid=(LOCAL);packetsize=4096 MaxPoolSize=50;MinPoolSize=1); 

If the connection string matches that of a connection pool, a connection object is served from the pool. As more connections are requested , they're also taken from the pool, and if necessary the number of connections in the pool is increased to match the demand for connections. Once the maximum number of connections is reached and requests for connections can no longer be honored, the requests are placed in a queue until they can be fulfilled by connections returning to the pool. To return a connection to the pool, you must explicitly call its Close method.

Consuming Connection Events

Connection objects can fire two types of events: InfoMessage and StateChange . You can use the InfoMessage event to retrieve messages from a database. You can also retrieve warnings from the database if these warnings do not result in an OleDbException or SQLException being thrown. The following code fragment shows an event handler that writes the details of the messages returned by the data source to the console:

 privatevoidsqlConnection_InfoMessage(Objectsender, SqlInfoMessageEventArgse) { //gettheerrorscollection SqlErrorCollectionsec=e.get_Errors(); //getthemessagecount intmsgCount=sec.get_Count(); //iteratethroughmessages for(inti=0;i<msgCount;i++) { Console.WriteLine("INFO: " +sec.get_Item(i).ToString()); } } 

This code is provided as part of the ConnectionEvents.jsl sample file. It illustrates that the System.Data.SqlClient.SqlInfoMessageEventsArgs object exposes an SqlErrorCollections property. The object retrieved through this property contains a collection of error messages returned by the underlying data source. The example calls the get_Count method to get the total number of messages the SqlErrorCollection contains, and then it calls the get_Item method to get the next error message.

The second type of event, StateChange , occurs when the state of a connection changes, such as when it closes . The following code fragment, also from the ConnectionEvents.jsl sample file, illustrates the use of this event. The event handler in the code takes a System.Data.StateChangeEventArgs (for both SQL and OLE DB providers), which exposes two useful properties: OriginalState and CurrentState . In this example, the code prints out both the original and current states to the standard output:

 privatevoidconnection_StateChanged(Objectsender,StateChangeEventArgse) { Console.WriteLine("StateChanged"); Console.WriteLine("\tOriginalState: " +e.get_OriginalState()); Console.WriteLine("\tCurrentState: " +e.get_CurrentState()); } 

I l @ ve RuBoard