Summary

The subject of data access is a large one, especially in .NET, because there is an abundance of new material to cover. This chapter has provided an outline of the main classes in the ADO.NET namespaces and shown how to use the classes when manipulating data from a data source.

First, the Connection object was explored, through the use of both SqlConnection (SQL Server–specific) and OleDbConnection (for any OLE DB data sources). The programming model for these two classes is so similar that one can normally be substituted for the other and the code will continue to run. With the advent of .NET version 1.1, you can now use an Oracle provider and also an ODBC provider.

This chapter also discussed how to use connections properly, so that these scarce resources could be closed as early as possible. All of the connection classes implement the IDisposable interface, called when the object is placed within a using clause. If there’s one thing you should take away from this chapter, it is the importance of closing database connections as early as possible.

In addition, this chapter discussed database commands by way of examples that executed with no returned data to calling stored procedures with input and output parameters. It described various execute methods, including the ExecuteXmlReader method available only on the SQL Server provider. This vastly simplifies the selection and manipulation of XML-based data.

The generic classes within the System.Data namespace were all described in detail, from the DataSet class through DataTable, DataColumn, DataRow and on to relationships and constraints. The DataSet class is an excellent container of data, and various methods make it ideal for cross-tier data flow. The data within a DataSet is represented in XML for transport, and in addition, methods are available that pass a minimal amount of data between tiers. The ability to have many tables of data within a single DataSet can greatly increase its usability; being able to maintain relationships automatically between master/details rows is expanded upon in the next chapter.

Having the schema stored within a DataSet is one thing, but .NET also includes the data adapter that, next to various Command objects, can be used to select data for a DataSet and subsequently update data in the data store. One of the beneficial aspects of a data adapter is that a distinct command can be defined for each of the four actions: SELECT, INSERT, UPDATE, and DELETE. The system can create a default set of commands based on database schema information and a SELECT statement, but for the best performance, a set of stored procedures can be used, with the DataAdapter’s commands defined appropriately to pass only the necessary information to these stored procedures.

The XSD tool (XSD.EXE) was described, using an example that shows how to work with classes based on an XML schema from within .NET. The classes produced are ready to be used within an application, and their automatic generation can save many hours of laborious typing.

Finally, this chapter discussed some best practices and naming conventions for database development.

Further information about accessing SQL Server databases is provided in Chapter 27, “.NET Programming with SQL Server.”