>
VISUAL BASIC 2010
Coding Briefs
DATA ACCESS
KEVIN HOUGH
VISUAL BASIC 2010
CODING BRIEFS
DATA ACCESS
© 2011 Runtime Publishing, LLC
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any other means without the expressed written permission of Runtime Publishing, LLC, except for brief quotations or excerpts.
For information on obtaining written permission for the use of material from this publication, please submit a written request to:
Runtime Publishing, LLC
14902 Preston Rd, Suite 404-505
Dallas, TX 75254-5434
The author and the publisher have made every effort in the publication of this book to ensure the accuracy of the content. The content in this book is sold without warranty, either expressed or implied.
Published in eBook format by Runtime Publishing, LLC
Converted by http://www.eBookIt.com
ISBN-13: 978-0-9836-1511-8
To my wife, Cheryl, for all of her support, encouragement, and love.
About the Author
Kevin Hough heads the Project Management Office and the Software Engineering department at Trans-Trade, Inc in Dallas/Ft. Worth.
Kevin has designed and developed large scale commercial applications and Web sites for corporations including EXXON, Pennzoil, Compaq, Shell, and Texaco, as well as for leading universities, including Harvard, Stanford, and Rice. You can reach Kevin at [email protected].
The Audience for Coding Briefs
Visual Basic 2010 Coding Briefs Data Access is written for intermediate level developers. To get the most out of this book, the developer should be familiar with the following tasks:
•Creating solutions in Visual Studio 2010
•Developing code in Visual Basic 2010
•Writing If/Then/Else statements
•Creating and calling Functions and Procedures
•Working with My.Settings to store Strings
•Debugging code
•Working with Stored Procedures
Obtaining the Code
The source code is included in the project files that can be downloaded from www.runtimepublishing.com. Follow these steps to get the code up and running:
1. Follow the steps in the section, Quick Start, to register and unlock the code for this Coding Brief
2. Enter the Unlock Code that is located in the section Running the Windows Sample Application later in this brief
3. Open the projects in Visual Studio 2010.
The code for this brief includes the following projects:
| Project | Description |
| CBDaraAccess | Data Access framework |
| CodingBriefsVolume1 | Windows WinForms application |
| CodingBriefsVolume1Web | ASP .Net based Web application |
VISUAL BASIC 2010
Coding Briefs
DATA ACCESS
In today’s high-tech, information driven world, the security of personal data is vital. We have all heard the horror stories of personal data being compromised from companies, both large and small.
The data access framework that we will develop in Coding Briefs will allow us to create datasets, execute queries, and select data in a secure, managed process.
This framework will provide a solid foundation for managing secure data access through stored procedures, allowing us to select, insert, update, and delete records from a SQL Server database. We will have access to parameterized queries and will develop an automated process for discovering stored procedure parameters.
The major areas that we will address in this brief are as follows:
•Discussing the Data Access Framework (DAL)
•Importing Libraries and Declaring Values
•Managing the Database Connections
•Selecting Records
•Manipulating Data
•Preparing the Select and Execute Methods
•Working with Parameters
•Testing the Data Access Framework
And, as a bonus, two complete sample applications have been added; one for Windows, and one in ASP .Net, that will test all of the features on the data access framework.
Defining the Data Access Framework
The data access framework in this brief needs to be able to store and retrieve all of the data that is necessary for our data centric applications to perform as they are designed to. In order to accomplish this data access task, we will rely on the following tools and practices:
•All of the data for Coding Briefs will be stored in a SQL Server 2008 database
•All of the business rules will be managed in a series of Business Object classes
•We will have a central data access layer (DAL) that can be called from any form, class, or module in Windows and ASPX applications
•All database interaction will employ stored procedures
With the Microsoft tools that are available to us, and a little ingenuity, we can design and develop a very robust Data Access Framework that can be used, not only for Coding Briefs, but for most other applications that we develop in the future
In the next section, we design the data access framework.
Designing the Data Access Framework
For a data access framework to be useful and effective, it must be easy for the developer to use, and flexible enough to accommodate all types of data access requests. The data access framework in Coding Briefs solves both those important issues.
Figure 1: Basic Data Access Flow, as shown below, depicts the basic flow of the data access framework.