Beginning Asp.Net 1.1 Databases

by Foggon, Damien

• ISBN13: 9781590593691
• ISBN10: 1590593693
• Format: Paperback
• Copyright: 2004-08-09
• Publisher: Apress

Summary

This book focuses on the all-important area of database programming with ASP.NET. The vast majority of ASP.NET developers and ASP.NET applications need to access a database quickly and efficiently - it's a huge skill to have as an ASP.NET developer, and this book focuses on this area and shows the expert's voice on the best way to achieve professional ASP.NET and database solutions. This book targets the skill sets that programmers specifically need in their ASP.NET programming career - offering the right skills that the reader requires in their work and growth towards becoming an expert.

Table of Contents

About the Authors	xv
About the Technical Reviewer	xvii
Acknowledgments	xix
Introduction	xxi
Part One Introductions	1	(78)
Chapter 1 Data Sources and the Web	3	(24)
Are Data-Driven Web Sites a Good Idea?	4	(3)
How Do Web Sites Use Data Sources?	7	(3)
Looking for Information	10	(3)
Database Servers	11	(1)
Flat Files	12	(1)
Services	12	(1)
Introducing ADO. NET	13	(7)
Data Access Technology: A Brief History	13	(2)
Data Providers	15	(5)
Developing Your First Example	20	(5)
Try It Out: Data-Driven Web Page No. 1	21	(3)
How It Works	24	(1)
Summary	25	(2)
Chapter 2 Introducing Relational Databases	27	(52)
Introducing Tables, Rows, and Fields	28	(16)
Creating a New Table	31	(12)
Properties and Constraints	43	(1)
Statements and Stored Procedures	44	(6)
Introducing Structured Query Language (SQL)	45	(5)
Stored Procedures	50	(1)
Indexes	50	(5)
Adding Indexes to a Database	52	(3)
Building Relationships Between Tables	55	(13)
One-to-One Relationships	57	(1)
One-to-Many Relationships	58	(1)
Many-to-Many Relationships	58	(3)
Enforcing Relationships with Foreign Key Constraints	61	(6)
Relationship Diagrams	67	(1)
Users, Roles, and Permissions	68	(5)
Adding a New User Account	70	(3)
Completing the Sample Database	73	(3)
Try It Out: Filling the Database with Web Matrix	74	(1)
Try It Out: Filling the Database with MSDE Administrator	74	(1)
Try It Out: Filling the Database with MySQL Control Center	75	(1)
How It Works	76	(1)
Views and Triggers	76	(1)
Summary	77	(2)
Part Two Core Skills	79	(322)
Chapter 3 Connecting to Data Sources	81	(38)
Connection Objects	81	(1)
Connection Strings	82	(1)
Connecting to the Data Source	83	(22)
Connecting to MSDE Using the SqlConnection Object	83	(7)
Connecting to MySQL Using the OdbcConnection Object	90	(4)
Connecting to Access Using the OleDbConnection Object	94	(3)
Connecting to Other Data Sources	97	(8)
The Connection Object in More Detail	105	(3)
The ConnectionString Property	106	(1)
The State Property	106	(1)
SQL Server and the PacketSize Property	106	(1)
There's More?	107	(1)
Storing the Connection String	108	(3)
Try It Out: Storing the Connection String in web.config	109	(1)
How It Works	110	(1)
Error Handling	111	(5)
Try It Out: Catching and Handling Errors	111	(3)
How It Works	114	(2)
Summary	116	(3)
Chapter 4 Commands and SQL Queries I	119	(36)
Introducing the Connection and Command Life Cycle	119	(1)
Introducing the Command Object	120	(1)
Creating a Command	121	(1)
Introducing Query Commands	122	(25)
Querying a Single Table	123	(5)
Ordering the Results	128	(4)
Querying Multiple Tables	132	(4)
Filtering the Results	136	(11)
Introducing the WHERE Clause	147	(3)
Using Comparison Operators	147	(1)
Using Logical Operators	148	(1)
Using the IN and BETWEEN Operators	148	(2)
Using the Execute Methods	150	(2)
Using ExecuteReaderQ and Closing Connections	151	(1)
Summary	152	(3)
Chapter 5 Commands and SQL Queries II	155	(28)
Using the ExecuteNonQuery() Method	156	(13)
Inserting Data	156	(7)
Updating Data	163	(4)
Deleting Data	167	(2)
Using the ExecuteScalar() Method	169	(4)
Scalar Functions	170	(3)
Using Microsoft Access to Build Queries	173	(8)
Try It Out: Constructing a SELECT Query in Microsoft Access	174	(4)
How It Works	178	(1)
Try It Out: Constructing an INSERT Query in Microsoft Access	179	(1)
How It Works	180	(1)
Summary	181	(2)
Chapter 6 DataReader and DataSet	183	(36)
The DataReader Object	184	(12)
Iterating Through a DataReader	185	(8)
DataReader Properties and Methods	193	(3)
The DataSet Object	196	(18)
How to Fill a DataSet	196	(4)
DataSet Components	200	(5)
Creating a DataSet from Scratch	205	(9)
DataSet vs. DataReader	214	(2)
Good Practices	216	(1)
Summary	217	(2)
Chapter 7 Displaying Data on a Page	219	(66)
Data Binding	220	(5)
Data-Aware Web Form Controls	221	(1)
Associating Data to Control	222	(2)
Calling DataBind()	224	(1)
Inline Binding	225	(11)
Try It Out: Inline Binding from a DataReader	225	(4)
How It Works	229	(2)
Try It Out: Inline Binding to a DataSet	231	(4)
How It Works	235	(1)
List Binding	236	(17)
Try It Out: Single-Value Lookup Lists with a DataReader	238	(4)
How It Works	242	(1)
Try It Out: Multiple-Value Lookup Lists with a DataReader	243	(3)
How It Works	246	(2)
Try It Out: Look Up Fields Using DataSets and Events	248	(3)
How It Works	251	(2)
Table Binding	253	(22)
Working the Controls	254	(3)
Customizing the DataGrid	257	(18)
Handling Multiple Queries	275	(6)
Try It Out: Handling a Batch Of Results	276	(2)
How It Works	278	(3)
DataSet vs. DataReader	281	(1)
Summary	282	(3)
Chapter 8 Writing Data from a Page	285	(42)
Making Changes to a Database	286	(1)
Adding New Data to the Database	287	(18)
INSERT Statements	287	(1)
Working to the Database's Rules	288	(17)
Deleting Data from the Database	305	(7)
SQL DELETE Statements	306	(1)
Sympathy for the User: DataGrid ButtonColumns	306	(6)
Editing Data	312	(14)
The UPDATE Statement	313	(1)
Building an Editable DataGrid	314	(12)
Summary	326	(1)
Chapter 9 Stored Procedures	327	(32)
Why Stored Procedures?	328	(1)
Creating Stored Procedures	329	(4)
Try It Out: Creating a Stored Procedure	329	(2)
How It Works	331	(2)
Security and Stored Procedures	333	(1)
Calling Stored Procedures	334	(3)
Try It Out: Calling a Stored Procedure	334	(2)
How It Works	336	(1)
Which Execute method?	337	(1)
Altering and Deleting Stored Procedures	337	(3)
Try It Out: Modifying a Stored Procedure	338	(2)
How It Works	340	(1)
Stored Procedures and Parameters	340	(18)
Making Flexible Stored Procedures with Input Parameters	341	(9)
Returning Data Using Output Parameters	350	(8)
Summary	358	(1)
Chapter 10 Modifying the Database Structure	359	(42)
Command-Line Tools	361	(6)
osgl.exe	361	(3)
mysql.exe	364	(3)
Creating Databases	367	(4)
Try It Out: Creating a Database	367	(3)
How It Works	370	(1)
Creating Tables	371	(7)
Try It Out: Creating Tables in MSDE	372	(2)
How It Works	374	(1)
Try It Out: Creating Tables in MySQL	375	(2)
How It Works	377	(1)
Adding, Modifying, and Removing Columns	378	(5)
Try It Out: Changing a Table Definition	379	(3)
How It Works	382	(1)
Creating and Deleting Indexes	383	(5)
Creating Indexes	384	(2)
Deleting Indexes	386	(2)
Creating and Deleting Relationships	388	(5)
Creating Relationships	388	(4)
Deleting Relationships	392	(1)
Deleting Tables	393	(4)
Try It Out: Deleting Database Tables	394	(2)
How It Works	396	(1)
Deleting Databases	397	(2)
Try It Out: Deleting a Database	397	(2)
How It Works	399	(1)
Summary	399	(2)
Part Three The Real World	401	(134)
Chapter 11 Application Design and Implementation	403	(28)
Analysis	404	(8)
Client Requirements	404	(5)
Choosing the Right Tools	409	(3)
Design	412	(12)
Database Design	412	(7)
Application Design	419	(5)
Implementation	424	(3)
Prototyping	424	(1)
Stored Procedures	425	(1)
Code Issues	426	(1)
Testing and Debugging	427	(3)
Unit Testing	428	(1)
Measuring Performance	429	(1)
Summary	430	(1)
Chapter 12 The Case Study, Part I	431	(38)
Introducing the Application	432	(5)
The Admin Pages	433	(4)
Designing the Database	437	(8)
The Initial Design	437	(1)
First Normal Form (1NF)	438	(1)
Relating the Tables: Second Normal Form (2NF)	439	(3)
Third Normal Form (3NF)	442	(2)
The Remaining Entities	444	(1)
Creating the Database	445	(2)
Try It Out: Using the Database Script	445	(1)
How It Works	446	(1)
Creating the Data Access Tier	447	(20)
Designing the Data Access Tier	448	(7)
Try It Out: Building the DataAccess Class	455	(5)
How It Works	460	(4)
Try It Out: Using the DataAccess Class	464	(2)
How It Works	466	(1)
Summary	467	(2)
Chapter 13 The Case Study, Part II	469	(66)
Error Handling	470	(9)
Catching Errors in Code Using try..catch..finally	471	(1)
Database Errors and @@ERROR	472	(1)
Catching Errors for an Entire Site	473	(1)
Try It Out: Handling Errors	474	(3)
How It Works	477	(2)
Stored Procedures and the DataAccess Class	479	(12)
Try It Out: Updating the DataAccess Class	480	(2)
How It Works	482	(2)
Try It Out: Adding Parameters to Database Calls	484	(4)
How It Works	488	(3)
Controlling User Access	491	(10)
Try It Out: Building the Security Class	492	(2)
How It Works	494	(2)
Try It Out: Using the Security Class	496	(4)
How It Works	500	(1)
Using Transactions	501	(33)
Transactions in the Database	503	(1)
Using Transactions in Code	504	(2)
Changes to the DataAccess Class	506	(5)
Editing an Element	511	(23)
Summary	534	(1)
Part Four Appendixes	535	(56)
Appendix A Installation	537	(10)
.NET 1.1	537	(1)
.NET ODBC Data Provider	538	(1)
Web Matrix	538	(1)
MSDE	539	(1)
SQL Web Data Administrator	540	(1)
MySQL	541	(2)
MySQL Control Center	543	(2)
MySQL Administrator	545	(1)
MySQL ODBC Connector	546	(1)
Appendix B SQL Primer	547	(26)
SELECT	548	(2)
INSERT	550	(1)
DELETE	551	(1)
UPDATE	552	(1)
CREATE DATABASE	553	(1)
CREATE TABLE	554	(1)
DROP DATABASE	555	(1)
DROP TABLE	555	(1)
ALTER DATABASE	556	(1)
ALTER TABLE	557	(1)
SQL Data Types	558	(15)
Text Types	559	(3)
Numeric Types	562	(4)
Date and Time Types	566	(2)
Binary Types	568	(2)
Miscellaneous Types	570	(3)
Appendix C Sample Database Tables	573	(10)
The Publisher Table	574	(2)
The Author Table	576	(2)
The Book Table	578	(2)
The WhoWroteWhat Table	580	(2)
Using the Database Scripts	582	(1)
Appendix D Webliography	583	(8)
Installation Links	583	(1)
Data Source Product Web Sites	584	(1)
Object-Oriented Data Stores	584	(1)
XML Databases	584	(1)
Relational Databases	584	(1)
Community-Based Development Sites	585	(1)
Chapter 2	586	(1)
Chapter 3	586	(1)
Chapter 6	587	(1)
Chapter 7	587	(1)
Chapter 9	587	(1)
Chapter 10	587	(1)
Chapter 11	588	(3)
Index	591