Summary

 

Chapter 9 - Data Access with .NET
bySimon Robinsonet al.
Wrox Press 2002

  

The subject of data access is a large one, especially in .NET as 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.

Firstly, we explored the use of the Connection object, through the use of both the 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.

After illustrating how to connect to and disconnect from the data source, we then discussed how to do it properly, so that scarce resources, such as database connections, could be closed as early as possible. Both of the connection classes implement the IDisposable interface, called when the object is placed within a using clause. If there's one thing I'd like you to take away from this chapter is the importance of closing database connections as early as possible.

We then discussed database commands, through examples that executed with no returned data, to calling stored procedures with input and output parameters. Various execute methods were described, 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 can be represented in XML for transport, and in addition, methods are available that will pass a minimal amount of data between tiers. The ability of having many tables of data within a single DataSet can greatly increase its usability; being able to maintain relationships automatically between master/details rows will be expanded upon in the next chapter.

Having the schema stored within a DataSet is one thing, but .NET also includes the data adapter that along with various Command objects can be used to select data into 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.

As XML and XSD schemas have become feverishly popular over the past couple of years , we discussed how to convert an XSD schema into a set of database classes using the XSD tool XSD.EXE that ships with .NET. The classes produced are ready to be used within an application, and their automatic generation can save many hours of laborious typing.

During the last few pages of the chapter we've gone through some best practices and naming conventions for database development. Although not strictly .NET- related , these were thought to be a worthwhile inclusion. A set of conventions should always be adhered to when programming, whether in C# against a SQL Server database or in Perl scripts on Linux.

Armed with this knowledge, we're now in a good position to move on to the next chapter, where we'll explore the use of Visual Studio and .NET's Windows Forms data controls.