Potty Little Details

Just another WordPress.com weblog

Archive for the ‘ADO dot net’ Category

Obtaining BLOB Values from a Database [C#]

leave a comment »

The default behavior of the DataReader is to load incoming data as a row as soon as an entire row of data is available. Binary large objects, or BLOBs, need to be treated differently, however, because they can contain gigabytes of data that cannot be contained in a single row. The Command.ExecuteReader method has an overload which will take a CommandBehavior argument to modify the default behavior of the DataReader. You can pass CommandBehavior.SequentialAccess to the ExecuteReader method to modify the default behavior of the DataReader so that instead of loading rows of data, it will load data sequentially as it is received. This is ideal for loading BLOBs or other large data structures.

When setting the DataReader to use SequentialAccess, it is important to note the sequence in which you access the fields returned. The default behavior of the DataReader, which loads an entire row as soon as it is available, allows you to access the fields returned in any order until the next row is read. When using SequentialAccess however, you must access the different fields returned by the DataReader in order. For example, if your query returns three columns, the third of which is a BLOB, you must return the values of the first and second fields before accessing the BLOB data in the third field. If you access the third field before the first or second fields, the first and second field values will no longer be available. This is because SequentialAccess has modified the DataReader to return data in sequence and the data will not be available after the DataReader has read past it.

When accessing the data in the BLOB field, use the GetBytes typed accessor of the DataReader, which fills a byte array with the binary data. You can specify a specific buffer size of data to be returned, and a starting location for the first byte read from the returned data. GetBytes will return a long value, which represents the number of bytes returned. If you pass a null byte array to GetBytes, the long value returned will be the total number of bytes in the BLOB. You can optionally specify an index in the byte array as a start position for the data being read.

The following example returns the publisher id and logo from the pubs sample database in Microsoft SQL Server. The publisher id (pub_id) is a character field, and the logo is an image, which is a BLOB. Notice that the publisher id is accessed for the current row of data before the logo, because the fields must be accessed sequentially.

SqlConnection pubsConn = new SqlConnection(“Data Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs;”);

SqlCommand logoCMD = new SqlCommand(“SELECT pub_id, logo FROM pub_info”, pubsConn);

FileStream fs; // Writes the BLOB to a file (*.bmp).

BinaryWriter bw; // Streams the BLOB to the FileStream object.

int bufferSize = 100; // Size of the BLOB buffer.

byte[] outbyte = new byte[bufferSize]; // The BLOB byte[] buffer to be filled by GetBytes.

long retval; // The bytes returned from GetBytes.

long startIndex = 0; // The starting position in the BLOB output.

string pub_id = “”; // The publisher id to use in the file name.

// Open the connection and read data into the DataReader.


SqlDataReader myReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAccess);

while (myReader.Read())


// Get the publisher id, which must occur before getting the logo.

pub_id = myReader.GetString(0);

// Create a file to hold the output.

fs = new FileStream(“logo” + pub_id + “.bmp”, FileMode.OpenOrCreate, FileAccess.Write);

bw = new BinaryWriter(fs);

// Reset the starting byte for the new BLOB.

startIndex = 0;

// Read the bytes into outbyte[] and retain the number of bytes returned.

retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

// Continue reading and writing while there are bytes beyond the size of the buffer.

while (retval == bufferSize)




// Reposition the start index to the end of the last buffer and fill the buffer.

startIndex+= bufferSize;

retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);


// Write the remaining buffer.



// Close the output file.




// Close the reader and the connection.




Written by oneil

September 11, 2008 at 3:01 am

Posted in ADO dot net

Working with Connection Events [C#]

leave a comment »

The Connection object has two events you can use to retrieve informational messages from a data source or to determine if the state of a Connection has changed. The following table lists the events of the Connection object.




Occurs when an informational message is returned from a data source. Informational messages are messages from a data source that do not result in an exception being thrown.


Occurs when the state of the Connection changes.


You can retrieve warnings and informational messages from a data source using the InfoMessage event of the Connection object. Errors returned from the data source result in an exception being thrown. However, the InfoMessage event can be used to obtain messages from the data source that are not associated with an error. In the case of Microsoft SQL Server, any message with a severity of 10 or less is considered informational and would be captured using the InfoMessage event.

The InfoMessage event receives an InfoMessageEventArgs object containing, in its Errors property, a collection of the messages from the data source. You can query the Error objects in this collection for the error number and message text, as well as the source of the error. The SQL Server .NET Data Provider also includes detail about the database, stored procedure, and line number that the message came from.

The following code example shows how to add an event handler for the InfoMessage event.

SqlConnection nwindConn = new SqlConnection(“Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;”);

nwindConn.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);

protected static void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)


foreach (SqlError err in args.Errors)


Console.WriteLine(“The {0} has received a severity {1}, state {2} error number {3}\n” +

“on line {4} of procedure {5} on server {6}:\n{7}”,

err.Source, err.Class, err.State, err.Number, err.LineNumber,

err.Procedure, err.Server, err.Message);




The StateChange event occurs when the state of a Connection changes. The StateChange event receives StateChangeEventArgs that enable you to determine the change in state of the Connection using the OriginalState and CurrentState properties. The OriginalState property is a ConnectionState enumeration that indicates the state of the Connection before it changed. CurrentState is a ConnectionState enum that indicates the state of the Connection after it changed.

The following code example uses the StateChange event to write a message to the console when the state of the Connection changes.

nwindConn.StateChange += new StateChangeEventHandler(OnStateChange);

protected static void OnStateChange(object sender, StateChangeEventArgs args)


Console.WriteLine(“The current Connection state has changed from {0} to {1}.”,

args.OriginalState, args.CurrentState);


Written by oneil

September 11, 2008 at 2:59 am

Posted in ADO dot net

Connection Pooling for the SQL Server .NET Data Provider

leave a comment »

Pooling connections can significantly enhance the performance and scalability of your application. The SQL Server .NET Data Provider provides connection pooling automatically for your ADO.NET client application. You can also supply several connection string modifiers to control connection pooling behavior (see the section “Controlling Connection Pooling with Connection String Keywords” later in this topic).

Pool Creation and Assignment

When a connection is opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created.

In the following example, three new SqlConnection objects are created, but only two connection pools are required to manage them. Note that the first and second connection strings differ by the value assigned for Initial Catalog.

SqlConnection conn = new SqlConnection();

conn.ConnectionString = “Integrated Security=SSPI;Initial Catalog=northwind”;


// Pool A is created.

SqlConnection conn = new SqlConnection();

conn.ConnectionString = “Integrated Security=SSPI;Initial Catalog=pubs”;


// Pool B is created because the connection strings differ.

SqlConnection conn = new SqlConnection();

conn.ConnectionString = “Integrated Security=SSPI;Initial Catalog=northwind”;


// The connection string matches pool A.

Once created, connection pools are not destroyed until the active process ends. Maintenance of inactive or empty pools involves minimal system overhead.

Connection Addition

A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size.

When a SqlConnection object is requested, it is obtained from the pool if a usable connection is available. To be usable, the connection must currently be unused, have a matching transaction context or not be associated with any transaction context, and have a valid link to the server.

If the maximum pool size has been reached and no usable connection is available, the request is queued. The object pooler satisfies these requests by reallocating connections as they are released back into the pool. If the time-out period (determined by the Connect Timeout connection string property) elapses before a connection object can be obtained, an error occurs.

CAUTION You must always close the Connection when you are finished using it. This can be done using either the Close or Dispose methods of the Connection object. Connections that are not explicitly closed are not added or returned to the pool.

Connection Removal

The object pooler will remove a connection from the pool if the connection lifetime has expired, or if the pooler detects that the connection with the server has been severed. Note that this can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid. The object pooler periodically scans connection pools looking for objects that have been released to the pool and are marked as invalid. These connections are then permanently removed.

If a connection exists to a server that has disappeared, it is possible for this connection to be drawn from the pool even if the object pooler has not detected the severed connection and marked it as invalid. When this occurs, an exception is generated. However, you must still close the connection in order to release it back into the pool.

Transaction Support

Connections are drawn from the pool and assigned based on transaction context. The context of the requesting thread and the assigned connection must match. Therefore, each connection pool is actually subdivided into connections with no transaction context associated with them, and into N subdivisions that each contain connections with a particular transaction context.

When a connection is closed, it is released back into the pool and into the appropriate subdivision based on its transaction context. Therefore, you can close the connection without generating an error, even though a distributed transaction is still pending. This allows you to commit or abort the distributed transaction at a later time.

Controlling Connection Pooling with Connection String Keywords

The ConnectionString property of the SQLConnection object supports connection string key/value pairs that can be used to adjust the behavior of the connection pooling logic.

The following table describes the ConnectionString values you can use to adjust connection pooling behavior.




Connection Lifetime


When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online.

A value of zero (0) will cause pooled connections to have the maximum time-out.

Connection Reset


Determines whether the database connection is reset when being removed from the pool. For Microsoft SQL Server version 7.0, setting to false avoids making an additional server round trip when obtaining a connection, but you must be aware that the connection state, such as database context, is not being reset.



When true, the pooler automatically enlists the connection in the current transaction context of the creation thread if a transaction context exists.

Max Pool Size


The maximum number of connections allowed in the pool.

Min Pool Size


The minimum number of connections maintained in the pool.



When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool.

Performance Counters for Connection Pooling

The SQL Server .NET Data Provider adds several performance counters that enable you to fine-tune connection pooling characteristics, detect intermittent problems related to failed connection attempts, and detect problems related to timed-out requests to your SQL Server.

The following table lists the connection pooling counters that can be accessed in Performance Monitor under the “.NET CLR Data” performance object.



SqlClient: Current # of pooled and non pooled connections

Current number of connections, pooled or not.

SqlClient: Current # pooled connections

Current number of connections in all pools associated with the process.

SqlClient: Current # connection pools

Current number of pools associated with the process.

SqlClient: Peak # pooled connections

The highest number of connections in all pools since the process started. Note: this counter is only available when associated with a specific process instance. The _Global instance will always return 0.

SqlClient: Total # failed connects

The total number of connection open attempts that have failed for any reason.

Written by oneil

September 11, 2008 at 2:54 am

Posted in ADO dot net