Maharry Beginning VB .NET 1.1 Databases

From Novice to Professional
1. ed
ISBN: 978-1-4302-0010-9
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 632 Seiten

* Written by best selling author Peter Wright, and marks a return to his best form as he writes with a mission again. 'Beginning VB6' by Peter Wright sold circa 100k copies, and he followed up with Beginning VB6 Objects which sold circa 30k copies on a more niche topic. * A personal journey with an expert in VB.NET database programming, this is a book that stands out from the crowd. * A complete tutorial involving the reader in all the key areas of VB.NET database programming that they need to progress their skill set in VB.NET. * All the skills are covered that a novice programmer will need to meet the database programming challenges they may meet in their programming career. * 2 months free subscription to ASP.Today for readers of this title.

Dan Maharry is a UK-based professional technical author with over a dozen books to his name and many more as technical reviewer and editor for a variety of publishers including Wrox, Apress, Microsoft Press and O'Reilly Associates. He's also a .NET developer with past stints for the dotCoop TLD registry and various web development houses under his belt. He contributes code and documentation to various open source projects and tries to blog and to speak from time to time to user groups about cool stuff. He listens to a lot of new music as he does the above.

Autoren/Hrsg.

Maharry, Dan

Inhaltsverzeichnis

1;Contents;7
2;About the Authors;19
3;Acknowledgments;20
4;Introduction;21
5;Chapter 1 Installing MSDE;24
5.1;Obtaining and Installing MSDE;24
5.2;Installing the Sample Databases;27
5.3;Installing the SQL Server Documentation;28
5.4;Introducing Authentication Modes;30
5.4.1;Windows Authentication;30
5.4.2;SQL Server Authentication;30
5.4.3;Mixed Mode;31
5.5;Connecting with Server Explorer;31
5.6;Summary;34
6;Chapter 2 Creating a Simple Database Application;35
6.1;Creating the Application;35
6.1.1;Try It Out: Creating the Foundations;36
6.1.2;How It Works;37
6.2;Establishing a Database Connection;37
6.2.1;Try It Out: Creating a Connection;38
6.2.2;How It Works;41
6.3;Introducing Data Binding;41
6.3.1;Try It Out: Creating a User Interface;42
6.3.2;How It Works;45
6.3.3;Try It Out: Making the Form Dynamic;46
6.3.4;How It Works;47
6.4;Editing Data;48
6.4.1;Try It Out: Adding Edit Functionality;48
6.4.2;How It Works;49
6.5;Using the Data Form Wizard;50
6.5.1;Try It Out: Creating a User Interface with the Data Form Wizard;50
6.6;Summary;55
7;Chapter 3 Introducing SQL;57
7.1;What Is SQL?;57
7.2;Using SQL;59
7.2.1;Executing SQL from Visual Studio .NET;59
7.2.2;Executing SQL from the Command Line;62
7.2.3;Using the Custom Query Tool;64
7.3;Retrieving Data;65
7.3.1;Using Queries;65
7.3.2;Using the WHERE Clause;69
7.3.3;Sorting Data;73
7.4;Inserting Data;75
7.5;Updating Data;76
7.6;Deleting Data;78
7.7;Using SQL Data Types;79
7.7.1;Numeric Data Types;80
7.7.2;Character String Data Types;80
7.7.3;Date and Time Data Types;80
7.8;Using Binary Data Types;81
7.9;Using Money Data Types;81
7.10;Using Other Data Types;82
7.11;Summary;82
8;Chapter 4 What’s ADO.NET?;83
8.1;Why ADO.NET?;83
8.1.1;A Brief History;83
8.1.2;ADO.NET Isn’t a New Version of ADO;84
8.1.3;ADO.NET and the .NET Framework;85
8.2;Understanding ADO.NET Architecture;87
8.2.1;Using the SQL Server Data Provider;89
8.2.2;Using the OLE DB Data Provider;95
8.2.3;Using the ODBC Data Provider;99
8.3;Data Providers Are APIs;107
8.4;Summary;108
9;Chapter 5 Creating Connections;109
9.1;Introducing the Data Provider Connection Classes;109
9.2;Connecting to MSDE with SqlConnection;110
9.2.1;Try It Out: Using SqlConnection;110
9.2.2;How It Works;112
9.2.3;Debugging Connections to SQL Server;114
9.2.4;Setting Up Security and Passwords in SqlConnection;116
9.2.5;Using SQL Server Security;116
9.2.6;Setting Connection String Parameters for SqlConnection;116
9.2.7;Understanding Connection Pooling;118
9.3;Improving Your Use of Connection Objects;118
9.3.1;Using the Connection String in the Connection Constructor;118
9.3.2;Displaying Connection Information;118
9.4;Connecting to Microsoft Access with OleDbConnection;122
9.4.1;Try It Out: Connecting to Access with the OLE DB Data Provider;122
9.4.2;How It Works;124
9.5;Connecting to Oracle;125
9.5.1;Try It Out: Connecting to Oracle with System.Data.OracleClient;125
9.5.2;How It Works;126
9.6;Connecting to DB2;127
9.6.1;Try It Out: Connecting to DB2 with IBM.Data.DB2;127
9.6.2;How It Works;129
9.7;Connecting to MySQL;129
9.7.1;Try It Out: Connecting to MySql with MySql.Data.MySqlClient;129
9.7.2;How It Works;131
9.8;Summary;131
10;Chapter 6 Introducing Commands;133
10.1;Creating a Command;133
10.1.1;Try It Out: Creating a Command with a Constructor;134
10.1.2;How It Works;135
10.1.3;Associating a Command with a Connection;135
10.1.4;Assigning Text to a Command;137
10.2;Executing Commands;138
10.2.1;Try It Out: Using the ExecuteScalar Method;138
10.2.2;How It Works;140
10.3;Executing Commands with Multiple Results;141
10.3.1;Try It Out: Using the ExecuteReader Method;141
10.3.2;How It Works;142
10.4;Executing Statements;143
10.4.1;Try It Out: Using the ExecuteNonQuery Method;143
10.4.2;How It Works;145
10.4.3;Creating Tables;147
10.4.4;Creating Databases;150
10.5;Setting Command Parameters;153
10.5.1;Try It Out: Using Command Parameters and the Prepare Method;155
10.5.2;How It Works;156
10.6;Using Commands with Other Data Providers;158
10.6.1;Try It Out: Using OleDbCommand;158
10.6.2;How It Works;160
10.6.3;Try It Out: Using OdbcCommand;160
10.6.4;How It Works;162
10.7;Summary;164
11;Chapter 7 Introducing Data Readers;165
11.1;Understanding Data Readers in General;165
11.1.1;Try It Out: Looping Through a Result Set;166
11.1.2;How It Works;168
11.1.3;Using Ordinal Indexers;170
11.1.4;Using Column Name Indexers;173
11.1.5;Using Typed Accessor Methods;174
11.2;Getting Data About Data;180
11.2.1;Try It Out: Getting Information About a Result Set with a Data Reader;181
11.2.2;How It Works;183
11.3;Getting Data About Tables;184
11.3.1;Try It Out: Getting Schema Information;184
11.3.2;How It Works;186
11.4;Using Multiple Result Sets with a Data Reader;187
11.4.1;Try It Out: Handling Multiple Result Sets;187
11.4.2;How It Works;190
11.5;Summary;190
12;Chapter 8 Introducing Datasets and Data Adapters;191
12.1;Understanding the Object Model;192
12.1.1;Datasets vs. Data Readers;192
12.1.2;Introducing Datasets;192
12.1.3;Introducing Data Adapters;193
12.1.4;Introducing Data Tables, Data Columns, and Data Rows;194
12.2;Working with Datasets and Data Adapters;195
12.2.1;Try It Out: Populating a Dataset with a Data Adapter;195
12.2.2;How It Works;197
12.2.3;Filtering and Sorting in a DataSet;198
12.2.4;Comparing FilterSort to PopDataset;203
12.2.5;Using Data Views;204
12.2.6;Modifying Data in a Dataset;207
12.2.7;Propagating Changes to a Data Source;211
12.2.8;Understanding Concurrency;226
12.3;Using Datasets and XML;227
12.3.1;Try It Out: Extracting a Dataset to an XML File;227
12.3.2;How It Works;229
12.4;Understanding Typed and Untyped Datasets;230
12.5;Summary;231
13;Chapter 9 Building Windows Forms Applications;232
13.1;What’s Data Binding?;232
13.1.1;Performing Simple Data Binding;233
13.1.2;Performing Complex Data Binding;235
13.1.3;Understanding Data Binding: Behind the Scenes;238
13.1.4;Synchronizing Controls with a Data Source;239
13.2;Understanding Types of Data Sources;243
13.2.1;Binding to Arrays;243
13.2.2;Binding to Data Tables;244
13.2.3;Binding to Datasets;247
13.2.4;Binding to Data Views;247
13.3;Using Data Grids;249
13.3.1;Try It Out: Binding a Data Grid to Show Data in a Parent-Child Relationship;250
13.3.2;How It Works;252
13.4;Updating a Data Source Using a Data Grid;253
13.4.1;Try It Out: Updating a Data Source with a Bound Data Grid;253
13.4.2;How It Works;256
13.5;Summary;256
14;Chapter 10 Using ASP.NET;258
14.1;Understanding the Basics;258
14.1.1;Understanding Web Forms;259
14.1.2;Viewing the Code-Behind File;266
14.1.3;Binding Data to a Data Grid;268
14.1.4;Paging in a Data Grid;271
14.1.5;Combining a Data Grid and Dataset;273
14.2;Understanding the ASP.NET Page Life Cycle;276
14.2.1;Building for Scalability;277
14.2.2;Selecting a Row;280
14.2.3;Using Bound Columns;286
14.3;Editing a Data Grid;289
14.3.1;Try It Out: Adding Editing Capability;289
14.3.2;How It Works;291
14.3.3;Using Template Columns;293
14.3.4;Deleting Rows;296
14.3.5;Sorting a Data Grid;298
14.4;Summary;301
15;Chapter 11 Validating Web User Input;302
15.1;Try It Out: Creating a Validation Example;302
15.2;How It Works;304
15.3;Performing Web Validation;306
15.3.1;Enabling Client-Side Validation;306
15.3.2;Disabling Client-Side Validation;306
15.3.3;Enabling Server-Side Validation;306
15.3.4;Understanding Validation in the Page Life Cycle;307
15.4;Using the ASP.NET Validation Controls;307
15.4.1;Using the RangeValidator Control;308
15.4.2;Using the CompareValidator Control;311
15.4.3;Using the RegularExpressionValidator Control;312
15.4.4;Using the CustomValidator Control;314
15.4.5;Using the ValidationSummary Control;316
15.4.6;Using Inline and Summary Errors;321
15.5;Performing Programmatic Validation;322
15.6;Summary;323
16;Chapter 12 Working with Tables and Relationships;324
16.1;Working with Tables;324
16.1.1;Creating a Table;325
16.1.2;Adding Rows to a Table;328
16.1.3;Dropping a Table;329
16.2;Specifying Table Relationships;329
16.2.1;Understanding Keys and Constraints;330
16.2.2;Understanding Types of Relationships;333
16.2.3;Understanding Referential Integrity;334
16.2.4;Understanding Database Diagrams;335
16.3;Working with SQL in VB .NET;340
16.3.1;Try It Out: Creating a SQL Statement Processor;340
16.3.2;How It Works;343
16.3.3;Try It Out: Adding Rows to a Table;344
16.3.4;How It Works;345
16.3.5;Try It Out: Testing the Primary Key Constraint;346
16.3.6;How It Works;347
16.3.7;Try It Out: Testing the Foreign Key Constraint(Referential Integrity);347
16.3.8;How It Works;347
16.3.9;Try It Out: Dropping a Table;347
16.3.10;How It Works;348
16.4;Summary;348
17;Chapter 13 Learning More About Queries;349
17.1;Building the Northwind Query Application, Part II;350
17.1.1;Try It Out: Extending the Northwind SQL Application;350
17.1.2;How It Works;353
17.2;Writing More SQL Query Syntax;356
17.2.1;Using DISTINCT;356
17.2.2;Using Subqueries;357
17.2.3;Using GROUP BY;362
17.2.4;Using Other Aggregates;363
17.2.5;Using Datetime Functions;365
17.2.6;Using CASE Expressions;367
17.2.7;Using Joins;372
17.3;Summary;384
18;Chapter 14 Understanding Views and Stored Procedures;385
18.1;Using Views;385
18.1.1;Try It Out: Creating and Using a View from Server Explorer;387
18.1.2;How It Works;388
18.1.3;Try It Out: Creating a View Using Another View;388
18.1.4;How It Works;389
18.2;Creating a View Using VB .NET;390
18.2.1;Try It Out: Creating and Accessing a View from VB .NET;390
18.2.2;How It Works;395
18.3;Using Stored Procedures;396
18.3.1;Try It Out: Creating and Executing a Stored Procedure with No Parameters;396
18.3.2;How It Works;398
18.3.3;Try It Out: Using an Alternative Method for Creating a Stored Procedure;399
18.3.4;How It Works;400
18.3.5;Try It Out: Creating and Executing a Stored Procedure with a RETURN Value;400
18.3.6;How It Works;401
18.3.7;Try It Out: Creating and Executing a Stored Procedure with an Input Parameter;403
18.3.8;How It Works;404
18.3.9;Try It Out: Executing a Stored Procedure with Input and Output Parameters;405
18.3.10;How It Works;406
18.4;Working with Stored Procedures in VB .NET;407
18.4.1;Try It Out: Setting Up a VB .NET Project to Execute Stored Procedures;407
18.4.2;Try It Out: Executing a Stored Procedure with No Input Parameters from VB .NET;408
18.4.3;Try It Out: Executing a Stored Procedure with One Input Parameter from VB .NET;411
18.4.4;How It Works;413
18.4.5;Try It Out: Handling a Stored Procedure Return Value;414
18.4.6;How It Works;415
18.4.7;Try It Out: Executing a Stored Procedure with Both Input and Output Parameters;416
18.4.8;How It Works;418
18.5;Summary;419
19;Chapter 15 Using Indexes and Constraints;420
19.1;Understanding Indexes;420
19.1.1;Try It Out: Creating a Simple Index;422
19.1.2;How It Works;425
19.1.3;Taking Advantage of Indexes;426
19.1.4;Using Clustered Indexes;429
19.1.5;Using Composite Keys;431
19.1.6;Learning Additional Index Guidelines;433
19.1.7;Maintaining Indexes;434
19.2;Understanding Constraints;434
19.2.1;Understanding Data Integrity;434
19.2.2;Defining Constraints;435
19.2.3;Using Check Constraints;440
19.2.4;Using NULL Constraints;442
19.2.5;Using Default Constraints;442
19.2.6;Dropping Constraints;442
19.3;Summary;443
20;Chapter 16 Securing Your Database;444
20.1;Understanding the Security Process;445
20.2;Understanding Authentication;446
20.2.1;Windows Authentication;446
20.2.2;Mixed Mode Authentication;447
20.3;Managing Users;448
20.3.1;Database Logins;448
20.3.2;Special Users;451
20.3.3;User Accounts;452
20.3.4;Managing Roles;454
20.4;Managing Permissions;459
20.4.1;Object Permissions;462
20.4.2;Statement Permissions;463
20.4.3;Viewing Permissions;464
20.5;Implementing Security;467
20.6;Summary;468
21;Chapter 17 Using XML and ADO.NET;469
21.1;The Pros and Cons of XML;469
21.2;Understanding the Structure of an XML Document;471
21.2.1;Try It Out: Creating an XML Document in Visual Studio .NET;472
21.3;Understanding Schemas;474
21.3.1;Try It Out: Creating an XML Schema in Visual Studio .NET;474
21.3.2;How It Works;476
21.3.3;Writing Valid XML;477
21.3.4;Try It Out: Validating an XML Document;477
21.4;Using XML with ADO.NET;477
21.4.1;Try It Out: Reading an XML Document into a Data Set;478
21.4.2;How It Works;479
21.4.3;Try It Out: Writing a Data Set to an XML File;479
21.4.4;How It Works;480
21.5;Defining Application Configuration Using XML;481
21.5.1;Try It Out: Creating an Application Configuration;481
21.5.2;How It Works;483
21.6;Summary;485
22;Chapter 18 Handling Exceptions;486
22.1;Handling Exceptions with VB .NET;486
22.1.1;Using the Try Statement;487
22.1.2;Using Class System.Exception;487
22.1.3;Using the throw Statement;488
22.2;Handling ADO.NET Exceptions;488
22.2.1;Try It Out: Handling an ADO.NET Exception (Part 1);488
22.2.2;How It Works;491
22.2.3;Try It Out: Handling an ADO.NET Exception (Part 2);492
22.2.4;How It Works;495
22.3;Handling Database Exceptions;496
22.3.1;Try It Out: Handling a Database Exception (Part 1): RAISERROR;497
22.3.2;How It Works;500
22.3.3;Try It Out: Handling a Database Exception (Part 2): Stored Procedure;501
22.3.4;How It Works;502
22.3.5;Try It Out: Handling a Database Exception (Part 3):Errors Collection;502
22.3.6;How It Works;504
22.4;Summary;505
23;Chapter 19 Using Transactions;506
23.1;When to Use Transactions;507
23.2;Understanding the ACID Properties of a Transaction;508
23.3;How to Code Transactions;508
23.3.1;Coding Transactions in SQL;509
23.3.2;Understanding ADO.NET Transactions;514
23.4;Suggestions for Further Study;519
23.5;Summary;519
24;Chapter 20 Working with ADO.NET Events;520
24.1;Understanding Events;520
24.2;Adding and Removing Event Handlers;521
24.3;Raising and Handling ADO.NET Events;522
24.3.1;Working with Connection Object Events;523
24.3.2;Working with Row Update Events;529
24.3.3;Working with Multiple Handlers;535
24.4;Summary;538
25;Chapter 21 Working with Text and Binary Data;539
25.1;Understanding SQL Server Text and Binary Data Types;539
25.2;Storing Images in a Database;541
25.2.1;Try It Out: Loading Image Binary Data from Files;541
25.2.2;How It Works;545
25.2.3;Rerunning the Program;547
25.2.4;Using a Different SQL Data Type;548
25.3;Retrieving Images from a Database;548
25.3.1;Try It Out: Displaying Stored Images;548
25.3.2;How It Works;552
25.4;Working with Text Data;553
25.4.1;Retrieving Data from Text Columns;558
25.5;Summary;561
26;Chapter 22 Using ADO.NET 2.0;562
26.1;Converting ADO.NET 1.1 Programs to ADO.NET 2.0;562
26.1.1;Try It Out: Rebuilding an ADO.NET 1.1 Project for ADO.NET 2.0;563
26.1.2;How It Works;565
26.2;Loading Data Tables with Data Readers;566
26.2.1;Try It Out: Loading a Data Table with a Data Reader;566
26.2.2;How It Works;567
26.3;Serializing Data More Compactly;568
26.3.1;Try It Out: Serializing a Dataset to a Binary File;568
26.3.2;How It Works;570
26.4;Updating in Batches;572
26.4.1;Try It Out: Specifying Batch Updates;572
26.4.2;How It Works;572
26.5;Summary;573
27;APPENDIX A Creating the SQL Tool Application;574
27.1;Try It Out: Building a Custom Query Tool;574
27.2;How It Works;581
27.3;Summary;583
28;APPENDIX B XML Primer;584
28.1;Understanding XML Documents;584
28.2;Using XML Namespaces;587
28.3;Understanding Well-Formed and Valid XML;588
28.4;Validating XML Documents;589
28.5;Try It Out: Creating an XML Document in Visual Studio .NET;591
28.6;How It Works;596
28.7;Summary;596
29;INDEX;597