| .NET is a set of application interfaces implemented as a .NET class library. This class library is used by VB .NET (and the other managed code languages, C# and managed C++) to provide programmatic access to databases. There are three basic kinds of classes. -
Connection -
Command and DataAdapter -
DataSet Connection classes provide the properties and methods needed to open a channel to a database. This may be a network connection, in the case of SQL Server or other server-based database systems, or a file handle, in the case of desktop databases such as Microsoft Access (JET) databases or Excel files. Command and DataAdapter classes provide the means to execute queries and return data from the database objects referenced by the open database connection. DataSet classes (containing DataTable and DataView objects) provide a local data cache to store the results of queries that return data. The DataSet classes have no relationship to the Command or DataAdapter class that was used to retrieve the data. DataSet can be used as a stand-alone data storage object without any relationship to an external database. This is a very powerful set of classes that forms the heart of ADO .NET. The basic process for accessing data is to open a connection to a database using a Connection object, and then use a DataAdapter object to execute an SQL query against the database and stream the data into a DataTable inside a DataSet object. Then you would close the database connection. ADO .NET automates much of this processing. The basic process for updating a database from data residing in a DataTable inside a DataSet is to open a connection, then execute the Update method of the DataAdapter object and reference the DataSet object containing the data you wish to update. You can also execute stand-alone SQL statements that update data (Insert, Update, Delete) by using a Command object directly. Both the processes for reading and updating can use stored procedures instead of SQL statements. VB 6 VB 6 coders had a plethora of database access tools. There was Data Access Object (DAO), Remote Data Object (RDO), DAO Direct, and ActiveX Data Object (ADO). You also had a choice of whether to use object libraries, data controls, or a combination. Each of these tools came with a data control, a visual component that you could drop on a form. These provided simple database access and a visual navigation control. With VB .NET your choices are much simpler. Basically, you have ADO .NET. There is no visual navigation control in ADO .NET. (We will develop our own in Chapter 11.) You can choose which set of client classes to use: the SQL client classes, which provide high-performance access to SQL Server Version 7.0 and 2000 databases, or the OLEDB client classes, which provide access to older SQL Server databases, Oracle, Access, Excel, Flat files, and the other OLE DB providers, as well as any ODBC-compliant database system. There is a way to access these older technologies using VB .NET, through Component Object Model (COM) Interop. We will not be covering these in this book. If you need to port an application to VB .NET quickly, these may be of interest to you. Be warned that although you will have programmatic access to the object libraries, you will not be able to use the VB .NET control binding mechanism to bind controls to them. If your application really must still use ADO, RDO, or DAO, it is better left in VB 6. Any new applications should be developed using ADO .NET from the beginning.
The ADO .NET Class Library ADO .NET is implemented in the .NET environment as a set of classes which belong to the System.Data namespace. A namespace is a logical grouping of classes. A namespace usually compiles into a single dynamic link library (DLL) but it doesn't have to. When we want to access the classes in a namespace, we have to create a reference to it. This is similar to creating a reference to a COM object library in VB 6. The good news here is that when we create a project in VB .NET, the System.Data namespace is included automatically. The System.Data library is divided into three major branches as illustrated in Figure 1.1. The top level contains the classes that make up the DataSet object. The System.Data.SqlClient level contains the SQL Server-optimized SqlConnection and SqlDataAdapter classes. The System.Data.OleDb level contains the general-purpose OLEDB OleDbConnection and OleDbDataAdapter classes. Figure 1.1. The ADO .NET class structure (simplified).  The OleDb classes are for accessing non-SQL Server databases such as Oracle and Access. These two namespaces are on parallel levels. Since we will be using SQL Server in all of our examples, we will concentrate on the SqlClient classes. The OleDBClient classes are very similar; however, there are a few differences, mainly with how OleDBDataAdapter handles parameters. When I demonstrate these differences I will use an Access database (.mdb) file, since most of us would not have access to an Oracle database ( myself included). Let's take a quick tour: -
SqlConnection Handles all communications with the server, including connecting to databases, authenticating users and resource management. For ADO users, it is similar to the ADO Connection object. Connections are identified through a ConnectionString property. This contains information such as server name, database name, user name, and password. For you veterans , the Connection string is similar in format to an ODBC Data Source Name (DSN) string. The good news is that you don't have to remember the arcane syntax of the Connection string. The SqlConnection object will build one for you. -
SqlCommand Basically wraps an SQL statement. This statement can be a select query, a query that updates the database, or a stored procedure call. SqlCommand must reference an SqlConnection object for it to work. Because ADO .NET uses a disconnected architecture, the command object establishes a connection only when needed. You can force it to connect using the Open method of the Connection object. Use the SqlCommand object to execute SQL statements directly on the database. The most obvious use is for updating the database using dynamic SQL. This is where you build your SQL statement programmatically, then send it to the server for execution. I prefer this method because it provides the most control. The Command class also contains the parameters collection, which contains any parameter objects needed to execute the query, such as stored procedure parameters (input and output) and ODBC-style placeholder parameters within an SQL statement. -
SqlParameter Represents a parameter to be passed to a stored procedure or a placeholder in an SQL statement. You can create your parameters manually or have ADO .NET create them for you by parsing your SQL statement or by fetching them from the stored procedure. -
SqlDataAdapter Contains up to four SqlCommand objects, one each to Select, Insert, Update, and Delete. The DataAdapter class functions as a mediator between the DataSet and the database. Remember we said that ADO .NET is an inherently disconnected architecture? This class is used to fetch data from the database and fill the tables in the DataSet. It is also used to update the database with changes made to the DataSet. The DataAdapter is also responsible for marshaling, or converting, data types from native .NET types to those supported by the underlying database. -
DataSet The main class you will work with programmatically. The DataSet can be bound to Windows Forms controls and ASP .NET controls. It is roughly analogous to the Recordset object of ADO but it is much more powerful. The DataSet is more like an in-memory database all by itself. It contains tables, keys, constraints, and relations. As a matter of fact, you can use a DataSet as a stand-alone in-memory database. It has no direct relationship to any external database. The other important thing about DataSets is that they persist themselves as Extensible Markup Language (XML). You can save a DataSet to disk as an XML document. Likewise, when a DataSet needs to move from computer to computer across a network, it does so using an XML stream. Let's look at the subcomponents of the DataSet class. -
DataTable The DataTable is an in-memory representation of a database table. It contains rows and columns . It is a fully database system-independent representation of the data. In other words, no matter which in which database system your data resides (SQL Server, Oracle, etc.) the data will look the same. The DataTable contains the following collections: -
Columns collection A collection of DataColumn objects that define the metadata of the table. -
Rows collection The rows of data returned from the database or some other source. -
Constraints A collection of constraints placed on the columns. A constraint is a primary key, unique index, and so forth. These are inherited from the source database table. -
Tables collection A collection of all the DataTables in the DataSet. It is indexed numerically and by the table name. -
Relations Defines the relationship between DataTables. It is analogous to a foreign key or declarative referential integrity (DRI). The Relations collection contains all of the Relations objects in a DataSet. -
DataReaders There are two versions of this class: SqlDataReader and OleDbDataReader. Both provide a way to read forward-only cursorless data streams from a database. This is the most efficient way to read data from a database. The drawback is that the data cannot be updated using one of these classes. However if you need to read lots of data with blinding speed, and plan to use some other way of updating it or updating isn't necessary (with a report, for example), then this is the class to use. The class is also very useful in batch (command-line) programs. -
DataView Used to implement data binding. It also contains methods for navigating, editing, sorting, searching, and filtering the data in a Datatable. For you VB 6 veterans, many of the old Recordset methods can be found here. Each Datatable has a default view manager, but you can create several and apply them one at a time to the Datatable to quickly change sorts, filters, and so forth. This is a powerful class for displaying data. The General Process for Accessing a Database Creating an SQL Server or Microsoft Data Engine (MSDE) database connection, accessing and updating the data, and closing the connection take 18 steps: -
Create an instance of the SqlConnection class. -
Format a Connection string with the server name, database name, user name, and password. Assign the string to the ConnectionString property of the SqlConnection class. -
Create an instance of an SqlCommand class. -
Format a Select statement and assign it to the CommandText property of the SqlCommand class. -
Create another SqlCommand class. -
Assign the SqlCommand object's Connection property to the SqlConnection you just created. -
Format an SQL Update statement and assign it to the CommandText property. -
Create an instance of the SqlDataAdapter class. -
Assign the Select command object to the DataAdapter's SelectCommand property. -
Assign the Update command object to the DataAdapter's UpdateCommand object. -
Create an instance of a DataSet class. -
Open the connection using the SqlConnection object's Open method. -
Use the Fill method of the DataAdapter to fill the DataSet with a DataTable containing the data returned from the SQL Select command. -
Close the connection using the SqlConnection object's Close method. -
Using the DataSet's DataTables collection, programmatically manipulate the data in the Rows property of the DataTable. -
Open the connection again using the SqlConnection object's Open method. -
Use the Update method of the DataAdapter to flush the changes back to the database. -
Close the connection using the SqlConnection object's Close method.  |
