Visual Basic with SQL Server

6 Sessions –
18 Hours of Interactive Training

LearnKey’s Visual Basic with SQL Server 2000 for Developers course will teach experienced Visual Basic developers the conceptual understanding and practical skills required for building successful database applications with Visual Basic and SQL Server. Experts Andy Baron and Mary Chipman teach you how to write stored procedures on the server, how to build maintainable middle-tier objects in Visual Basic, and how to marshal data using ADO recordsets or XML. At the conclusion of this course, you will understand how to build scalable and maintainable n-tiered applications that work efficiently with SQL Server.

Prerequisites: Previous programming experience and some knowledge of Visual Basic helpful.

Benefits

Increase your earning potential with technical mastery and proficiency.

Enhance employment opportunities with in-demand IT development knowledge.

LearnKey courses let you move along at your own pace and gain new skills in a useful, productive manner.

Also Available:
· Study Guide

About The Author

Mary Chipman and Andy Baron are Senior Consultants with MCW Technologies, and both are Microsoft MVPs for 5 years running. Specializing in database application development and writing about SQL Server, Visual Basic, the .NET framework, and related technologies, they are co-authors of the Microsoft Access Developer's Guide to SQL Server, published by Sams, and Mary co-authored SQL Server 7.0 in Record Time, published by Sybex. Their combination of experience brings a unique programming and management view to the concepts of SQL development.

Session 1

Section A: Database Applications
· Introduction to Database Applications
· Common Needs
· The Basic Tiers
· Tier Organization
· Marshalling Data Between Tiers
· HTTP/XML Marshalling
· HTTP/XML Marshalling (long)
· "Reach" vs. "Rich"
· N-Tier is Component-Oriented
· Tools for Building N-Tier Applications
· Advantages of N-Tier Component Design

Section B: Data Tier
· Enterprise Manager
· Shark Database
· Stored Procedures
· "Created" Procedures
· Query Analyzer

Section C: VB Middle Tier
· Prerequisites
· Project Group
· Form Helper
· Methods
· Data Access Helper
· Connection String
· ADO & OLEDB
· Tread Lightly
· Handling Errors
· Get Order Details

Section D: Presentation Tier
· Combo Box Wrapper
· Item Data
· No Selection
· Advantages of Combo Wrapper
· Efficient Data Access
· Keeping Track of Forms

Section E: Know Your Server
· The SQL Server Engine
· Data Storage
· Security
· Security Roles
· Create New Login
· Permissions
· Built-in Accounts

Session 2

Section A: Database Design
· Specification
· Normalization
· Relating Tables
· Relationship Diagram
· Enforcing Business Rules

Section B: Understanding Transactions
· Transaction Log
· Implicit & Explicit
· Locking
· ACID Test
· Isolation Levels
· Blocking & Deadlocks
· Avoiding Blocking & Deadlocks

Section C: Intelligent Indexing
· Clustered Index
· Execution Plan
· Index Tuning Wizard
· Index Tuning Script

Section D: Monitoring Performance
· Current Activity
· SQL Profiler
· Trace Properties

Section E: Using Stored Procedures & Functions
· Stored Procedures
· User-Defined Functions
· Combine Procedures & Functions
· Security Issues
· Limitations of Stored Procedures

Section F: Data Retrieval
· Create Procedure
· Procedure Body
· Create Function

Section G: Action Stored Procedures - Inserting Data
· Error Handling
· Alter Procedure
· Handle Parameters
· Validate Input
· Alter Function
· Function Body
· Check Data
· Explicit Transaction

Session 3

Section A: Action Stored Procedures - Updating Data
· Handle Data Conflicts
· Update Procedure
· Validate Input
· Start Transaction
· Create or Delete Stored Procedure
· Error Checking

Section B: VB Data Tools & Debugging
· Data Environment Cons
· Create Data Link
· Data View Window
· Working with Database Diagram
· Create New Table
· Create Stored Procedures
· Procedure Creation
· Debug Stored Procedures
· Using the Debug Tool
· Complex Procedure in Debug Tool
· Fixing Code
· Check Fixes
· Fixing Table in Query Analyzer

Section C: Using ADO with SQL Server
· Create Recordset
· Set Active Connection
· Statelessness
· Source & Open
· Close Recordset
· Error Handling

· Append
· Stored Procedures as Methods
· Security
· UDL
· By-Ref Parameters

Section D: ADO Stored Procedure Add-In
· Register
· Documentation
· Reference
· Insert Stored Procedure
· Add Variables
· Add Payment Method

Session 4

Section A: Interfaces & Co-Classes
· Exploring the Group
· Exploring the Code
· Visual Basic & COM+
· Stateless Method Calls
· Concrete Class
· Using Implements & Private Function
· Project Properties
· Add New Project to Class
· Plan for Scalability

Section B: Using a Database to Support Polymorphism
· Create SQL Table
· SQL Code Explanation
· ADO Recordset

Section C: Versioning By Adding Interfaces
· Add Project/Interface
· Planning for Backwards Compatibility
· Code for New Interface
· Test the Project
· Troubleshoot & Fix the Project

Section D: Extending Combo Box Functionality
· Combo Wrapper Class
· Using the Combo Box
· Combo Box Events
· Code for Combo Box
· Code for List in Box
· VB & Windows API
· ItemData vs. ListIndex
· Code to Create Object
· Recordsets
· Bind Records to Combo Box
· Combo Wrapper Code
· Create Custom Event

Session 5

Section A: Using the Combo Wrapper
· Forms & Hidden Controls
· Code for Adding Data to Box
· Enumerating
· Store Data Locally with XML File
· Recordset Types

Visual Basic with SQL Server continued

Section B: Hierarchical FlexGrid
· Load Order Button Code
· Concurrency ID
· Handling Data Changes
· Setting Up the FlexGrid
· Working in the FlexGrid

Section C: Searching for Records
· Search in Shark
· Design View
· Stored Procedure
· Procedure Code
· Dynamic SQL
· Table Sort
· Handling Parameters

Section D: Overlaying Controls on MSHFlexGrid
· Working in the Grid
· Saving Changes
· "Dirty" Forms
· Error Handling
· Middle Tier Object
· Build String from Order Details
· Reload Form
· Keeping the Display Straight
· IsNew & IsDirty
· Captions (States)
· Adjust Display
· Fix Up Grid
· Alignment
· Grid
· Tab Router
· Tab Router Code

Session 6

Section A: Overview of COM+
· Windows NT4
· Windows 2000
· Security Concepts
· User Account
· Securing Objects
· Creating Middle Tier Objects
· In-Process vs. Out-of-Process
· Testing

Section B: Moving the Middle Tier to COM+
· Component Services Manager
· Install Wizard
· Components
· Configuring COM+
· Roles
· Role Security
· Activate Role Security
· Add Users & Groups

Section C: Testing COM+ Security
· Project References
· Test as Administrator
· Test as Mary

Section D: Client Deployment
· Export Wizard
· VB Package & Deploy Wizard
· Create Client Package

Section E: HTTP/XML
· HTTP
· XML
· Defining XML Grammars
· Using a Local Web Page
· Example of Attribute XML
· Example of Object XML
· Advantages of HTTP/XML
· Clients & Configuration
· Stateless/Load Balancing

Section F: HTTP/XML Technology Options
· Active Server Pages
· MSXML
· SOAP
· SQL 2000
· Annotated Schemas

Section G: Building an XML-Based Middle Tier
· SQL Support for XML
· Setup Virtual Directory
· Retrieve Data Over HTTP
· FOR XML AUTO/RAW
· XML Template for SQL Server
· Using Parameters
· XSL Stylesheet & XML
· VB Using XML