Mastering C#Database Programming

by Price, Jason

• ISBN13: 9780782141832
• ISBN10: 0782141838
• Edition: 1st
• Format: Paperback
• Copyright: 2003-05-06
• Publisher: Sybex

Summary

Enter a New World of Database Programming C# and ADO.NET facilitate the development of a new generation of database applications, including remote applications that run on the Web. Mastering C# Database Programming is the resource you need to thrive in this new world. Assuming no prior experience with database programming, this book teaches you every aspect of the craft, from GUI design to server development to middle-tier implementation. If you're familiar with earlier versions of ADO, you'll master the many new features of ADO.NET all the more quickly. You'll also learn the importance of XML within the new .NET paradigm. Coverage includes: * Accessing a database using C# and ADO.NET * Using SQL to access a database * Using Visual Studio .NET to build applications * Creating and modifying database tables * Understanding ADO.NET classes * Designing, building, and deploying Web applications that access a database * Designing, building, and deploying effective Web services * Using SQL Server's built-in XML capabilities * Working with a database in a disconnected manner * Using advanced transaction controls * Using Transact-SQL to create stored procedures and functions in a SQL Server database

Author Biography

Jason Price has more than 10 years' experience in the software industry. He is an MCSE, OCP, and has extensive experience with C#, .NET, and Java. He is the author of Sybex's Mastering Visual C# .NET and two Oracle books for other publishers. All his books have received 5-star reviews on Amazon.

Table of Contents

Introduction	xvii
Part 1 Introduction to ADO.NET and Databases	1	(184)
Chapter 1 Introduction to Database Programming with ADO.NET	3	(26)
Obtaining the Required Software	4	(1)
Developing Your First ADO.NET Program	4	(8)
Step 1: Create a SqlConnection Object to Connect to the Database	7	(1)
Step 2: Create a SqlCommand Object	8	(1)
Step 3: Set the CommandText Property of the SqlCommand Object	8	(1)
Step 4: Open the SqlConnection Object	8	(1)
Step 5: Run the SELECT Statement	8	(1)
Step 6: Read the Row Using the SqlDataReader Object	9	(1)
Step 7: Display the Column Values from the SqlDataReader Object	9	(1)
Step 8: Close the SqlDataReader Object	9	(1)
Step 9: Close the SqlConnection Object	9	(1)
Handling Exceptions	9	(1)
Compiling and Running FirstExample.cs	10	(2)
Connecting to Access and Oracle Databases	12	(5)
Connecting to an Access Database	12	(2)
Connecting to an Oracle Database	14	(3)
Introducing Visual Studio NET	17	(6)
Starting Visual Studio NET and Creating a Project	17	(6)
Compiling and Running the Program Using VS NET	23	(1)
Using the .NET Documentation	23	(3)
Accessing the Documentation Using the .NET SDK	23	(2)
Accessing the Documentation Using VS .NET	25	(1)
Using the SQL Server Documentation	26	(2)
Summary	28	(1)
Chapter 2 Introduction to Databases	29	(30)
Introducing Databases	29	(1)
Using SQL Server	30	(9)
Starting and Stopping SQL Server	30	(1)
Using Enterprise Manager	31	(8)
Exploring the Northwind Database	39	(9)
Primary Keys	40	(1)
Table Relationships and Foreign Keys	40	(1)
Null Values	41	(1)
Indexes	41	(1)
Column Types	42	(2)
The Customers Table	44	(1)
The Orders Table	45	(2)
The Order Details Table	47	(1)
The Products Table	48	(1)
Building Queries Using Enterprise Manager	48	(3)
Creating a Table	51	(6)
The Columns Tab	52	(1)
Setting the Primary Key	53	(1)
Setting the Permissions	53	(1)
Creating the Relationship	54	(1)
Creating an Index	55	(1)
Creating a Constraint	56	(1)
Summary	57	(2)
Chapter 3 Introduction to Structured Query Language (SQL)	59	(36)
Using SQL	59	(32)
Using Query Analyzer	60	(2)
Understanding Data Manipulation Language (DML) Statements	62	(27)
Introducing Data Definition Language (DDL) Statements	89	(2)
Accessing a Database Using Visual Studio NET	91	(3)
Summary	94	(1)
Chapter 4 Introduction to Transact-SQL Programming	95	(42)
Fundamentals of Transact-SQL	95	(8)
Using Variables	95	(2)
Using Comments	97	(1)
Using Conditional Logic	97	(1)
Using CASE Statements	98	(1)
Using WHILE Loops	99	(2)
Using Labels and the GOTO Statement	101	(1)
Using RETURN Statements	102	(1)
Using WAITFOR Statements	102	(1)
Using RAISERROR Statements	103	(1)
Using Cursors	103	(4)
Step 1: Declare Variables to Store the Column Values from the SELECT Statement	104	(1)
Step 2: Declare the Cursor	104	(1)
Step 3: Open the Cursor	104	(1)
Step 4: Fetch the Rows from the Cursor	104	(1)
Step 5: Close the cursor	105	(1)
Complete Example:Product Cursor.sql	106	(1)
Using Functions	107	(12)
Using Aggregate Functions	107	(4)
Using Mathematical Functions	111	(2)
Using String Functions	113	(3)
Using Date and Time Functions	116	(1)
Using System Functions	117	(2)
Creating User-Defined Functions	119	(5)
Using Scalar Functions	120	(1)
Using Inline Table-Valued Functions	121	(1)
Using MultistatementTable-Valued Functions	122	(2)
Introducing Stored Procedures	124	(3)
Creating a Stored Procedure	125	(2)
Running a Stored Procedure	127	(1)
Introducing Triggers	127	(8)
Creating InsertProduct Trigger	129	(1)
Testing InsertProduct Trigger	130	(1)
Creating and Testing UpdateUnitPriceProduct Trigger	131	(2)
Creating and Testing DeleteProduct Trigger	133	(2)
Summary	135	(2)
Chapter 5 Overview of the ADO.NET Classes	137	(16)
The Managed Provider and Generic Data Set Classes	138	(6)
The Managed Provider Classes	138	(3)
The Generic Data Classes	141	(3)
Performing a SQL SELECT Statement and Storing the Rows Locally	144	(7)
Outlining the Procedure	144	(4)
Putting It All Together	148	(3)
Summary	151	(2)
Chapter 6 Introducing Windows Applications and ADO.NET	153	(32)
Developing a Simple Windows Application	153	(11)
Creating the Windows Application	154	(4)
Examining the Code behind the Form	158	(5)
Working with the Solution Explorer	163	(1)
Working with the Class View	164	(1)
Using Windows Controls	164	(1)
Using a DataGrid Control to Access a Database	165	(7)
Using the Data Form Wizard to Create a Windows Form	172	(11)
Data Binding	179	(1)
Adding Controls to the Form	180	(1)
Adding the Main() Method	181	(1)
Setting the Password	181	(1)
Running the Form	182	(1)
Summary	183	(2)
Part 2 Fundamental Database Programming with ADO.NET	185	(298)
Chapter 7 Connecting to a Database	187	(20)
Understanding the SqlConnection Class	187	(2)
Using a SqlConnection Object to Connect to a SQL Server Database	189	(11)
Opening and Closing a Database Connection	190	(2)
Connection Pooling	192	(3)
Getting the State of a Connection Object	195	(1)
Using Connection Events	196	(4)
Creating a Connection Object Using Visual Studio NET	200	(5)
Coding an Event in VS .NET	203	(2)
Summary	205	(2)
Chapter 8 Executing Database Commands	207	(52)
The SqlCommand Class	207	(2)
Creating a SqlCommand Object	209	(2)
Creating a SqlCommand Object Using a Constructor	209	(2)
Creating a SqlCommand Object Using the CreateCommand() Method	211	(1)
Executing SELECT Statements and TableDirect Commands	211	(19)
Executing a SELECT Statement Using the ExecuteReader() Method	212	(2)
Controlling the Command Behavior Using the ExecuteReader() Method	214	(9)
Executing a TableDirect Statement Using the ExecuteReaderO Method	223	(2)
Executing a SELECT Statement Using the ExecuteScalar() Method	225	(2)
Executing a Command that Retrieves Data as XML Using the
ExecuteXMLReader() Method	227	(3)
Executing Commands that Modify Information in the Database	230	(6)
Executing INSERT, UPDATE, and DELETE Statements Using the ExecuteNonQuery() Method	230	(3)
Executing DDL Statements Using the ExecuteNonQueryO Method	233	(3)
Introducing Transactions	236	(3)
Supplying Parameters to Commands	239	(6)
Step 1: Create a Command Object Containing a SQL Statement with Parameter Placeholders	240	(1)
Step 2: Add Parameters to the Command Object	241	(2)
Step 3: Set the Parameters to Specified Values	243	(1)
Step 4: Execute the Command	244	(1)
Executing SQL Server Stored Procedures	245	(10)
Executing a Stored Procedure That Does Not Return a Result Set	245	(7)
Executing a Stored Procedure that Does Return a Result Set	252	(3)
Creating a Command Object Using Visual Studio NET	255	(3)
Summary	258	(1)
Chapter 9 Using DataReader Objects to Read Results	259	(34)
The SqlDataKeader Class	259	(3)
Creating a SqlDataReader Object	262	(1)
Reading Rows from a SqlDataReader Object	263	(3)
Returning Strongly Typed Column Values	266	(15)
Using the Get* Methods to Read Column Values	268	(2)
An Example of Using the Get* Methods	270	(6)
Using the GetSql* Methods to Read Column Values	276	(2)
An Example of Using the GetSql* Methods	278	(3)
Reading Null Values	281	(1)
Executing Multiple SQL Statements	282	(6)
Executing Multiple SELECT Statements	282	(4)
Executing Multiple SELECT, INSERT, UPDATE, and DELETE Statements	286	(2)
Using a DataReader Object in Visual Studio .NET	288	(3)
Summary	291	(2)
Chapter 10 Using DataSet Objects to Store Data	293	(52)
The SqlDataAdapter Class	294	(4)
Creating a SqlDataAdapter Object	297	(1)
The DataSet Class	298	(23)
Creating a DataSet Object	302	(1)
Populating a DataSet Object	302	(9)
Populating a DataSet with Multiple dateable Objects	311	(7)
Merging DataRow, DataSet, and DataTable Objects into Another DataSet	318	(3)
Writing and Reading XML Using a DataSet Object	321	(7)
Using the WriteXml() Method	322	(2)
Using the WriteXmlSchema() Method	324	(1)
Using the ReadXml() Method	325	(3)
Mapping Tables and Columns	328	(4)
Reading a Column Value Using Strongly Typed DataSet Classes	332	(5)
Creating a Strongly Typed DataSet Class	332	(4)
Using a Strongly Typed DataSet Class	336	(1)
Creating a DataAdapter Object Using Visual Studio .NET	337	(5)
Creating a DataSet Object Using Visual Studio .NET	342	(2)
Summary	344	(1)
Chapter 11 Using DataSet Objects to Modify Data	345	(72)
The DataTable Class	345	(2)
The DataRow Class	347	(2)
The DataColumn Class	349	(1)
Adding Restrictions to DataTable and DataColumn Objects	350	(17)
Adding the Restrictions Yourself	351	(11)
Adding Restrictions by Calling the DataAdapter Object's FillSchema() Method	362	(5)
Finding, Filtering, and Sorting Rows in a DataTable	367	(6)
Finding a DataRow in a DataTable	367	(1)
Filtering and Sorting DataRow Objects in a Dateable	368	(5)
Modifying Rows in a Dateable	373	(11)
Setting up a DataAdapter to Push Changes to the Database	373	(4)
Adding a DataRow to a Dateable	377	(3)
Modifying a DataRow in a DataTable	380	(2)
Removing a DataRow from a DataTable	382	(2)
Retrieving New Identity Column Values	384	(2)
Using Stored Procedures to Add, Modify, and Remove Rows from the Database	386	(12)
Creating the Stored Procedures in the Database	387	(3)
Setting Up a DataAdapter to Call Stored Procedures	390	(3)
Adding a DataRow to a Dateable	393	(2)
Modifying a DataRow in a Dateable	395	(1)
Removing a DataRow from a DataTable	396	(2)
Automatically Generating SQL Statements	398	(1)
Exploring the DataAdapter and DataTable Events	399	(9)
The DataAdapter Events	400	(4)
The DataTable Events	404	(4)
Dealing with Update Failures	408	(4)
An Update Failure Scenario	408	(1)
Setting the ContinueUpdateOnError Property	409	(1)
Programming a Failed Update Example	409	(2)
Checking for Errors	411	(1)
Fixing the Error	411	(1)
Using Transactions with a DataSet (SQL)	412	(1)
Using the DataAdapter Command Object's Transaction Property	413	(1)
Modifying Data Using a Strongly Typed DataSet	413	(2)
Summary	415	(2)
Chapter 12 Navigating and Modifying Related Data	417	(36)
The UniqueConstraint Class	418	(1)
Creating a UniqueConstraint Object	418	(2)
The ForeignKeyConstraint Class	420	(2)
Creating a ForeignKeyConstraint Object	422	(1)
The DataRelation Class	423	(1)
Creating and Using a DataRelation Object	424	(5)
Creating the DataRelation	425	(2)
Examining the Constraints Created by the DataRelation	427	(1)
Navigating DataRow Objects in the Parent and Child DataTable Objects	428	(1)
Adding, Updating, and Deleting Related Rows	429	(8)
Setting Up the DataAdapter Objects	429	(4)
Creating and Populating a DataSet	433	(1)
Adding DataRow Objects to customersDT and ordersDT	434	(1)
Updating DataRow Objects in customersDT and ordersDT	434	(1)
Deleting DataRow Objects from customersDT and ordersDT	435	(1)
Pushing Changes in customersDT and ordersDT to the Database	435	(2)
Issues Involved When Updating the Primary Key of a Parent Row	437	(6)
Controlling Updates and Deletes Using SQL Server	438	(1)
Controlling Updates and Deletes Using the UpdateRule and DeleteRule Properties of a ForeignKeyConstraint Object	439	(1)
Updating the Primary Key of a Parent Table and Pushing the Change to the Database	440	(3)
Nested XML	443	(5)
Defining a Relationship Using Visual Studio .NET	448	(4)
Create the Windows Application	448	(2)
Adding a Relation to the XML Schema of the DataSet	450	(2)
Summary	452	(1)
Chapter 13 Using DataView Objects	453	(30)
The Data View Class	453	(3)
Creating and Using a Data View Object	456	(4)
Using the Default Sort Algorithm	460	(1)
Performing Advanced Filtering	460	(1)
The DataRowView Class	461	(1)
Finding DataRowView Objects in a Data View	462	(4)
Finding the Index of a DataRowView Using the Find() Method	463	(1)
Finding DataRowView Objects Using the FindRows() Method	464	(2)
Adding, Modifying, and Removing DataRow View Objects from a DataView	466	(6)
Adding a DataRowView to a DataView	467	(1)
Modifying an Existing DataRowView	467	(1)
Removing an Existing DataRowView	468	(4)
Creating Child DataView Objects	472	(3)
The DataViewManager Class	475	(1)
Creating and Using a DataViewManager Object	475	(3)
Creating a DataView Using Visual Studio NET	478	(2)
Summary	480	(3)
Part 3 Advanced Database Programming with ADO.NET	483	(150)
Chapter 14 Advanced Transaction Control	485	(32)
The SqlTransaction Class	486	(1)
Setting a Savepoint	486	(6)
Setting a Savepoint Using T-SQL	486	(2)
Setting a Savepoint Using a SqlTransaction Object	488	(4)
Setting the Transaction Isolation Level	492	(8)
Setting the Transaction Isolation Level Using T-SQL	493	(3)
Setting the Transaction Isolation Level of a SqlTransaction Object	496	(4)
Understanding SQL Server Locks	500	(15)
Types of SQL Server Locks	500	(1)
SQL Server Locking Modes	501	(1)
Viewing SQL Server Lock Information	502	(2)
Transaction Blocking	504	(1)
Setting the Lock Timeout	505	(1)
Blocking and Serializable/Repeatable Read Transactions	506	(3)
Deadlocks	509	(6)
Summary	515	(2)
Chapter 15 Introducing Web Applications-ASP.NET	517	(50)
Creating a Simple ASPNET Web Application Using VS NET	517	(7)
The WebForml.aspx File	520	(2)
The WebForml.aspx.cs File	522	(2)
The Web Form Controls	524	(3)
Building a More Complex Application	527	(5)
Using a DataGrid Control to Access a Database	532	(11)
Creating the Web Application	532	(3)
Customizing the DataGrid	535	(8)
Using a DataList Control to Access a Database	543	(9)
Maintaining State in a Web Application	552	(8)
Storing Information on the Client	552	(4)
Storing Information on the Server	556	(4)
Creating a Simple Shopping Cart Application	560	(5)
Adding the Buy Button	560	(3)
Adding the Shopping Cart	563	(1)
Adding Code to the WebForm1.aspx.cs File	563	(2)
Summary	565	(2)
Chapter 16 Using SQL Server's XML Support	567	(46)
Using the SQL Server FOR XML Clause	567	(11)
Using the RAW Mode	568	(2)
Using the AUTO Mode	570	(1)
Using the EXPLICIT Mode	570	(4)
Using the XMLDATA Option	574	(2)
Using the ELEMENTS Option	576	(1)
Using the BINARY BASE64 Option	576	(2)
Introducing XPath	578	(4)
XML Document Structure	578	(2)
XPath Expressions	580	(2)
Introducing XSLT	582	(4)
Accessing SQL Server Using HTTP	586	(10)
Configuring SQL XML Support for IIS	586	(4)
Running Direct SQL Statements Using a Browser	590	(3)
Running SQL Statements Using an XML Template	593	(1)
Formatting XML Output Using an XSL Stylesheet	594	(2)
Using the SQL Server OPENXML() Function	596	(4)
OPENXML() Syntax	597	(1)
Using OPENXML()	597	(3)
Using an XmlDocument Object to Store an XML Document	600	(6)
Using an XmlDataDocument Object to Store an XML Document	606	(6)
Summary	612	(1)
Chapter 17 Web Services	613	(20)
Creating a Web Service	613	(4)
Viewing a WSDL File and Testing a Web Service	617	(7)
Viewing the WSDL File for the Web Service	618	(3)
Testing a Web Service	621	(3)
Using a Web Service	624	(2)
Registering a Web Service	626	(5)
Summary	631	(2)
Index	633