ADO.NET provides classes that allow you to retrieve and manipulate data from databases for use in your code.
ADO.NET was designed to use a disconnected data architecture, meaning that information is retrieved and stored locally, to diminish use of resource-intensive database connections.
A database is a structured repository of information, and a relational database is a database that organizes the data into tables.
The tables in relational databases are further divided into rows, where each row represents a single record, and columns, which represent categories of data.
The primary key in a table is a column containing values that are unique for each record in that table.
A foreign key is a column that serves as the primary key for a different table, and helps to create one-to-many relationships among data in separate tables.
Normalization is the process of removing redundant information from records into separate tables, which reduces complexity and speeds up the retrieval process.
Constraints set limitations on data to avoid data conflicts and errors.
SQL is a language commonly used to access and manipulate databases. The fundamental operation in SQL is the query.
Defining filters with a query allows you to retrieve specific subsets of information.
Using a join in a query allows you to retrieve data based on membership in more than one table.
In C#, the
DataSetobject represents a subset of data retrieved from the database.The
DataSetobject contains a collection calledTables, which in turn containsDataTableobjects.The
DataTableobject contains a collection calledColumns, which containsDataColumnobjects, and a collection calledRows, which containsDataRowobjects.The
Rowscollection allows you to examine the results of your query, usually by iterating over the collection with a loop.The
DataAdapteris a class that forms a bridge between the database and theDataSetclass, using a connection string and a query string. TheDataAdaptercan then be used to populate theDataSetobject using theFill( )method.
As we said at the beginning of this chapter, data access is a complex topic, and this chapter just scratches the surface of it. There’s plenty more to explore, beyond the simple SQL commands we showed you here. The remarkable thing about the SQL you learned in this chapter is that it opens up a different way of thinking about data access—using a query to extract and filter just the data you want. Once you get the hang of thinking in queries, it’s pretty simple. It’s a methodology that could be applied outside the database, to other kinds of data objects. In fact, it has been—it’s called LINQ, it’s new to C# 3.0, and it’s the subject of the final chapter.