Visual Basic 2010 Coding Briefs Data Access. Kevin Ph.D. 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:

      VISUAL BASIC ²⁰¹⁰

      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.