Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

Ready to drill down into the classes you'll use to access SQL Server? As I said earlier, there are two general categories of data classes you'll use to access and manage information extracted from your data source. The classes used to connect to the data source, run the query, and return the data are implemented by the .NET Data Provider, as exposed by the System.Data.SqlClient namespace. These are described next. The classes used to manage the returned resultsets in memory are exposed in the System.Data namespace, which is discussed later in this chapter.

To make the discovery and exploration process easy, this section highlights the parts of the .NET Framework namespace that you'll most likely reference in your application. Sure, many of the classes exposed by the Object Browser are used by Visual Studio and other tools behind the scenes and might not be of much use to you unless you're writing your own tools, so let's focus on the handful of namespaces you will need to referenceat least, in applications accessing SQL Server.

• System.Data.SqlClient: This namespace exposes the SQL Server .NET Data Provider. Here, you'll leverage classes used to manage connections, execute SQL, and generate the System.Data classes used to manage the values returned from your queries and post changes back to the server.

• System.Data: This namespace contains the objects you'll use to manage data in memory. These classes permit you to bind, change, sort, filter, find, and refine your data.

• System.Data.SqlTypes: This namespace defines the SQL datatype structures, as well as the classes used (behind the scenes) to transmogrify a System.Type to a SqlType and back.

• Microsoft.SqlServer.Server: When you reach Chapter 13, "Managing SQL Server CLR Executables," you'll be guided through Common Language Runtime (CLR) development, where you can explore this namespace. It contains classes used to manage connections, commands, and data passed to and from CLR procedures.

The following summaries highlight the core (and most frequently used) SqlClient classes. I discuss each of these classes in detail in later chapters, so you can look forward to far more detail and a rich set of examples.

The SqlConnection Class

When you need to access data in a SQL Server database, you need to open a "connection" to it. In the most basic sense, a connection is simply a two-directional communications link to a specific instance of SQL Server from your application. When interacting with SQL Server, a connection transmits and receives provider-unique Tabular Data Stream (TDS) packets. As long as the connection is open, SQL Server dedicates a block of memory to the connection and monitors it for traffic generated when you tell ADO.NET to run a query, fetch a resultset, or any other T-SQL or utility operation. In some cases, SQL Server and the provider also perform background handshake operations to determine if the server and client are still working. When the connection is broken (on either end), the resources used by the connection are released (at least, on the server end).

Typically, a connection to SQL Server can handle only one "conversation" at a time. However, if you enable Multiple Active Result Sets (MARS), you might (just might) be able to hold several conversations at once on a single connectionwell, sort of. While MARS permits you to have more than one operation being executed on the same connection, the resultsets from these operations are simply interleavedthe command execution is still sequential. As with a telephone party line (where several customers share the same phone line), there are specific rules and protocols to observe so that the connection can be safely and efficiently shared. I discuss MARS in Chapter 9just don't jump the gun and start enabling it before you're sure it's necessary.

Because you can't reference any SQL Server data without a connection of some kind, one of the first SqlClient classes you'll have to work with is SqlConnection. It's important enough to devote Chapter 9 to the SqlConnection class. There, I discuss how to use the SqlConnection to establish a link between a specified SQL Server instance and how to manage the connection pool used by ADO.NET to optimize the connection process. Later in Chapter 13, I show how to use a "context" connection when coding SQL CLR procedures.

This SqlConnection class is used to capture the parameters needed to establish your application's credentials, point to a specific SQL Server instance or MDF filename, set a default database, and set many other options. These option/value pairs are stored as a string in the SqlConnection object's ConnectionString property. As I'll cover in Chapter 9, the ConnectionString determines what, who, and how your application gets connected, as well as if the SqlConnection is pooled or not. In ADO.NET 2.0, the SqlConnection class also exposes the underlying schema of the target DBMS using the GetSchema method. Again, this is a feature useful for building schema-driven tools and utilities.

The SqlConnectionStringBuilder Class

The 2.0 version of ADO.NET also includes another new classthe SqlConnectionStringBuilder. Developed for use behind the scenes in Visual Studio, this class can help build a ConnectionString without having to code a string containing the correct argument/value pairs. Because the SqlConnectionStringBuilder properties are strongly typed, you'll be able to construct this crucial ConnectionString more safely. I illustrate how to use this class in Chapter 9.

The SqlCommand Class

Once the connection has been established, you must use the SqlCommand class to execute T-SQL queries against an instantiated and open SqlConnection object. Unlike ADO classic, the only way to execute T-SQL is to use the SqlCommand object. Its properties describe the query, contain the T-SQL, and include a pointer to the connection. The SqlCommand is also used to manage client-side transactions. As shown in Chapter 11, "Executing SqlCommand Objects," a SqlCommand object can return a rowset in the form of a SqlDataReader, a single object value (a scalar), a data stream, XML, and, in some cases, a rows affected value. You also use SqlCommand objects to execute stored procedures, manage input parameters, and return their rowsets, OUTPUT, and RETURN value parameters. The higher-level classes, like the SqlDataAdapter and the Visual Studio-generated TableAdapter, use the SqlCommand object behind the scenes to execute queries.

The SqlParameter and SqlParameterCollection Classes

The SqlCommand class exposes a SqlParameter class used to manage query input, input/output, OUTPUT, and RETURN value parameters. In SQL Server, the SqlCommand object's Parameters collection manages a set of named parameters that are automatically passed to SQL Server at runtime so they can be inserted into the query as they are needed. This same set of SqlParameter objects can be automatically populated with OUTPUT parameters that carry the new Identity value. I illustrate how this is done in Chapter 13.

The SqlTransaction Class

The SqlCommand class can also help manage transactions through its Transaction property, which can point to a SqlTransaction object. This way, you can tie the success or failure of one or more "atomic" queries or database DML operations with other related operations. At any time, you can instruct SQL Server to accept or roll back the changes using SqlTransaction class methods. The SqlTransaction class is also used to create ADO.NET's form of pessimistic locking cursors. These are discussed in Chapter 11.

The SqlDataReader Class

This class is used to extract raw data from the TDS stream that is returned when you execute a query that returns a rowset or BLOB stream. The SqlDataReader is unusual, in that no Microsoft data access interface since DB-Library has exposed anything like itall pre-ADO.NET interfaces have hidden this raw data stream to make data access easier (albeit less flexible) for developers. Hard-coding the SqlDataReader means you might have to handle every aspect of connecting, resultset management, and fetching for each column one at a time. While the SqlDataReader can return rows very quickly, it can be code-intensive and expensive to support if you choose to address the individual rows and columns in code. In addition, since the SqlDataReader requires manual connection management, it makes applications more prone to errors caused by orphaned SqlConnection objects, as I discuss in Chapter 9. However, since ADO.NET 2.0 exposes the new DataTable and DataSet Load methods, it's relatively easy to eliminate costly row-by-row parsing code, as I illustrate in Chapter 11.

The SqlDataReader class is implemented in much the same way as other .NET Data Providers implement their specific DataReader objects. You'll also discover that the SqlBulkCopy class introduced with ADO.NET 2.0 can accept data from any of these DataReader streamswhether they're generated by SqlClient or by Odbc, OleDb, or some provider-specific .NET Data Provider.

The SqlDataAdapter Class

When you need to create an application that fetches data from a simple table-based data stream and (possibly) update that data, consider use of the SqlDataAdapter, shown in Figure 8.14.

For reasons I don't endorse or agree with, Microsoft has chosen to "hide" the SqlDataAdapter class wizards and several data-centric icons from the toolbox. While it's still fully functional, the SqlDataAdapter is now missing from its usual toolbox "Data" tab location.

Think of the SqlDataAdapter as a "super-class". It's one of the most sophisticated classes in ADO.NET and has been in place since the first version of the .NET Framework. Unlike the new Visual Studiogenerated TableAdapter, the SqlDataAdapter is a repository for a set of other classes that perform specific support tasks and supports methods to perform both simple DataTable population and update operations. The key here is that the SqlDataAdapter is designed to provide updateability to a single table's rowset, even though its DataSet is capable of containing many DataTable instances. In practice, the Visual Studio tools assume that you're populating the SqlDataAdapter, DataSet, and DataTable objects from a single table's rowset (hopefully with a subset of the table's rows). Yes, the SqlDataAdapter can fetch data from two or more tablesbuilding a separate DataTable for each rowset returned. The SelectCommand associated with the SqlDataAdapter can also execute a SELECT that returns the product of a JOIN or any number of unrelated rowsets. However, the SqlDataAdapter contains only one set of DML commands to change the target DataTable. This means when you execute the UPDATE method, you get only one set of action commands to executeeven though there might be 2 or 22 tables at the root of the rowset.

Database updateability assumes that the DBA has granted update rights to the base tablethey usually don't. This does lessen the usefulness of the SqlDataAdaptereven if you don't plan to use its Update method to post changes to the database.

The SqlDataAdapter Fill method is a powerful tool when fetching rows from several tables at once in a single round-trip. It's a great tool to populate lookup tables bound to pick lists in your UI.

You set up the SqlDataAdapter by using the Data Adapter Configuration Wizard (DACW) or by setting specific properties:

• The Connection property points to the SqlConnection object used to perform all query and update operations.

• The SelectCommand property points to a SqlCommand object that's responsible for fetching one or more rowsets, which usually contain a SELECT statement or a stored procedure. This SELECT is also used to auto-generate the DML queries.

• The UpdateCommand, InsertCommand, and DeleteCommand properties point to SqlCommand objects preset to execute appropriate DML queries that update the database to reflect changes made to the DataTable object populated by the SqlDataAdapter. The SqlCommandBuilder might be able to generate these if the SELECT is not too complexunless it's a Tuesday before the full moon.

• The UpdateBatchSize property determines how many rows are batched together for each round-trip to SQL Server. Increasing this value can dramatically improve Update performance by reducing round-trips but increase Update complexity in order of magnitude. See Chapter 12, "Managing Updates," for details.

• The ContinueUpdateOnError property indicates whether the Update operation should stop after the first exception or continue onwhich might leave your database or referential integrity dented. This defaults to stop on first exception.

• The FillLoadOption property determines how the Fill method deals with data values that already exist in the target DataTable objects. Since the Fill can merge data into existing DataTable objects, you can tell Fill to overwrite or preserve the current and original data values.

• By configuring the DataTableMappingCollection, your code can map the SQL Server table names from the SELECT to the column names created for the DataTable objects.

• The MissingMappingAction property determines how ADO.NET handles mis-matched columns.

• When a rowset is added to a DataSet and the target DataTable or DataColumn is missing, the MissingSchemaAction property determines how Fill behaves. It can be programmed to simply add the missing column, add the column and primary key, do nothing, or throw an exception.

• The ReturnProviderSpecificTypes property determines whether the Fill method returns provider-specific datatypes or CLS-compliant types. This property is new for ADO.NET 2.0 and is used to support the new SQL Server 2005 CLR executables.

• The TableMappings property determines how the DataTable names map to the rowsets returned by the SELECT queries. These can be used to map the DataTable instances to database table namesassuming that makes sense.

To make the process of configuring the SqlDataAdapter easier, Visual Studio (still) includes the Data Adapter Configuration Wizard, but it (still) sets only a few of the properties listed. While it's hard to find, you can reconfigure Visual Studio 2005 to place the DACW launch icons back on the toolbox menu (as discussed in Chapter 7, "Managing Data Tools and Data Binding") and step you through the process of building a SelectCommand. From there, the DACW uses the SqlCommandBuilder to at least attempt to generate the DML queries. Visual Studio augments this code to help manage identity values and updated rowsets but does not expose any new functionality to let you alter the default (and dumb) behavior of the SqlCommandBuilder. As I'll show you in Chapter 9, the SqlCommandBuilder is very limited in its flexibility. Most developers learn to build the DML queries themselvesor simply point to appropriate stored procedures. As already discussed, the new TableAdapter configuration wizard does expose a few more CommandBuilder switches to alter how concurrency is managed.

ADO.NET and Updates

When I discuss database updates in Chapter 12, I'll show how the SqlDataAdapter is designed to handle the update chores for a single database tablea subset of whose rows are managed in an associated DataSet and DataTable. No, there is no way to get the SqlDataAdapter to handle more sophisticated operations unless you code the DML action SqlCommand objects yourself. That's exactly what I discuss in Chapter 10, "Managing SqlCommand Objects"it's not that hard.

Even if you don't plan to change the target data, the SqlDataAdapter can help make your development faster and less trouble-prone. First, because the SqlDataAdapter automatically handles the SqlConnection object for you, your code won't have to open or close the connection. The SqlDataAdapter also knows how to handle complex SELECT queries that contain two or more SELECT statementseach is used to create a separate DataTable objectas long as you don't expect to update more than one of these tables.

Introducing the SqlDataAdapter Fill and Update Methods

The most powerful features of the SqlDataAdapter are the Fill and Update methods. These take on a long list of tasks to ensure that your code need not concern itself with the clearly defined process of populating DataTable objects from rowsets. Basically, the Fill method performs the following operations:

• Opens the specified SqlConnection (if it's not already open).

• Executes the SelectCommand query and returns a SqlDataReader behind the scenes.
• Using the schema returned by the SELECT, determines whether the target DataTable already exists in the DataSet. If it's already there, merges the two rowsets together; if not, creates a new DataTable object and adds it to the designated DataSet object's Tables collection.

• Populates the DataTable with rows returned by the hidden SqlDataReader. This is actually a complex process that I'll discuss in Chapter 11.

• Repeats this process for each resultset containing a rowset in the SelectCommand.

• Ensures that the SqlConnection is returned to the pre-Fill stateeven if the Fill trips an exception.

The SqlDataAdapter Update method is just as sophisticated. It's designed to help eliminate considerable code when using ADO.NET to post changes made to an in-memory DataSet and DataTable objects back to the database. The Update method in ADO.NET 2.0 has expanded functionality that permits it to process several DML changes in a single round-trip to the server. This promises to reduce the time wasted making a round-trip to the server for each change made to the DataTable. The operations performed by the Update method include:

• Scans the TableMappings to see which table is being referenced.
• Opens the specified SqlConnection (if it's not already open).
• Scans the DataTable object looking for rows that have been changed, added, or deleted. This is accomplished by checking the DataRow State property. If a row has been changed, the UpdateCommand is processed. Likewise, if a row has been added, the InsertCommand is processed; if a row has been deleted, the DeleteCommand is processed. If your referential integrity constraints (like primary key/foreign key relationships) require that you execute changes in a specific order, it's up to you to filter the rows being accessed by the Update method and repeat the Update method as many times as necessary to encompass changes to all parents and related children in the proper sequence (as discussed in Chapter 12).

• This "processing" means executing the designated SqlCommand immediately (using one round-trip to the SQL Server per row), or, if batch operations have been requested, the operations are batched at the TDS level and sent in a single round-trip.

• Once a row has been updated, the database can be queried (usually in the UpdateCommand or InsertCommand T-SQL) to determine the current value of selected columns in the database. These values are used to update the TimeStamp and Identity column(s) in the in-memory DataTable. You determine this behavior by setting specific properties of the SqlParameter instances associated with the UpdateCommand and InsertCommand. At this point, any child rows are updated to reflect the new parent identity.

• Fire events as rows are about to be changed or after they are changed. These events can be used to perform custom operations on the data or simply derail the Update process.

• The DataTable scan of the DataRow State property is continued until all rows have been processedas I said, this might take one round-trip to SQL Server for each change unless you have enabled batch updates.
• Ensure that the SqlConnection (as indicated by the State property) is returned to the pre-Update stateeven if the Update trips an exception(s).

As you can see, the SqlDataAdapter is a seemingly complex beast, but it can save you a lot of time and code when working with single-table DataTable objects. When you need to fetch rows from several database tables, you have an important choice to make. As I discussed in Chapter 1, you have to decide whether or not to use SQL Server's ability to create a rowset product by joining two or more database tables, or let ADO.NET informally "join" the two rowsets using DataRelation objects. I'll discuss this issue again and cover the rest of the SqlDataAdapter functionality in Chapters 10 and 11.

The SqlBulkCopy Class

Because so many developers try to use ADO.NET to move dozens to many millions of rows from place to place, Microsoft (wisely) decided to include a new class in ADO.NET 2.0SqlBulkCopy. For the first time since DB-Library, developers can program their applications to automate the process of uploading bulk data into SQL Server without having to learn how to program BCP or SSIS. Using SQL Server's unique bulkcopy mode, the SqlBulkCopy class passes data from a SqlDataReader up to a selected SQL Server table. I encourage you to look for places to leverage its powerit can shorten a day-long upload to a few minutes. SqlBulkCopy is designed to read data from any .NET Data Provider that exposes a DataReaderthat includes all of them. This means SqlBulkCopy can import from other SQL Server systems, Oracle, DB2, flat files, or even JET.

The SqlDependency Class

Another new feature of ADO.NET leverages the .NET Framework 2.0 and SQL Server 2005's ability to notify your code when selected data changes. This means your code can execute a typical (albeit focused) SELECT command and have an event fire when any server-side changes are made to the data in that rowset. I discuss this in Chapter 11 when describing how to execute SqlCommand objects.

The SqlError, SQLException, and SqlErrorCollection Classes

When your application throws a SqlException, you'll want to explore the properties of the SqlError class to see what went wrong. The SqlErrorCollection class is used to manage the set of SqlError objects returned in a SQLException.

The SQLClientFactory Class

In situations where you need to build a generic application, you can leverage the "factory" classes to generate appropriate provider-specific classes to implement your data access interfaces.