Chapter 5: ADO Command Strategies | Ado Examples and Best Practices

Team-Fly

Overview

WHEN WE GET TO CHAPTERS 6 and 7, which are all about Recordsets, you'll learn lots of different ways to execute queries—many of which don't require use of the Command object. The one important case where the Command object is required is when you have to capture parameters returned from stored procedures. ADO is very smart when it comes to handling stored procedures, so if you aren't returning OUTPUT parameters, and if you don't care about the stored procedure return status, you don't have to construct a Command object.

However, there are advantages to using Command objects. In this chapter, you'll learn that the Command object leverages new SQL Server and MDAC technology to execute all kinds of queries more efficiently. This means that when you execute rowset-returning or action queries, setting up the Command object can make the process of managing the query and its parameters far more sane. After a Command object is created, you no longer have to worry about putting single quotes around strings or figuring out how to deal with embedded apostrophes. This means you won't have to remove all of the Irish surnames from your database—such as O'Malley or O'Brien. This chapter also discusses how you can tell what your query is doing. We'll spend a significant amount of time pouring over SQL Profiler logs to see exactly what unnatural acts SQL Server is being asked to perform.

When it comes time to execute your SQL query, the best object to use is often the ADO Command. However, as you'll learn, it's not always the best choice. Fortunately, due to ADO's flexibility, there are other alternatives to draw on, as I discuss when we get to the Recordset object in Chapter 6. One thing you might not know—because it's under documented—is that all ADO Command objects appear as methods on their associated Connection objects. This innovative technique (well, it was stolen from RDO) enables you to code the Command by name, followed by its parameters, followed by the Recordset to contain the rowset. Cool. I discuss how to set this up later in this chapter.

Team-Fly