New Connection Components

First let’s look at the differences in the two connection components. We’re not going to cover the basics, but we’ll focus instead on just the differences and where you might run into problems. Whether you are using the SqlConnection component or the OleDbConnection class, keep one thing in mind when developing your application: Are you using integrated security? If so, you might run into some problems when deploying your web page. Don’t forget that when your web page is running, the trusted user will be IUSR_COMPUTERNAME because this is the default account for IIS. If you specify a user account, it will be much easier for you to debug because you already know which user account you are using. If you use a separate account to log into the database, you will also have more control over the amount of security that user account has.

SqlClient.SqlConnection

This component was designed to be used specifically with Microsoft SQL Server and nothing else. One of the benefits that you get by using it is speed. SqlClient uses its own protocol to communicate with SQL Server. This eliminates the overhead and layers of OleDbConnection. Keep that in mind if you want to accomplish any specific task related to SQL Server, such as use a remote server.

One of the features available on SqlConnection that you don’t have in OleDbConnection is the Packetsize property. This can be very useful if you need to adjust the size of the network packets being sent. This is not something that you would typically change, but if you were sending large amounts of text or even images, you might want to increase the packet size. On the flip side, let’s say that you are developing a wireless site that will use very small chunks of data; you might want to adjust the packet size to a more efficient size because the default value is 8192 bytes. You might not think that this is that important, but when your web site starts to scale up and your traffic increases, attention to details like this will start to add up and make a big difference.

If you are having a problem connecting to SQL Server 6.5, that is because the SQLClient namespace does not support it; it supports only SQL Server 7.0 and higher. You will need to use the OleDb namespace to connect to earlier versions of SQL Server.

OleDb.OleDbConnection

One of the first problems you might run into is trying to use a data source name (DSN). This option is no longer supported in the .NET Framework, so if you want to transition your code over to .NET, keep in mind that you will have to replace all your connection strings that contain DSN.

If you compare the OleDbConnection string to its counterpart, it is almost identical, except for a few noticeable modifications in the use of keywords and properties. The major difference between the two is that the OleDbConnection component is designed to be backward compatible and to work with all databases that have an OLE DB driver. The following list contains these exceptions:

• You must use the Provider keyword.

• The URL keyword is not supported.

• The Remote Provider keyword is not supported.

• The Remote Server keyword is not supported.

Also if you take a close look at the properties you will notice that the PacketSize property is not available for the OleDbConnection object. If you happen to be using this with SqlConnection object, it will not port over to the OleDbConnection side.