Beginning C# Databases

by Huddleston, James; Raghuram, Ranga; Allen, Scott; Gilani, Syed Fahad; Pedersen, Jacob Hammer; Reid, Jon

• ISBN13: 9781590594339
• ISBN10: 1590594339
• Format: Paperback
• Copyright: 2004-11-30
• Publisher: Apress

Summary

The book flows from topic to topic very smoothly and stays on the subject. It is such an easy read. - Trish Middleton, .NET Programmer Thoughts No matter what programs you develop, there always exists a bottom line: you must know how to access and manipulate data. This book teaches you all the essential data manipulation skills that you will need when you code in C#. Data can be stored in many places, but large quantities of data that need to be frequently accessed are usually stored in relational databases such as SQL Server. Knowing how this data is structured, and how to access and update it, are therefore the most important programming tasks the professional programmer needs to learn. As well as teaching you database basics, such as using SQL to communicate with databases, this book provides you with detailed and code-practical techniques to access data in C# across a wide range of specific coding situations. Code-heavy and full of practical detail, this book has been fully revised and upgraded for .NET 1.1 and offers you the best contemporary practice in this core programming area that you'll find yourself using in nearly all your .NET projects.

Table of Contents

About the Authors	xix
Acknowledgments	xxi
Introduction	xxiii
Installing MSDE	1	(16)
Obtaining and Installing MSDE	1	(3)
Installing the Sample Databases	4	(4)
Installing the SQL Server Documentation	8	(3)
Introducing Authentication Modes	11	(1)
Windows Authentication	12	(1)
SQL Server Authentication	12	(1)
Mixed Mode	12	(1)
Connecting with Server Explorer	12	(3)
Summary	15	(2)
Creating a Simple Database Application	17	(22)
Creating the Application	17	(2)
Try It Out: Creating the Foundations	17	(2)
How It Works	19	(1)
Establishing a Database Connection	19	(4)
Try It Out: Creating a Connection	19	(3)
How It Works	22	(1)
Introducing Data Binding	23	(6)
Try It Out: Creating a User Interface	23	(3)
How It Works	26	(2)
Try It Out: Making the Form Dynamic	28	(1)
How It Works	29	(1)
Editing Data	29	(3)
Try It Out: Adding Edit Functionality	30	(1)
How It Works	31	(1)
Using the Data Form Wizard	32	(5)
Try It Out: Creating a User Interface with the Data Form Wizard	32	(5)
Summary	37	(2)
Introducing SQL	39	(24)
What Is SQL?	39	(1)
Using SQL	40	(5)
Executing SQL from Visual Studio .NET	41	(2)
Executing SQL from the Command Line	43	(2)
Using the Custom Query Tool	45	(1)
Retrieving Data	45	(10)
Using Queries	46	(3)
Using the WHERE Clause	49	(3)
Sorting Data	52	(3)
Inserting Data	55	(1)
Updating Data	56	(2)
Deleting Data	58	(1)
Using SQL Data Types	59	(2)
Numeric Data Types	60	(1)
Character String Data Types	60	(1)
Date and Time Data Types	60	(1)
Using Binary Data Types	61	(1)
Using Money Data Types	61	(1)
Using Other Data Types	61	(1)
Summary	61	(2)
What's ADO.NET?	63	(26)
Why ADO.NET?	63	(3)
From ADO to ADO.NET	63	(1)
ADO.NET Isn't a New Version of ADO	64	(1)
ADO.NET and the .NET Framework	65	(1)
Understanding ADO.NET Architecture	66	(22)
Using the SQL Server Data Provider	69	(5)
Using the OLE DB Data Provider	74	(5)
Using the ODBC Data Provider	79	(9)
Data Providers Are APIs	88	(1)
Summary	88	(1)
Creating Connections	89	(24)
Introducing the Data Provider Connection Classes	89	(1)
Connecting to MSDE with SqlConnection	90	(8)
Try It Out: Using SqlConnection	90	(1)
How It Works	91	(3)
Debugging Connections to SQL Server	94	(1)
Security and Passwords in SqlConnection	95	(1)
How to Use SQL Server Security	96	(1)
Connection String Parameters for SqlConnection	96	(1)
Connection Pooling	97	(1)
Improving Your Use of Connection Objects	98	(4)
Using the Connection String in the Connection Constructor	98	(1)
Displaying Connection Information	98	(4)
Connecting to Microsoft Access with OleDbConnection	102	(3)
Try It Out: Connecting to Access with the OLE DB Data Provider	103	(1)
How It Works	104	(1)
Connecting to Oracle	105	(2)
Try It Out: Connecting to Oracle with System.Data.OracleClient	105	(2)
How It Works	107	(1)
Connecting to DB2	107	(3)
Try It Out: Connecting to DB2 with IBM.Data.DB2	107	(2)
How It Works	109	(1)
Connecting to MySQL with ByteFX.Data.MySqlClient	110	(2)
Try It Out: Connecting to MySql with ByteFX.Data.MySqlClient	110	(2)
How It Works	112	(1)
Summary	112	(1)
Introducing Commands	113	(32)
Creating a Command	113	(5)
Try It Out: Creating a Command with a Constructor	114	(1)
How It Works	115	(1)
Associating a Command with a Connection	115	(1)
Assigning Text to a Command	116	(2)
Executing Commands	118	(2)
Try It Out: Using the ExecuteScalar Method	118	(2)
How It Works	120	(1)
Executing Commands with Multiple Results	120	(3)
Try It Out: Using the ExecuteReader Method	121	(1)
How It Works	122	(1)
Executing Statements	123	(10)
Try It Out: Using the ExecuteNonQuery Method	123	(2)
How It Works	125	(2)
Creating Tables	127	(3)
Creating Databases	130	(3)
Command Parameters	133	(4)
Try It Out: Using Command Parameters and the Prepare Method	134	(1)
How It Works	135	(2)
Using Commands with Other Data Providers	137	(7)
Try It Out: Using OleDbCommand	137	(3)
How It Works	140	(1)
Try It Out: Using OdbcCommand	140	(2)
How It Works	142	(2)
Summary	144	(1)
Introducing Data Readers	145	(28)
Understanding Data Readers in General	145	(15)
Try It Out: Looping Through a Result Set	146	(2)
How It Works	148	(2)
Using Ordinal Indexers	150	(3)
Using Column Name Indexers	153	(1)
Using Typed Accessor Methods	154	(6)
Getting Data About Data	160	(4)
Try It Out: Getting Information About a Result Set with a Data Reader	161	(3)
How It Works	164	(1)
Getting Data About Tables	164	(4)
Try It Out: Getting Schema Information	165	(2)
How It Works	167	(1)
Using Multiple Result Sets with a Data Reader	168	(3)
Try It Out: Handling Multiple Result Sets	168	(3)
How It Works	171	(1)
Summary	171	(2)
Introducing Datasets and Data Adapters	173	(46)
Understanding the Object Model	174	(3)
Datasets vs. Data Readers	174	(1)
A Brief Introduction to Datasets	174	(1)
A Brief Introduction to Data Adapters	175	(2)
A Brief Introduction to Data Tables, Data Columns, and Data Rows	177	(1)
Working with Datasets and Data Adapters	177	(36)
Try It Out: Populating a Dataset with a Data Adapter	178	(2)
How It Works	180	(1)
Filtering and Sorting in a DataSet	181	(5)
Comparing FilterSort to PopDataset	186	(1)
Using Data Views	187	(4)
Modifying Data in a Dataset	191	(3)
Propagating Changes to a Data Source	194	(19)
Using Datasets and XML	213	(4)
Try It Out: Extracting a Dataset to an XML File	214	(2)
How It Works	216	(1)
Understanding Typed and Untyped Datasets	217	(1)
Summary	218	(1)
Building Windows Forms Applications	219	(30)
What's Data Binding?	219	(11)
Performing Simple Data Binding	220	(2)
Performing Complex Data Binding	222	(3)
Understanding Data Binding: Behind the Scenes	225	(1)
Synchronizing Controls with a Data Source	226	(4)
Understanding Types of Data Sources	230	(10)
Binding to Arrays	230	(1)
Binding to Data Tables	231	(3)
Binding to Datasets	234	(3)
Binding to Data Views	237	(3)
Using Data Grids	240	(5)
Try It Out: Binding a Data Grid to Show Data in a Parent-Child Relationship	241	(2)
How It Works	243	(2)
Updating a Data Source Using a Data Grid	245	(3)
Try It Out: Updating a Data Source with a Bound Data Grid	245	(2)
How It Works	247	(1)
Summary	248	(1)
Using ASP.NET	249	(42)
Understanding the Basics	249	(18)
Understanding Web Forms	250	(8)
Viewing the Code-Behind File	258	(2)
Binding Data to a Data Grid	260	(2)
Paging in a Data Grid	262	(2)
Combining a Data Grid and Dataset	264	(3)
Understanding the ASP.NET Page Life Cycle	267	(12)
Building for Scalability	268	(2)
Selecting a Row	270	(6)
Using Bound Columns	276	(3)
Editing a Data Grid	279	(11)
Try It Out: Adding Editing Capability	279	(2)
How It Works	281	(1)
Using Template Columns	282	(3)
Deleting Rows	285	(2)
Sorting a Data Grid	287	(3)
Summary	290	(1)
Validating Web User Input	291	(26)
Try It Out: Creating a Validation Example	291	(2)
How It Works	293	(2)
Performing Web Validation	295	(1)
Enabling Client-Side Validation	295	(1)
Disabling Client-Side Validation	295	(1)
Server-Side Validation	295	(1)
Understanding Validation in the Page Life Cycle	296	(1)
Using the ASP.NET Validation Controls	296	(18)
Using the RangeValidator Control	297	(4)
Using the CompareValidator Control	301	(2)
Using the RegularExpressionValidator Control	303	(2)
Using the CustomValidator Control	305	(3)
Using the ValidationSummary Control	308	(4)
Using Inline and Summary Errors	312	(2)
Performing Programmatic Validation	314	(1)
Summary	315	(2)
Working with Tables and Relationships	317	(26)
Working with Tables	317	(5)
Creating a Table	317	(4)
Adding Rows to a Table	321	(1)
Dropping a Table	322	(1)
Specifying Table Relationships	322	(11)
Understanding Keys and Constraints	323	(3)
Understanding Types of Relationships	326	(1)
Understanding Referential Integrity	327	(1)
Understanding Database Diagrams	328	(5)
Working with SQL in C#	333	(8)
Try It Out: Creating a SQL Statement Processor	333	(3)
How It Works	336	(1)
Try It Out: Adding Rows to a Table	337	(1)
How It Works	338	(1)
Try It Out: Testing the Primary Key Constraint	339	(1)
How It Works	340	(1)
Try It Out: Testing the Foreign Key Constraint (Referential Integrity)	340	(1)
How It Works	340	(1)
Try It Out: Dropping a Table	341	(1)
How It Works	341	(1)
Summary	341	(2)
Learning More About Queries	343	(38)
Building the Northwind Query Application, Part II	343	(6)
Try It Out: Extending the Northwind SQL Application	344	(2)
How It Works	346	(3)
Writing More SQL Query Syntax	349	(30)
Distinct	349	(2)
Subqueries	351	(5)
Group By	356	(1)
Other Aggregates	357	(2)
Datetime Functions	359	(2)
CASE Expressions	361	(6)
Joins	367	(12)
Summary	379	(2)
Using Views and Stored Procedures	381	(36)
Using Views	381	(5)
Try It Out: Creating and Using a View from Server Explorer	382	(1)
How It Works	383	(1)
Try It Out: Creating a View Using Another View	384	(1)
How It Works	385	(1)
Creating a View Using C#	386	(6)
Try It Out: Creating and Accessing a View from C#	386	(5)
How It Works	391	(1)
Using Stored Procedures	392	(11)
Try It Out: Creating and Executing a Stored Procedure with No Parameters	392	(2)
How It Works	394	(1)
Try It Out: Using an Alternative Method for Creating a Stored Procedure	395	(1)
How It Works	396	(1)
Try It Out: Creating and Executing a Stored Procedure with a Return Value	396	(2)
How It Works	398	(1)
Try It Out: Creating and Executing a Stored Procedure with an Input Parameter	399	(1)
How It Works	400	(1)
Try It Out: Executing a Stored Procedure with Input and Output Parameters	401	(1)
How It Works	402	(1)
Working with Stored Procedures in C#	403	(13)
Try It Out: Setting Up a C# Project to Execute Stored Procedures	403	(2)
Try It Out: Executing a Stored Procedure with No Input Parameters from C#	405	(2)
How It Works	407	(1)
Try It Out: Executing a Stored Procedure with One Input Parameter from C#	408	(2)
How It Works	410	(1)
Try It Out: Handling a Stored Procedure Return Value	411	(1)
How It Works	412	(1)
Try It Out: Executing a Stored Procedure with Both Input and Output Parameters	413	(2)
How It Works	415	(1)
Summary	416	(1)
Using Indexes and Constraints	417	(24)
Understanding Indexes	417	(14)
Try It Out: Creating a Simple Index	419	(3)
How It Works	422	(1)
Taking Advantage of Indexes	423	(4)
Using Clustered Indexes	427	(1)
Using Composite Keys	428	(2)
Learning Additional Index Guidelines	430	(1)
Maintaining Indexes	431	(1)
Understanding Constraints	431	(9)
Understanding Data Integrity	431	(1)
Defining Constraints	432	(5)
Using Check Constraints	437	(2)
Using NULL Constraints	439	(1)
Using Default Constraints	439	(1)
Dropping Constraints	439	(1)
Summary	440	(1)
Securing Your Database	441	(26)
Using the AdminHelp Utility	442	(1)
Introducing Database Security	443	(1)
Understanding Authentication	444	(1)
Windows Authentication	444	(1)
Mixed Mode Authentication	445	(1)
Managing Users	445	(11)
Database Logins	445	(3)
Special Users	448	(1)
User Accounts	449	(2)
Managing Roles	451	(5)
Managing Permissions	456	(7)
Object Permissions	458	(1)
Statement Permissions	459	(1)
Viewing Permissions	460	(3)
Implementing Security	463	(2)
Summary	465	(2)
Using XML and ADO.NET	467	(20)
The Pros and Cons of XML	467	(5)
Understanding the Structure of an XML Document	469	(1)
Try It Out: Creating an XML Document in Visual Studio .NET	469	(3)
How It Works	472	(1)
Understanding Schemas	472	(5)
Try It Out: Creating an XML Schema in Visual Studio .NET	472	(4)
How It Works	476	(1)
Writing Valid XML	476	(1)
Try It Out: Validating an XML Document	476	(1)
How It Works	477	(1)
Using XML with ADO.NET	477	(4)
Try It Out: Reading an XML Document into a DataSet	477	(2)
How It Works	479	(1)
Try It Out: Writing a DataSet to an XML File	479	(2)
How It Works	481	(1)
Defining Application Configuration Using XML	481	(5)
Try It Out: Creating an Application Configuration File	482	(2)
How It Works	484	(2)
Summary	486	(1)
Handling Exceptions	487	(22)
Handling Exceptions C#	487	(2)
Using the try Statement	487	(1)
Using Class System.Exception	488	(1)
Using the throw Statement	489	(1)
Handling ADO.NET Exceptions	489	(8)
Try It Out: Handling an ADO.NET Exception (Part 1)	489	(3)
How It Works	492	(1)
Try It Out: Handling an ADO.NET Exception (Part 2)	493	(3)
How It Works	496	(1)
Handling Database Exceptions	497	(10)
Try It Out: Handling a Database Exception (Part 1): RAISERROR	498	(3)
How It Works	501	(1)
Try It Out: Handling a Database Exception (Part 2): Stored Procedure	502	(2)
How It Works	504	(1)
Try It Out: Handling a Database Exception (Part 3): Errors Collection	504	(2)
How It Works	506	(1)
Summary	507	(2)
Using Transactions	509	(14)
When to Use Transactions	509	(1)
Understanding ACID Properties	510	(1)
How to Code Transactions	511	(11)
Coding Transactions in SQL	511	(5)
ADO.NET Transactions	516	(6)
Suggestions for Further Study	522	(1)
Summary	522	(1)
Working with ADO.NET Events	523	(18)
Understanding Events and Delegates	523	(2)
Adding and Removing Event Handlers	525	(1)
Raising and Handling ADO.NET Events	525	(15)
Working with Connection Object Events	525	(7)
Working with Row Update Events	532	(6)
Working with Multiple Handlers	538	(2)
Summary	540	(1)
Working with Text and Binary Data	541	(26)
Understanding SQL Server Text and Binary Data Types	541	(2)
Storing Images in a Database	543	(8)
Try It Out: Loading Image Binary Data from Files	543	(5)
How it Works	548	(2)
Rerunning the Program	550	(1)
Using a Different SQL Data Type	550	(1)
Retrieving Images from a Database	551	(6)
Try It Out: Displaying Stored Images	551	(4)
How It Works	555	(2)
Working with Text Data	557	(9)
Retrieving Data from Text Columns	562	(4)
Summary	566	(1)
Using ADO.NET 2.0	567	(14)
Converting ADO.NET 1.1 Programs to ADO.NET 2.0	567	(5)
Try It Out: Rebuilding an ADO.NET 1.1 Project for ADO.NET 2.0	568	(4)
How It Works	572	(1)
Loading Data Tables with Data Readers	572	(3)
Try It Out: Loading a Data Table with a Data Reader	572	(1)
How It Works	573	(2)
Serializing Data More Compactly	575	(3)
Try It Out: Serializing a Dataset to a Binary File	575	(2)
How It Works	577	(1)
Updating in Batches	578	(1)
Try It Out: Specifying Batch Updates	578	(1)
How It Works	579	(1)
Summary	579	(2)
APPENDIX A Creating the SQL Tool Application	581	(12)
Try It Out: Building a Custom Query Tool	581	(8)
How It Works	589	(2)
Summary	591	(2)
APPENDIX B XML Primer	593	(14)
Understanding XML Documents	593	(3)
XML Elements	593	(2)
Attributes	595	(1)
The XML Declaration	595	(1)
Structure of an XML Document	596	(1)
Using XML Namespaces	596	(1)
Understanding Well-Formed and Valid XML	597	(1)
Validating XML Documents	598	(2)
Schemas	598	(1)
XSD Schemas	598	(1)
XDR Schemas	599	(1)
Try It Out: Creating an XML Document in Visual Studio .NET	600	(6)
How It Works	606	(1)
Summary	606	(1)
Index	607