4/12/2009
Objectives Knowledge · Explain how a table in a relational database is organized.
Chapter 12
· Explain how the tables in a relational database are related.
An introduction to database programming
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 1
The Products table in a Halloween database
· Describe the use of these SQL statements: Select, Insert, Update, and Delete. · Describe the use of these ADO.NET components: data adapter, command, connection, data reader, dataset, data table. · Describe concurrency, optimistic concurrency control, and “last in wins.”
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 2
How a table is organized · A relational database uses tables to store and manipulate data. · Each table consists of one or more records, or rows, that contain the data for a single entry. · Each row contains one or more fields, or columns, with each column representing a single item of data. · Most tables contain a primary key that uniquely identifies each row in the table. · Some database management systems let you define one or more nonprimary keys. In SQL Server, these keys are called unique keys, and they’re implemented using unique key constraints. · A table can also be defined with one or more indexes to efficiently access data based on the values in specific columns. An index is automatically created for a table’s primary and nonprimary keys.
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 3
Two related tables: Categories and Products
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 4
How the tables in a database are related · The tables in a relational database are related to each other through their key columns. A column that identifies a related row in another table is called a foreign key. · Usually, a foreign key corresponds to the primary key in the related table. In SQL Server, however, a foreign key can also correspond to a unique key in the related table. · When two tables are related via a foreign key, the table with the foreign key is referred to as the foreign key table and the table with the primary key is referred to as the primary key table. · The relationships between the tables in a database correspond to the relationships between the entities they represent. The most common type of relationship is a onetomany relationship. A table can also have a onetoone relationship or a manytomany relationship with another table.
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 5
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 6
1
4/12/2009
The Server Explorer design view window for the Products table
Common SQL Server data types · bit · char, varchar, text · datetime, smalldatetime · decimal, numeric · float, real · bigint, int, smallint, tinyint · money, smallmoney
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 7
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 8
The tables that make up the Halloween database
Simplified syntax of the Select statement Select column1 [, column2]... From table1 [Where selectioncriteria] [Order By column1 [Asc|Desc] [, column2 [Asc|Desc]]...]
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 9
A Select statement that retrieves and sorts data
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 10
The syntax of the Select statement for joining two tables
Select ProductID, Name, UnitPrice From Products Where CategoryID = 'Props' Order By UnitPrice
Select columnlist From table1 [Inner] Join table2 On table1.column1 {=||=|} table2.column2 [Where selectioncriteria] [Order By columnlist]
The result set defined by the Select statement
A Select statement that joins data from the Products and Categories tables Select ShortName, ProductID, Name, UnitPrice From Products Inner Join Categories On Categories.CategoryID = Products.CategoryID Order By Categories.CategoryID
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 11
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 12
2
4/12/2009
The result set defined by the Select statement
How to add a single row The syntax of the Insert statement for adding a single row Insert [Into] tablename [(columnlist)] Values (valuelist)
A statement that adds a single row to a table Insert Into Categories (CategoryID, ShortName, LongName) Values ('food', 'Spooky Food', 'The very best in Halloween cuisine')
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 13
How to update rows
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 14
How to delete rows
The syntax of the Update statement
The syntax of the Delete statement
Update tablename Set expression1 [, expression2]... [Where selectioncriteria]
Delete [From] tablename [Where selectioncriteria]
A statement that deletes a specified category
A statement that changes the value of the ShortName column for a selected row
Delete From Categories Where CategoryID = 'food'
Update Categories Set ShortName = 'Halloween cuisine' Where CategoryID = 'food'
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 15
Basic ADO.NET objects Dataset Data table
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 16
Two users who are working with copies of the same data
.NET data provider Data adapter
Command
Database server Connection
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Database
Slide 17
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 18
3
4/12/2009
Concurrency concepts
Concurrency concepts (continued)
· When two or more users retrieve the data in the same row of a database table at the same time, it is called concurrency. Because ADO.NET uses a disconnected data architecture, the database management system can’t prevent this from happening.
· If optimistic concurrency isn’t in effect, the operation proceeds without throwing an exception. This is referred to as “last in wins” because the last update overwrites any previous update. And this can lead to errors in the database.
· If two users try to update the same row in a database table at the same time, the second user’s changes could overwrite the changes made by the first user. Whether or not that happens depends on the concurrency control that the programs use.
How to avoid concurrency errors
· By default, ADO.NET uses optimistic concurrency. This means that the program checks to see whether the database row that’s going to be updated or deleted has been changed since it was retrieved. If it has, a concurrency exception occurs and the update or deletion is refused. Then, the program should handle the exception.
· If concurrency is likely to be a problem, a program can be designed so it updates the database and refreshes the dataset frequently. That way, concurrency errors are less likely to occur.
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
· For many applications, concurrency errors rarely occur. As a result, optimistic concurrency is adequate.
· Another alternative is to design a program so it retrieves and updates just one row at a time. That way, there’s less chance that two users will retrieve and update the same row at the same time.
Slide 19
ADO.NET components for accessing a database directly .NET data provider
© 2008, Mike Murach & Associates, Inc.
Slide 20
Common property and methods of the SqlConnection class Property Description ConnectionString Contains information that lets you connect to a SQL Server database, including the server name, the database name, and login information.
Data reader
Select command
Method Open Close
Database server Connection
Murach’s ASP.NET 3.5/VB, C12
Database
Description Opens a connection to a database. Closes a connection to a database.
Insert, Delete, or Update command
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 21
Common properties of the SqlCommand class
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 22
Common properties of the SqlParameter class
· Connection · CommandText · CommandType · Parameters
Property ParameterName
Description The name of the parameter.
Value SqlDbType
The value assigned to the parameter. The SQL data type for the parameter.
Common methods of the SqlCommand class · ExecuteReader · ExecuteNonQuery · ExecuteScalar
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 23
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 24
4
4/12/2009
Common properties and methods of the SqlDataReader class
Common properties of the SqlDataAdapter class
Property Item
Description Accesses the column with the specified index or name from the current row.
· DeleteCommand
IsClosed
Gets a Boolean value that indicates if the data reader is closed.
Method Read
Description Reads the next row. Returns True if there are more rows. Otherwise, returns False. Closes the data reader.
Close
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
· SelectCommand · InsertCommand · UpdateCommand
Common methods of the SqlDataAdapter class
Slide 25
· Fill · Update
Murach’s ASP.NET 3.5/VB, C12
© 2008, Mike Murach & Associates, Inc.
Slide 26
5
