Working with Data—Databases
Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio
| CSharp-Online.NET:Articles |
| Database Articles |
|
© 2006 Jeffery Suddeth |
Contents |
Databases
Most business applications need to access some kind of a database. That database could be a flat file, a directory of files, or it could be a server program that handles data requests from client programs. The later case is most common because database servers are optimized for managing large amounts of data and many concurrent connections. In the case of Microsoft Access, the tables are contained within a file with the MDB extension and the "server" is the JET database engine that your program communicates with using the classes that ADO.NET provides.
A database consists of a collection of tables. Each table is defined as a set of columns and a collection of rows. Each row has a field of data for each column in its table.
A relational database is a database that defines relationships between the tables. Consider the logical relationship between a Customer table and an Orders table. A customer can have many orders, but each order can belong to only one customer. This type of relationship is called one-to-many.
The Customer record can be thought of as the parent record of each Order record belonging to that customer. If you delete the Customer record while it still has orders in the Orders table, then those orders would be orphaned records. A relational database gives us the ability make that logical relationship a physical one by putting constraints or rules on the table, such as not allowing a Customer record to be deleted if it has any Order records, preserving the integrity of the database.
A database table usually defines a primary key to uniquely identify every record in the table. The primary key is normally a combination of one or more fields in the table. If a table contains child records that are linked back to a parent record, then the value of that parent’s primary key is usually stored in the child record. It is that field that provides the link to the parent record. When a table contains a field that stores a primary key from some other table, that field is called a foreign key.
Consider two tables that store Customers and Orders. The Customer table may be defined to hold customer information and a primary key. Table 15.1 shows what a Customer table might look like.
Table 15.1 - Definition of the Customer Table
Field Name Data Type CustomerIDint, auto counter, primary keyNamestringAddressstringPhonestring
The Customer table is defined to store the Name, Address, and Phone of a Customer, as well as a CustomerID field that will uniquely identify a record in the table. The CustomerID field is the primary key.
The Customer and Orders table have a parent-child relationship, with Customers being the parent table. Each Customer can have one or more Orders, giving the tables a one-to-many relationship. The definition of the Orders table is below.
Table 15.2 - Definition of the Order Table
Field Name Data Type OrderIDint, auto counter, primary keyCustomerIDint, foreign keyOrderDateDateTimeShipDateDateTime
The Orders table definition stores the OrderID as its primary key. It also stores the CustomerID as the foreign key to link it back to the customer record of the customer who placed the order.
The Northwind Trader Database
An example of a database is the Northwind Trader database, which you can install with Microsoft Access. The Northwind Trader database is a sample database that contains tables for Customers, Orders, Employees, and other data commonly used by businesses. We will use the Northwind Trader database in the examples that follow.
Figure 15.1
 
|
