Common Pitfalls

This section looks at some common issues that you might encounter when developing and tells how to work around them.

Working with Multiple Connections and Using Connection Pooling

Connection pooling is built into the .NET Framework. If you create all your connections with the same connection string, the system automatically pools the connections for you. But if the connection strings differ, you will get a new nonpooled connection. Keep this in mind when you are developing. If you create connections all over your code and they all point to the same location, keep a global connection string around so that you don’t start creating unmeaningful database connections and wasting resources. A good place to store your connection string would be in the Web.Config file under a custom section called appsettings.

Listings 16.21 and 16.22 illustrate when a new connection will be made and when an existing connection will be used or pooled.

Listing 16.21. Connection Pooling (C#)

SqlConnection conn = new SqlConnection(); 
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=Store"; 
conn.Open();       // Pool 1 is created. 

SqlConnection conn = new SqlConnection(); 
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=Orders"; 
conn.Open();       // Pool 2 is created 
// The second pool is created due to the differences in connection strings 

SqlConnection conn = new SqlConnection(); 
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=Store"; 
conn.Open();       // pool 1 is used. 

Listing 16.22. Connection Pooling (Visual Basic .NET)

Dim conn = new SqlConnection(); 
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=Store"; 
conn.Open();       ' Pool 1 is created. 

Dim conn = new SqlConnection(); 
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=Orders"; 
conn.Open();       ' Pool 2 is created 
// The second pool is created due to the differences in connection strings 

Dim conn = new SqlConnection(); 
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=Store"; 
conn.Open();       ' pool 1 is used. 

In some cases, you will need to create two separate connections to perform an operation. This might occur when you are in the middle of reading information from a data reader and want to make changes to the database as you iterate the data.

Should I Use a DataReader or DataAdapter?

You should ask yourself a few basic questions before you start grabbing data from the database. First, what do you need to do with the data? Do you need to change it, add to it, read it, or display it in a data grid? These are important questions to consider because they determine which component you can use, as opposed to which one you want to use.

If you want to display data in a data grid, your only option is to use the DataAdapter. This is because the DataAdapter is used to populate a dataset, which is the preferred method of populating a data grid.

If you just need to read the data row by row, then, by all means, use a DataReader object. Also note that this is a forward, read-only mechanism; if you want to edit data as you are navigating through the rows, you won’t be able to. The DataReader is strictly for reading data—hence, the name DataReader.

How Many Times Do You Really Need to Talk to the Database?

So what is the big deal about calling the database a bazillion times? You don’t see any impact while developing it. And some of the stress testing seemed to be fine, so it must be okay to do.

In my experience, the number of trips that you make to the database can have a significant impact on the performance—not to mention the scalability—of your web site. It doesn’t matter if it is a very small amount of data that is being retrieved from the database; if it is in a high-traffic page, it can—and usually will—come back to bite you later.

Let’s say that you have a web store, and you sell books. On the front page, you want to rotate books from a preselected group of books on sale. Currently all the information that you need is contained in a database. So you figure that you will just query the database every time someone requests the page. But what happens when you start to receive a lot of hits on your web site? I have seen this happen literally overnight! The next day traffic doubles and continues to climb day by day. These are the situations that you thought you saw only in TV commercials . But they are real, trust me!

If you stop to look at this situation, you might be only rotating 30 different books. So, after the 30th time to the database, you start doing redundant work. One solution is to keep on the web server an XML file of the books that are currently on sale. Then your server only needs to look on its own hard drive to retrieve the data that it needs. Now just write some custom code behind the web page to do the rotating and reading the data from the XML file.

If you are worried about new books going on sale and having to update the XML file, don’t worry. On MS SQL Server, you can use a trigger or schedule a task to check for new books on sale. If there are new items, the system can export the results to an XML file, and, presto—your web page automatically picks up the new XML file.

This is just one creative way to accomplish this task, but there are many other ways to accomplish the same thing. Just bear in mind the following suggestions:

• Keep it simple.

• Persist common data elements in memory or to a local file.

• Keep the number of trips to the database minimal.

• Keep the number of connection to a minimum.

Keep these points in mind when you are developing your web site, and think through what you are trying to accomplish. As much as you might want to jump in and start coding, it is always beneficial to have a plan can be called in a few different ways without using the parameter components. Here we focus on the issues with the command object and parameter collections, though.We could never understand why people used the parameter object to add parameters to their query when they could simply format a text string to do the same thing and use less code to accomplish the task. So what is the benefit of using parameters? Let’s take a look at the type of problems you might run into and how to avoid them.

Using Parameters with SqlCommand

When using parameters with SqlCommand, the names of the parameters must match the names of the parameter placeholders in the stored procedure. The SQL Server .NET Data Provider treats these as named parameters and searches for the matching parameter placeholders.

The SQLCommand class does not support the question mark (?) placeholder for passing parameters to a SQL statement or a stored procedure call. If you accidentally use the question mark, you will probably get the error shown in Figure 16.5.

In this case, named parameters must be used. Take a look at Listings 16.22 and 16.23 to see how you would properly implement a parameter using the SqlCommand class.

Listing 16.22. Implementing a Parameter Using the SqlCommand Class (C#)

SqlConnection conn = new SqlConnection("server=(local);database=north-
wind;Trusted_Connection=yes"); 
conn.Open(); 
//Build the Command using a named parameter 
SqlCommand myCommand2 = new SqlCommand("SELECT CategoryID, CategoryName 
FROM 
Categories where CategoryID > @param1", conn); 

//Add the parameter and value to the command object 
myCommand2.Parameters.Add("@param1",3); 

SqlDataReader myReader = myCommand2.ExecuteReader(); 

Listing 16.23. Implementing a Parameter Using the SqlCommand Class (Visual Basic .NET)

Dim conn = New 
SqlConnection("server=(local);database=northwind;Trusted_Connection=yes") 
conn.Open() 
'Build the Command using a named parameter 
Dim myCommand2 = New SqlCommand("SELECT CategoryID, CategoryName FROM 
Categories where CategoryID > @param1", conn) 

'Add the parameter and value to the command object 
myCommand2.Parameters.Add("@param1", 3) 

Dim myReader = myCommand2.ExecuteReader() 

In these listings, you will notice that when we created SqlCommand, we included a named parameter called @param1. The next line of code added the parameter value to the command statement by adding it to the parameter collection and specifying the parameter name and value that should replace it. Just remember that the OleDBCommand object does not operate the same way. Let’s take a look at the differences in the next section.

Using Parameters with OleDbCommand

When using parameters with OleDbCommand, the names of the parameters added to OleDbParameterCollection must match the names of the parameter in the stored procedure. The OLE DB .NET Data Provider treats these as named parameters and searches for the matching parameter marker.

The OLE DB .NET Data Provider does not support named parameters for passing parameters to a SQL statement or a stored procedure called by a command object . In this case, the question mark (?) placeholder must be used. Take a look at the following example:

SELECT * FROM Products WHERE ProductID = ? and price = ? 

It is important that the order in which parameter objects are added to the ParametersCollection must directly correspond to the position of the question marks in the SQL statement. Unlike the SqlCommand object, in which the parameters have names associated with them, you don’t have that option here.